Restore the RMAN backup of a Oracle 12c RAC to a non ASM Single Instance database


We have a RMAN full backup Oracle 12c RAC ( 1 CDB +3 PDB )  and we want to restore it to a non ASM single instance database.

First take database full backup +(archive log, controlfile, spfile), then copy backups and parameter file of the RAC database to the destination server. Edit copied parameter file to create parameter file for the single instsance database like above:

TESTCDB.__db_cache_size=12569803776
TESTCDB.__java_pool_size=459524096
TESTCDB.__large_pool_size=1081701376
TESTCDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTCDB.__pga_aggregate_target=12038239232
TESTCDB.__sga_target=19131100672
TESTCDB.__shared_io_pool_size=266870912
TESTCDB.__shared_pool_size=5100223320
TESTCDB.__streams_pool_size=128435456
*.audit_file_dest='/u01/app/oracle/admin/TESTCDB/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/TESTCDB/CONTROLFILE/control10.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/TESTCDB/ONLINELOG','/u01/app/oracle/oradata'
*.db_name='TESTCDB'
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.db_recovery_file_dest_size=100G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTCDBXDB)'
*.enable_pluggable_database=true
*.log_archive_format='arc_%t_%s_%r.dbf'
*.memory_target=45G
*.open_cursors=300
*.processes=900
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.undo_tablespace='UNDOTBS1'

If we restore backup to the server which have a old restored copy of the database, then we must drop database and remove all data on Flash Recovery Area:

SQL> shut abort;
ORACLE instance shut down.

SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.3891E+10 bytes
Database Buffers     2.4293E+10 bytes
Redo Buffers          128917504 bytes
Database mounted.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@testdb4 bkp]$ cd /u01/app/oracle/fra/
[oracle@testdb4 fra]$ ll
total 4
drwxr-x---. 4 oracle oinstall 4096 Apr 20 19:11 TESTCDB
[oracle@testdb4 fra]$ rm -r *

 

Start the database with edited parameter file in nomount state:

SQL> startup nomount pfile=/u01/initTESTCDB.ora;
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.6307E+10 bytes
Database Buffers     2.1877E+10 bytes
Redo Buffers          128917504 bytes
SQL>

Create directories that where we will restore datafiles and controlfile

[oracle@TESTdb4]$ mkdir -p /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED
[oracle@TESTdb4]$ cd /u01/app/oracle/oradata/TESTCDB
[oracle@TESTdb4]$ mkdir PDB1 PDB2 PDB3 CONTROLFILE

Connect to the RMAN and restore control file:

[oracle@testdb4 oradata]$ rman target /

RMAN> set decryption identified by 'yourpassword';
executing command: SET decryption
using target database control file instead of recovery catalog

RMAN> restore controlfile from '/home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp';

Starting restore at 20-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=948 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TESTCDB/CONTROLFILE/control10.ctl
Finished restore at 20-APR-16

Then create spfile and pfile from our init.ora file , startup mount the database and catalog the copied  backups .:

RMAN> create spfile from pfile='/u01/initTESTCDB.ora';             
Statement processed
RMAN> create pfile from spfile;
Statement processed

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.6307E+10 bytes
Database Buffers     2.1877E+10 bytes
Redo Buffers          128917504 bytes
Database mounted.
SQL>

SQL>
[oracle@TEST4 fra]$ rman target /
RMAN>  catalog start with '/home/oracle/backup/rman/bkp2';


Starting implicit crosscheck backup at 20-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=948 device type=DISK
Crosschecked 24 objects
Finished implicit crosscheck backup at 20-APR-16

Starting implicit crosscheck copy at 20-APR-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-APR-16

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/backup/rman/bkp2

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_20r3h7q8_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_arch_22r3h7r4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1sr3h7gg_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_spfile_24r3h7rh_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1tr3h7ma_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1vr3h7p4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1ur3h7o1_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)?
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_20r3h7q8_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_arch_22r3h7r4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1sr3h7gg_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_spfile_24r3h7rh_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1tr3h7ma_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1vr3h7p4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1ur3h7o1_1_1.bkp
RMAN>

You can generate RMAN restore scripts using queries below:

--For Daatafiles
SELECT    'set newname for datafile '
       || file#
       || ' to ''/your_new_datafile_location/'
       || SUBSTR (name, 17, LENGTH (name) - 16)
       || ''';'
  FROM v$datafile;

--For Tempfiles
SELECT    'set newname for datafile '
       || file#
       || ' to ''/your_new_tempfile_location/'
       || SUBSTR (name, 17, LENGTH (name) - 16)
       || ''';'
  FROM v$tempfile;

--For Redologs
SELECT    'sql "alter database rename file '''''
       || MEMBER
       || ''''' to '
       || '''''/u01/app/oracle/oradata/logfile_'
       || ROWNUM
       || ''''' ";'
  FROM v$logfile

Set new name for all datafiles,tempfiles and logfile and then restore them

