{"id":34,"date":"2017-08-10T06:36:12","date_gmt":"2017-08-10T06:36:12","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=34"},"modified":"2018-06-04T16:51:05","modified_gmt":"2018-06-04T06:51:05","slug":"fool-proof-way-to-restore-a-database-using-rman","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/10\/fool-proof-way-to-restore-a-database-using-rman\/","title":{"rendered":"Fool proof way to restore a database using RMAN"},"content":{"rendered":"

Oracle – Fool proof way to restore a database using RMAN<\/strong><\/p>\n

Goal<\/strong><\/em>
\nThis 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.<\/p>\n

Solution<\/strong><\/em>
\nStep 1. First gather the required information from the source database. This can also be obtained using the script below:<\/p>\n

select dbid from v$database;\r\n      DBID\r\n----------\r\n2393662071<\/code><\/pre>\n
Step 2. If using Netbackup, Obtain the policy\/client name<\/p>\n
more \/usr\/openv\/netbackup\/logs\/user_ops\/dbext\/oracle\/progress.1393436045.6828.log\r\nSEND 'NB_ORA_CLIENT=hostdb-bkup,NB_ORA_SID=MOVP01,NB_ORA_SERV=tardis-bkup'\r\n<\/pre>\n
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<\/p>\n
scp \/usr\/local\/oracle\/11.2.0.3 :\/usr\/local\/oracle\r\n<\/code><\/pre>\n
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.<\/p>\n
create pfile from spfile;\r\nDBP01.__db_cache_size=11811160064\r\nDBP01.__java_pool_size=536870912\r\nDBP01.__large_pool_size=268435456\r\nDBP01.__oracle_base='\/usr\/local\/oracle'#ORACLE_BASE set from environment\r\nDBP01.__pga_aggregate_target=1073741824\r\nDBP01.__sga_target=21474836480\r\nDBP01.__shared_io_pool_size=0\r\nDBP01.__shared_pool_size=8321499136\r\nDBP01.__streams_pool_size=268435456\r\n*.archive_lag_target=600\r\n*.audit_file_dest='\/usr\/local\/oracle\/admin\/DBP01\/adump'\r\n*.audit_trail='db'\r\n*.compatible='11.1.0.0.0'\r\n*.control_file_record_keep_time=22\r\n*.control_files='\/databases\/DBP01\/DBP01_control01.ctl'\r\n*.db_block_size=8192\r\n*.db_domain=''\r\n*.db_name='DBP01'\r\n*.db_recovery_file_dest='\/databases\/archive\/flash_recovery_area'\r\n*.db_recovery_file_dest_size=21474836480\r\n*.diagnostic_dest='\/usr\/local\/oracle'\r\n*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBP01XDB)'\r\n*.filesystemio_options='SETALL'\r\n*.log_archive_dest_1='LOCATION=\/databases\/archive\/DB1\/log'\r\n*.log_archive_format='DBP01_%t_%s_%r.arc'\r\n*.log_checkpoints_to_alert=TRUE\r\n*.open_cursors=20000\r\n*.optimizer_index_cost_adj=20\r\n*.pga_aggregate_target=1073741824\r\n*.processes=1000\r\n*.remote_login_passwordfile='EXCLUSIVE'\r\n*.sessions=1000\r\n*.sga_max_size=21474836480\r\n*.sga_target=214748quit\r\n<\/code><\/pre>\n
Step 5. Start the database, and restore the controlfile and then mount the database<\/p>\n
startup force nomount pfile='initDBP01.ora'\r\n<\/code><\/pre>\n
Start an RMAN session and perform the control file restore.<\/p>\n
set DBID = 2393662071;\r\n \r\nrun {\r\n allocate channel c0 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01-bkup)';\r\n restore controlfile;\r\n release channel c0;\r\n }\r\nallocated channel: c0\r\nchannel c0: SID=1088 device type=SBT_TAPE\r\nchannel c0: Veritas NetBackup for Oracle - Release 7.5 (2013061020)\r\nStarting restore at 26-FEB-14\r\nchannel c0: starting datafile backup set restore\r\nchannel c0: restoring control file\r\nchannel c0: reading from backup piece ctrl_ug5p1icpr_s12805_p1_t840512315\r\nchannel c0: piece handle=ctrl_ug5p1icpr_s12805_p1_t840512315 tag=TAG20140226T033835\r\nchannel c0: restored backup piece 1\r\nchannel c0: restore complete, elapsed time: 00:00:55\r\noutput file name=\/databases\/DBP01\/DBP01_control01.ctl\r\nFinished restore at 26-FEB-14\r\nreleased channel: c0\r\nexit\r\n<\/code><\/pre>\n
Then, mount the database<\/p>\n
alter database mount;\r\nsql statement: alter database mount\r\n<\/code><\/pre>\n
Step 6. If there is a requirement to change the datafile location, the following script will handle the names based on version<\/p>\n
SET SERVEROUTPUT ON;\r\nSET LINESIZE 300;\r\nSET PAGESIZE 300;\r\nDECLARE\r\n   v_string    VARCHAR2 (80);\r\n   v_newval    VARCHAR2 (255);\r\n   v_dbid      VARCHAR2 (20);\r\n   v_host      VARCHAR2 (50);\r\n   v_version   VARCHAR (4);\r\n    \r\n   CURSOR df\r\n   IS\r\n      SELECT file#,\r\n       DECODE (\r\n          SUBSTR (NAME, 0, 1),\r\n          '+', REPLACE (name,\r\n                        (SUBSTR (name, 0, INSTR (name, '\/'))),\r\n                        '\/databases\/'),\r\n          name)\r\n          AS name\r\n  FROM V$DATAFILE;\r\nBEGIN\r\n   --get the dbid so we can restore the controlfile\r\n   SELECT dbid INTO v_dbid FROM v$database;\r\n   SELECT host_name INTO v_host FROM v$instance;\r\n   SELECT SUBSTR (version, 0, 4) INTO v_version FROM v$instance;\r\n   DBMS_OUTPUT.put_line ('version is ' || v_version);\r\n   DBMS_OUTPUT.put_line ('set dbid=' || v_dbid || ';');\r\n   DBMS_OUTPUT.put_line ('run');\r\n   DBMS_OUTPUT.put_line ('{');\r\n   DBMS_OUTPUT.put_line (\r\n         'allocate channel c0 device type sbt parms='''\r\n      || 'ENV=(NB_ORA_CLIENT='\r\n      || v_host || '-bkup'\r\n      || ')'';');\r\n   DBMS_OUTPUT.put_line ('restore controlfile;');\r\n   DBMS_OUTPUT.put_line ('release channel c0;');\r\n   DBMS_OUTPUT.put_line ('}');\r\n   DBMS_OUTPUT.put_line ('sql \"alter database mount\"');\r\n   DBMS_OUTPUT.put_line ('run');\r\n   DBMS_OUTPUT.put_line ('{');\r\n   DBMS_OUTPUT.put_line (\r\n         'allocate channel c1 device type sbt parms='''\r\n      || 'ENV=(NB_ORA_CLIENT='\r\n      || v_host || '-bkup'\r\n      || ')'';');\r\n   DBMS_OUTPUT.put_line (\r\n         'allocate channel c2 device type sbt parms='''\r\n      || 'ENV=(NB_ORA_CLIENT='\r\n      || v_host || '-bkup'\r\n      || ')'';');\r\n   DBMS_OUTPUT.put_line (\r\n         'allocate channel c3 device type sbt parms='''\r\n      || 'ENV=(NB_ORA_CLIENT='\r\n      || v_host || '-bkup'\r\n      || ')'';');\r\n   IF v_version = '11.1'\r\n   THEN\r\n      --Now lets grab the datafiles and rename them to the new location on our test server\r\n      FOR dfrec IN df\r\n      LOOP\r\n         DBMS_OUTPUT.put_line (\r\n               'set newname for datafile '\r\n            || dfrec.file#\r\n            || ' to '''\r\n            || dfrec.name\r\n            || ''' ;');\r\n      END LOOP;\r\n   ELSIF substr(v_version,0,2) = '10'\r\n   THEN\r\n      FOR dfrec IN df\r\n      LOOP\r\n         DBMS_OUTPUT.put_line (\r\n               'set newname for datafile '\r\n            || dfrec.file#\r\n            || ' to '''\r\n            || dfrec.name\r\n            || ''' ;');\r\n      END LOOP;\r\n   \r\n   ELSIF v_version = '11.2'\r\n   THEN\r\n      DBMS_OUTPUT.put_line (\r\n         'set newname for database to ' || '''\/databases\/%b'';');\r\n   END IF;\r\n   --Get a good restore point from the last backup so we can ensure we dont get errors from resetlogs\r\n   SELECT (CASE\r\n              WHEN MAX (checkpoint_change#) > MAX (absolute_fuzzy_change#)\r\n              THEN\r\n                 MAX (checkpoint_change#)\r\n              ELSE\r\n                 MAX (absolute_fuzzy_change#)\r\n           END)\r\n     INTO v_string\r\n     FROM v$backup_datafile\r\n    WHERE file# <> 0;\r\n   DBMS_OUTPUT.put_line ('set until scn ' || v_string || ';');\r\n   DBMS_OUTPUT.put_line ('restore database;');\r\n   DBMS_OUTPUT.put_line ('switch datafile all;');\r\n   DBMS_OUTPUT.put_line ('recover database;');\r\n   DBMS_OUTPUT.put_line ('release channel c1;');\r\n   DBMS_OUTPUT.put_line ('release channel c2;');\r\n   DBMS_OUTPUT.put_line ('release channel c3;');\r\n   DBMS_OUTPUT.put_line ('}');\r\nEND;\r\n<\/code><\/pre>\n
Step 7. Get a valid restore point, to ensure we can successfully open with the resetlogs options without any error
\nWith the destination database mounted, run the following sql<\/p>\n
col SCN format 99999999999999999999999999\r\nSELECT (CASE\r\n           WHEN MAX (checkpoint_change#) > MAX (absolute_fuzzy_change#)\r\n           THEN\r\n              MAX (checkpoint_change#)\r\n           ELSE\r\n              MAX (absolute_fuzzy_change#)\r\n        END)\r\n          AS SCN\r\n  FROM v$backup_datafile\r\n WHERE file# <> 0;\r\n                        SCN\r\n---------------------------\r\n                16726490367\r\n<\/code><\/pre>\n
Step 8. Restore and recover the database using the information we have gathered. DO NOT CONNECT TO THE RECOVERY CATALOG when restoring the database<\/p>\n
run\r\n {\r\n allocate channel c1 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01.toll.com.au)';\r\n allocate channel c2 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01.toll.com.au)';\r\n allocate channel c3 device type sbt parms='ENV=(NB_ORA_CLIENT=movdbaup01.toll.com.au)';\r\n set until scn 16726490367;\r\n restore database;\r\n recover database;\r\n release channel c1;\r\n release channel c2;\r\n release channel c3;\r\n}\r\n \r\n........\r\nmedia recovery complete, elapsed time: 00:00:04\r\nFinished recover at 26-FEB-14\r\nreleased channel: c1\r\nreleased channel: c2\r\nreleased channel: c3\r\n<\/code><\/pre>\n
Step 9. Disable data block change tracking if it activated<\/p>\n
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;\r\nDatabase altered.\r\n<\/code><\/pre>\n
Step 10. After the database has been restored and recovered, you can open the database<\/p>\n
alter database open resetlogs;\r\nDatabase altered.\r\n<\/code><\/pre>\n
If block change tracking needs to be enabled:<\/p>\n
SQL> show parameter DB_CREATE_FILE_DEST\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_create_file_dest                  string\r\n \r\nSQL> alter system set DB_CREATE_FILE_DEST='\/databases\/ITRKAUU1';\r\nSystem altered.\r\nSQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;\r\nDatabase altered.\r\n<\/code><\/pre>\n
Step 10. After the database has been open, create spfile from pfile and restart the DB and create the temp file<\/p>\n
How to restore a database from 1 week prior<\/strong><\/h3>\n
Follow the same steps above, however when obtaining the SCN use the following syntax:<\/p>\n
SELECT (CASE\r\n           WHEN MAX (checkpoint_change#) > MAX (absolute_fuzzy_change#)\r\n           THEN\r\n              MAX (checkpoint_change#)\r\n           ELSE\r\n              MAX (absolute_fuzzy_change#)\r\n        END)\r\n          AS SCN\r\n  FROM v$backup_datafile\r\n WHERE TO_CHAR (checkpoint_time, 'DD\/MM\/YYYY') = '01\/09\/2016' AND file# <> 0; --Put the date you want to restore back to here\r\n \r\nSCN\r\n--------------------------\r\n2547438170\r\n<\/code><\/pre>\n
Then restore the control file<\/p>\n
run\r\n{\r\nset until time \"to_date('01092016','ddmmyyyy')\";\r\nallocate channel c1 device type sbt;\r\nrestore controlfile from autobackup;\r\n}\r\n<\/code><\/pre>\n
Restore the database as normal using the SCN captured from above.
\nHow to find the last level 0 backup<\/p>\n
SELECT DISTINCT TO_CHAR((b.CHECKPOINT_TIME), 'YYYY-MM-DD HH:MI.SS') t\r\n    FROM v$backup_datafile b, v$tablespace ts, v$datafile f\r\n    WHERE b.incremental_level = 0\r\n      AND INCLUDED_IN_DATABASE_BACKUP='YES'\r\n      AND f.file#=b.file#\r\n      AND f.ts#=ts.ts#\r\n    GROUP BY b.checkpoint_time\r\n    ORDER BY 1;\r\n<\/code><\/pre>\n
Known Issues and Problems:<\/p>\n
run {\r\n2> allocate channel c1 device type sbt;\r\n3> SEND 'NB_ORA_CLIENT=omsdbaup01,NB_ORA_SID=emrep11p,NB_ORA_SERV=tardis-bkup,NB_ORA_POLICY=oms_prd_ora_EMREP11P_DB_WEEKLY';\r\n4> restore datafile 1;\r\n5> }\r\nallocated channel: c1\r\nchannel c1: SID=1885 device type=SBT_TAPE\r\nchannel c1: Veritas NetBackup for Oracle - Release 7.1 (2011020316)\r\nsent command to channel: c1\r\nStarting restore at 14-APR-16\r\nStarting implicit crosscheck backup at 14-APR-16\r\nFinished implicit crosscheck backup at 14-APR-16\r\nStarting implicit crosscheck copy at 14-APR-16\r\nCrosschecked 2 objects\r\nFinished implicit crosscheck copy at 14-APR-16\r\nsearching for all files in the recovery area\r\ncataloging files...\r\ncataloging done\r\nList of Cataloged Files\r\n=======================\r\nFile Name: \/archive\/fast_recovery_area\/EMREP11P\/archivelog\/2015_04_17\/o1_mf_1_1_bm0rb7g6_.arc\r\nFile Name: \/archive\/fast_recovery_area\/EMREP11P\/archivelog\/2015_04_17\/o1_mf_1_2_bm0rb7gf_.arc\r\nreleased channel: c1\r\nRMAN-00571: ===========================================================\r\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\r\nRMAN-00571: ===========================================================\r\nRMAN-03002: failure of restore command at 04\/14\/2016 08:52:48\r\nRMAN-06026: some targets not found - aborting restore\r\nRMAN-06023: no backup or copy of datafile 1 found to restore\r\n<\/code><\/pre>\n
Solution:<\/strong><\/p>\n
These are old archive logs that are being cataloged which is causing an issue. Remove the files and restore the control file again.
\nRename Redo Log files prior to open<\/p>\n
alter database open resetlogs\r\n*\r\nERROR at line 1:\r\nORA-00344: unable to re-create online log '\/databases\/TRCOPPRD\/redo01.log'\r\nORA-27040: file create error, unable to create file\r\nSVR4 Error: 2: No such file or directory\r\nAdditional information: 1\r\n \r\nselect member from v$logfile;\r\nMEMBER\r\n----------------------------------------------------------------------------------------------------------------------    \r\n\/databases\/TRCOPPRD\/redo03.log\r\n\/databases\/TRCOPPRD\/redo02.log\r\n\/databases\/TRCOPPRD\/redo01.log\r\n \r\nSQL> alter database rename file '\/databases\/TRCOPPRD\/redo03.log' to '\/databases2\/da010\/ redo03.log';\r\nDatabase altered.\r\n \r\nThere was an error in this file which prevented to complete the open resetlogs command:\r\n\r\nselect member from v$logfile;\r\nMEMBER\r\n----------------------------------------------------------------------------------------------------------------------    \r\n\/usr\/local\/oracle\/home\/11g\/UAT21\/dbs\/\/databases2\/da010\/ redo03.log\r\n\/usr\/local\/oracle\/home\/11g\/UAT21\/dbs\/\/databases2\/da010\/ redo02.log\r\n\/databases2\/da010\/redo01.log\r\n \r\nalter database rename file '\/usr\/local\/oracle\/home\/11g\/UAT21\/dbs\/\/databases2\/da010\/ redo02.log' to '\/databases2\/da010\/redo02.log';\r\nDatabase altered.\r\n \r\nselect member from v$logfile;\r\nMEMBER\r\n----------------------------------------------------------------------------------------------------------------------\r\n\/databases2\/da010\/redo03.log\r\n\/databases2\/da010\/redo02.log\r\n\/databases2\/da010\/redo01.log\r\n \r\nalter database open resetlogs;\r\n*\r\nERROR at line 1:\r\nORA-00392: log 3 of thread 1 is being cleared, operation not allowed\r\nORA-00312: online log 3 thread 1: '\/databases2\/da010\/redo03.log'\r\n \r\nSELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;\r\n \r\nalter database clear unarchived logfile group 3;\r\nDatabase altered.\r\n \r\nalter database open resetlogs;\r\nDatabase altered.\r\n \r\nSQL> select name, open_mode, DATABASE_ROLE from v$database;\r\n \r\nNAME      OPEN_MODE              DATABASE_ROLE\r\n--------- --------------------   ----------------\r\nTRCOPPRD  READ WRITE             PRIMARY\r\n\r\nCan't login as sys user from remote system?\r\n<\/code><\/pre>\n
Applies to:<\/strong><\/em><\/p>\n
RMAN on Oracle Version: 10g and 11g and later.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[10,9],"class_list":["post-34","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-fool","tag-rman"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/34","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=34"}],"version-history":[{"count":15,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/34\/revisions"}],"predecessor-version":[{"id":51,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/34\/revisions\/51"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=34"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=34"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=34"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}