Oracle – Fool proof way to restore a database using RMAN
Goal
This method will ensure a clean restore from tape. Although this is designed to run from ASM to non-ASM, this method can be adapted from NON ASM as well.
Solution
Step 1. First gather the required information from the source database. This can also be obtained using the script below:
select dbid from v$database;
DBID
----------
2393662071Step 2. If using Netbackup, Obtain the policy/client name
more /usr/openv/netbackup/logs/user_ops/dbext/oracle/progress.1393436045.6828.log SEND 'NB_ORA_CLIENT=hostdb-bkup,NB_ORA_SID=MOVP01,NB_ORA_SERV=tardis-bkup'
Step 3. Copy the Oracle binaries to the destination and ensure all entries of the TNSNAMES.ora are removed. The only entry that should be left is the CATALOGDB
scp /usr/local/oracle/11.2.0.3 :/usr/local/oracle
Step 4. Make a copy of the Pfile and modify the contents, ensuring all Data Guard related items and any unnecessary parameters are removed. Also, create the relevant directory structures as listed.
create pfile from spfile;
DBP01.__db_cache_size=11811160064
DBP01.__java_pool_size=536870912
DBP01.__large_pool_size=268435456
DBP01.__oracle_base='/usr/local/oracle'#ORACLE_BASE set from environment
DBP01.__pga_aggregate_target=1073741824
DBP01.__sga_target=21474836480
DBP01.__shared_io_pool_size=0
DBP01.__shared_pool_size=8321499136
DBP01.__streams_pool_size=268435456
*.archive_lag_target=600
*.audit_file_dest='/usr/local/oracle/admin/DBP01/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_file_record_keep_time=22
*.control_files='/databases/DBP01/DBP01_control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBP01'
*.db_recovery_file_dest='/databases/archive/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/usr/local/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBP01XDB)'
*.filesystemio_options='SETALL'
*.log_archive_dest_1='LOCATION=/databases/archive/DB1/log'
*.log_archive_format='DBP01_%t_%s_%r.arc'
*.log_checkpoints_to_alert=TRUE
*.open_cursors=20000
*.optimizer_index_cost_adj=20
*.pga_aggregate_target=1073741824
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1000
*.sga_max_size=21474836480
*.sga_target=214748quit
Step 5. Start the database, and restore the controlfile and then mount the database
startup force nomount pfile='initDBP01.ora'
Start an RMAN session and perform the control file restore.
set DBID = 2393662071;
run {
allocate channel c0 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01-bkup)';
restore controlfile;
release channel c0;
}
allocated channel: c0
channel c0: SID=1088 device type=SBT_TAPE
channel c0: Veritas NetBackup for Oracle - Release 7.5 (2013061020)
Starting restore at 26-FEB-14
channel c0: starting datafile backup set restore
channel c0: restoring control file
channel c0: reading from backup piece ctrl_ug5p1icpr_s12805_p1_t840512315
channel c0: piece handle=ctrl_ug5p1icpr_s12805_p1_t840512315 tag=TAG20140226T033835
channel c0: restored backup piece 1
channel c0: restore complete, elapsed time: 00:00:55
output file name=/databases/DBP01/DBP01_control01.ctl
Finished restore at 26-FEB-14
released channel: c0
exit
Then, mount the database
alter database mount;
sql statement: alter database mount
Step 6. If there is a requirement to change the datafile location, the following script will handle the names based on version
SET SERVEROUTPUT ON;
SET LINESIZE 300;
SET PAGESIZE 300;
DECLARE
v_string VARCHAR2 (80);
v_newval VARCHAR2 (255);
v_dbid VARCHAR2 (20);
v_host VARCHAR2 (50);
v_version VARCHAR (4);
CURSOR df
IS
SELECT file#,
DECODE (
SUBSTR (NAME, 0, 1),
'+', REPLACE (name,
(SUBSTR (name, 0, INSTR (name, '/'))),
'/databases/'),
name)
AS name
FROM V$DATAFILE;
BEGIN
--get the dbid so we can restore the controlfile
SELECT dbid INTO v_dbid FROM v$database;
SELECT host_name INTO v_host FROM v$instance;
SELECT SUBSTR (version, 0, 4) INTO v_version FROM v$instance;
DBMS_OUTPUT.put_line ('version is ' || v_version);
DBMS_OUTPUT.put_line ('set dbid=' || v_dbid || ';');
DBMS_OUTPUT.put_line ('run');
DBMS_OUTPUT.put_line ('{');
DBMS_OUTPUT.put_line (
'allocate channel c0 device type sbt parms='''
|| 'ENV=(NB_ORA_CLIENT='
|| v_host || '-bkup'
|| ')'';');
DBMS_OUTPUT.put_line ('restore controlfile;');
DBMS_OUTPUT.put_line ('release channel c0;');
DBMS_OUTPUT.put_line ('}');
DBMS_OUTPUT.put_line ('sql "alter database mount"');
DBMS_OUTPUT.put_line ('run');
DBMS_OUTPUT.put_line ('{');
DBMS_OUTPUT.put_line (
'allocate channel c1 device type sbt parms='''
|| 'ENV=(NB_ORA_CLIENT='
|| v_host || '-bkup'
|| ')'';');
DBMS_OUTPUT.put_line (
'allocate channel c2 device type sbt parms='''
|| 'ENV=(NB_ORA_CLIENT='
|| v_host || '-bkup'
|| ')'';');
DBMS_OUTPUT.put_line (
'allocate channel c3 device type sbt parms='''
|| 'ENV=(NB_ORA_CLIENT='
|| v_host || '-bkup'
|| ')'';');
IF v_version = '11.1'
THEN
--Now lets grab the datafiles and rename them to the new location on our test server
FOR dfrec IN df
LOOP
DBMS_OUTPUT.put_line (
'set newname for datafile '
|| dfrec.file#
|| ' to '''
|| dfrec.name
|| ''' ;');
END LOOP;
ELSIF substr(v_version,0,2) = '10'
THEN
FOR dfrec IN df
LOOP
DBMS_OUTPUT.put_line (
'set newname for datafile '
|| dfrec.file#
|| ' to '''
|| dfrec.name
|| ''' ;');
END LOOP;
ELSIF v_version = '11.2'
THEN
DBMS_OUTPUT.put_line (
'set newname for database to ' || '''/databases/%b'';');
END IF;
--Get a good restore point from the last backup so we can ensure we dont get errors from resetlogs
SELECT (CASE
WHEN MAX (checkpoint_change#) > MAX (absolute_fuzzy_change#)
THEN
MAX (checkpoint_change#)
ELSE
MAX (absolute_fuzzy_change#)
END)
INTO v_string
FROM v$backup_datafile
WHERE file# <> 0;
DBMS_OUTPUT.put_line ('set until scn ' || v_string || ';');
DBMS_OUTPUT.put_line ('restore database;');
DBMS_OUTPUT.put_line ('switch datafile all;');
DBMS_OUTPUT.put_line ('recover database;');
DBMS_OUTPUT.put_line ('release channel c1;');
DBMS_OUTPUT.put_line ('release channel c2;');
DBMS_OUTPUT.put_line ('release channel c3;');
DBMS_OUTPUT.put_line ('}');
END;
Step 7. Get a valid restore point, to ensure we can successfully open with the resetlogs options without any error
With the destination database mounted, run the following sql
col SCN format 99999999999999999999999999
SELECT (CASE
WHEN MAX (checkpoint_change#) > MAX (absolute_fuzzy_change#)
THEN
MAX (checkpoint_change#)
ELSE
MAX (absolute_fuzzy_change#)
END)
AS SCN
FROM v$backup_datafile
WHERE file# <> 0;
SCN
---------------------------
16726490367
Step 8. Restore and recover the database using the information we have gathered. DO NOT CONNECT TO THE RECOVERY CATALOG when restoring the database
run
{
allocate channel c1 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01.toll.com.au)';
allocate channel c2 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01.toll.com.au)';
allocate channel c3 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01.toll.com.au)';
set until scn 16726490367;
restore database;
recover database;
release channel c1;
release channel c2;
release channel c3;
}
........
media recovery complete, elapsed time: 00:00:04
Finished recover at 26-FEB-14
released channel: c1
released channel: c2
released channel: c3
Step 9. Disable data block change tracking if it activated
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Database altered.
Step 10. After the database has been restored and recovered, you can open the database
alter database open resetlogs;
Database altered.
If block change tracking needs to be enabled:
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter system set DB_CREATE_FILE_DEST='/databases/ITRKAUU1';
System altered.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Database altered.
Step 10. After the database has been open, create spfile from pfile and restart the DB and create the temp file
How to restore a database from 1 week prior
Follow the same steps above, however when obtaining the SCN use the following syntax:
SELECT (CASE
WHEN MAX (checkpoint_change#) > MAX (absolute_fuzzy_change#)
THEN
MAX (checkpoint_change#)
ELSE
MAX (absolute_fuzzy_change#)
END)
AS SCN
FROM v$backup_datafile
WHERE TO_CHAR (checkpoint_time, 'DD/MM/YYYY') = '01/09/2016' AND file# <> 0; --Put the date you want to restore back to here
SCN
--------------------------
2547438170
Then restore the control file
run
{
set until time "to_date('01092016','ddmmyyyy')";
allocate channel c1 device type sbt;
restore controlfile from autobackup;
}
Restore the database as normal using the SCN captured from above.
How to find the last level 0 backup
SELECT DISTINCT TO_CHAR((b.CHECKPOINT_TIME), 'YYYY-MM-DD HH:MI.SS') t
FROM v$backup_datafile b, v$tablespace ts, v$datafile f
WHERE b.incremental_level = 0
AND INCLUDED_IN_DATABASE_BACKUP='YES'
AND f.file#=b.file#
AND f.ts#=ts.ts#
GROUP BY b.checkpoint_time
ORDER BY 1;
Known Issues and Problems:
run {
2> allocate channel c1 device type sbt;
3> SEND 'NB_ORA_CLIENT=omsdbaup01,NB_ORA_SID=emrep11p,NB_ORA_SERV=tardis-bkup,NB_ORA_POLICY=oms_prd_ora_EMREP11P_DB_WEEKLY';
4> restore datafile 1;
5> }
allocated channel: c1
channel c1: SID=1885 device type=SBT_TAPE
channel c1: Veritas NetBackup for Oracle - Release 7.1 (2011020316)
sent command to channel: c1
Starting restore at 14-APR-16
Starting implicit crosscheck backup at 14-APR-16
Finished implicit crosscheck backup at 14-APR-16
Starting implicit crosscheck copy at 14-APR-16
Crosschecked 2 objects
Finished implicit crosscheck copy at 14-APR-16
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /archive/fast_recovery_area/EMREP11P/archivelog/2015_04_17/o1_mf_1_1_bm0rb7g6_.arc
File Name: /archive/fast_recovery_area/EMREP11P/archivelog/2015_04_17/o1_mf_1_2_bm0rb7gf_.arc
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/14/2016 08:52:48
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore
Solution:
These are old archive logs that are being cataloged which is causing an issue. Remove the files and restore the control file again.
Rename Redo Log files prior to open
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/databases/TRCOPPRD/redo01.log'
ORA-27040: file create error, unable to create file
SVR4 Error: 2: No such file or directory
Additional information: 1
select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------------------------
/databases/TRCOPPRD/redo03.log
/databases/TRCOPPRD/redo02.log
/databases/TRCOPPRD/redo01.log
SQL> alter database rename file '/databases/TRCOPPRD/redo03.log' to '/databases2/da010/ redo03.log';
Database altered.
There was an error in this file which prevented to complete the open resetlogs command:
select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------------------------
/usr/local/oracle/home/11g/UAT21/dbs//databases2/da010/ redo03.log
/usr/local/oracle/home/11g/UAT21/dbs//databases2/da010/ redo02.log
/databases2/da010/redo01.log
alter database rename file '/usr/local/oracle/home/11g/UAT21/dbs//databases2/da010/ redo02.log' to '/databases2/da010/redo02.log';
Database altered.
select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------------------------
/databases2/da010/redo03.log
/databases2/da010/redo02.log
/databases2/da010/redo01.log
alter database open resetlogs;
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/databases2/da010/redo03.log'
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
alter database clear unarchived logfile group 3;
Database altered.
alter database open resetlogs;
Database altered.
SQL> select name, open_mode, DATABASE_ROLE from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRCOPPRD READ WRITE PRIMARY
Can't login as sys user from remote system?
Applies to:
RMAN on Oracle Version: 10g and 11g and later.