RMAN> run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/system.299.903908697';
set newname for datafile 3 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/sysaux.285.903908663';
set newname for datafile 4 to '/u01/app/oracle/2> 3> oradata/TESTCDB/TESTCDB/DATAFILE/undotbs1.281.903908743';
set newname for datafile 5 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_system01';
set newname for datafile 6 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/users.282.903908743'4> 5> 6> 7> ;
set newname for datafile 7 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_sysaux01';
set newname for datafile 8 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs2.293.903909187';
set newname for datafile 22 to '/u01/app/oracle/o8> 9> 10> radata/PDB1/system01.dbf';
set newname for datafile 23 to '/u01/app/oracle/oradata/PDB1/sysaux01.dbf';
set newname for datafile 24 to '/u01/app/oracle/oradata/PDB1/PDB1_users01.dbf';
set newname for datafile 25 to '/u01/app/oracle/oradata/PDB1/tbs_inz11> 12> 13> 02.dbf';
set newname for datafile 26 to '/u01/app/oracle/oradata/PDB1/tbs_inz03.dbf';
set newname for datafile 27 to '/u01/app/oracle/oradata/PDB1/tbs_inz01.dbf';
set newname for datafile 34 to '/u01/app/oracle/oradata/PDB2/system01.dbf';
set newname for14> 15> 16> 17>  datafile 35 to '/u01/app/oracle/oradata/PDB2/sysaux01.dbf';
set newname for datafile 36 to '/u01/app/oracle/oradata/PDB2/PDB2_users01.dbf';
set newname for datafile 37 to '/u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf';
set newname for datafile 38 to '/u01/18> 19> 20> app/oracle/oradata/PDB2/tbs_PDB202.dbf';
set newname for datafile 39 to '/u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf';
set newname for datafile 40 to '/u01/app/oracle/oradata/PDB3/system01.dbf';
set newname for datafile 41 to '/u01/app/oracle/oradata/PDB3/sy21> 22> 23> saux01.dbf';
set newname for datafile 42 to '/u01/app/oracle/oradata/PDB3/PDB3_users01.dbf';
set newname for datafile 43 to '/u01/app/oracle/oradata/PDB3/tbs_archive05.dbf';
set newname for datafile 44 to '/u01/app/oracle/oradata/PDB3/tbs_archive04.dbf';
set n24> 25> 26> 27> ewname for datafile 45 to '/u01/app/oracle/oradata/PDB3/tbs_archive03.dbf';
set newname for datafile 46 to '/u01/app/oracle/oradata/PDB3/tbs_archive02.dbf';
set newname for datafile 47 to '/u01/app/oracle/oradata/PDB3/tbs_archive01.dbf';
set newname for dataf28> 29> 30> ile 48 to '/u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf';
set newname for datafile 49 to '/u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf';
set newname for datafile 50 to '/u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf';
set newname for tempfile 3 to '/u01/app/oracle/or31> 32> 33> adata/PDB3/temp012016-02-06_03-13-05-pm.dbf';
set newname for tempfile 4 to '/u01/app/oracle/oradata/PDB1/temp012016-02-06_03-13-05-pm.dbf';
set newname for tempfile 6 to '/u01/app/oracle/oradata/PDB2/temp022016-02-20_03-13-05-pm.dbf';
set newname for temp34> 35> 36> file 7 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/cdb_temp01';
set newname for tempfile 8 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_temp01';
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_2.284.903908803'' to '37> 38> '/u01/app/oracle/oradata/logfile_1'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_2.258.903908803'' to ''/u01/app/oracle/oradata/logfile_2'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_1.283.903908803'' to ''39> 40> /u01/app/oracle/oradata/logfile_3'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_1.259.903908803'' to ''/u01/app/oracle/oradata/logfile_4'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_5.1266.904278355'' to ''41> 42> /u01/app/oracle/oradata/logfile_5'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_3.286.903909317'' to ''/u01/app/oracle/oradata/logfile_6'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_3.257.903909317'' to ''/43> 44> u01/app/oracle/oradata/logfile_7'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_4.300.903909317'' to ''/u01/app/oracle/oradata/logfile_8'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_4.256.903909317'' to ''/u45> 46> 01/app/oracle/oradata/logfile_9'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_5.265.904278355'' to ''/u01/app/oracle/oradata/logfile_10'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_6.1264.904278355'' to ''/47> 48> u01/app/oracle/oradata/logfile_11'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_6.264.904278355'' to ''/u01/app/oracle/oradata/logfile_12'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_7.1263.904278357'' to '49> 50> '/u01/app/oracle/oradata/logfile_13'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_7.263.904278357'' to ''/u01/app/oracle/oradata/logfile_14'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_8.1262.904278357'' to51> 52>  ''/u01/app/oracle/oradata/logfile_15'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_8.262.904278357'' to ''/u01/app/oracle/oradata/logfile_16'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_9.1261.904278357'' 53> 54> to ''/u01/app/oracle/oradata/logfile_17'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_9.261.904278357'' to ''/u01/app/oracle/oradata/logfile_18'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_10.1260.90427839355> 56> '' to ''/u01/app/oracle/oradata/logfile_19'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_10.267.904278393'' to ''/u01/app/oracle/oradata/logfile_20'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_11.1258.9042757> 58> 8393'' to ''/u01/app/oracle/oradata/logfile_21'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_11.266.904278393'' to ''/u01/app/oracle/oradata/logfile_22'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_12.1259.959> 60> 04278393'' to ''/u01/app/oracle/oradata/logfile_23'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_12.268.904278393'' to ''/u01/app/oracle/oradata/logfile_24'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_13.1261> 62> 55.904278395'' to ''/u01/app/oracle/oradata/logfile_25'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_13.269.904278395'' to ''/u01/app/oracle/oradata/logfile_26'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_163> 64> 4.1257.904278395'' to ''/u01/app/oracle/oradata/logfile_27'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_14.270.904278395'' to ''/u01/app/oracle/oradata/logfile_28'' ";
restore database;
switch datafile all;
switch tempfile all;
65> 66> 67> 68> 69> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_2.284.903908803'' to ''/u01/app/oracle/oradata/logfile_1''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_2.258.903908803'' to ''/u01/app/oracle/oradata/logfile_2''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_1.283.903908803'' to ''/u01/app/oracle/oradata/logfile_3''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_1.259.903908803'' to ''/u01/app/oracle/oradata/logfile_4''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_5.1266.904278355'' to ''/u01/app/oracle/oradata/logfile_5''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_3.286.903909317'' to ''/u01/app/oracle/oradata/logfile_6''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_3.257.903909317'' to ''/u01/app/oracle/oradata/logfile_7''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_4.300.903909317'' to ''/u01/app/oracle/oradata/logfile_8''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_4.256.903909317'' to ''/u01/app/oracle/oradata/logfile_9''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_5.265.904278355'' to ''/u01/app/oracle/oradata/logfile_10''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_6.1264.904278355'' to ''/u01/app/oracle/oradata/logfile_11''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_6.264.904278355'' to ''/u01/app/oracle/oradata/logfile_12''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_7.1263.904278357'' to ''/u01/app/oracle/oradata/logfile_13''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_7.263.904278357'' to ''/u01/app/oracle/oradata/logfile_14''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_8.1262.904278357'' to ''/u01/app/oracle/oradata/logfile_15''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_8.262.904278357'' to ''/u01/app/oracle/oradata/logfile_16''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_9.1261.904278357'' to ''/u01/app/oracle/oradata/logfile_17''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_9.261.904278357'' to ''/u01/app/oracle/oradata/logfile_18''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_10.1260.904278393'' to ''/u01/app/oracle/oradata/logfile_19''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_10.267.904278393'' to ''/u01/app/oracle/oradata/logfile_20''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_11.1258.904278393'' to ''/u01/app/oracle/oradata/logfile_21''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_11.266.904278393'' to ''/u01/app/oracle/oradata/logfile_22''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_12.1259.904278393'' to ''/u01/app/oracle/oradata/logfile_23''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_12.268.904278393'' to ''/u01/app/oracle/oradata/logfile_24''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_13.1255.904278395'' to ''/u01/app/oracle/oradata/logfile_25''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_13.269.904278395'' to ''/u01/app/oracle/oradata/logfile_26''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_14.1257.904278395'' to ''/u01/app/oracle/oradata/logfile_27''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_14.270.904278395'' to ''/u01/app/oracle/oradata/logfile_28''

Starting restore at 20-APR-16
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 00040 to /u01/app/oracle/oradata/PDB3/system01.dbf
channel ORA_DISK_1: restoring datafile 00041 to /u01/app/oracle/oradata/PDB3/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00042 to /u01/app/oracle/oradata/PDB3/PDB3_users01.dbf
channel ORA_DISK_1: restoring datafile 00043 to /u01/app/oracle/oradata/PDB3/tbs_archive05.dbf
channel ORA_DISK_1: restoring datafile 00044 to /u01/app/oracle/oradata/PDB3/tbs_archive04.dbf
channel ORA_DISK_1: restoring datafile 00045 to /u01/app/oracle/oradata/PDB3/tbs_archive03.dbf
channel ORA_DISK_1: restoring datafile 00046 to /u01/app/oracle/oradata/PDB3/tbs_archive02.dbf
channel ORA_DISK_1: restoring datafile 00047 to /u01/app/oracle/oradata/PDB3/tbs_archive01.dbf
channel ORA_DISK_1: restoring datafile 00048 to /u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf
channel ORA_DISK_1: restoring datafile 00049 to /u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf
channel ORA_DISK_1: restoring datafile 00050 to /u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1sr3h7gg_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1sr3h7gg_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:35
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 00034 to /u01/app/oracle/oradata/PDB2/system01.dbf
channel ORA_DISK_1: restoring datafile 00035 to /u01/app/oracle/oradata/PDB2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00036 to /u01/app/oracle/oradata/PDB2/PDB2_users01.dbf
channel ORA_DISK_1: restoring datafile 00037 to /u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf
channel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/PDB2/tbs_PDB202.dbf
channel ORA_DISK_1: restoring datafile 00039 to /u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1tr3h7ma_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1tr3h7ma_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01: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 00022 to /u01/app/oracle/oradata/PDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/PDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/PDB1/PDB1_users01.dbf
channel ORA_DISK_1: restoring datafile 00025 to /u01/app/oracle/oradata/PDB1/tbs_inz02.dbf
channel ORA_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/PDB1/tbs_inz03.dbf
channel ORA_DISK_1: restoring datafile 00027 to /u01/app/oracle/oradata/PDB1/tbs_inz01.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1vr3h7p4_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1vr3h7p4_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20-APR-16

