Oracle migration to ASM disk

1, Login +asm instance to see if ASM disk is normal

[Email protected] ~]$ export oracle_sid=+asm
[Email protected] ~]$ Sqlplus/as SYSDBA

Sql> select Name,state from V$asm_diskgroup;
NAME State
---------------           -----------
DG1 Mounted

2. Login Database Prod
[Email protected] ~]$ export Oracle_sid=prod
[Email protected] ~]$ Sqlplus/as SYSDBA

A. View Control files
Sql> Show parameter control_files;

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
Control_files string
/u01/app/oracle/oradata/prod/c
Ontrol01.ctl,/u01/app/oracle/
Oradata/prod/control02.ctl,/U
01/app/oracle/oradata/prod/con
Trol03.ctl
Sql> select name from V$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl



B. View Db_create_file_dest parameters
Sql> Show parameter db_create_file_dest;

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
Db_create_file_dest string


C, modify the location of the control file

Sql> alter system set control_files= ' +DG1 ' scope=spfile;

System altered.

Sql> alter system set db_create_file_dest= ' +DG1 ' scope=spfile;

System altered.


D. Close the database

sql> shutdown immediate;

3. Login Rman

[Email protected] ~]$ export Oracle_sid=prod
[[email protected] ~]$ Rman target/

A. Using Rman to migrate target database control files and data files

rman> startup Nomount;

B. Using Rman to reconstruct the control file on the file system to the DG1 of the ASM disk
rman> restore Controlfile from '/u01/app/oracle/oradata/prod/control01.ctl ';

C. Use Rman to copy database files to ASM disk Group DG1
Rman> ALTER DATABASE Mount;
Rman> run{
Allocate channel C1 type disk;
Allocate channel C2 type disk;
Allocate channel C3 type disk;
Allocate channel C4 type disk;
Backup as Copy database format ' +DG1 ';
}

D. Use the Rman Switch command to modify the pointer to the data file within the control file to point to the new location
rman> switch database to copy;
rman> Recover database;
rman> ALTER DATABASE open;


4. Login Database Prod
[Email protected] ~]$ export Oracle_sid=prod
[Email protected] ~]$ Sqlplus/as SYSDBA

Sql> select name from V$controlfile;

NAME
--------------------------------------------------------------------------------
+dg1/prod/controlfile/backup.271.842150617

Sql> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
+dg1/prod/datafile/system.272.842151185
+dg1/prod/datafile/undotbs1.275.842151191
+dg1/prod/datafile/sysaux.273.842151185
+dg1/prod/datafile/users.276.842151211
+dg1/prod/datafile/example.274.842151187

A. Migrating temp files
Sql> select name,status,enabled from V$tempfile;

NAME
--------------------------------------------------------------------------------
STATUS ENABLED
--------------------- ------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
ONLINE READ WRITE

Because the temp file does not have the data available just to cache the data, temp can add a new temp file directly and then delete the old temp file
sql> Alter tablespace temp add tempfile ' +DG1 ';

Tablespace altered.

sql> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/prod/temp01.dbf ';

Tablespace altered.


Sql> select name from V$tempfile;

NAME
--------------------------------------------------------------------------------
+dg1/prod/tempfile/temp.279.842151759

B. Migrate log files, create new log files in DG1, and then delete old files

Sql> select Group#,member from V$logfile;

group#
----------
MEMBER
--------------------------------------------------------------------------------
3
/u01/app/oracle/oradata/prod/redo03.log

2
/u01/app/oracle/oradata/prod/redo02.log

1
/u01/app/oracle/oradata/prod/redo01.log




sql> ALTER DATABASE Add logfile ' +dg1 ' size 51m;

Database altered.

sql> ALTER DATABASE Add logfile ' +dg1 ' size 51m;

Database altered.

sql> ALTER DATABASE Add logfile ' +dg1 ' size 51m;

Database altered.


A log file group status of inactive is required for deletion, but cannot be deleted, stating that the log filegroup is not inactive state

Sql> select Group#,status from V$log;

group# STATUS
---------- ------------------------------------------------
1 INACTIVE
2 Current
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
sql> ALTER DATABASE drop logfile '/u01/app/oracle/oradata/prod/redo01.log ';

Database altered.

sql> ALTER DATABASE drop logfile '/u01/app/oracle/oradata/prod/redo02.log ';
ALTER DATABASE drop logfile '/u01/app/oracle/oradata/prod/redo02.log '
*
ERROR at line 1:
Ora-01623:log 2 is the current log for instance prod (thread 1)-Cannot drop
Ora-00312:online Log 2 thread 1: '/u01/app/oracle/oradata/prod/redo02.log '


sql> ALTER DATABASE drop logfile '/u01/app/oracle/oradata/prod/redo03.log ';

Database altered.



If you do not delete the log file status using the following command, you know you can delete
sql> alter system switch logfile;
sql> ALTER DATABASE drop logfile '/u01/app/oracle/oradata/prod/redo02.log ';

Database altered.

viewing log files
Sql> Select member from V$logfile;

MEMBER
--------------------------------------------------------------------------------
+dg1/prod/onlinelog/group_4.280.842152057
+dg1/prod/onlinelog/group_5.281.842152065
+dg1/prod/onlinelog/group_6.282.842152075


View the migrated files
Sql> select name from v$datafile Union select name from v$tempfile Union select name from V$controlfile Union select Me Mber from V$logfile;

NAME
--------------------------------------------------------------------------------
+dg1/prod/controlfile/backup.271.842150617
+dg1/prod/datafile/example.274.842151187
+dg1/prod/datafile/sysaux.273.842151185
+dg1/prod/datafile/system.272.842151185
+dg1/prod/datafile/undotbs1.275.842151191
+dg1/prod/datafile/users.276.842151211
+dg1/prod/onlinelog/group_4.280.842152057
+dg1/prod/onlinelog/group_5.281.842152065
+dg1/prod/onlinelog/group_6.282.842152075
+dg1/prod/tempfile/temp.279.842151759


Final migration of initialization parameter file
Sql> create Pfile from SPFile;

File created.

sql> create spfile= ' +DG1 ' from pfile;

File created.



Restart the database to see if it starts properly

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> Startup
ORACLE instance started.

Total System Global area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 71305220 bytes
Database buffers 134217728 bytes
Redo buffers 2973696 bytes
Database mounted.
Database opened.

View the migrated files
Sql> select name from v$datafile Union select name from v$tempfile Union select name from V$controlfile Union select Me Mber from V$logfile;

NAME
--------------------------------------------------------------------------------
+dg1/prod/controlfile/backup.271.842150617
+dg1/prod/datafile/example.274.842151187
+dg1/prod/datafile/sysaux.273.842151185
+dg1/prod/datafile/system.272.842151185
+dg1/prod/datafile/undotbs1.275.842151191
+dg1/prod/datafile/users.276.842151211
+dg1/prod/onlinelog/group_4.280.842152057
+dg1/prod/onlinelog/group_5.281.842152065
+dg1/prod/onlinelog/group_6.282.842152075
+dg1/prod/tempfile/temp.279.842151759

Oracle migration to ASM disk