Cold-Backup Strategy for a Noarchivelog Mode Oracle Database 12C

You perform a user-managed cold backup by copying files after the database has been shut down. This type of backup is also known as an offline backup. Your Oracle database 12C can be in either noarchivelog mode or archivelog mode when you make a cold backup.

DBAs tend to think of a cold backup as being synonymous with a backup of a database in noarchivelog mode. That isn’t correct. You can make a cold backup of a database in archivelog mode, and that’s a backup strategy that many shops employ. The differences between a cold backup with the database in noarchivelog mode and in archivelog mode are detailed in the following sections.

Making a Cold Backup of a Noarchivelog Mode Database

One main reason for making a cold backup of a Oracle database 12C in noarchivelog mode is to give you a way to restore a database back to a point in time in the past. You should use this type of backup only if you don’t need to recover transactions that occurred after the backup. This type of backup and recovery strategy is acceptable only if your business requirements allow for the loss of data and downtime. Rarely would you ever implement this type of backup and recovery solution for a production business database.

Having said that, there are some good reasons to implement this type of backup. One common use is to make a cold backup of a development/test/training database and periodically reset the database back to the baseline. This gives you a way to restart a performance test or a training session with the same point-in-time snapshot of the database.

The example in this section shows you how to make a backup of every critical file in your database: all control files, data files, temporary data files, and online redo log files. With this type of backup, you can easily restore your database back to the point in time when the backup was made. The main advantages of this approach are that it’s conceptually simple and easy to implement. Here are the steps required for a cold backup of a database in noarchivelog mode:

1. Determine where to copy the backup files and how much space is required.
2. Identify the locations and names of the database files to copy.
3. Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause.
4. Copy the files (identified in step 2) to the backup location (determined in step 1).
5. Restart your Oracle database 12C.

The following sections elaborate on these steps.

Step 1. Determine Where to Copy the Backup Files and How Much Space Is Required

Ideally, the backup location should be on a set of disks separate from your live data files location. However, in many shops, you may not have a choice and may be told which mount points are to be used by the database. For this example the backup location is the directory /u01/cbackup/O12C. To get a rough idea of how much space you need to store one copy of the backups, you can run this query:

select sum(sum_bytes)/1024/1024 m_bytes

from(

select sum(bytes) sum_bytes from v$datafile

union

select sum(bytes) sum_bytes from v$tempfile

union

select (sum(bytes) * members) sum_bytes from v$log

group by members);

You can verify how much operating disk space is available with the Linux/Unix df (disk free) command. Make sure that the amount of disk space available at the OS is greater than the sum returned from the prior query:

$ df -h

Step 2. Identify the Locations and Names of the Database Files to Copy

Run this query to list the names (and paths) of the files that are included in a cold backup of a noarchivelog mode database:

select name from v$datafile

union

select name from v$controlfile

union

select name from v$tempfile

union

select member from v$logfile;

Step 3. Shut Down the Database

Connect to your database as the SYS (or as a SYSDBA-privileged user), and shut down your database, using IMMEDIATE, TRANSACTIONAL, or NORMAL. In almost every situation, using IMMEDIATE is the preferred method. This mode disconnects users, rolls back incomplete transactions, and shuts down the database:

$ sqlplus / as sysdba

SQL> shutdown immediate;

Step 4. Create Backup Copies of the Files

For every file identified in step 2, use an OS utility to copy the files to a backup directory (identified in step 1). In this simple example all the data files, control files, temporary database files, and online redo logs are in the same directory. In production environments, you’ll most likely have files spread out in several different directories. This example uses the Linux/Unix cp command to copy the database files from /u01/dbfile/O12C to the /u01/cbackup/O12C directory:

