Skip to main content

Non ASM to ASM migration steps for Standlone database

prerequisite step:

1. Need to install and configure the GRID on the standalone server.

2. First check the locations of Parameter file, Control file, Data file, Temp file and Redo Log files. This is important piece of information as we move database files to ASM.

show parameter pfile;
select name from v$controlfile;
select name from v$datafile;
select name from v$tempfile;
select member from v$logfile;

3.Take backup of spfile using RMAN and restore it to ASM disk group.

rman target /

backup as backupset spfile;

restore spfile to '+DATA/spfileoradb.ora';


4.Rename old spfile as it no longer required

mv $ORACLE_HOME/dbs/spfileoradb.ora $ORACLE_HOME/dbs/spfileoradb.ora_old

5.Create new pfile which points to the spfile on ASM

vi $ORACLE_HOME/dbs/initoradb.ora

spfile='+DATA/spfileoradb.ora'

6.Bounce the database and check if it’s showing new location for spfile

shut immediate

startup nomount;

show Parameter pfile;

7. As our database is already in no-mount mode, we shall connect to RMAN to copy controlfiles from filesystem to ASM

rman target/

restore controlfile to '+DATA' from '/u01/app/oracle/oradata/oradb/control01.ctl';

restore controlfile to '+RECV' from '/u01/app/oracle/FRA/oradb/control02.ctl';


8.Find new controlfiles locations from asmcmd and update inside database

alter system set control_files='+DATA/CDB1/CONTROL/control01.ctl','+RECV/CDB1/CONTROL/control02.ctl' scope=spfile;

9.Bounce the database and put in mount mode. Check if database is showing new location for control files

shut immediate;

startup mount;

show parameter control_files

Controlfile migration to ASM done!


10.Let us move datafiles from filesystem to ASM. As our database is in mount mode, we can go ahead and use RMAN to copy datafiles to ASM


rman target/

backup as copy database format '+DATA';


11. witch the database to the data files on ASM

switch database to copy;


12. Migrate Temp File to ASM

Use below run code to migrate temp file to ASM

RMAN>run

{

set newname for tempfile '/u01/app/oracle/oradata/oradb/temp01.dbf' to '+DATA';

switch tempfile all;

}

Open the database and check new locations for datafiles and tempfile


alter database open;


select name from v$datafile;

select name from v$tempfile;



13.Migrate Redo Log Files to ASM


The simple way is to add new log members on ASM to every Redo Log group and drop the log members on disk. Let’s check available logfile and their path and status


SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;



Add log members at new location, in ASM


alter database add logfile member '+DATA' to group 1;

alter database add logfile member '+DATA' to group 2;

alter database add logfile member '+DATA' to group 3;

alter database add logfile member '+RECV' to group 1;

alter database add logfile member '+RECV' to group 2;

alter database add logfile member '+RECV' to group 3;

Check if logfile are added to new location


SELECT a.group#, b.member, a.status FROM v$log a, V$logfile b WHERE a. group#=b.group#;


Let’s drop old logfiles from file system. To drop logfile member, Status should not be in current mode


SQL> ALTER DATABASE DROP LOGFILE MEMBER /u01/app/oracle/oradb/redo03.log';


SQL> alter system switch logfile;


SQL> ALTER DATABASE DROP LOGFILE MEMBER /u01/app/oracle/oradb/redo01.log';


SQL> alter system switch logfile;


SQL> ALTER DATABASE DROP LOGFILE MEMBER /u01/app/oracle/oradb/redo02.log';

Verify if redo log members are migrated to ASM


SQL> SELECT a.group#,b. member, a.status FROM v$log a , V$logfile b WHERE a.group#=b.group#;

Redo log file are migrated to ASM!


-- Re-start application services

Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more