Tuesday, May 13, 2008

Moving SPFILE and CONTROLFILE from cooked FileSystem to ASM

You have a standby database (or any database for that matter) and it uses a pfile sitting under a non-standard directory (like your home directory). Ditto for controlfile. Now you plan to move the pfile to an spfile (under ASM) and the controlfile from your cooked filesystem to ASM. It seems rather easy, but I found something rather interesting while doing this exercise.

First, I used the standard syntax for creating the spfile --
PWPRDDR1 > create spfile='+PWPRD_DATA' from pfile;
File created.


All is well, and I could see a new spfile created under ASM.
ASMCMD [+PWPRD_DATA/PWPRDDR] > ls -lt PARAMETERFILE/
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAY 13 13:00:00 Y spfile.325.654606981


I also created an alias for this spfile.
ASMCMD [+PWPRD_DATA/PWPRDDR] > mkalias +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.326.65460698 spfilePWPRDDR.ora

And reference this alias in the init file under dbs directory.
echo "spfile='+PWPRD_DATA/PWPRDDR/spfilePWPRDDR.ora'" > $ORACLE_HOME/dbs/initPWPRDDR1.ora
(notice I'm doing this for a RAC standby)

Finally, I tried starting the database in nomount mode --
PWPRDDR1 > startup nomount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.325.654601887'
ORA-17503: ksfdopn:2 Failed to open file +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.325.654601887
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887
ORA-17503: ksfdopn:2 Failed to open file +PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887
ORA-15012: ASM file '+PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887' does not exist
ORA-06512: at line 4



Oops! what went wrong? I spent some time redoing the steps but every time it resulted in the same error.
Well, the mistake I was doing was that the database was started using a pfile sitting under non-standard location (my work area folder). The syntax for spfile creation needs to know that.

The simple fix therefore was:
1) I restarted the database (mount mode).
PWPRDDR1 > startup nomount pfile=/home/pwprd/ravi/initPWPRDDR.ora

2) Created an spfile using the following syntax.
PWPRDDR1 > create spfile='+PWPRD_DATA' from pfile='/home/pwprd/ravi/initPWPRDDR.ora';
File created.


3) Removed and (re)created the alias in ASM (asmcmd) pointing to the new spfile.
ASMCMD [+PWPRD_DATA/PWPRDDR] > rmalias spfilePWPRDDR.ora
ASMCMD [+PWPRD_DATA/PWPRDDR] > mkalias +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.326.654614871 spfilePWPRDDR.ora


4) Finally started the instance (without using the pfile this time).
PWPRDDR1 > startup nomount


The next task is to move the controlfile into ASM. This was rather straight -- My current controlfile is sitting under /home/pwprd/ravi.
PWPRDDR1 > show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/pwprd/ravi/stby_PWPRD.ctl


From a different session, while the database (standby) is still mounted, I create a backup of the controlfile using RMAN.
RMAN> copy current controlfile to '+PWPRD_DATA/PWPRDDR/CONTROLFILE/control01.ctl';
Starting backup at 13-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+PWPRD_DATA/pwprddr/controlfile/control01.ctl tag=TAG20080513T140358 recid=138 stamp=654617041
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-MAY-08

[Note: You may also use DBMS_FILE_TRANSFER.COPY_FILE procedure to copy files. For doing that, you'd need to create 2 directory objects and copy the file. For example, to copy the above controlfile -
SQL > create directory D1 as '/home/pwprd/ravi';
SQL > create directory D2 as '
+PWPRD_DATA/PWPRDDR/CONTROLFILE';
SQL > exec dbms_file_transfer.copy_file('D1','
stby_PWPRD.ctl','D2', 'control01.ctl');
]

I can now see the backup and an alias created to that inside ASM.
ASMCMD [+PWPRD_DATA/PWPRDDR/CONTROLFILE] > ls -lt
Type Redund Striped Time Sys Name
N control01.ctl => +PWPRD_DATA/PWPRDDR/CONTROLFILE/Backup.324.654617039
CONTROLFILE UNPROT FINE MAY 13 14:00:00 Y Backup.324.654617039
ASMCMD [+PWPRD_DATA/PWPRDDR/CONTROLFILE] >


I change the control_files parameter in the spfile.
ALTER SYSTEM SET control_files='+PWPRD_DATA/PWPRDDR/CONTROLFILE/control01.ctl' SCOPE=SPFILE;

And simply bounce the instance. Indeed, that was rather straight.

No comments: