1- Write Non-ASM DBS Environment in .bash_file and Source
[oracle@oda12 ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
# for oracle
TMP=/tmp;
TMPDIR=/tmp;
ORACLE_BASE=/u01/app/oracle;
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1;
ORACLE_SID=orcl;
ORACLE_HOSTNAME=oda12.oracle.com;
ORACLE_UNQNAME=orcl;
TZ=Etc/GMT-5;
LD_LIBRARY_PATH=$ORACLE_PATH/lib:/lib:/usr/lib;
PATH=$ORACLE_HOME/bin:$PATH;
EDITOR=vi;
CLASS_PATH=$ORACLE_HOME/Jre;
TNS_ADMIN=/u01/app/oracle/product/12.1.0/db_1/network/admin;
alias
hmdb='source /home/oracle/hmdb.env'
export TNS_ADMIN TMP TMPDIR ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_HOSTNAME ORACLE_UNQNAME TZ PATH EDITOR LD_LIBRARY_PATH CLASS_PATH
export PATH
:wq!
[oracle@oda12 ~]$ source .bash_profile
[oracle@oda12 ~]$ hmdb
2- Check Achivelog Mode is Enable in Database
[oracle@oda12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 01:41:41 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set lines 250 pages 250
SQL> select INSTANCE_NAME,HOST_NAME,ARCHIVER,DATABASE_STATUS ,VERSION from v$instance;
INSTANCE_NAME HOST_NAME
ARCHIVE DATABASE_STATUS VERSION
----------------
---------------------------------------------------------------- -------
----------------- -----------------
hmdb oda12.oracle.com STOPPED ACTIVE 12.1.0.2.0
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Current log sequence 24
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ -----------
-----------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4560M
3- Enable Achivelog Mode in Database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1308622848 bytes
Fixed Size 2924064 bytes
Variable Size
452985312 bytes
Database Buffers
838860800 bytes
Redo Buffers
13852672 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Next log sequence to archive 24
Current log sequence 24
SQL>
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
4- Check and verify All Datafiles, redolog file,controlfile,tempfile with ASM Disk Groups etc..
SQL> select name from v$database;
NAME
---------
HMDB
SQL> col NAME format a30
SQL> col PATH format a30
SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME
STATE TOTAL_MB PATH
------------------------------ -------- ----------
------------------------------
DISK1 NORMAL 10239 ORCL:DISK1
DISK2 NORMAL 10239 ORCL:DISK2
DISK3 NORMAL 10239 ORCL:DISK3
DISK4 NORMAL 10239 ORCL:DISK4
DISK5 NORMAL 20479 ORCL:DISK5
DISK6 NORMAL 20479 ORCL:DISK6
6 rows selected.
[root@oda12 ~]# su - grid
Last login: Thu Oct 15 01:31:56 PKT 2020
[grid@oda12 ~]$ asmcmd
ASMCMD> lsdg
State
Type Rebal Sector
Block AU Total_MB
Free_MB Req_mir_free_MB Usable_file_MB Offline_disks
Voting_files Name
MOUNTED
NORMAL N 512
4096 1048576 20478
20352 0 10176 0 N
CRS/
MOUNTED
NORMAL N 512
4096 1048576 40958
36698 0 18349 0 N
DATA/
MOUNTED
NORMAL N 512
4096 1048576 20478
20012 0 10006 0 N
FRA/
ASMCMD> exit
[grid@oda12 ~]$ exit
logout
[root@oda12 ~]# su - oracle
[oracle@oda12 ~]$ hmdb
[oracle@oda12 ~]$
[oracle@oda12 ~]$ sqlplus / as sysdb
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 01:41:41 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> col NAME format a70
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/HMDB/datafile/o1_mf_system_hqh83991_.dbf
/u01/app/oracle/oradata/HMDB/datafile/o1_mf_sysaux_hqh81kwf_.dbf
/u01/app/oracle/oradata/HMDB/datafile/o1_mf_undotbs1_hqh85f2m_.dbf
/u01/app/oracle/oradata/HMDB/datafile/o1_mf_users_hqh85cxr_.dbf
SQL> col NAME format a75
SQL> select name from v$controlfile;
NAME
---------------------------------------------------------------------------
/u01/app/oracle/oradata/HMDB/controlfile/o1_mf_hqh86fch_.ctl
/u01/app/oracle/fast_recovery_area/HMDB/controlfile/o1_mf_hqh86fd7_.ctl
SQL> col member format a75
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------
/u01/app/oracle/oradata/HMDB/onlinelog/o1_mf_3_hqh86jjz_.log
/u01/app/oracle/fast_recovery_area/HMDB/onlinelog/o1_mf_3_hqh86jpb_.log
/u01/app/oracle/oradata/HMDB/onlinelog/o1_mf_2_hqh86j0q_.log
/u01/app/oracle/fast_recovery_area/HMDB/onlinelog/o1_mf_2_hqh86j7j_.log
/u01/app/oracle/oradata/HMDB/onlinelog/o1_mf_1_hqh86hkv_.log
/u01/app/oracle/fast_recovery_area/HMDB/onlinelog/o1_mf_1_hqh86hms_.log
6 rows selected.
SQL> show parameter control_files
NAME
TYPE VALUE
---------------------- ---------- ------------------------------
control_files string
/u01/app/oracle/oradata/HMDB/controlfile/o1_mf_hqh86fch_.ctl
, /u01/app/oracle/fast_recovery_area/HMDB/controlfile/o1_mf_hqh86fd7_.ctl
5- Define Disk group for controlfile, datafile, redologfile, tempfile with archivelog in Spfile from Sqlplus Prompt
SQL> ALTER SYSTEM SET control_files='+DATA' scope=spfile;
System altered.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_create_file_dest string /u01/app/oracle/oradata
SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' scope=spfile;
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4560M
SQL> ALTER SYSTEM SET db_recovery_file_dest='+FRA' scope=spfile;
System altered.
Here We Start Migration through RMAN Utility
6- Shutdown Database and Startup No-Mount Mode
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1308622848 bytes
Fixed Size 2924064 bytes
Variable Size
452985312 bytes
Database Buffers
838860800 bytes
Redo Buffers
13852672 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
7- Restore Controlfile in ASM through RMAN Utility and Startup Database in Mount Mode
[oracle@oda12 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 15 03:12:19 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: HMDB (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/oradata/HMDB/controlfile/o1_mf_hqh86fch_.ctl';
Starting restore at 15-OCT-20
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: copied control file copy
output file
name=+DATA/HMDB/CONTROLFILE/current.266.1053832453
Finished restore at 15-OCT-20
RMAN> exit
Recovery Manager complete.
[oracle@oda12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 03:14:54 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Automatic Storage Management,
OLAP, Advanced Analytics
and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
Database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>exit
8- Taking Backup as Copy in ASM Disk Group and Switch Database to Copy
[oracle@oda12 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 15 03:12:19 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: HMDB (not mounted)
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 15-OCT-20
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=250 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/u01/app/oracle/oradata/HMDB/datafile/o1_mf_system_hqh83991_.dbf
output file name=+DATA/HMDB/DATAFILE/system.267.1053832677
tag=TAG20201015T031757 RECID=1 STAMP=1053832766
channel ORA_DISK_1: datafile copy complete, elapsed time:
00:01:37
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/HMDB/datafile/o1_mf_sysaux_hqh81kwf_.dbf
output file
name=+DATA/HMDB/DATAFILE/sysaux.268.1053832775 tag=TAG20201015T031757 RECID=2
STAMP=1053832849
channel ORA_DISK_1: datafile copy complete, elapsed time:
00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/u01/app/oracle/oradata/HMDB/datafile/o1_mf_undotbs1_hqh85f2m_.dbf
output file
name=+DATA/HMDB/DATAFILE/undotbs1.269.1053832851 tag=TAG20201015T031757 RECID=3
STAMP=1053832865
channel ORA_DISK_1: datafile copy complete, elapsed time:
00:00:16
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=+DATA/HMDB/CONTROLFILE/backup.270.1053832869 tag=TAG20201015T031757
RECID=4 STAMP=1053832871
channel ORA_DISK_1: datafile copy complete, elapsed time:
00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006
name=/u01/app/oracle/oradata/HMDB/datafile/o1_mf_users_hqh85cxr_.dbf
output file name=+DATA/HMDB/DATAFILE/users.271.1053832873
tag=TAG20201015T031757 RECID=5 STAMP=1053832873
channel ORA_DISK_1: datafile copy complete, elapsed time:
00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 15-OCT-20
channel ORA_DISK_1: finished piece 1 at 15-OCT-20
piece
handle=+DATA/HMDB/BACKUPSET/2020_10_15/nnsnf0_tag20201015t031757_0.272.1053832875
tag=TAG20201015T031757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time:
00:00:01
Finished backup at 15-OCT-20
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy
"+DATA/HMDB/DATAFILE/system.267.1053832677"
datafile 3 switched to datafile copy
"+DATA/HMDB/DATAFILE/sysaux.268.1053832775"
datafile 4 switched to datafile copy
"+DATA/HMDB/DATAFILE/undotbs1.269.1053832851"
datafile 6 switched to datafile copy "+DATA/HMDB/DATAFILE/users.271.1053832873"
9- Verify Datafiles and Controlfiles
[root@oda12 ~]# su - grid
Last login: Thu Oct 15 03:04:43 PKT 2020 on pts/0
[grid@oda12 ~]$ asmcmd
ASMCMD> ls
CRS/
DATA/
FRA/
ASMCMD> cd DATA
ASMCMD>
ls
HMDB/
ORCL/
ASMCMD> cd DATA/HMDB/DATAFILE
ASMCMD> ls
SYSAUX.268.1053832775
SYSTEM.267.1053832677
UNDOTBS1.269.1053832851
USERS.271.1053832873
ASMCMD> cd DATA/HMDB/CONTROLFILE/
ASMCMD> ls
Backup.270.1053832869
current.266.1053832453
ASMCMD>
ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> ls
HMDB/
ORCL/
ASMCMD> cd HMDB
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ASMCMD> cd BACKUPSET/
ASMCMD> ls
2020_10_15/
ASMCMD> cd 2020_10_15/
ASMCMD> ls
nnsnf0_TAG20201015T031757_0.272.1053832875
ASMCMD> ls --permission
User Group Permission
Name
rw-rw-rw- nnsnf0_TAG20201015T031757_0.272.1053832875
ASMCMD> exit
[oracle@oda12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 03:41:31 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Automatic Storage Management,
OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/HMDB/DATAFILE/system.267.1053832677
+DATA/HMDB/DATAFILE/sysaux.268.1053832775
+DATA/HMDB/DATAFILE/undotbs1.269.1053832851
+DATA/HMDB/DATAFILE/users.271.1053832873
10- Migrate Tempfile in ASM Through RMAN Utility and Startup database in Open Mode
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HMDB/datafile/o1_mf_temp_hqh86ypn_.tmp
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management,
OLAP, Advanced Analytics
and Real Application Testing options
[oracle@oda12 ~]$
[oracle@oda12 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 15 03:46:11 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: HMDB (DBID=719363102, not open)
RMAN> run
{
set
newname for tempfile
'/u01/app/oracle/oradata/HMDB/datafile/o1_mf_temp_hqh86ypn_.tmp' to '+DATA';
switch
tempfile all;
}
executing command: SET NEWNAME
using target database control file instead of recovery
catalog
renamed tempfile 1 to +DATA in control file
RMAN> exit
[oracle@oda12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 10:13:49 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Automatic Storage Management,
OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/HMDB/TEMPFILE/temp.273.1053857497
11- Migrate Redolog File in ASM Disk Group
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HMDB/onlinelog/o1_mf_3_hqh86jjz_.log
/u01/app/oracle/fast_recovery_area/HMDB/onlinelog/o1_mf_3_hqh86jpb_.log
/u01/app/oracle/oradata/HMDB/onlinelog/o1_mf_2_hqh86j0q_.log
/u01/app/oracle/fast_recovery_area/HMDB/onlinelog/o1_mf_2_hqh86j7j_.log
/u01/app/oracle/oradata/HMDB/onlinelog/o1_mf_1_hqh86hkv_.log
/u01/app/oracle/fast_recovery_area/HMDB/onlinelog/o1_mf_1_hqh86hms_.log
6 rows selected.
SQL>
SQL> desc V$logfile
Name
Null? Type
-----------------------------------------
-------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER
VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
CON_ID NUMBER
SQL> desc v$log;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE
NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS
VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
CON_ID NUMBER
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 "MB" from v$log;
GROUP# MEMBERS
STATUS MB
---------- ---------- ---------------- ----------
1 2 CURRENT 50
2 2 INACTIVE 50
3 2 INACTIVE 50
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 4 size 50m;
Database altered.
SQL> alter database add logfile group 5 size 50m;
Database altered.
SQL> alter database add logfile group 6 size 50m;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 "MB" from v$log;
GROUP# MEMBERS
STATUS MB
---------- ----------
---------------- ----------
1 2 CURRENT 50
4 2 UNUSED 50
5 2 UNUSED 50
6 2 UNUSED 50
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 "MB" from v$log;
GROUP# MEMBERS STATUS MB
---------- ----------
---------------- ----------
1 2 ACTIVE 50
4 2 CURRENT 50
5 2 UNUSED 50
6 2 UNUSED 50
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 "MB" from v$log;
GROUP# MEMBERS STATUS MB
---------- ---------- ---------------- ----------
1 2
ACTIVE 50
4 2 ACTIVE 50
5 2 CURRENT 50
6 2 UNUSED 50
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 "MB" from v$log;
GROUP# MEMBERS STATUS MB
---------- ---------- ---------------- ----------
1 2 INACTIVE 50
4 2 INACTIVE 50
5 2 CURRENT 50
6 2 UNUSED 50
SQL> alter database drop logfile group 1;
Database altered.
12- Verify Redolog file location
SQL> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/HMDB/ONLINELOG/group_4.274.1053858807
+FRA/HMDB/ONLINELOG/group_4.260.1053858811
+DATA/HMDB/ONLINELOG/group_5.275.1053858831
+FRA/HMDB/ONLINELOG/group_5.261.1053858855
+DATA/HMDB/ONLINELOG/group_6.276.1053858907
+FRA/HMDB/ONLINELOG/group_6.262.1053858937
6 rows selected.
SQL> exit
[root@oda12 ~]# su - grid
Last login: Thu Oct 15 10:10:14 PKT 2020
[grid@oda12 ~]$ asmcmd
ASMCMD> ls
CRS/
DATA/
FRA/
ASMCMD> cd +DATA/HMDB/ONLINELOG
ASMCMD> ls
group_4.274.1053858807
group_5.275.1053858831
group_6.276.1053858907
ASMCMD> cd +FRA/HMDB/ONLINELOG
ASMCMD> ls
group_4.260.1053858811
group_5.261.1053858855
group_6.262.1053858937
ASMCMD>
Note:
Your Oracle Database Migration Linux File System to ASM has been Completed.
My Future Post Will Linked with this Post