datafile 1 switched to datafile copy
input datafile copy RECID=33 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/system.299.903908697
datafile 3 switched to datafile copy
input datafile copy RECID=34 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/sysaux.285.903908663
datafile 4 switched to datafile copy
input datafile copy RECID=35 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs1.281.903908743
datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_system01
datafile 6 switched to datafile copy
input datafile copy RECID=37 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/users.282.903908743
datafile 7 switched to datafile copy
input datafile copy RECID=38 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_sysaux01
datafile 8 switched to datafile copy
input datafile copy RECID=39 STAMP=909693180 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs2.293.903909187
datafile 22 switched to datafile copy
input datafile copy RECID=40 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/system01.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=41 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/sysaux01.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=42 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/PDB1_users01.dbf
datafile 25 switched to datafile copy
input datafile copy RECID=43 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/tbs_inz02.dbf
datafile 26 switched to datafile copy
input datafile copy RECID=44 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/tbs_inz03.dbf
datafile 27 switched to datafile copy
input datafile copy RECID=45 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB1/tbs_inz01.dbf
datafile 34 switched to datafile copy
input datafile copy RECID=46 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/system01.dbf
datafile 35 switched to datafile copy
input datafile copy RECID=47 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/sysaux01.dbf
datafile 36 switched to datafile copy
input datafile copy RECID=48 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/PDB2_users01.dbf
datafile 37 switched to datafile copy
input datafile copy RECID=49 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf
datafile 38 switched to datafile copy
input datafile copy RECID=50 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB202.dbf
datafile 39 switched to datafile copy
input datafile copy RECID=51 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf
datafile 40 switched to datafile copy
input datafile copy RECID=52 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/system01.dbf
datafile 41 switched to datafile copy
input datafile copy RECID=53 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/sysaux01.dbf
datafile 42 switched to datafile copy
input datafile copy RECID=54 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/PDB3_users01.dbf
datafile 43 switched to datafile copy
input datafile copy RECID=55 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/tbs_archive05.dbf
datafile 44 switched to datafile copy
input datafile copy RECID=56 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/tbs_archive04.dbf
datafile 45 switched to datafile copy
input datafile copy RECID=57 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive03.dbf
datafile 46 switched to datafile copy
input datafile copy RECID=58 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive02.dbf
datafile 47 switched to datafile copy
input datafile copy RECID=59 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive01.dbf
datafile 48 switched to datafile copy
input datafile copy RECID=60 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf
datafile 49 switched to datafile copy
input datafile copy RECID=61 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf
datafile 50 switched to datafile copy
input datafile copy RECID=62 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf

renamed tempfile 3 to /u01/app/oracle/oradata/PDB3/temp012016-02-06_03-13-05-pm.dbf in control file
renamed tempfile 4 to /u01/app/oracle/oradata/PDB1/temp012016-02-06_03-13-05-pm.dbf in control file
renamed tempfile 6 to /u01/app/oracle/oradata/PDB2/temp022016-02-20_03-13-05-pm.dbf in control file
renamed tempfile 7 to /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/cdb_temp01 in control file
renamed tempfile 8 to /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_temp01 in control file

Recover database

RMAN> recover database;

Starting recover at 20-APR-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10275 is already on disk as file /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10272
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11124
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11125
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10273
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11126
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11127
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10274
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_arch_22r3h7r4_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_arch_22r3h7r4_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10272_ckhd9vrm_.arc thread=1 sequence=10272
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11124_ckhd9twc_.arc thread=2 sequence=11124
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11124_ckhd9twc_.arc RECID=59793 STAMP=909693691
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11125_ckhd9ttm_.arc thread=2 sequence=11125
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10272_ckhd9vrm_.arc RECID=59794 STAMP=909693692
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10273_ckhd9vwm_.arc thread=1 sequence=10273
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11125_ckhd9ttm_.arc RECID=59792 STAMP=909693691
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11126_ckhd9w8h_.arc thread=2 sequence=11126
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11126_ckhd9w8h_.arc RECID=59798 STAMP=909693693
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11127_ckhd9w9m_.arc thread=2 sequence=11127
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10273_ckhd9vwm_.arc RECID=59797 STAMP=909693692
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10274_ckhd9wbq_.arc thread=1 sequence=10274
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10274_ckhd9wbq_.arc RECID=59796 STAMP=909693692
archived log file name=/home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931 thread=1 sequence=10275
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11127_ckhd9w9m_.arc RECID=59795 STAMP=909693692
unable to find archived log
archived log thread=2 sequence=11128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2016 20:42:18
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11128 and starting SCN of 842116000

