Step by Step Migrate Database from Non-ASM to ASM in Oracle

Step by Step Migrate Database from Non-ASM to ASM in Oracle


This article is a continuation of a recent article where we have demonstrated Offline Restore or Clone Database on the same server or rename database in Oracle click here to go through it. In this article, we will perform a Step by Step Migrate Database from Non-ASM to ASM in Oracle. There are various methods (RMAN, Online datafiles move 12c onwards, ASMCMD, DBMS_FILE_TRANSFER, etc) to achieve migrate databases from Non-ASM to ASM. Here we will use two types of RMAN mix methods to migrate local filesystems storage (Non-ASM) to ASM storage. Below are database details:

COMPONENTSSOURCETARGET
INSTANCE TYPEHASHAS
DATABASELABDB05 LABDB05
STORAGE/oradata/labdb05+DATA1 / +DATA1/oradata/labdb05
VERSION12.2.0.1.0 12.2.0.1.0
OSLinux 7Linux 7


Method 1


1.1: Validate the database size. Click here to get the SQL command to check the size of DATABASE.

SQL> DATABASE_SIZE.sql

DATED     HOST_NAME            INSTANCE_NAME    VERSION           TOTAL_ALLOCATED_GB    USED_GB    FREE_GB
--------- -------------------- ---------------- ----------------- ------------------ ---------- ----------
28-JUL-21 dbsgurun3.com         labdb05         12.2.0.1.0        5.75593302         4.8397162 .91621682 


1.2 Capture all physical files. Click here to get the SQL command for How to find all physicals files in Oracle.

SQL> @Physical_Files.sql

NAME
-------------------------------------------------------------------------------------------
/oradata/labdb05/tbls01.dbf
/oradata/labdb05/tbls02.dbf
/oradata/labdb05/tbls03.dbf
/oradata/labdb05/tbls04.dbf
/oradata/labdb05/control01.ctl
/oradata/labdb05/control02.ctl
/oradata/labdb05/redo01.log
/oradata/labdb05/redo02.log
/oradata/labdb05/redo03.log
/oradata/labdb05/sysaux01.dbf
/oradata/labdb05/system01.dbf
/oradata/labdb05/temp_01.dbf
/oradata/labdb05/temp_02.dbf
/oradata/labdb05/undotbs01.dbf
/oradata/labdb05/users01.dbf

15 rows selected.


1.3 Backup of SPFILE.

SQL> create pfile='/home/oracle/initlabdb05_before.ora' from spfile;

File created.


1.4: Validate available free size of target ASM DISK GROUP. Make sure you have enough free space in DG as require to migrate the database. Click here to get the SQL command for How to check ASM diskgroup Size.

SQL> @Disk_Group_Size.sql

NAME                             TOTAL_GB    FREE_GB    USED_GB   FREE_PER
------------------------------ ---------- ---------- ---------- ----------
DATA1                           31657.9961 4369.72168 27288.2744 13.8029004
OCR                            8.44433594 7.89257813 .551757813 93.4659419
ORAARCH                        404.601563 404.313477 .288085938 99.9287976
REDO                           1511.53613 1446.42969 65.1064453 95.6926967


1.5: Blackout in OEM, disable jobs in DBMS_scheduler/cronjobs, stop dependent applications, etc as applicable if any.
Click here to know about Target Blackouts ENABLE/DISABLE in OEM 13c.

1.6: Copy CONTROLFILES to new location DG +DATA1 using RMAN. Click here to know about Multiplexing of Control Files Using RMAN, SPFILE & PFILE in Oracle.

1.6.A: Shutdown the database and start in NOMOUNT state.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size                  8622000 bytes
Variable Size            1493172304 bytes
Database Buffers          369098752 bytes
Redo Buffers                8155136 bytes


1.6.B: Connect to RMAN session and restore CONTROLFILES to DG +DATA1 using any one existing CONTROLFILE.

RMAN> restore controlfile to '+DATA1' from '/oradata/labdb05/control01.ctl';


Note: Repeat step to a multiplex of CONTROLFILE to other DG or same DG.

1.6.C: Identify the new name of CONTROLFILES either connect to ASM instance using ASMCMD or refer to new name output on RMAN session while restoring.

ASMCMD> find --type CONTROLFILE +DATA1 *
+DATA1/LABDB05/CONTROLFILE/Current.289.938012955
+DATA1/LABDB05/CONTROLFILE/Current.290.938012955



1.6.D: Modify the new name of CONTROLFILE either in PFILE or SPFILE and start the database in MOUNT state. Here we are performing in SPFILE.

SQL> alter system set control_files= '+DATA1/LABDB05/CONTROLFILE/Current.289.938012955', '+DATA1/LABDB05/CONTROLFILE/Current.290.938012955' scope=spfile;

System altered.
SQL> ALTER DATABASE MOUNT;

Database mounted.
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE
--------- --------------------
LABDB05  MOUNTED
SQL> SHOW PARAMETER CONTROL_FILES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA1/LABDB05/CONTROLFILE/Current.289.938012955
                                                           +DATA1/LABDB05/CONTROLFILE/Current.290.938012955


1.7: Connect to RMAN session, copy datafiles from Non-ASM to ASM location.

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> 
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA1';


1.8: Rename datafiles connected to RMAN session.

RMAN> SWITCH DATABASE TO COPY;


1.9: Switch all tempfiles to ASM DG +DATA1.

RMAN> run
{
set newname for tempfile '/oradata/labdb05/temp_01.dbf' to '+DATA1';
set newname for tempfile '/oradata/labdb05/temp_02.dbf' to '+DATA1';
switch tempfile all;
}


1.10. Now OPEN database in READ/WRITE mode.

SQL> ALTER DATABASE OPEN;

Database altered.


1.11: Add new REDOLOG member to DG +DATA1 and drop old MEMBER.

SQL> SELECT A.GROUP#, B.MEMBER, A.STATUS FROM V$LOG A, V$LOGFILE B WHERE A.GROUP#=B.GROUP#;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 3;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oradata/labdb05/redo01.log';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oradata/labdb05/redo02.log';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oradata/labdb05/redo03.log';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT A.GROUP#, B.MEMBER, A.STATUS FROM V$LOG A, V$LOGFILE B WHERE A.GROUP#=B.GROUP#;


Note: To drop redolog MEMBER/GROUP, status with INACTIVE only can be dropped and if required perform log switch to make it INACTIVE. Click here to know in detail about ADD-DROP Redo log files in Oracle Database.


1.12: Register database in a server control, applicable only if it was not done earlier.

[oracle@DBsGuruN3 dbs]$ srvctl add database -d labdb05 -n labdb05  -o /u01/app/oracle/product/12.2.0.1/db_2 -p /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
[oracle@DBsGuruN3 dbs]$ srvctl config database -d labdb05
Database unique name: labdb05
Database name: labdb05
Oracle home: /u01/app/oracle/product/12.2.0.1/db_2
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA1
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: labdb05
[oracle@DBsGuruN3 dbs]$


1.13: Stop the database and start using SRVCTL.

SQL> SHU IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@DBsGuruN3 dbs]$ srvctl start  database -d labdb05
[oracle@DBsGuruN3 dbs]$ srvctl status  database -d labdb05
Database is running.


1.14: Validate all physical files after migration from Non-ASM to ASM storage.
Click here to get the SQL command for How to find all physicals files in Oracle.

SQL> @Physical_Files.sql

+DATA1/LABDB05/CONTROLFILE/Current.289.938012955
+DATA1/LABDB05/CONTROLFILE/Current.290.938012955
+DATA1/LABDB05/DATAFILE/TBLS01.287.938012897
+DATA1/LABDB05/DATAFILE/TBLS02.285.938180911
+DATA1/LABDB05/DATAFILE/TBLS03.288.938012897
+DATA1/LABDB05/DATAFILE/TBLS04.286.938180915
+DATA1/LABDB05/DATAFILE/SYSAUX.282.938012823
+DATA1/LABDB05/DATAFILE/SYSTEM.281.938012823  
+DATA1/LABDB05/DATAFILE/UNDOTBS1.283.938012899
+DATA1/LABDB05/DATAFILE/USERS.284.938012897
+DATA1/LABDB05/TEMPFILE/TEMP_NEW.294.938012985
+DATA1/LABDB05/TEMPFILE/TEMP_NEW.295.938012987
+DATA1/LABDB05/ONLINELOG/group_1.291.938012963
+DATA1/LABDB05/ONLINELOG/group_2.292.938012963
+DATA1/LABDB05/ONLINELOG/group_3.293.938012963

15 rows selected.


1.15: Finally delete Blackout in OEM, enable jobs DBMS_scheduler/cronjobs, start dependent applications, etc as applicable if any. Click here to know about Target Blackouts ENABLE/DISABLE in OEM 13c.


Method 2


2.1: Validate the database size. Click here to get the SQL command to check the size of DATABASE.

SQL> DATABASE_SIZE.sql

DATED     HOST_NAME            INSTANCE_NAME    VERSION           TOTAL_ALLOCATED_GB    USED_GB    FREE_GB
--------- -------------------- ---------------- ----------------- ------------------ ---------- ----------
28-JUL-21 dbsgurun3.com         labdb05         12.2.0.1.0        5.75593302         4.8397162 .91621682 


2.2 Capture all physical files. Click here to get the SQL command for How to find all physicals files in Oracle.

SQL> @Physical_Files.sql

NAME
-------------------------------------------------------------------------------------------
/oradata/labdb05/tbls01.dbf
/oradata/labdb05/tbls02.dbf
/oradata/labdb05/tbls03.dbf
/oradata/labdb05/tbls04.dbf
/oradata/labdb05/control01.ctl
/oradata/labdb05/control02.ctl
/oradata/labdb05/redo01.log
/oradata/labdb05/redo02.log
/oradata/labdb05/redo03.log
/oradata/labdb05/sysaux01.dbf
/oradata/labdb05/system01.dbf
/oradata/labdb05/temp_01.dbf
/oradata/labdb05/temp_02.dbf
/oradata/labdb05/undotbs01.dbf
/oradata/labdb05/users01.dbf

15 rows selected.


2.3 Backup of SPFILE and CONTROLFILE (text format).

SQL> create pfile='/home/oracle/initlabdb05_before.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace as '/home/oracle/labdb05_controlfile.sql';

Database altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> !ls -lrt /home/oracle/initlabdb05_before.ora /home/oracle/labdb05_controlfile.sql
-rw-r--r-- 1 oracle dba 1346 Jul 28 22:32 /home/oracle/initlabdb05_before.ora
-rw-r--r-- 1 oracle dba 6284 Jul 28 22:32 /home/oracle/labdb05_controlfile.sql


2.4: Create a directory.

[oracle@DBsGuruN3 ~]$ . setenv +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN3 ~]$ asmcmd
ASMCMD> cd +DATA1/oradata/
ASMCMD> mkdir labdb05
ASMCMD> cd labdb05
ASMCMD> pwd
+DATA1/oradata/labdb05


Note: This step is completely optional step instead of it we can use DG name +DATA1 while copy datafiles in the RMAN session.


2.5: Blackout in OEM, disable jobs in DBMS_scheduler/cronjobs, stop dependent applications, etc as applicable if any.
Click here to know about Target Blackouts ENABLE/DISABLE in OEM 13c.


2.6: Stop database and start in MOUNT state.

SQL> SHU IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size                  8622000 bytes
Variable Size            1493172304 bytes
Database Buffers          369098752 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE
--------- --------------------
LABDB05   MOUNTED


2.7: Connect to RMAN session, copy datafiles from Non-ASM to ASM location.

