Nothing but my learning and experience with Oracle..!

In my previous two posts I discussed about how to recover a database from the loss of all controlfile and recovering it from a cold backup. When comparing with RMAN, restoring and recovering controlfile from a cold backup is bit complicated and it get more complex if you have made any structural change to the database after last successful controlfile backup. But if you are using RMAN to recover your database (and is the preferable option),the entire recovery process will be transparent even if you made any structural change to your database.

Lets simulate this scenario-

[oracle@mydb01]:[TTREC1] $ . oraenv

ORACLE_SID = [TTREC1] ? TTREC1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@mydb01]:[TTREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 15 16:06:29 2016

connected to target database: TTREC (DBID=4201939161)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TTREC are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/t4_nfs/TTREC/%U';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE ON;

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_TTREC1.f'; # default

Lets take a full database backup.

RMAN> backup incremental level 0 database ;

Starting backup at 15-SEP-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=366 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=2 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=35 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=69 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

input datafile file number=00009 name=/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

input datafile file number=00007 name=/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

channel ORA_DISK_1: starting piece 1 at 15-SEP-16

channel ORA_DISK_2: starting incremental level 0 datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00001 name=/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf

input datafile file number=00008 name=/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

input datafile file number=00006 name=/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

channel ORA_DISK_2: starting piece 1 at 15-SEP-16

channel ORA_DISK_3: starting incremental level 0 datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00002 name=/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

input datafile file number=00003 name=/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

input datafile file number=00005 name=/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

channel ORA_DISK_3: starting piece 1 at 15-SEP-16

channel ORA_DISK_1: finished piece 1 at 15-SEP-16

piece handle=/t4_nfs/TTREC/6lrfsksp_1_1 tag=TAG20160915T160649 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_3: finished piece 1 at 15-SEP-16

piece handle=/t4_nfs/TTREC/6nrfsksp_1_1 tag=TAG20160915T160649 comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:01:45

channel ORA_DISK_2: finished piece 1 at 15-SEP-16

piece handle=/t4_nfs/TTREC/6mrfsksp_1_1 tag=TAG20160915T160649 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:05:05

Finished backup at 15-SEP-16

Starting Control File and SPFILE Autobackup at 15-SEP-16

piece handle=/data2/TTREC/autobackup/2016_09_15/o1_mf_s_922637514_cxngxc17_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 15-SEP-16

RMAN> exit

Recovery Manager complete.

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15 16:29:12 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table test1 (id number);

Table created.

SQL> insert into test1 values (333);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

----------

333

Lets make some structural change to your database, Here I am creating one tablespace.

SQL> create tablespace TESTB datafile size 50M;

Tablespace created.

SQL> select name from v$datafile ;

NAME

-----------------------------------------------------

/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf

/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

10 rows selected.

SQL> select name from v$controlfile;

NAME

---------------------------------

/data1/control1.ctl

/data2/control2.ctl

SQL>

SQL> alter system switch logfile;

System altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

Let lose your controlfiles-

[oracle@mydb01]:[TTREC1] $ rm -f /data1/control1.ctl /data2/control2.ctl

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15 16:31:12 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> create table test4 (id number);

Table created.

SQL> insert into test4 values (555);

1 row created.

SQL> commit;

Commit complete.

Even after losing all the controlfile database still functioning as normal. Isn’t strange – database supposed to crash right ? I am not very sure about this odd behaviour.

But when I tried to access v$ view ,it failed as its populating its information from controlfile.

SQL> select name from v$datafile ;

select name from v$datafile

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data1/control1.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> alter system switch logfile;

System altered.

SQL> exit

Lets see the alertlog file output.

[oracle@mydb01]:[TTREC1] $ cdtrace

[oracle@mydb01]:[TTREC1] $ tail -f alert_TTREC1.log

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Thu Sep 15 16:34:34 2016

Errors in file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_m000_24241.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data1/control1.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Lets crash your database forcefully –

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15 16:37:31 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut abort;

ORACLE instance shut down.

SQL>

SQL> startup;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 3712904 bytes

Variable Size 1996490872 bytes

Database Buffers 134217728 bytes

Redo Buffers 13062144 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

Lets restore your controlfile from RMAN auto backup.

[oracle@mydb01]:[TTREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 15 16:38:05 2016

connected to target database: TTREC (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 15-SEP-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=365 device type=DISK

recovery area destination: /data2

database name (or database unique name) used for search: TTREC

channel ORA_DISK_1: AUTOBACKUP /data2/TTREC/autobackup/2016_09_15/o1_mf_s_922637514_cxngxc17_.bkp found in the recovery area

AUTOBACKUP search with format "%F" not attempted because DBID was not set

channel ORA_DISK_1: restoring control file from AUTOBACKUP /data2/TTREC/autobackup/2016_09_15/o1_mf_s_922637514_cxngxc17_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/data1/control1.ctl

output file name=/data2/control2.ctl

Finished restore at 15-SEP-16

RMAN>

RMAN> alter database mount ;

Statement processed

released channel: ORA_DISK_1

Note:- No need to use recover database using backup controlfile clause as RMAN will automatically detects controlfile as a backup controlfile.

RMAN> recover database ;

Starting recover at 15-SEP-16

Starting implicit crosscheck backup at 15-SEP-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=365 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=398 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=431 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=464 device type=DISK

Crosschecked 17 objects

Crosschecked 12 objects

Finished implicit crosscheck backup at 15-SEP-16

Starting implicit crosscheck copy at 15-SEP-16

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

Finished implicit crosscheck copy at 15-SEP-16

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /data2/TTREC/autobackup/2016_09_15/o1_mf_s_922637514_cxngxc17_.bkp

File Name: /data2/TTREC/archivelog/2016_09_15/o1_mf_1_9_cxnj5qv1_.arc

File Name: /data2/TTREC/archivelog/2016_09_15/o1_mf_1_8_cxnj233x_.arc

File Name: /data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc

archived log for thread 1 with sequence 8 is already on disk as file /data2/TTREC/archivelog/2016_09_15/o1_mf_1_8_cxnj233x_.arc

archived log for thread 1 with sequence 9 is already on disk as file /data2/TTREC/archivelog/2016_09_15/o1_mf_1_9_cxnj5qv1_.arc

archived log for thread 1 with sequence 10 is already on disk as file /data1/TTREC/onlinelog/o1_mf_1_cwzoob3m_.log

archived log file name=/data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc thread=1 sequence=7

creating datafile file number=10 name=/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

archived log file name=/data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc thread=1 sequence=7

archived log file name=/data2/TTREC/archivelog/2016_09_15/o1_mf_1_8_cxnj233x_.arc thread=1 sequence=8

archived log file name=/data2/TTREC/archivelog/2016_09_15/o1_mf_1_9_cxnj5qv1_.arc thread=1 sequence=9

archived log file name=/data1/TTREC/onlinelog/o1_mf_1_cwzoob3m_.log thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 15-SEP-16

RMAN> alter database open resetlogs;

Statement processed

RMAN> select * from test1;

----------

333

RMAN> select * from test4;

----------

555

RMAN> exit

Recovery Manager complete.

Lets inspect the alertlog file- you could see RMAN internally doing everything for you.

[oracle@mydb01]:[TTREC1] $ cdtrace

Here is the alertlog output during RMAN recovery

Thu Sep 15 16:38:33 2016

Parallel Media Recovery started with 24 slaves

ORA-279 signalled during: alter database recover if needed

start until cancel using backup controlfile

...

alter database recover logfile '/data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc'

Thu Sep 15 16:38:33 2016

Media Recovery Log /data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc

Thu Sep 15 16:38:33 2016

File #10 added to control file as 'UNNAMED00010'. Originally created as:

'/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf'

Thu Sep 15 16:38:33 2016

Media Recovery failed with error 1244

Recovery interrupted!

Thu Sep 15 16:38:33 2016

Errors in file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_pr00_30602.trc:

ORA-00283: recovery session canceled due to errors

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

ORA-01110: data file 10: '/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf'

ORA-283 signalled during: alter database recover logfile '/data2/TTREC/archivelog/2016_09_15/o1_mf_1_7_cxnj0wll_.arc'...

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list 10

Completed: alter database recover datafile list 10

alter database recover datafile list

1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9

Completed: alter database recover datafile list

1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9

alter database recover if needed

start until cancel using backup controlfile

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15 16:48:26 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME

--------------------------------------------------------

/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf

/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

10 rows selected.

SQL>

SQL> select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='TESTB';

TABLESPACE_NAME

------------------------------

TESTB

SQL>

Nothing but my learning and experience with Oracle..!

Pages

Thursday, September 15, 2016

Recovering database from the loss of all controlfile from RMAN backup.

Popular Posts