1. Connect into RMAN and restore spfile from Backup.
[oracle@OEL564ASMN1 dbs]$
rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:11:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN>
STARTUP FORCE NOMOUNT
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_2/dbs/initgolddb.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
RMAN>
RESTORE spfile FROM '/u01/rman_backup/rman_bkp_spfile_GOLDDB_12_20161122.bak';
Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/rman_backup/rman_bkp_spfile_GOLDDB_12_20161122.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 22-NOV-16
RMAN>
exit
Recovery Manager complete.
2. Connect into database and modify the parameter (control_files, db_create_file_dest, db_recovery_file_dest).
[oracle@OEL564ASMN1 dbs]$
sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:11:58 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
startup nomount;
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 805310400 bytes
Database Buffers 452984832 bytes
Redo Buffers 8818688 bytes
SQL>
alter system set control_files='/u01/app/oracle/oradata/controlfile01.ctl' scope=spfile;
System altered.
SQL>
alter system set db_create_file_dest='/u01/app/oracle/oradata/' scope=spfile;
System altered.
SQL>
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/' scope=spfile;
System altered.
SQL>
shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
startup nomount;
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 805310400 bytes
Database Buffers 452984832 bytes
Redo Buffers 8818688 bytes
SQL>
exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
3. Restore control file from RMAN Backup.
[oracle@OEL564ASMN1 dbs]$
rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:14:18 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: GOLDDB (not mounted)
RMAN>
RESTORE CONTROLFILE TO '/u01/app/oracle/oradata/controlfile01.ctl' FROM '/u01/rman_backup/rman_bkp_ctl_GOLDDB_928602759_11_1_0brlimk7_1_1';
Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-16
RMAN>
exit
Recovery Manager complete.
4. Mount the database and create control file to trace file for find the data file name which we need to rename.
[oracle@OEL564ASMN1 dbs]$
sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:16:14 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
alter database mount;
Database altered.
SQL>
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/oradata/control_file_records.sql';
Database altered.
SQL>
exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OEL564ASMN1 dbs]$
5. Open the control file and find out the name of datafile for generate recovery scripts.
[oracle@OEL564ASMN1 dbs]$
cat /u01/app/oracle/oradata/control_file_records.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="golddb"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "GOLDDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/golddb/onlinelog/group_1.261.925664911' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/golddb/onlinelog/group_2.262.925664915' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+DATA/golddb/onlinelog/group_3.263.925664917' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/golddb/datafile/system.256.925664777',
'+DATA/golddb/datafile/sysaux.257.925664781',
'+DATA/golddb/datafile/undotbs1.258.925664783',
'+DATA/golddb/datafile/users.259.925664783',
'+DATA/golddb/datafile/example.265.925664949'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/golddb/tempfile/temp.264.925664933' REUSE;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "GOLDDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/golddb/onlinelog/group_1.261.925664911' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/golddb/onlinelog/group_2.262.925664915' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+DATA/golddb/onlinelog/group_3.263.925664917' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/golddb/datafile/system.256.925664777',
'+DATA/golddb/datafile/sysaux.257.925664781',
'+DATA/golddb/datafile/undotbs1.258.925664783',
'+DATA/golddb/datafile/users.259.925664783',
'+DATA/golddb/datafile/example.265.925664949'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/golddb/tempfile/temp.264.925664933' REUSE;
-- End of tempfile additions.
--
[oracle@OEL564ASMN1 dbs]$
6. Restore the database from RMAN Backup. [ Recovery will replay error cause of redo log issue]
[oracle@OEL564ASMN1 ~]$
rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:22:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: GOLDDB (DBID=1679955469, not open)
RMAN>
RUN
{
2> SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/system.256.925664777' TO '/u01/app/oracle/oradata/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/sysaux.257.925664781' TO '/u01/app/oracle/oradata/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/undotbs1.258.925664783' TO '/u01/app/oracle/oradata/undotbs101.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/users.259.925664783' TO '/u01/app/oracle/oradata/users01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/example.265.925664949' TO '/u01/app/oracle/oradata/example01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
3> 4> 5> 6> 7> 8> 9> 10> 11>
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-NOV-16
Starting implicit crosscheck backup at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 22-NOV-16
Starting implicit crosscheck copy at 22-NOV-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-NOV-16
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_df2_GOLDDB_928602649_6_1_06rlimgp_1_1
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_df2_GOLDDB_928602649_6_1_06rlimgp_1_1 tag=TAG20161122T171048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs101.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_df1_GOLDDB_928602649_5_1_05rlimgp_1_1
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_df1_GOLDDB_928602649_5_1_05rlimgp_1_1 tag=TAG20161122T171048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-NOV-16
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=928607018 file name=/u01/app/oracle/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=928607018 file name=/u01/app/oracle/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=928607018 file name=/u01/app/oracle/oradata/undotbs101.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=928607018 file name=/u01/app/oracle/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=928607018 file name=/u01/app/oracle/oradata/example01.dbf
Starting recover at 22-NOV-16
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_9_1_09rlimk2_1_1.arch
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_9_1_09rlimk2_1_1.arch tag=TAG20161122T171233
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_4_d38g6f68_.arc thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_4_d38g6f68_.arc RECID=3 STAMP=928607021
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_10_1_0arlimk2_1_1.arch
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_10_1_0arlimk2_1_1.arch tag=TAG20161122T171233
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_5_d38g6grf_.arc thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_5_d38g6grf_.arc RECID=4 STAMP=928607022
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/22/2016 18:23:44
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 984242
RMAN>
7. Connect into database and perform log clear and open the database with resetlog.
[oracle@OEL564ASMN1 ~]$
sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:46:22 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database altered.
SQL>
ALTER DATABASE CLEAR LOGFILE GROUP 2;
Database altered.
SQL>
ALTER DATABASE CLEAR LOGFILE GROUP 3;
Database altered.
SQL>
alter database open RESETLOGS;
Database altered.
SQL>
select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/undotbs101.dbf
/u01/app/oracle/oradata/sysaux01.dbf
/u01/app/oracle/oradata/system01.dbf
/u01/app/oracle/oradata/example01.dbf
SQL>
SQL>
col member format a35
col member format a65
set lines 130
set pages 100
select member, group# from v$logfile;SQL> SQL> SQL> SQL>
MEMBER GROUP#
----------------------------------------------------------------- ----------
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_3_d38hkmq7_.log 3
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_2_d38hkf99_.log 2
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_1_d38hk2jp_.log 1
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_1_d38hk2pw_.log 1
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_2_d38hkfht_.log 2
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_3_d38hkmxq_.log 3
6 rows selected.
SQL>
