ORACLE does not have parameter files and control files. How can I restore databases through rman?

ORACLE does not have parameter files and control files. How can I restore databases through rman?
Scenario: a dev tells me that a user's table cannot be seen in the production environment and needs to be restored. At this time, the disk directory file of the parameter file control file that is automatically backed up in the production database is broken, therefore, only data files and archive log files are backed up by rman. In this case, how can we use rman to restore data on the test server? Google has a lot of information and consulted a friend. The recovery process is as follows: Preparations: Check the original data file path first.

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/home/oradata/stuorcl/system01.dbf/home/oradata/stuorcl/sysaux01.dbf/home/oradata/stuorcl/undotbs01.dbf/home/oradata/stuorcl/users01.dbf/home/oradata/stuorcl/stuorclk01.dbf/home/oradata/stuorcl/plas01.dbf/home/oradata/stuorcl/pl01.dbf/home/oradata/stuorcl/help01.dbf/home/oradata/stuorcl/adobelc01.dbf/home/oradata/stuorcl/sms01.dbf10 rows selected.SQL> 

 

Obtain the redo log file path in advance

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/home/oradata/stuorcl/redo03.log/home/oradata/stuorcl/redo02.log/home/oradata/stuorcl/redo01.log/home/oradata/stuorcl/redo_dg_01.log/home/oradata/stuorcl/redo_dg_02.log/home/oradata/stuorcl/redo_dg_03.log6 rows selected.SQL> 

 

1. Create a control file under nomount;

[root@xuelong4 ~]# su - oracle[oracle@xuelong4 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 4 13:48:23 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> 

 

2. Start to nomount.

SQL> startup nomountORACLE instance started.Total System Global Area 1.1358E+10 bytesFixed Size    2216744 bytesVariable Size 8589937880 bytesDatabase Buffers 2751463424 bytesRedo Buffers   13946880 bytesSQL> 

 

3. Create a control file

CREATE CONTROLFILE REUSE set DATABASE "stuorcl" RESETLOGS ARCHIVELOG   MAXLOGFILES 16   MAXLOGMEMBERS 3   MAXDATAFILES 100   MAXINSTANCES 8   MAXLOGHISTORY 454DATAFILE'/home/oradata/stuorcl/system01.dbf','/home/oradata/stuorcl/sysaux01.dbf','/home/oradata/stuorcl/undotbs01.dbf','/home/oradata/stuorcl/users01.dbf','/home/oradata/stuorcl/stuorclk01.dbf','/home/oradata/stuorcl/plas01.dbf','/home/oradata/stuorcl/pl01.dbf','/home/oradata/stuorcl/help01.dbf','/home/oradata/stuorcl/adobelc01.dbf','/home/oradata/stuorcl/sms01.dbf'LOGFILEGROUP 1 ('/home/oradata/stuorcl/redo03.log')  SIZE 10M,GROUP 2 ('/home/oradata/stuorcl/redo02.log')  SIZE 10M,GROUP 3 ('/home/oradata/stuorcl/redo01.log')  SIZE 10M,GROUP 4 ('/home/oradata/stuorcl/redo_dg_01.log')  SIZE 10M,GROUP 5 ('/home/oradata/stuorcl/redo_dg_02.log')  SIZE 10M,GROUP 6 ('/home/oradata/stuorcl/redo_dg_03.log')  SIZE 10MCHARACTER SET ZHS16GBK;

 

Execution Process:

SQL> CREATE CONTROLFILE REUSE set DATABASE "stuorcl" RESETLOGS ARCHIVELOG   MAXLOGFILES 16   MAXLOGMEMBERS 3   MAXDATAFILES 100   MAXINSTANCES 8   MAXLOGHISTORY 454DATAFILE'/home/oradata/stuorcl/system01.dbf','/home/oradata/stuorcl/sysaux01.dbf', '/home/oradata/stuorcl/undotbs01.dbf','/home/oradata/stuorcl/users01.dbf','/home/oradata/stuorcl/stuorclk01.dbf','/home/oradata/stuorcl/plas01.dbf','/home/oradata/stuorcl/pl01.dbf','/home/oradata/stuorcl/help01.dbf','/home/oradata/stuorcl/adobelc01.dbf','/home/oradata/stuorcl/sms01.dbf'LOGFILEGROUP 1 ('/home/oradata/stuorcl/redo03.log')  SIZE 10M,GROUP 2 ('/home/oradata/stuorcl/redo02.log')  SIZE 10M,GROUP 3 ('/home/oradata/stuorcl/redo01.log')  SIZE 10M,GROUP 4 ('/home/oradata/stuorcl/redo_dg_01.log')  SIZE 10M,GROUP 5 ('/home/oradata/stuorcl/redo_dg_02.log')  SIZE 10M,GROUP 6 ('/home/oradata/stuorcl/redo_dg_03.log')  SIZE 10MCHARACTER SET ZHS16GBK; Control file created.SQL> 

 

