Wednesday, November 12, 2008

Mutiplex Controlfiles on ASM diskgroup

How can I multiplex my controlfile that is on ASM diskgroup? I use ASM on 10gR2.

I use rman autobackup controlfile to solve this case, But I have to startup database with resetlogs.

If USE RAC, Need to start database one node.

And I need to use pfile (assume /tmp/pfile), that easily to modify for testing.

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl

$ export ORACLE_SID=db1
$ sqlplus / as sysdba

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA1/db/spfiledb.ora

SQL> create pfile='/tmp/pfile' from spfile;

$ grep control_files /tmp/pfile
*.control_files='+DATA1/db/control01.ctl'

SQL> shutdown

SQL> startup mount pfile='/tmp/pfile';

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl


I need DBID value and autobackup controlfile:

$ rman target /

connected to target database: DB (DBID=2422235186, not open)
RMAN> backup current controlfile;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F';
new RMAN configuration parameters are successfully stored

RMAN> backup current controlfile;
Starting backup at 12-NOV-08
.
.
.

Starting Control File and SPFILE Autobackup at 12-NOV-08
piece handle=/tmp/c-2422235186-20081112-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-08

RMAN> shutdown immediate;

database dismounted
Oracle instance shut down
DBID=2422235186
AUTOBACKUP CONTROLFILE=/tmp/c-2422235186-20081112-00

Modified control_files parameter on /tmp/pfile file:
*.control_files='+DATA1/db/control01.ctl','+DATA1/db/control04.ctl'

After that begin to recovery controlfile (startup nomount mode)

$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/pfile';

use rman =>

$ rman target /
connected to target database: db (not mounted)
RMAN> SET DBID 2422235186

RMAN> run {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F';
3> RESTORE CONTROLFILE FROM AUTOBACKUP;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 12-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=db1 devtype=DISK

.
.
.
database name (or database unique name) used for search: DB
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20081112
channel ORA_DISK_1: autobackup found: /tmp/c-2422235186-20081112-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+DATA1/db/control01.ctl
output filename=+DATA1/db/control04.ctl
Finished restore at 12-NOV-08

$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

SQL> alter database open ;
alter database open
*

ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;
Database altered.

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl, +DATA1/db/control04.ctl

Now I have multiplex controlfiles(+DATA1/db/control01.ctl,+DATA1/db/control04.ctl) on ASM. I can create spfile from pfile (/tmp/pfile file) and stop/start anyway.

SQL> create spfile='+DATA1/db/spfiledb.ora' from pfile='/tmp/pfile'

**** However, If we don't need to use this idea, we may restore controlfile backup to new file and then change control_files parameter.
Or ... use asmca for help (copy file in ASM Disk Group)

Some Idea:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.277.741286407

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

RMAN> startup nomount;

Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2229080 bytes
Variable Size 390073512 bytes
Database Buffers 226492416 bytes
Redo Buffers 7532544 bytes

RMAN> restore controlfile to '+DATA' from '+DATA/orcl/controlfile/current.277.741286407';

Starting restore at 25-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl_2 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 25-APR-11

ASMCMD> ls +DATA/orcl/CONTROLFILE/
Current.277.741286407
current.285.749426511

SQL> alter system set control_files='+DATA/orcl/controlfile/current.277.741286407','+DATA/orcl/controlfile/current.285.749426511' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 390073512 bytes
Database Buffers 226492416 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.277.741286407, +DATA/orcl/controlfile/current.285.749426511

1 comment:

Anonymous said...

why not use rman 'convert'?