$ cp /u01/dbfile/O12C/*.*  /u01/cbackup/O12C

Step 5. Restart Your Database

After all the files are copied, you can start up your database:

$ sqlplus / as sysdba

SQL> startup;

Restoring a Cold Backup in Noarchivelog Mode with Online Redo Logs

The next example explains how to restore from a cold backup of a database in noarchivelog mode. If you included the online redo logs as part of the cold backup, you can include them when you restore the files. Here are the steps involved in this procedure:

1. Shut down the instance.
2. Copy the data files, online redo logs, temporary files, and control files back from the backup to the live database data file locations.
3. Start up your database.

These steps are detailed in the following sections.

Step 1. Shut Down the Instance

Shut down the instance, if it’s running. In this scenario it doesn’t matter how you shut down the Oracle database 12C, because you’re restoring back to a point in time (with no recovery of transactions). Any files in the live database directory locations are overwritten when the backup files are copied back. If your instance is running, you can abruptly abort it. As a SYSDBA-privileged user, do the following:

$ sqlplus / as sysdba

SQL> shutdown abort;

Step 2. Copy the Files Back from the Backup

This step does the reverse of the backup: you’re copying files from the backup location to the live database file locations. In this example all the backup files are located in the /u01/cbackup/O12C directory, and all files are being copied to the /u01/dbfile/O12C directory:

$ cp /u01/cbackup/O12C/*.*  /u01/dbfile/O12C

Step 3. Start Up the Database

Connect to your database as SYS (or a user that has SYSDBA privileges), and start up your database:

$ sqlplus / as sysdba

SQL> startup;

After you finish these steps, you should have an exact copy of your database as it was when you made the cold backup. It’s as if you set your database back to the point in time when you made the backup.

Restoring a Cold Backup in Noarchivelog Mode Without Online Redo Logs

As mentioned earlier, you don’t ever need the online redo logs when restoring from a cold backup. If you made a cold backup of your database in noarchivelog mode and didn’t include the online redo logs as part of the backup, the steps to restore are nearly identical to the steps in the previous section. The main difference is that the last step requires you to open your database, using the OPEN RESETLOGS clause. Here are the steps:

1. Shut down the instance.
2. Copy the control files and data files back from the backup.
3. Start up the database in mount mode.
4. Open the database with the OPEN RESETLOGS clause.

Step 1. Shut Down the Instance

Shut down the instance, if it’s running. In this scenario it doesn’t matter how you shut down the Oracle database 12C, because you’re restoring back to a point in time. Any files in the live database directory locations are overwritten when the backups are copied. If your instance is running, you can abruptly abort it. As a SYSDBA-privileged user, do the following:

$ sqlplus / as sysdba

SQL> shutdown abort;

Step 2. Copy the Files Back from the Backup

Copy the control files and data files from the backup location to the live data file locations:

$ cp <backup directory>/*.*  <live database file directory>

Step 3. Start Up the Database in Mount Mode

Connect to your database as SYS or a user with SYSDBA privileges, and start the database in mount mode:

$ sqlplus / as sysdba

SQL> startup mount

Step 4. Open the Database with the OPEN RESETLOGS Clause

Open your database for use with the OPEN RESETLOGS clause:

SQL> alter database open resetlogs;

If you see the Database altered message, the command was successful. However, you may see this error:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

In this case, issue the following command:

SQL> recover database until cancel;

You should see this message:

Media recovery complete.

Now, attempt to open your database with the OPEN RESETLOGS clause:

SQL> alter database open resetlogs;

This statement instructs Oracle to recreate the online redo logs. Oracle uses information in the control file for the placement, name, and size of the redo logs. If there are old online redo log files in those locations, they’re overwritten.

If you’re monitoring your alert.log throughout this process, you may see ORA-00312 and ORA-00313. This means that Oracle can’t find the online redo log files; this is okay, because these files aren’t physically available until they’re recreated by the OPEN RESETLOGS command.

Scripting a Cold Backup and Restore

It’s instructional to view how to script a cold backup. The basic idea is to dynamically query the data dictionary to determine the locations and names of the files to be backed up. This is preferable to hard-coding the directory locations and file names in a script. The dynamic generation of a script is less prone to errors and surprises (e.g., the addition of new data files to a database but not to an old, hard-coded backup script).

The first script in this section makes a cold backup of a database. Before you use the cold backup script, you need to modify these variables in the script to match your database environment:

• ORACLE_SID
• ORACLE_HOME
• cbdir

The cbdir variable specifies the name of the backup-directory location. The script creates a file named coldback.sql, which is executed from SQL*Plus to initiate a cold backup of the database:

#!/bin/bash

ORACLE_SID=O12C

ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1

PATH=$PATH:$ORACLE_HOME/bin

#

sqlplus -s <<EOF

/ as sysdba

set head off pages0 lines 132 verify off feed off trimsp on

define cbdir=/u01/cbackup/O12C

spo coldback.sql

select 'shutdown immediate;' from dual;

select '!cp ' || name   || ' ' || '&&cbdir'   from v\$datafile;

select '!cp ' || name   || ' ' || '&&cbdir'   from v\$tempfile;

select '!cp ' || member || ' ' || '&&cbdir' from v\$logfile;

select '!cp ' || name   || ' ' || '&&cbdir'   from v\$controlfile;

select 'startup;' from dual;

spo off;

@@coldback.sql

EOF

exit 0

This file generates commands that are to be executed from an SQL*Plus script to make a cold backup of a Oracle database. You place an exclamation mark (!) in front of the Unix cp command to instruct SQL*Plus to host out to the OS to run the cp command. You also place a backward slash (\) in front of each dollar sign ($) when referencing v$ data dictionary views; this is required in a Linux/Unix shell script. The \ escapes the $ and tells the shell script not to treat the $ as a special character (the $ normally signifies a shell variable).

After you run this script, here is a sample of the copy commands written to the coldback.sql script:

shutdown immediate;

!cp /u01/dbfile/O12C/system01.dbf /u01/cbackup/O12C

!cp /u01/dbfile/O12C/sysaux01.dbf /u01/cbackup/O12C

!cp /u01/dbfile/O12C/undotbs01.dbf /u01/cbackup/O12C

!cp /u01/dbfile/O12C/users01.dbf /u01/cbackup/O12C

!cp /u01/dbfile/O12C/tools01.dbf /u01/cbackup/O12C

!cp /u01/dbfile/O12C/temp01.dbf /u01/cbackup/O12C

!cp /u01/oraredo/O12C/redo02a.rdo /u01/cbackup/O12C

!cp /u02/oraredo/O12C/redo02b.rdo /u01/cbackup/O12C

!cp /u01/oraredo/O12C/redo01a.rdo /u01/cbackup/O12C

!cp /u02/oraredo/O12C/redo01b.rdo /u01/cbackup/O12C

!cp /u01/oraredo/O12C/redo03a.rdo /u01/cbackup/O12C

!cp /u02/oraredo/O12C/redo03b.rdo /u01/cbackup/O12C

!cp /u01/dbfile/O12C/control01.ctl /u01/cbackup/O12C

!cp /u01/dbfile/O12C/control02.ctl /u01/cbackup/O12C

startup;

While you make a cold backup, you should also generate a script that provides the commands to copy data files, temp files, log files, and control files back to their original locations. You can use this script to restore from the cold backup. The next script in this section dynamically creates a coldrest.sql script that copies files from the backup location to the original data file locations. You need to modify this script in the same manner that you modified the cold backup script (i.e., change the ORACLE_SID, ORACLE_HOME, and cbdir variables to match your environment):

#!/bin/bash

ORACLE_SID=O12C

ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1

PATH=$PATH:$ORACLE_HOME/bin

#

sqlplus -s <<EOF

/ as sysdba

set head off pages0 lines 132 verify off feed off trimsp on

define cbdir=/u01/cbackup/O12C

define dbname=$ORACLE_SID

spo coldrest.sql

select 'shutdown abort;' from dual;

select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) ||

       ' ' || name   from v\$datafile;

select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) ||

       ' ' || name   from v\$tempfile;

select '!cp ' || '&&cbdir/' || substr(member, instr(member,'/',-1,1)+1) ||

       ' ' || member from v\$logfile;

select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) ||

       ' ' || name   from v\$controlfile;

select 'startup;' from dual;

spo off;

EOF

exit 0

This script creates a script, named coldrest.sql, that generates the copy commands to restore your data files, temp files, log files, and control files back to their original locations. After you run this shell script, here is a snippet of the code in the coldrest.sql file:

shutdown abort;

!cp /u01/cbackup/O12C/system01.dbf /u01/dbfile/O12C/system01.dbf

!cp /u01/cbackup/O12C/sysaux01.dbf /u01/dbfile/O12C/sysaux01.dbf

!cp /u01/cbackup/O12C/undotbs01.dbf /u01/dbfile/O12C/undotbs01.dbf

!cp /u01/cbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf

!cp /u01/cbackup/O12C/tools01.dbf /u01/dbfile/O12C/tools01.dbf

...

!cp /u01/cbackup/O12C/redo03b.rdo /u02/oraredo/O12C/redo03b.rdo

!cp /u01/cbackup/O12C/control01.ctl /u01/dbfile/O12C/control01.ctl

!cp /u01/cbackup/O12C/control02.ctl /u01/dbfile/O12C/control02.ctl

startup;

If you need to restore from a cold backup using this script, log in to SQL*Plus as SYS, and execute the script:

$ sqlplus / as sysdba

SQL> @coldrest.sql

Вас заинтересует / Intresting for you:

Managing Oracle Database 12C O... 6768 views Андрей Волков Sat, 29 Feb 2020, 10:18:11

Implementing a Hot Backup Stra... 9509 views Андрей Волков Sat, 29 Feb 2020, 10:16:34

Matching Oracle 12c Table Type... 2501 views Ded Sat, 18 Aug 2018, 16:59:35

Oracle Database 12C Files in S... 1510 views Андрей Волков Sat, 29 Feb 2020, 10:19:02