Copy archivelogs that recovery needs and recover database

[oracle@testdb1 2016_04_20]$ scp thread_2_seq_11128.989.909680709  192.168.1.109:/home/oracle/backup/rman/bkp2/
oracle@192.168.1.109's password:
thread_2_seq_11128.989.909680709

RMAN> recover database
2> ;

Starting recover at 20-APR-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10275 is already on disk as file /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
unable to find archived log
archived log thread=2 sequence=11128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2016 20:45:52
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11128 and starting SCN of 842116000

Open database with resetlogs

RMAN> alter database open resetlogs;

Statement processed

RMAN>

SQL> show pdbs;

CON_ID 	CON_NAME OPEN MODE	RESTRICTED
------ --------- --------- ---------- 	
2 		PDB$SEED  READ ONLY	 NO
3 		PDB1      MOUNTED
4 		PDB2 	  MOUNTED
5 		PDB3      MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

You can also use set new name only for database if you dont want place all files to different directories like in source:

RMAN> run
{
set newname for database to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/%U';
set newname for tempfile 1 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/%U';
restore database;
switch datafile all;
switch tempfile all;
}

Scenario 8 – Loss of all control files


Vəziyyət:

  • Bütün kontrol faylar silinmişdir
  • Kontrol faylın backupını aldıqdan sonra yeni yaradılmış data file silinmişdir
  • Kontrol faylın binary backup-ı vardır

.

Bu vəziyyəti test etmək üçün ilk öncə kontrol faylın binary backup-nı alırıq və daha sonra bütün kontrol faylları silirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';

Database altered.
Create new tablespace

SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;

Tablespace created.

SQL> !rm /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info



[/pcsh]

DB-nı mount etmək üçün kontrol faylın backupını silinmiş faylların yerınə kopyalayırıq və adını dəyişirik. Sonra isə kontrol faylın backupından istifadə edərək recovery etməyə cəhd edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl

[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/

control01.ctl  control02.ctl  control03.ctl
SQL> alter database mount;

Database altered.



SQL> recover database using backup controlfile;

::::::::::::::output trimmed::::::::::::::

ORA-00280: change 13917957 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/DB11G/redo01.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf'

ORA-01112: media recovery not started

[/pcsh]

ORA-01244 errorundan biz aydın olurkı bizim adı təyin olunmamış datafaylımız var. İndi biz kontrol faylın backupından sonra yaradılmış yeni  datafaylın adını tapmalı və onun əsasında datafaylı yaratmalıyıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005

10 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';

Database altered.

[/pcsh]

Yeniden recover etməyə cəhd edirik və open edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
------------------------------ -------------------------------------------------
SYSTEM        /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX        /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2        /u01/app/oracle/oradata/DB11G/tbs_test2.dbf

10 rows selected.



[/pcsh]Situation:

  • All control files have been lost
  • After creating backup of control file new created datafile lost
  • Control file binary backup is available

.

Take binary backup of the control file and remove all control files to simulate lost of all control files:

[pcsh lang=”sql” tab_size=”” message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';

Database altered.
Create new tablespace

SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;

Tablespace created.

SQL> !rm /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info



--

[/pcsh]

To mount Database copy backup of control file to the original control file locations and rename to original control file names.Then recover database using backup control file

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl

[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/

control01.ctl  control02.ctl  control03.ctl
SQL> alter database mount;

Database altered.



SQL> recover database using backup controlfile;

::::::::::::::output trimmed::::::::::::::

ORA-00280: change 13917957 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/DB11G/redo01.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf'

ORA-01112: media recovery not started

[/pcsh]

ORA-01244 says that we have unnamed datafile. Now we must  find this datafile and creat datafile based on it with name of datafile that were created after taking backup of control file:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005

10 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';

Database altered.

[/pcsh]

Now recover the database again and then open  the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
------------------------------ -------------------------------------------------
SYSTEM        /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX        /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2        /u01/app/oracle/oradata/DB11G/tbs_test2.dbf

10 rows selected.



[/pcsh]

Scenario 7 – Recovery read only tablespace from loss of control files


Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol faylımızın yaranma scripti var
  • Tablespce-lərdən biri read only-dir

.

İlk olaraq bir tablespace yaradırıq , only read only edirik, kontrol faylın backup-nı alırıq  və bütün kontrol faylları silirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;

Tablespace created.

SQL> alter tablespace tbs_test read only;

Tablespace altered.

SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';

Database altered.

SQL> !rm  /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more inf

[/pcsh]

ctl_backup.dat faylından istifadə edərək kontrol faylı yaradırıq və DB-nı işə salmağa cəh edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/DB11G/system01.dbf',

'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',

'/u01/app/oracle/oradata/DB11G/users2_01.dbf',

'/home/oracle/backups/fortest/tbs1.dbf',

'/home/oracle/backups/fortest/tbs2.dbf',

'/home/oracle/backups/fortest/users02.dbf',

'/home/oracle/backups/fortest/users03.dbf',

'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'

CHARACTER SET AL32UTF8;  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

Control file created.

[/pcsh]

Və biz görürük ki kontrol faylın yaranma skriptində read only tablespace barədə məlumat yoxdur.

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> startup force;

ORACLE instance started.

::::::::::::::output trimmed::::::::::::::

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'

SQL>

[/pcsh]

Data faylların adını sorğuladıqda görürük ki tbs_test readonly tablespacesinin datafaylları barədə məlumat yoxdur:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

8 rows selected.

[/pcsh]

DB-nı recover etməyə cəhd edirik və redo məlumatları əlavə etmək üçün redo log-ları təqdim edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started

[/pcsh]

Recovery başlamır, çünki oracle itmiş read only datafaylı UNNAMED00004 kimi qəbul etdiyindən redolog-lar əlavə oluna bilmir. Ona görədə biz UNNAMED00004 faylının adını onun orijinal adına dəyişirik

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.


SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';

Database altered.

[/pcsh]

İndi recovery davam edə bilər , çünki datafayl öz düzgün adına rename olundu. DB-ni recover edib bazanı açıqırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
-----------    - -------------------------------------------------

SYSTEM       /u01/app/oracle/oradata/DB11G/system01.dbf

SYSAUX       /u01/app/oracle/oradata/DB11G/sysaux01.dbf

TBS_TEST       /u01/app/oracle/oradata/DB11G/test_tbs.dbf

::::::::::::::output trimmed::::::::::::::


9 rows selected.

[/pcsh]Situation:

  • All control files are lost
  • You have a creation script of the control file
  • One of the tablespaces is read only

.

First, create tablespace and make it read only, then take backup of the control file and delete all available control files.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;

Tablespace created.

SQL> alter tablespace tbs_test read only;

Tablespace altered.

SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';

Database altered.

SQL> !rm  /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more inf

[/pcsh]

Create control file using ctl_backup.dat file and try opening the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/DB11G/system01.dbf',

'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',

'/u01/app/oracle/oradata/DB11G/users2_01.dbf',

'/home/oracle/backups/fortest/tbs1.dbf',

'/home/oracle/backups/fortest/tbs2.dbf',

'/home/oracle/backups/fortest/users02.dbf',

'/home/oracle/backups/fortest/users03.dbf',

'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'

CHARACTER SET AL32UTF8;  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

Control file created.

[/pcsh]

And we saw that there is not information about readonly datafile on this script.

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> startup force;

ORACLE instance started.

::::::::::::::output trimmed::::::::::::::

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'

SQL>

[/pcsh]

Select the name  of datafiles and you say it is no information about tbs_test readonly datafile:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

8 rows selected.

[/pcsh]

Try to recover the database and provide the redo log file to applying redo entries:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started

[/pcsh]

Recovery hasn’t started because it applied information about missing read only datafile which is missing since Oracle accept it as UNNAMED00004.

Select name of datafiles, now you can see UNNAMED00004 file, which is the our missing read only datafile and rename the datafile:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.


SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';

Database altered.

[/pcsh]

Now recovery will continue because the datafile has been renamed to its  correct name. Recover database and open it:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
-----------    - -------------------------------------------------

SYSTEM       /u01/app/oracle/oradata/DB11G/system01.dbf

SYSAUX       /u01/app/oracle/oradata/DB11G/sysaux01.dbf

TBS_TEST       /u01/app/oracle/oradata/DB11G/test_tbs.dbf

::::::::::::::output trimmed::::::::::::::


9 rows selected.

[/pcsh]

Scenario 6 – Recover from lost of a member of Multiplexed Control File


Vəziyyət:

  • Kontrol fayllar multipleks olunmuşdur
  • Kontrol fayllardan biri silinmişdir

Bu halda kontrol fayllar multipleks olunduğu üçün yenidən kontrol fayl yaratmağa ehtiyac qalmır. Silinmiş kontrol faylları bərpa etmək üçün sadəcə  bazanı söndürüb, qaydasında olan kontrol faylı silinmiş faylın yerinə kopylayıb adını silinmiş faylın adına dəyişirik. Gəlin bunu test edək:

İlk olaraq bütün kontrol fayllarımızın adını sorğulayırıq, daha sonra onlardan birini silib DB-nı reboot edirik.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> !rm /home/oracle/backups/controlfiles/control01.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Daha sonra qaydasında olan kontrol faylı silinmiş faylın yerinə kopyalayıb adını dəyişirik və bazanı işə salırıq.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cp /home/oracle/backups/controlfiles/control02.ctl /home/oracle/backups/contolfiles/control01.ctl

SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

Database mounted.

Database opened.

[/pcsh]Situation:

  • Control files multiplexed
  • One of the controlfiles is lost

If you have a multiplexed control file, there is no need to create a new control file. Instead, only shut down the database and copy the available control file to the directory of the missed one. Let’s test it:

Select name of all control files, delete one of them and reboot the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> !rm /home/oracle/backups/controlfiles/control01.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Copy available control file to the directory of the missed control file and open the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cp /home/oracle/backups/controlfiles/control02.ctl /home/oracle/backups/contolfiles/control01.ctl

SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

Database mounted.

Database opened.

[/pcsh]

Scenario 5 – Recovering from loss of all control files using binary backup control file


Recovering from loss of all control files using backup control

Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol faylın binary backupı var

.

Bu vəziyyəti yoxlamaq üçün ilk olaraq kontrol faylın binary backup-nı alırıq və bazanı söndürürük. DB-nı mount vəziyyətinə gətirmək istədikdə error ilə qarşılaşırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/ctl_binary_bckp.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> host
[oracle@orcl ~]$ cd /home/oracle/backups/controlfiles/
[oracle@orcl controlfiles]$ rm *.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Bu vəziyyətdən çıxış üçün binary backup-ı silinmiş kontrol faylların yerinə adını silinmiş faylların adına dəyişərək kopyalayır və bazanı mount edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /home/oracle/backups/
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl backups]$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

[/pcsh]

Recover database using backup control file komandasından istifadə edərək bazanı binary backup-dan recover etməyə cəhd edək:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>

[/pcsh]

Burada oracle bizə deyirki kontrol faylin backup-ı alındıqdan sonra edilmish deyishiklikler teqdim edilmelidir. Yuxarıda error bizə dəyishiklik olunmush redolog faylin adını ve scn nomresini teqdim edir. Spesifik scn-den 13877247  sonra deyishikler olunmush faylı dəqiq tapmaq üçün biz  log və archivelog  faylların yerləşdiyi view-ni sorğulayırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select group#,status,first_change# from v$log;

GROUP# STATUS    FIRST_CHANGE#
----- --------  -------------
	1 CURRENT 	13877246

	3 INACTIVE 	13847806

	2 INACTIVE 	3847403s

[/pcsh]

Yuxarıdakı nəticədən görünür ki, 2 və 3 inactive olduğu üçün 1-ci qrupda olan dəyişikliklər əlavə olunmalıdır. Ona görədə biz yeniden recovery emrini işə salırıq və ona parametr olaraq redo log faylını /u01/app/oracle/oradata/DB11G/redo01.log veririk.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

 

SQL> alter database open resetlogs;
Database altered.

[/pcsh]Situation:

  • All control files are lost
  • You have binary backup of control file

.

At first lets take a binary backup of control files and shutdown force database. When you attempt to mount the database, you encounter with error:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/ctl_binary_bckp.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> host
[oracle@orcl ~]$ cd /home/oracle/backups/controlfiles/
[oracle@orcl controlfiles]$ rm *.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Then copy binary backup of control file to te original control file’s destination and rename to the original control file name. Then mount the database:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /home/oracle/backups/
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl backups]$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

[/pcsh]

You can use the recover database using backup control file command to tell Oracle that an attempt to recover the database from the binary copy of the control file:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>

[/pcsh]

Changes after backup are not presented . To find a file that contains changes after specific scn 13877247 we query the v$log view

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select group#,status,first_change# from v$log;

GROUP# STATUS    FIRST_CHANGE#
----- --------  -------------
	1 CURRENT 	13877246

	3 INACTIVE 	13847806

	2 INACTIVE 	3847403s

[/pcsh]

From result above we see that because second and third group is inactive ve must apply first group redo file . That’s why retrie recovery command with  /u01/app/oracle/oradata/DB11G/redo01.log parameter.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

 

SQL> alter database open resetlogs;
Database altered.

[/pcsh]

Scenario 4 – Recover from loss of the control files when no backup files are available


Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol Faylların backuplarıda həmçinin silinmişdir
.

Kontrol fayllar silinmişdir və kontrol faylların backup-ı yoxdur. Buna görə kontrol fayl create controlfile komandası vasitəsilə yenidən yaradılmalıdır. Bunu test etmək üçün  kontrol faylları silirik, kontrol fayl və redo log faylların yerləşdiyi yeri sorğulayedib nəticəni  saxlayırıq. Bu nəticə kontrol faylı yaradan zaman lazım olacaq. Daha sonra bazanı sondürürük. Bazanı mount etməyə cəhd etdikdə error baş verməlidir:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ rm *.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

SQL> shutdown;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup;

SQL> ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount

*

ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> Alter database backup controlfile to trace;
Alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted

[/pcsh]

Indi isə aşağıdakı qaydada create controlfile komandasından istifadə edərək kontrol faylı yaradırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /u01/app/oracle/oradata/DB11G/


[oracle@orcl DB11G]$ ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  users2_01.dbf

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292

LOGFILE
GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE
'/u01/app/oracle/oradata/DB11G/system01.dbf',
'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',
::::::::::::::output trimmed::::::::::::::
'/u01/app/oracle/oradata/DB11G/users2_01.dbf'
CHARACTER SET AL32UTF8; 
Control file created.
SQL> alter database open;

Database altered.

[/pcsh]Situation:

  • All control files have been lost
  • Backup of control files also lost
.

Control files lost and you have not backup of the control file. Therefore, you need to recreate the control file by the command create controlfile. To test this situation remove  control files, select name of datafiles, redo logs files and copy result somewhere. We will use this result when manually creating the control file. Then shut down the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ rm *.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

SQL> shutdown;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup;

SQL> ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount

*

ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> Alter database backup controlfile to trace;
Alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted

[/pcsh]

Now you must manually create a statement which, when run, creates a control file. To manually create, control you need the names of all of the data files in the database. You can construct a CREATE CONTROLFILE statement as shown above:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /u01/app/oracle/oradata/DB11G/


[oracle@orcl DB11G]$ ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  users2_01.dbf

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292

LOGFILE
GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE
'/u01/app/oracle/oradata/DB11G/system01.dbf',
'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',
::::::::::::::output trimmed::::::::::::::
'/u01/app/oracle/oradata/DB11G/users2_01.dbf'
CHARACTER SET AL32UTF8; 
Control file created.
SQL> alter database open;

Database altered.

[/pcsh]

Scenario 3 – When control files, datafiles and Redo Logs are lost


Vəziyyət:

  •  Bütün kontrol, redo log və data fayllar silinmişdir
  • Archive log-lar qaydasındadır
  • Bazanın full backup-ı var
  • Baza archive log rejimindədir
  • Kontrol faylın backup-ı var

Bu halda biz bazanı recover edə bilərik, lakin son dəyişikliklər hansı ki online redo log-larda yerləşir, redo loglar silindiyi üçün bərpa edilə bilməyəcək (Buna görədə Oracle redlo logları müxtəlif  yerlərdə multipleks etməyi məsləhət görür).

1

Bu ssenarini test etmək üçün içində məlumat olan bir cədvəl yaradırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create table test_tbl as select * from all_tables;
SQL> commit;
SQL> select count(*) from test_tbl;

 COUNT(*)
----------
     2736

[/pcsh]

Bazanın bakup-nı alırıq

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> backup database;
Then insert a lot more data to the table
SQL> insert into test_tbl select * from all_tables;
2737 rows created.
SQL> /
SQL> select count(*) from test_tbl;
 COUNT(*)
----------
   52002

[/pcsh]

Bütün kontrol, redo log və  data faylları silirik .

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cd  /u01/app/oracle/oradata/DB11G/ 
[oracle@orcl DB11G]$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@orcl DB11G]$ rm *
Then shut down darabase and restore control file from autobackup
SQL> shut abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
RMAN> restore controlfile from autobackup;
Then mount and recovery database
RMAN> alter database mount;
database mounted
RMAN> restore database;
RMAN> recover database;
::::::::::::::output trimmed::::::::::::::
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/05/2015 15:36:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 13707555

