Duplicating a controlfile into ASM when Original controlfile is stored in ASM
============================================
******** Step By Step Procedure ********
#########################################
# 1) Database name & Disk Groups
#########################################
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
MYDB READ WRITE ARCHIVELOG
Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';
SQL> Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';
NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
MYDB_DATA 1167455 1144193
MYDB_RECO 102518 100036
#########################################
# 2) Control files in the database
#########################################
SQL> sho parameter control
NAME TYPE VALUE
------------------------------------ --------------------------------
control_files string +MYDB_RECO/MYDB/controlfile/current.258.798205861,
+MYDB_RECO/MYDB/controlfile/current.259.798205863
#########################################
# 3) Add New controlfile to spfile
#########################################
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ --------------------------------
spfile string +MYDB_DATA/MYDB/spfileMYDB.ora
If using spfile to start the instance:
Modify the spfile specifically the parameter control_files. In this example, a thrid controlfile is going to be created on different diskgroup MYDB_DATA.
alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863','+MYDB_DATA'
scope=spfile sid='*';
SQL> alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863','+MYDB_DATA'
scope=spfile sid='*';
System altered.
#########################################
# 4) Shutdown & Startup in Nomount
#########################################
Shutdown and Start the instance in NOMOUNT mode.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 4409401344 bytes
Fixed Size 2235528 bytes
Variable Size 2667578232 bytes
Database Buffers 1694498816 bytes
Redo Buffers 45088768 bytes
SQL>
#########################################
# 5) Duplicate Controlfile using RMAN
#########################################
restore controlfile from '+MYDB_RECO/MYDB/controlfile/current.258.798205861';
> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Nov 12 00:00:42 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (not mounted)
RMAN>
restore controlfile from '+MYDB_RECO/MYDB/controlfile/current.258.798205861';
RMAN> restore controlfile from '+MYDB_RECO/MYDB/controlfile/current.258.798205861';
Starting restore at 12-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=507 instance=MYDB_1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+MYDB_RECO/MYDB/controlfile/current.258.798205861
output file name=+MYDB_RECO/MYDB/controlfile/current.259.798205863
output file name=+MYDB_DATA/MYDB/controlfile/current.281.799113673
Finished restore at 12-NOV-12
RMAN>
Note that the command prints the name of the new created file: +MYDB_DATA/MYDB/controlfile/current.281.799113673
#########################################
# 6) Modify Controlfile parameter
#########################################
Modify the control_file parameter with the complete path of the new file:
SQL> sho parameter control
NAME TYPE VALUE
------------------------------------ --------------------------------
control_files string +MYDB_RECO/MYDB/controlfile/current.258.798205861,
+MYDB_RECO/MYDB/controlfile/current.259.798205863,
+MYDB_DATA
alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863',
'+MYDB_DATA/MYDB/controlfile/current.281.799113673' scope=spfile sid='*';
SQL> alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863',
'+MYDB_DATA/MYDB/controlfile/current.281.799113673' scope=spfile sid='*';
System altered.
SQL> sho parameter control
NAME TYPE VALUE
------------------------------------ --------------------------------
control_files string +MYDB_RECO/MYDB/controlfile/current.258.798205861,
+MYDB_RECO/MYDB/controlfile/current.259.798205863,
+MYDB_DATA/MYDB/controlfile/current.281.799113673
#########################################
# 7) Mount and Open Database
#########################################
Mount and Open the database
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
#########################################
# 8) Validate controlfiles
#########################################
Validate both controlfiles are present
select name from v$controlfile;
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------
+MYDB_RECO/MYDB/controlfile/current.258.798205861
+MYDB_RECO/MYDB/controlfile/current.259.798205863
+MYDB_DATA/MYDB/controlfile/current.281.799113673
SQL>
Next time instance are restarted, will pick all the three control files.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4409401344 bytes
Fixed Size 2235528 bytes
Variable Size 2667578232 bytes
Database Buffers 1694498816 bytes
Redo Buffers 45088768 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
MYDB READ WRITE ARCHIVELOG
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
spfile string +MYDB_DATA/MYDB/spfileMYDB.ora
SQL> sho parameter control
NAME TYPE VALUE
------------------------------------ --------------------------------
control_files string +MYDB_RECO/MYDB/controlfile/current.258.798205861,
+MYDB_RECO/MYDB/controlfile/current.259.798205863,
+MYDB_DATA/MYDB/controlfile/current.281.799113673
1 comment:
Good post !
Post a Comment