TO BACKUP A CONTROL FILE TO A TRACE FILE

This will generate a script to recreate a control file. The script can also be useful as reference for directory paths of all your database files when creating directories in your target restore server.

Note that the script does not contain any backup records if all your backups are done in NOCATALOG. In this case, you MUST backup the control file as binary file and use the generated binary file for your restore.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/disk1/backup/controlDBNAME.trc';

TO BACKUP A CONTROL FILE AS BINARY FILE

From source db,

RMAN> backup tag full_db_ctlfile format '/home/oracle/ctlDBNAME_bak.ctl' current controlfile;

Then, copy the above file to target server for reference of restore.

TO BACKUP A NOARCHIVELOG DATABASE

SET ORACLE_HOME=/ORACLE/APP/PRODUCT/10.2.0/

SET ORACLE_SID=ORASID123

** Shutdown ORASID123 database then startup in mount mode.

SQL> connect / as sysdba

SQL> shutdown immediate

SQL> startup mount

rman target /

RMAN> spool log to /home/oracle/ORASID123_full_backup.log

RMAN> show all;

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

RMAN> set encryption identified by 'SuperSecret123!' only;

RMAN> run {

allocate channel ch1 type disk;

allocate channel ch2 type disk;

allocate channel ch3 type disk;

allocate channel ch4 type disk;

allocate channel ch5 type disk;

allocate channel ch6 type disk;

allocate channel ch7 type disk;

allocate channel ch8 type disk;

backup as backupset filesperset 10 tag full_db_backup database format '/disk1/backup/full_db_ORASID123_t%t_s%s_p%p.rman';

backup tag full_db_ctlfile format '/disk1/backup/ctlORASID123_bak.ctl' current controlfile;

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

release channel ch5;

release channel ch6;

release channel ch7;

release channel ch8;

}

RMAN> sql 'alter database backup controlfile to trace';

copy init.ora to /disk1/backup

copy orapw file to /disk1/backup

copy trace file to /disk1/backup

RMAN> spool log to /home/oracle/ORASID123_backup_list.log

RMAN> list backup;

RMAN> list backup by file;

Copy all resulting logs (ORASID123_full_backup.log, ORASID123_backup_list.log) to /disk1/backup

Or, send email of all logs. This helps on giving the user an idea of what the backup is.

TO BACKUP AN ARCHIVELOG DATABASE

NOTE: Hot backup is done with OPEN mode. You can also use this in MOUNT mode but that would become cold backup.

The “plus archivelog” would force Rman to backup the archive logs before and after the datafile backup.

A sample encryption is set below. Keep in mind that if you forgot the password, there is no way to decrypt the

backup files even if you go to Oracle for help.

At the later part, backup of current control file is required because it now has the backup history entries

which is important when restoring to another server.

rman target /

RMAN> spool log to /home/oracle/ORASID123_full_backup.log

RMAN> show all;

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

RMAN> set encryption identified by 'SuperSecret123!' only;

RMAN> run {

allocate channel ch1 type disk;

allocate channel ch2 type disk;

backup as backupset filesperset 10 tag full_db_backup format '/disk1/backup/full_db_ORASID123_t%t_s%s_p%p.rman' database plus archivelog;

backup tag full_db_ctlfile format '/disk1/backup/ctlORASID123_bak.ctl' current controlfile;

release channel ch1;

release channel ch2;

}

RMAN> sql 'alter database backup controlfile to trace';

copy init.ora to /disk1/backup

copy orapw file to /disk1/backup

copy trace file to /disk1/backup

RMAN> spool log to /home/oracle/ORASID123_backup_list.log

RMAN> list backup;

RMAN> list backup by file;

Copy all resulting logs (ORASID123_full_backup.log, ORASID123_backup_list.log) to /disk1/backup

Or, send email of all logs. This helps on giving the user an idea of what the backup is.

RESTORE PREP TASKS