[/pcsh]

Sonda redo log silindiyindən Oracle archived faylı tapa bilmədiyi  üçün  error baş verir. Daha sonra bazanı resetlogs parametri ilə open edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> alter database open resetlogs;
database opened

[/pcsh]

Bərpadan sonra cədvəlimizdəki sətirlərin sayını sorğulayırıq və nəticədə görürük ki, biz yalnız arxiv loglar vasitəsilə yadda saxlaya bildiyimiz məlumatları restore ede bilmşik. Online redologlarda olan məlumatlar istə bərpa olunmamışdır.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select count(*) from test_tbl;
 COUNT(*)
----------
     2736

[/pcsh]Situation:

  • All control files, redo log files and data files have been lost
  • Archive logs are available
  • We have a full backup of this database
  • The database running in archive log mode
  • We have a backup of the control file

In this situation we can recover database but we cannot be able apply last changes that were in the online redo logs (That is why Oracle recommends multiplexing the redo log files to different hard drives.).

1

To test this recovery scenario first we create a table with data:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create table test_tbl as select * from all_tables;
SQL> commit;
SQL> select count(*) from test_tbl;

 COUNT(*)
----------
     2736

[/pcsh]

Then backup the database

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> backup database;
Then insert a lot more data to the table
SQL> insert into test_tbl select * from all_tables;
2737 rows created.
SQL> /
SQL> select count(*) from test_tbl;
 COUNT(*)
----------
   52002

[/pcsh]

Then we remove all control files, data files and redo log files to crash the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cd  /u01/app/oracle/oradata/DB11G/ 
[oracle@orcl DB11G]$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@orcl DB11G]$ rm *
Then shut down darabase and restore control file from autobackup
SQL> shut abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
RMAN> restore controlfile from autobackup;
Then mount and recovery database
RMAN> alter database mount;
database mounted
RMAN> restore database;
RMAN> recover database;
::::::::::::::output trimmed::::::::::::::
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/05/2015 15:36:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 13707555

[/pcsh]

Suddenly you get an error because oracle cannot find the archived file because redo log is deleted. Then open the database with resetlogs option:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> alter database open resetlogs;
database opened

[/pcsh]

Select rowcount of the table, and you’ll see we could only recover data in archived logs. Data in online redo logs are lost.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select count(*) from test_tbl;
 COUNT(*)
----------
     2736

[/pcsh]