HEMESH ORAWORLD: Controlfile

Current Controlfie location (using Init.ora file here)

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ cat initTEST11G.ora

*.control_files='/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl'

Change to new location in ASM with random controlfile number, that will changed later by RMAN

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora

*.control_files='+DATA/TESTDB/controlfile/current.261.772293749','+DATA/TESTDB/controlfile/current.260.772293749'

Startup database with nomount

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:55:43 2013

Connected to an idle instance.

SYS@TESTDB >> startup nomount;

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2231088 bytes

Variable Size 2499806416 bytes

Database Buffers 822083584 bytes

Redo Buffers 16330752 bytes

SYS@TESTDB >> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connect Target db with RMAN and restore controlfile from original location

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jun 26 16:56:24 2013

connected to target database: TESTDB (not mounted)

RMAN> restore controlfile from '/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl';

Starting restore at 26-JUN-2013 16:56:34

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=132 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/TESTDB/controlfile/current.261.819132997

output file name=+DATA/TESTDB/controlfile/current.260.819132997

Finished restore at 26-JUN-2013 16:56:39

RMAN> exit

Recovery Manager complete.

Confirm location of new controlfile

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ s

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:57:04 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SYS@TESTDB >> alter database mount;

Database altered.

SYS@TESTDB >> show parameter controlfile

SYS@TESTDB >> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string +DATA/TESTDB/controlfile/cur

rent.261.819132997, +DATA/test

sits/controlfile/current.260.8

19132997

control_management_pack_access string DIAGNOSTIC+TUNING

SYS@TESTDB >> alter database open;

Database altered.

SYS@TESTDB >> exit

Modified Init.ora files with new controlfile location that created by RMAN , and restart the database.

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora

*.control_files='+DATA/TESTDB/controlfile/current.261.819132997','+DATA/TESTDB/controlfile/current.260.819132997'

SYS@TESTDB >> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@TESTDB >> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2231088 bytes

Variable Size 2499806416 bytes

Database Buffers 822083584 bytes

Redo Buffers 16330752 bytes

Database mounted.

Database opened.

Restore and Recover Controlfile ( Recovery Without Catalog database )

Last Controlfile Backup

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

44 Full 7.02M DISK 00:00:01 22-FEB-12

BP Key: 44 Status: AVAILABLE Compressed: NO Tag: TAG20120222T160900

Piece Name: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

Control File Included: Ckp SCN: 10880516 Ckp time: 22-FEB-12

Simulate Loss of Controlfile

[oracle@testbox:fwlive]$ pwd

/u01/app/oracle/oradata/fwlive

[oracle@testbox:fwlive]$ ls

control01.ctl control02.ctl control03.ctl indx.dbf paf_data.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

[oracle@testbox:fwlive]$ mkdir test

[oracle@testbox:fwlive]$ mv *.ctl test/

[oracle@testbox:fwlive]$ ls -lrt test/

total 21396

-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control03.ctl

-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control02.ctl

-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control01.ctl

[oracle@testbox:fwlive]$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 22 16:12:51 2012

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@fwlive >> select * from v$Database;

select * from v$Database

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/fwlive/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@fwlive >> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testbox:fwlive]$

[oracle@testbox:fwlive]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 22 16:13:10 2012

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06003: ORACLE error from target database:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/fwlive/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

[oracle@testbox:fwlive]$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 22 16:13:38 2012

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@fwlive >> shutdown abort;

ORACLE instance shut down.

SYS@fwlive >> exit

SYS@fwlive >> startup

ORACLE instance started.

Total System Global Area 851443712 bytes

Fixed Size 1270316 bytes

Variable Size 432016852 bytes

Database Buffers 415236096 bytes

Redo Buffers 2920448 bytes

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

SYS@fwlive >> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testbox:fwlive]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 22 16:16:24 2012

connected to target database: fwlive (not mounted)

RMAN> set DBID 2568429818;

executing command: SET DBID

RMAN> restore controlfile from autobackup; -- only works if your controlfile autobackup in flash recovery area

Otherwise

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp'; )

Starting restore at 22-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area

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

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u01/app/oracle/oradata/fwlive/control01.ctl

output filename=/u01/app/oracle/oradata/fwlive/control02.ctl

output filename=/u01/app/oracle/oradata/fwlive/control03.ctl

Finished restore at 22-FEB-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/22/2012 16:17:54

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/22/2012 16:18:00

ORA-01194: file 1 needs more recovery to be consistent

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

RMAN> recover database;

Starting recover at 22-FEB-12

Starting implicit crosscheck backup at 22-FEB-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 11 objects

Finished implicit crosscheck backup at 22-FEB-12

Starting implicit crosscheck copy at 22-FEB-12

using channel ORA_DISK_1

Crosschecked 14 objects

Finished implicit crosscheck copy at 22-FEB-12

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_7_7lj7nyfj_.arc

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_8_7lj7nznk_.arc

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_6_7lj7nzwb_.arc

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 43 is already on disk as file /u01/app/oracle/oradata/fwlive/redo01.log

archive log filename=/u01/app/oracle/oradata/fwlive/redo01.log thread=1 sequence=43

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

Finished recover at 22-FEB-12

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/22/2012 16:18:26

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>

HEMESH ORAWORLD

Wednesday, 26 June 2013

Move/Copy controlfile from file system to ASM

Thursday, 5 November 2009

Restore and Recover Controlfile