1. Set your init.ora with the following important parameters with the correct info based on the backup control file trace script generated from the source:

      db_name

      control_files

      db_block_size

      The rest are optional.

2. For backups done on disk at the source and performing a restore at a different server, make sure the backup drive (NFS, or transportable storage) is mounted with the same mount point name. Or, you may copy all the backup files to the target restore server into the directory having the same backup path as your source.

ex. /Disk1/backup of the source rman backup above, should exist in the target restore server and should contain the backup files.

3. Create the database directories for the files to be restored (/redo,/control,/undo,/data,/index,/lob,/arch, etc.). Use the resulting control file backup trace file as your reference.

4. If necessary, copy the available archive logs from your source database onto your /arch directory.  

TO RESTORE A NOARCHIVELOG MODE DATABASE

USING BACKUP CONTROL FILE

RMAN> connect target /

RMAN> startup nomount;

RMAN> restore controlfile from '/disk1/backup/ctlORASID123_bak.ctl';  

RMAN> alter database mount;

RMAN> list backup;   --- check the backup and verify the location of files matches the mounted backup drive.

RMAN> restore database;   --- you may alternatively run this command in a script

RMAN> alter database open resetlogs;

USING CURRENT CONTROL FILE

RMAN> connect target /

RMAN> startup mount;

RMAN> restore database;   --- you may alternatively run this command in a script

RMAN> alter database open resetlogs;

TO RESTORE AN ARCHIVELOG MODE DATABASE

INCOMPLETE RECOVERY  

NOTE: As an incomplete recovery, it is required that archive logs are part of your backup set or, they

      exist in the log_archive_dest path. RMan will start looking for it in the _dest path and if not found

      will check the backup set to fulfill the recovery command.

      Read the Restore Prep Tasks before proceeding.

STEP 1 CONTROL FILE OPTIONS

If current control file is present,

RMAN> connect target /

--- Startup options:

RMAN> startup mount;    --- use this if current control file is present

If current control file is NOT present or, is missing

RMAN> connect target /

RMAN> startup nomount;  --- optional, if control file is missing and need to be restored

RMAN> restore controlfile from '/disk1/backup/ctlORASID123_bak.ctl';  --- optional, do this only if the control file is missing (i.e. restoring to another server)

RMAN> alter database mount;

STEP 2 VERIFY BACKUP FILES

RMAN> list backup;   --- check the backup and verify the location of files matches the mounted backup drive.

STEP 3 RESTORE THE DATABASE

RMAN> SET DECRYPTION IDENTIFIED BY 'SuperSecret123!'; --- do this only for encrypted backup files

RMAN> restore database;   --- you may alternatively run this command in a script, then later, manually run the recovery

STEP 4 RECOVER THE DATABASE

Note: before running recover below, make sure archive logs are copied onto the appropriate arc directories.

For SCN:

Check V$LOG_HISTORY of your source db and pick your scn#

RMAN> recover database until scn 1234;

For time:

RMAN>  recover database until time “to_date(’21-JUN-2013 18:00:00’,’DD-MON-YYYY HH24:MI:SS’);

For Cancel based:

Note: If the restore database has been scripted, you have to execute the recovery commands on the sqlplus tool, otherwise run the recover commands on the same RMAN prompt.

SQL> connect / as sysdba

--- Recover options:

SQL> recover database until cancel;   --- use this if you had not restored the control file and is using current control file

Or,

SQL> recover database until cancel using BACKUP CONTROLFILE; --- use this if you had restored a control file from backup

SQL> alter database open resetlogs; ––- use this only if recovery from backup control file

Now, let’s check if everything looks good.

SQL> select count(*) from v$recover_file;

  COUNT(*)

----------

         0

SQL> select count(*) from dba_temp_files;

  COUNT(*)

----------

         2

SQL>

         

Add temp files if none existent, example:

ALTER TABLESPACE TEMP ADD TEMPFILE '/disk1/oradata/temp.dbf'

     SIZE 2048M REUSE AUTOEXTEND OFF;

SAMPLE RESTORE DATABASE SCRIPT

# FILENAME: restore_ORA10DB3.sh

# nohup ./restore_ORA10DB3.sh > restore_ORA10DB3_out.log 2>&1 &

#

LOG_DATE=`date +%Y%m%d%H%M%S`

ORACLE_BASE=/opt/app/oracle ; export ORACLE_BASE

ORACLE_SID=ORA10DB3 ; export ORACLE_SID

ORACLE_HOME=/opt/app/oracle/product/10.2.0 ; export ORACLE_HOME

PATH=$PATH:$ORACLE_HOME/bin ; export PATH

rman target sys/mypass log /home/oracle/RestoreFor_${ORACLE_SID}_${LOG_DATE}.log << EOF

SET DECRYPTION IDENTIFIED BY 'SuperSecret123!';

run {

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

restore database;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

EOF

RMAN CATALOG DATABASE

STEP 1. Create the catalog user in a database dedicated for RMan use only. You can have multiple catalog users to differentiate from one application database backup to another.

create user RCAT_TEST

  identified by RCATTEST123

  default tablespace RMAN_DATA_TEST

  temporary tablespace TEMP

  quota unlimited on RMAN_DATA_TEST

  profile APP_OWNER;  

grant connect, recovery_catalog_owner to RCAT_TEST;

STEP 2. Create the catalog repository.

>>>> create_rmancat.sh

export ORACLE_BASE=/ocat/app/rcatdb

export ORACLE_SID=RCATDB

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db

$ORACLE_HOME/bin/rman <<-EOF

connect catalog RCAT_TEST/RCATTEST123@RCATDB;

create catalog;

quit;

EOF

STEP 3. Register the target database.

The purpose of registering the database in the catalog is to have a record of existence as a target in the catalog.

The database is uniquely identified in the catalog by its DBID. You can register more than one database in a catalog, and you can also register the same database to multiple catalogs.

$ORACLE_HOME/bin/rman target / catalog RCAT_TEST/RCATTEST123@RCATDB;

RMAN> startup mount

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

By checking the db name and db ID from a list of incarnation, you can tell if the database was registered.

$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 11 12:46:39 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORA11DB2 (DBID=3101831220)

RMAN> connect catalog RCAT_TEST/RCATTEST123@RCATDB

connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       2       ORA11DB2 3101831220       CURRENT 1          24-FEB-14

RMAN>

SAMPLE HOT BACKUP SCRIPT

#!/bin/ksh

# Description: This is a sample rman hotbackup using Symantec Netbackup parameters.

# To run:  

# nohup ./hotbackup_level0_nb.sh &

#

LOG_DATE=`date +%Y%m%d%H%M`

ORACLE_BASE=/orabin/app/oracle

ORACLE_SID=ORA11DB2

ORACLE_HOME=/orabin/app/oracle/product/11.2.0.4/db

PATH=$PATH:$ORACLE_HOME/bin ; export PATH

RMAN=$ORACLE_HOME/bin/rman

TARGET_CONNECT_STR=sys/my_sys_password

CATALOG_CONNECT_STR=RCAT_TEST/my_catalog_password@RCATDB

NB_ORA_SCRIPTS=${ORACLE_BASE}/admin/${ORACLE_SID}/scripts

RMAN_LOG_FILE=${NB_ORA_SCRIPTS}/log/HotBackup_L0_For_${ORACLE_SID}_${LOG_DATE}.log

CTLFILE_BACKUP=$ORACLE_BASE/admin/$ORACLE_SID/backup/control${ORACLE_SID}_${LOG_DATE}.bak

#-- check if a directory for RMan log exist

if [ ! -d $NB_ORA_SCRIPTS/log ];

then

  mkdir -p $NB_ORA_SCRIPTS/log

fi

#-- check if a directory for control file backup exist

if [ ! -d $ORACLE_BASE/admin/$ORACLE_SID/backup ];

then

  mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/backup

fi

#-- get the current user id and server name

CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`

HNAME=`hostname`

  

#-- Initialize the log file.

 

echo >> $RMAN_LOG_FILE

chmod 666 $RMAN_LOG_FILE

echo Script: $0 >> $RMAN_LOG_FILE

echo Hostname: $HNAME >> $RMAN_LOG_FILE

echo OS User: $CUSER >> $RMAN_LOG_FILE

echo ==== started on `date` ==== >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

#-- Build the command string

CMD_STR="

ORACLE_HOME=$ORACLE_HOME

export ORACLE_HOME

ORACLE_SID=$ORACLE_SID

export ORACLE_SID

$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msglog $RMAN_LOG_FILE append << EOF

show all;

configure device type 'SBT_TAPE' parallelism 2 backup type to BACKUPSET;

configure channel device type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=mydbserver.domain.com, NB_ORA_SERV=nbmaster.domain.com, NB_ORA_POLICY=oracle_hotdb_backup, NB_ORA_SCHED=weekly)';

show all;

run {

  allocate channel ch0 type 'SBT_TAPE';

  allocate channel ch1 type 'SBT_TAPE';

  #-- Backup archive logs before db backup

  backup as compressed backupset

  tag ${ORACLE_SID}_beforedbbak_arc_logs

  filesperset 20

  format 'arclog_%s_%p_%t'

  (archivelog all skip inaccessible delete input);

  change archivelog all validate;

  #-- Hot database level 0 backup  

  backup as compressed backupset

    incremental level 0

    tag ${ORACLE_SID}_hot_db_level0

    filesperset 4

    format 'hotdb_%s_%p_%t'

    database;

  #-- Backup archive logs after db backup  

  sql 'alter system archive log current';

  backup as compressed backupset

  tag ${ORACLE_SID}_afterdbbak_arc_logs

  filesperset 20

  format 'arclog_%s_%p_%t'

  (archivelog all skip inaccessible delete input);

  change archivelog all validate;

  release channel ch0;

  release channel ch1;

  #-- Backup the control file to tape with the latest backup info

  allocate channel ch2 type 'SBT_TAPE';

  backup

    tag ${ORACLE_SID}_ctlfile

    format 'control_%s_%p_%t'

    current controlfile;

  release channel ch2;

  #-- Backup the control file to disk with the latest backup info as 2nd copy

  allocate channel ch3 type DISK;

  backup tag ${ORACLE_SID}_ctlfile_disk format '$CTLFILE_BACKUP' current controlfile;

  release channel ch3;

}

sql 'alter database backup controlfile to trace';

EOF

"

if [ "$CUSER" = "root" ]

then

    su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE

    RSTAT=$?

else

    /bin/ksh -c "$CMD_STR" >> $RMAN_LOG_FILE

    RSTAT=$?

fi

if [ "$RSTAT" = "0" ]

then

    LOGMSG="ended successfully"

else

    LOGMSG="ended in error"   

fi

 

echo >> $RMAN_LOG_FILE

echo Script $0 >> $RMAN_LOG_FILE

echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE

 

#if [ $RSTAT -ne 0 ]

#then

#cat $RMAN_LOG_FILE | /bin/mailx -s "$HNAME: $ORACLE_SID database backup failed" dba_oncall@domain.com

#fi;

exit $RSTAT

RMAN BACKUP MAINTENANCE

VALIDATING DATABASE FILES

Use Backup…Validate command to verify the database bound for backup is in good state by checking that the database files are found in the correct locations and reads through all the database files checking for block corruptions. The command does not produce a backup set or image copies.

If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions.

Example, validate that all database files and archived logs can be backed up:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

VALIDATING YOUR BACKUPS

Here’s the command to validate the restore process using the current backup set:

UNIX> rman target /  

allocate channel d1 type disk;

allocate channel d2 type disk;

restore database validate;

release channel d1;

release channel d2;

If you do not see an RMAN error that means RMAN confirms that it can use the current backup to successfully restore a database.

NOTE: Please do not omit the “validate” word in the command as that is the key to simulate the restore otherwise it would attempt to restore the real thing.

Another alternative is to check the integrity of the backup set without restore simulation using VALIDATE BACKUPSET:

Example:

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

31      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_BEFOREDBBAK_ARC_LOGS

32      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_BEFOREDBBAK_ARC_LOGS

33      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_BEFOREDBBAK_ARC_LOGS

34      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_BEFOREDBBAK_ARC_LOGS

35      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_BEFOREDBBAK_ARC_LOGS

36      B  0  A DISK        27-OCT-14       1       1       YES        ORA10DG1_HOT_DB_LEVEL0

37      B  0  A DISK        27-OCT-14       1       1       YES        ORA10DG1_HOT_DB_LEVEL0

38      B  0  A DISK        27-OCT-14       1       1       YES        ORA10DG1_HOT_DB_LEVEL0

39      B  0  A DISK        27-OCT-14       1       1       YES        ORA10DG1_HOT_DB_LEVEL0

40      B  0  A DISK        27-OCT-14       1       1       YES        ORA10DG1_HOT_DB_LEVEL0

41      B  0  A DISK        27-OCT-14       1       1       YES        ORA10DG1_HOT_DB_LEVEL0

42      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_AFTERDBBAK_ARC_LOGS

43      B  A  A DISK        27-OCT-14       1       1       YES        ORA10DG1_AFTERDBBAK_ARC_LOGS

44      B  F  A DISK        27-OCT-14       1       1       NO         ORA10DG1_CTLFILE_DISK

RMAN>

RMAN> VALIDATE BACKUPSET 36, 37, 38, 39, 40, 41, 42, 43, 44;

If the validate command exits with no error code, that means your backupset is good.

BACKUP CLEANUP

You may have manually deleted the old physical files of your backup for various reasons without the rman catalog knowing it.

In this case, you need to remove the backupset entries related to these deleted files from your catalog.

To cleanup the backup catalog (default: control file), you need to run a crosscheck for rman to determine if the backup files still exist. Then run a delete expired backup.

The crosscheck marks the backupset as "EXPIRED" if it couldn't find the files.

Example:

UNIX>  rman target /

RMAN>  allocate channel for maintenance device type disk;

RMAN> crosscheck backup;

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

RMAN> delete noprompt expired backup;

RMAN> release channel;

ALLOCATE CHANNEL FOR MAINTENANCE

The "allocate channel for maintenance" command is required when running crosscheck, delete and change commands. This is necessary if you didn't configure such channel in the rman.

You should always release the maintenance channel once done.

For special backup storage devices, you should specify the PARMS string for your device type.

Examples:

RMAN> SHOW DEFAULT DEVICE TYPE;

 

RMAN configuration parameters for database with db_unique_name ORA10DG1 are:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

 

RMAN> SHOW CHANNEL;

 

RMAN configuration parameters for database with db_unique_name ORA10DG1 are:

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/usr/openv/netbackup/lib/libobk.so64.1, ENV=(NB_ORA_CLIENT=mydbserver.domain.com, NB_ORA_SERV=nbmaster.domain.com, NB_ORA_POLICY=oracle_hotdb_backup, NB_ORA_SCHED=weekly)';

 

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/lib/libobk.so64.1, ENV=(NB_ORA_CLIENT=mydbserver.domain.com, NB_ORA_SERV=nbmaster.domain.com, NB_ORA_POLICY=oracle_hotdb_backup, NB_ORA_SCHED=weekly)';

 

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK FORMAT "/disk2/%U";