[oracle@DBsGuruN3 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jul 28 22:43:30 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LABDB05 (DBID=4936628084, not open)

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> BACKUP AS COPY DB_FILE_NAME_CONVERT ('/oradata/labdb05','+DATA1/oradata/labdb05') database;

Starting backup at 28-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=820 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=983 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata/labdb05/sysaux01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00001 name=/oradata/labdb05/system01.dbf
output file name=+DATA1/oradata/labdb05/system01.dbf tag=TAG20210716T224736 RECID=1 STAMP=9380094894
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=/oradata/labdb05/undotbs01.dbf
output file name=+DATA1/oradata/labdb05/undotbs01.dbf tag=TAG20210716T224736 RECID=2 STAMP=9380094937
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_2: starting datafile copy
input datafile file number=00008 name=/oradata/labdb05/users01.dbf
output file name=+DATA1/oradata/labdb05/users01.dbf tag=TAG20210716T224736 RECID=3 STAMP=9380094955
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_2: starting datafile copy
input datafile file number=00005 name=/oradata/labdb05/tbls02.dbf
output file name=+DATA1/oradata/labdb05/sysaux01.dbf tag=TAG20210716T224736 RECID=4 STAMP=9380094964
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:48
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/oradata/labdb05/tbls04.dbf
output file name=+DATA1/oradata/labdb05/tbls04.dbf tag=TAG20210716T224736 RECID=6 STAMP=9380094971
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/labdb05/tbls01.dbf
output file name=+DATA1/oradata/labdb05/tbls02.dbf tag=TAG20210716T224736 RECID=5 STAMP=9380094969
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:10
channel ORA_DISK_2: starting datafile copy
input datafile file number=00006 name=/oradata/labdb05/tbls03.dbf
output file name=+DATA1/oradata/labdb05/tbls01.dbf tag=TAG20210716T224736 RECID=7 STAMP=9380094975
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
output file name=+DATA1/oradata/labdb05/tbls03.dbf tag=TAG20210716T224736 RECID=8 STAMP=9380094975
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-JUL-21

Starting Control File and SPFILE Autobackup at 28-JUL-21
piece handle=/u01/app/oracle/product/12.2.0.1/db_2/dbs/c-1755204861-20210728-00 comment=NONE
Finished Control File and SPFILE Autobackup at 28-JUL-21

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2


2.8: Prepare PFILE & CONTROLFILE, edit the same file which we have taken in Step 2.3.


2.8.A: Edit PFILE: Replace parameter value of control_files to the new name, pointing to ASM location.

[oracle@DBsGuruN3 oracle]$ cat initlabdb05.ora | grep control
*.control_files='+DATA1/oradata/labdb05/control01.ctl','+DATA1/oradata/labdb05/control02.ctl'


2.8.B: Edit CONTROLFILE: To prepare to rename all physical of the database, we will edit CONTROLFILE (text format) where we will replace the new path of all files which is pointing to ASM DG +DATA1 as used in step 2.7 while copy datafiles to DG i.e replace from /oradata/labdb05 to +DATA1/oradata/labdb05 followed by below important points.


=> Remove all lines in trace file till up to line STARTUP NOMOUNT i.e remove lines that are comments (line started “–“).
=> Choose wisely options ARCHIVE/NOARCHIVE while creating CONTROLFILE with option RESETLOGS.
=> Replace path of redolog, datafile & tempfile to the new location where we copied files to ASM storage.
=> Make sure file extension should be .sql in case want to execute as a script on SQL prompt.
=> Below is a sample file for reference.

[oracle@DBsGuruN3 oracle]$ cat labdb05_controlfile.sql
SET ECHO ON;
SET TIMING ON;

CREATE CONTROLFILE REUSE DATABASE "LABDB05" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2921
LOGFILE
  GROUP 1 '+DATA1/oradata/labdb05/redo01.log'  SIZE 50M,
  GROUP 2 '+DATA1/oradata/labdb05/redo02.log'  SIZE 50M,
  GROUP 3 '+DATA1/oradata/labdb05/redo03.log'  SIZE 50M
DATAFILE
  '+DATA1/oradata/labdb05/system01.dbf',
  '+DATA1/oradata/labdb05/sysaux01.dbf',
  '+DATA1/oradata/labdb05/undotbs01.dbf',
  '+DATA1/oradata/labdb05/tbls01.dbf',
  '+DATA1/oradata/labdb05/tbls02.dbf',
  '+DATA1/oradata/labdb05/tbls03.dbf',
  '+DATA1/oradata/labdb05/tbls04.dbf',
  '+DATA1/oradata/labdb05/users01.dbf'
CHARACTER SET AL32UTF8
;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_01.dbf'
     SIZE 62914560  REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_02.dbf'
     SIZE 62914560  REUSE AUTOEXTEND OFF;


2.9: Stop database and start in NOMOUNT state using PFILE.

SQL> SHU IMMEDIATE
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> STARTUP NOMOUNT PFILE='/home/oracle/initlabdb05.ora'
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size                  8622000 bytes
Variable Size            1174405200 bytes
Database Buffers          687865856 bytes
Redo Buffers                8155136 bytes
SQL> SHOW PARAMETER CONTROL_FILES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA1/oradata/labdb05/con
                                                 trol01.ctl, +DATA1/oradata/
                                                 labdb05/control02.ctl


2.10: Execute create CONTROLFILE script which is the substitute of rename files connected to RMAN session where we followed in method 1 step 1.7, 1.8 and 1.10.

SQL> @/home/oracle/labdb05_controlfile.sql
SQL> SET TIMING ON;
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "LABDB05" RESETLOGS     NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2921
  7  LOGFILE
  8    GROUP 1 '+DATA1/oradata/labdb05/redo01.log'  SIZE 50M,
  9    GROUP 2 '+DATA1/oradata/labdb05/redo02.log'  SIZE 50M,
 10    GROUP 3 '+DATA1/oradata/labdb05/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '+DATA1/oradata/labdb05/system01.dbf',
 13    '+DATA1/oradata/labdb05/sysaux01.dbf',
 14    '+DATA1/oradata/labdb05/undotbs01.dbf',
 15    '+DATA1/oradata/labdb05/tbls01.dbf',
 16    '+DATA1/oradata/labdb05/tbls02.dbf',
 17    '+DATA1/oradata/labdb05/tbls03.dbf',
 18    '+DATA1/oradata/labdb05/tbls04.dbf',
 19    '+DATA1/oradata/labdb05/users01.dbf'
 20  CHARACTER SET AL32UTF8
 21  ;

Control file created.

Elapsed: 00:00:00.71
SQL>
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Elapsed: 00:00:09.57
SQL>
SQL> ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_01.dbf'
  2       SIZE 62914560  REUSE AUTOEXTEND OFF;

Tablespace altered.

Elapsed: 00:00:00.49
SQL> ALTER TABLESPACE TEMP_NEW ADD TEMPFILE '+DATA1/oradata/labdb05/temp_02.dbf'
  2       SIZE 62914560  REUSE AUTOEXTEND OFF;

Tablespace altered.

Elapsed: 00:00:00.50


2.11: Validate all physical files after successful migration. Click here to get the SQL command for How to find all physicals files in Oracle.

SQL> @Physical_Files.sql

NAME
--------------------------------------------------------------------------------
+DATA1/oradata/labdb05/control01.ctl
+DATA1/oradata/labdb05/control02.ctl
+DATA1/oradata/labdb05/tbls01.dbf
+DATA1/oradata/labdb05/tbls02.dbf
+DATA1/oradata/labdb05/tbls03.dbf
+DATA1/oradata/labdb05/tbls04.dbf
+DATA1/oradata/labdb05/redo01.log
+DATA1/oradata/labdb05/redo02.log
+DATA1/oradata/labdb05/redo03.log
+DATA1/oradata/labdb05/sysaux01.dbf
+DATA1/oradata/labdb05/system01.dbf
+DATA1/oradata/labdb05/temp_01.dbf
+DATA1/oradata/labdb05/temp_02.dbf
+DATA1/oradata/labdb05/undotbs01.dbf
+DATA1/oradata/labdb05/users01.dbf

15 rows selected.


2.12: Create SPFILE using PFILE and reboot the database, optionally also create PFILE in the default location after reboot.

SQL> CREATE SPFILE FROM PFILE='/home/oracle/initlabdb05.ora';

File created.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size                  8622000 bytes
Variable Size            1174405200 bytes
Database Buffers          687865856 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER SPFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.2.0.1/db_2/
                                                 dbs/spfilelabdb05.ora

SQL> CREATE PFILE FROM SPFILE;

File created.


2.13: Register database in a server control, applicable only if it was not done earlier.

[oracle@DBsGuruN3 dbs]$ srvctl add database -d labdb05 -n labdb05  -o /u01/app/oracle/product/12.2.0.1/db_2 -p /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
[oracle@DBsGuruN3 dbs]$ srvctl config database -d labdb05
Database unique name: labdb05
Database name: labdb05
Oracle home: /u01/app/oracle/product/12.2.0.1/db_2
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.2.0.1/db_2/dbs/spfilelabdb05.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA1
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: labdb05
[oracle@DBsGuruN3 dbs]$


2.14: Stop the database and start using SRVCTL.

SQL> SHU IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@DBsGuruN3 dbs]$ srvctl start  database -d labdb05
[oracle@DBsGuruN3 dbs]$ srvctl status  database -d labdb05
Database is running.


2.15: Finally delete Blackout in OEM, enable jobs DBMS_scheduler/cronjobs, start dependent applications, etc as applicable if any.
Click here to know about Target Blackouts ENABLE/DISABLE in OEM 13c.


Click here for Offline Restore or Clone Database on the same server or rename database in Oracle
Click here for How to Rename Database and DBID using DBNEWID in Oracle

Click here for How to Rename Database using CONTROLFILE in Oracle


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

ASM | BACKUP | BACKUP AND RECOVERY | DATABASE | MIGRATION | ORACLE | RMAN


 112 Total Views,  1 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

thirteen − eight =

Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

Technical Links Powered by DBsGuru.

Thanks.
Team DBsGuru.