4. register the data file backup and archive backup catalog start with '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/' in the new control file /'; run in the mount status

RMAN> catalog start with '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/';released channel: ORA_DISK_1searching for all files that match the pattern /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/List of Files Unknown to the Database=====================================File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/full_stuorcl_20150123_3051.bakFile Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/rman_backup.logFile Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3050.bakFile Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bakDo you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/full_stuorcl_20150123_3051.bakFile Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3050.bakFile Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bakList of Files Which Where Not Cataloged=======================================File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/rman_backup.log  RMAN-07517: Reason: The file header is corruptedRMAN> 

 

5. Perform Repair

RMAN>  restore database;Starting restore at 04-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=767 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /home/oradata/stuorcl/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /home/oradata/stuorcl/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /home/oradata/stuorcl/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /home/oradata/stuorcl/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /home/oradata/stuorcl/stuorclk01.dbfchannel ORA_DISK_1: restoring datafile 00006 to /home/oradata/stuorcl/plas01.dbfchannel ORA_DISK_1: restoring datafile 00007 to /home/oradata/stuorcl/pl01.dbfchannel ORA_DISK_1: restoring datafile 00008 to /home/oradata/stuorcl/help01.dbfchannel ORA_DISK_1: restoring datafile 00009 to /home/oradata/stuorcl/adobelc01.dbfchannel ORA_DISK_1: restoring datafile 00010 to /home/oradata/stuorcl/sms01.dbfchannel ORA_DISK_1: reading from backup piece /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/full_stuorcl_20150123_3051.bakchannel ORA_DISK_1: piece handle=/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/full_stuorcl_20150123_3051.bak tag=TAG20150123T030053channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:45:45Finished restore at 04-FEB-15RMAN> 

 

Go to the alert logs in the oracle background and repair the data files one by one. The information is as follows:

[root@xuelong4 trace]# tail -f /oracle/app/oracle/diag/rdbms/stuunq/stuorcl/trace/alert_stuorcl.log GROUP 5 ('/home/oradata/stuorcl/redo_dg_02.log')  SIZE 10M,GROUP 6 ('/home/oradata/stuorcl/redo_dg_03.log')  SIZE 10MCHARACTER SET ZHS16GBKWed Feb 04 17:05:40 2015alter database mountORA-1100 signalled during: alter database mount...Wed Feb 04 17:05:55 2015alter database mountORA-1100 signalled during: alter database mount...Wed Feb 04 17:08:38 2015Full restore complete of datafile 8 /home/oradata/stuorcl/help01.dbf.  Elapsed time: 0:00:02   checkpoint is 10903299975  last deallocation scn is 9881798870Full restore complete of datafile 9 /home/oradata/stuorcl/adobelc01.dbf.  Elapsed time: 0:00:00   checkpoint is 10903299975Wed Feb 04 17:08:53 2015Full restore complete of datafile 10 /home/oradata/stuorcl/sms01.dbf.  Elapsed time: 0:00:07   checkpoint is 10903299975Wed Feb 04 17:09:52 2015Full restore complete of datafile 7 /home/oradata/stuorcl/pl01.dbf.  Elapsed time: 0:00:46   checkpoint is 10903299975  last deallocation scn is 10891613103Wed Feb 04 17:12:41 2015Full restore complete of datafile 3 /home/oradata/stuorcl/undotbs01.dbf.  Elapsed time: 0:03:28   checkpoint is 10903299975  last deallocation scn is 10903242336  Undo Optimization current scn is 10903252140Wed Feb 04 17:14:00 2015Full restore complete of datafile 4 /home/oradata/stuorcl/users01.dbf.  Elapsed time: 0:05:26   checkpoint is 10903299975  last deallocation scn is 10893349080Wed Feb 04 17:17:52 2015Full restore complete of datafile 1 /home/oradata/stuorcl/system01.dbf.  Elapsed time: 0:08:11   checkpoint is 10903299975  last deallocation scn is 10825889348  Undo Optimization current scn is 10903252140Wed Feb 04 17:20:27 2015db_recovery_file_dest_size of 15360 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Wed Feb 04 17:20:40 2015Full restore complete of datafile 2 /home/oradata/stuorcl/sysaux01.dbf.  Elapsed time: 0:12:02   checkpoint is 10903299975  last deallocation scn is 10901674267  ........

 

6. Start the recovery operation.

RMAN>  recover database;Starting recover at 04-FEB-15using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=29053channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bakchannel ORA_DISK_1: piece handle=/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bak tag=TAG20150123T032709channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:02archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29053_821708334.dbf thread=1 sequence=29053unable to find archived logarchived log thread=1 sequence=29054RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 02/04/2015 16:19:14RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29054 and starting SCN of 10903302108RMAN> 

 

The error description in error messages is as follows:

RMAN-06054: media recovery requesting unknown archived log for thread string with sequence string and starting SCN of stringCause: Media recovery is requesting a log whose existence is not recorded in the recovery catalog or target database control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point-in-time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.

 

It can be seen that the cause of this error is that the log records required for recovery cannot be found in the control file or recovery directory. There are two solutions: 1. If the related log exists and is available, add the log to the control file or recovery directory. 2. If the related logs have been deleted or are unavailable, follow the error message "scn" to restore the database to this scn. In this case, the value is 10903302108. That is to say, the database can only be recovered completely. You must use resetlogs to open the database.

recover database until scn 10903302108;RMAN> recover database until scn 10903302108;Starting recover at 04-FEB-15using channel ORA_DISK_1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 02/04/2015 16:30:30RMAN-20208: UNTIL CHANGE is before RESETLOGS changeRMAN> List of Database IncarnationsDB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time------- ------- -------- ---------------- --- ---------- ----------2       2       stuorcl 3391761643       PARENT  945184     25-JUL-131       1       stuorcl 3391761643       CURRENT 10909913627 02-FEB-15RMAN> 

 

Google, see http://blog.itpub.net/26442936/viewspace-767439/?description

RMAN> reset database to incarnation 2;RMAN> restore database until scn 1045382;RMAN> recover database until scn 1045382;SQL> alter database open resetlogs;

 

But my incarnation 2 was too early in 2013. This method is not suitable. Switch to the SQL window to try the Incomplete recovery record of rman:

SQL> recover database using backup controlfile until cancel;ORA-00279: change 10903299975 generated at 01/23/2015 03:00:53 needed forthread 1ORA-00289: suggestion :/oracle/app/oracle/flash_recovery_area/archivelog/1_29053_821708334.dbfORA-00280: change 10903299975 for thread 1 is in sequence #29053Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 10903302108 generated at 01/23/2015 03:27:09 needed forthread 1ORA-00289: suggestion :/oracle/app/oracle/flash_recovery_area/archivelog/1_29054_821708334.dbfORA-00280: change 10903302108 for thread 1 is in sequence #29054ORA-00278: log file'/oracle/app/oracle/flash_recovery_area/archivelog/1_29053_821708334.dbf' nolonger needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log'/oracle/app/oracle/flash_recovery_area/archivelog/1_29054_821708334.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log'/oracle/app/oracle/flash_recovery_area/archivelog/1_29054_821708334.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> SQL> alter database open resetlogs;Database altered.SQL> 

 

7. Go to the background to verify the data and find a table with a date change:

SQL> select  to_char(a.last_login_date,'yyyy-mm-dd hh24:mi:ss') from stu.pst_ac a where to_char(a.last_login_date,'yyyy-mm-dd hh24:mi:ss')>'2015-01-23 00:00:00:00' order by to_char(a.last_login_date,'yyyy-mm-dd hh24:mi:ss')  desc;TO_CHAR(A.LAST_LOGI-------------------2015-01-23 03:10:382015-01-23 02:36:132015-01-23 01:47:362015-01-23 01:29:142015-01-23 01:27:412015-01-23 01:09:212015-01-23 00:52:142015-01-23 00:44:522015-01-23 00:43:462015-01-23 00:32:372015-01-23 00:31:59TO_CHAR(A.LAST_LOGI-------------------2015-01-23 00:30:442015-01-23 00:27:272015-01-23 00:23:202015-01-23 00:18:532015-01-23 00:17:462015-01-23 00:13:252015-01-23 00:00:1518 rows selected.SQL> 

 

The latest data in January 23 indicates that the recovered data was in January 23.

Go to [oracle @ xuelong4 admin] $ sqlplus elearning/plel0328 @ SC _SID; SQL * Plus: Release 11.2.0.1.0 Production on Wed Feb 4 19:03:56 2015 Copyright (c) 1982,200 9, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select ut. TABLE_NAME from user_tables ut where rownum <3;