RAC – RAC Database Cloning
Assumptions: In this cloning DELL database was cloned TO CDELL and then added to Same Cluster dellc
Cluster Name: dellc
GRID OS User: GRID
(DELL) ORACLE RDBMS User: ORACLE
(CDELL) ORACLE RDBMS User: ORACLE
RDBMS Version: 12.1.0.2
Here we created first RAC DB Clone to single node and then added to Cluster and converted to RAC DB under Same OS user ORACLE
=========================================================
Create Directory in ASM for NEW CLONE Database CDELL
[grid@rac1 ~]$ . grid.env
[grid@rac1 ~]$ asmcmd -p
ASMCMD> cd data
ASMCMD> mkdir CDELL
ASMCMD> exit
**********************************************************************************
*********************************************************************************
In order to perform rman cloning the database must be in archive log mode.
Changing the rac 2 nodes database to archive log mode
[oracle@rac2 ~]$
r2= sql> alter system set cluster_database=false scope=spfile sid='*';
. grid.env
srvctl stop database -d prod
. rdbms conn / as sysdba
sql> startup mount
r1 # ps -ef|grep pmon
r2
> archive log list;
> alter database archive log;
> archive log list;
> show parameter recover;
> alter system set db_recovery_file_dest_size=4g scope=both sid='*';
> alter system set db_recovery_file_dest=+DATA scope=both sid='*';
> alter system set cluster_database=true scope=spfile sid='*';
> shut abort;
. grid.env
$ srvctl start database -d prod
rdbms.env
!sq
sqlplus / as sysdba
> archive log list;
> alter system switch log file;
###################################################################################################
Verify CRD file location for DELL database:
select name from v$controlfile;
select name from v$datafile;
select name from v$tempfile;
select member from v$logfile;
================================================================================================================
Parameter file Creation for CDELL:
[oracle@rac1 ~]$
vi $ORACLE_HOME/dbs/initCDELL.ora
i#initial parameters for RMAN duplicate Cloninig for Clone database CDELL
compatible='12.1.0.2.0'
control_files='+DATA/CDELL/control01.ctl'
db_block_size=8192
db_name='CDELL'
diagnostic_dest='/u01/app/oracle'
pga_aggregate_target=104857600
sga_target=314572800
undo_tablespace=undotbs1
##############cluster parameters#######
cluster_database=false
CDELL2.thread=2
CDELL1.thread=1
CDELL1.undo_tablespace='UNDOTBS1'
CDELL2.undo_tablespace='UNDOTBS2'
CDELL2.instance_number=2
CDELL1.instance_number=1
#############clone parameters ##########
db_file_name_convert='+DATA/DELL/DATAFILE','+DATA/CDELL'
db_file_name_convert='+DATA/DELL/TEMPFILE','+DATA/CDELL'
log_file_name_convert='+DATA/DELL/ONLINELOG','+DATA/CDELL'
_no_recovery_through_resetlogs=true
#spfile='+DATA/CDELL/spfileCDELL.ora'
-----------------------------------
[oracle@rac1 ~]$
vi $HOME/cdell.env
i#environment file for Clone database CDELL
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH
export ORACLE_SID=CDELL
[oracle@rac1 ~]$ . cdell.env
[oracle@rac1 ~]$ echo $ORACLE_SID
CDELL
[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@rac1 ~]$
================================================================================================================
Create SP file in ASM
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 23 00:17:46 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
SQL> create spfile='+DATA/CDELL/spfileCDELL.ora' from pfile;
SQL> shut abort
ORACLE instance shut down.
SQL> exit
[oracle@rac1 ~]$
vi $ORACLE_HOME/dbs/initCDELL.ora
gg30ddispfile='+DATA/CDELL/spfileCDELL.ora'
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> startup nomount
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/CDELL/spfilecdell.ora
SQL>
SQL> show parameter _no
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_no_recovery_through_resetlogs boolean TRUE
diagnostic_dest string /u01/app/oracle
SQL>
================================================================================================================
Create Listner and tns files using netmgr and netca then modify accordingly
[oracle@rac1 ~]$ lsnrctl stop cdell
[oracle@rac1 ~]$ cat $TNS_ADMIN/listener.ora
CDELL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1529))
)
)
SID_LIST_CDELL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CDELL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = CDELL)
)
)
INBOUND_CONNECT_TIMEOUT_DELL = 100
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cat $TNS_ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DELL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dellc-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL)
)
)
CDELL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.dell.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = CDELL)(UR=A)
)
)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping dell
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-SEP-2021 00:28:48
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dellc-scan)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL)))
OK (0 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping cdell
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-SEP-2021 00:28:52
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.dell.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = CDELL)(UR=A)))
OK (10 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl status dell
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-SEP-2021 00:29:34
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dellc-scan)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DELL)))
TNS-01189: The listener could not authenticate the user
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl status cdell
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-SEP-2021 00:29:14
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.11)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias CDELL
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 22-SEP-2021 22:13:39
Uptime 0 days 2 hr. 15 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/cdell/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
Services Summary...
Service "CDELL" has 1 instance(s).
Instance "CDELL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$
================================================================================================================
Create Password File,
password must be same for target and auxiliary DB
cd $ORACLE_HOME/dbs
orapwd file=$ORACLE_HOME/dbs/orapwCDELL password=oracle force=y entries=30
ls -ltr $ORACLE_HOME/dbs/orapw*
sqlplus / as sysdba
set linesize 300;
col HOST_NAME for a9;
col OPEN_MODE for a10;
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST INSTANCE_MO
--------------- ---------------- --------- ------------ ----------------- ------------------ --------- -----------
1 CDELL rac1 STARTED ACTIVE UNKNOWN NORMAL REGULAR
SQL>
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SQL> exit
================================================================================================================
Create RMAN Duplication:
Make sure Auxiliary database is connected in not mounted State:
[oracle@rac1 ~]$ rman target sys/oracle@DELL nocatalog auxiliary sys/oracle@CDELL
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 23 00:35:31 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=4054497631)
using target database control file instead of recovery catalog
connected to auxiliary database: CDELL (not mounted)
RMAN>
RMAN> duplicate target database to 'CDELL' from active database;
RMAN> exit
[oracle@rac1 ~]$ echo $ORACLE_SID
CDELL
[oracle@rac1 ~]$ sqlplus / as sysdba
[oracle@rac1 ~]$
set linesize 300;
col HOST_NAME for a9;
col OPEN_MODE for a10;
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST INSTANCE_MO
--------------- ---------------- --------- ------------ ----------------- ------------------ --------- -----------
1 CDELL rac1 OPEN ACTIVE PRIMARY_INSTANCE NORMAL REGULAR
SQL>
SQL> select name,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database;
NAME OPEN_MODE CREATED LOG_MODE CONTROL FLASHBACK_ON DATABASE_ROLE GUARD_S PROTECTION_MODE
--------- ---------- --------- ------------ ------- ------------------ ---------------- ------- --------------------
CDELL READ WRITE 23-SEP-21 ARCHIVELOG CURRENT NO PRIMARY NONE MAXIMUM PERFORMANCE
SQL>
Verify C-R-D Files locations for Clone Database CDELL
SQL> select name from v$controlfile;
NAME
---------------------------------------------------------------------
+DATA/CDELL/control01.ctl
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------
+DATA/CDELL/ONLINELOG/group_2.354.1083976705
+DATA/CDELL/ONLINELOG/group_2.355.1083976705
+DATA/CDELL/ONLINELOG/group_1.352.1083976703
+DATA/CDELL/ONLINELOG/group_1.353.1083976705
+DATA/CDELL/ONLINELOG/group_3.329.1083976701
+DATA/CDELL/ONLINELOG/group_3.328.1083976701
+DATA/CDELL/ONLINELOG/group_4.327.1083976701
+DATA/CDELL/ONLINELOG/group_4.351.1083976701
8 rows selected.
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------
+DATA/CDELL/DATAFILE/system.346.1083976625
+DATA/CDELL/DATAFILE/sysaux.345.1083976625
+DATA/CDELL/DATAFILE/undotbs1.319.1083976655
+DATA/CDELL/DATAFILE/undotbs2.320.1083976647
+DATA/CDELL/DATAFILE/users.318.1083976659
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------------
+DATA/CDELL/TEMPFILE/temp.356.1083976707
SQL>
================================================================================================================
Verify whether database CDELL is able to start with new instance name (CDELL1, CDELL2) from both nodes.
Then convert single instance db to RAC
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL>
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
SQL> shut abort;
ORACLE instance shut down.
SQL> exit
[oracle@rac1 ~]$ mv $ORACLE_HOME/dbs/initCDELL.ora $ORACLE_HOME/dbs/initCDELL1.ora
[oracle@rac1 ~]$ ls -ltr $ORACLE_HOME/dbs/initCDELL*
-rw-r--r-- 1 oracle oinstall 37 Sep 23 00:26 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initCDELL1.ora
[oracle@rac1 ~]$
[oracle@rac1 ~]$ scp -r $ORACLE_HOME/dbs/initCDELL1.ora rac2:$ORACLE_HOME/dbs/initCDELL2.ora
initCDELL1.ora 100% 37 19.1KB/s 00:00
[oracle@rac1 ~]$ vi $HOME/cdell.env
[oracle@rac1 ~]$ grep ORA $HOME/cdell.env
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH
export ORACLE_SID=CDELL1
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . $HOME/cdell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_SID
CDELL1
[oracle@rac1 ~]$
Modify ORACLE_SID for rac2
[oracle@rac2 ~]$ vi cdell.env
[oracle@rac2 ~]$
[oracle@rac2 ~]$ grep ORA cdell.env
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH
export ORACLE_SID=CDELL2
[oracle@rac2 ~]$
[oracle@rac2 ~]$ . $HOME/cdell.env
[oracle@rac2 ~]$
[oracle@rac2 ~]$ echo $ORACLE_SID
CDELL2
[oracle@rac2 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 23 00:52:29 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2923920 bytes
Variable Size 239075952 bytes
Database Buffers 67108864 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.
SQL>
set linesize 300;
col HOST_NAME for a9;
col OPEN_MODE for a10;
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST INSTANCE_MO
--------------- ---------------- --------- ------------ ----------------- ------------------ --------- -----------
1 CDELL1 rac1 OPEN ACTIVE PRIMARY_INSTANCE NORMAL REGULAR
SQL>
SQL> select name,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database;
NAME OPEN_MODE CREATED LOG_MODE CONTROL FLASHBACK_ON DATABASE_ROLE GUARD_S PROTECTION_MODE
--------- -------------------- --------- ------------ ------- ------------------ ---------------- ------- --------------------
CDELL READ WRITE 23-SEP-21 ARCHIVELOG CURRENT NO PRIMARY NONE MAXIMUM PERFORMANCE
SQL>
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 23 00:54:39 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
SQL>
set linesize 300;
col HOST_NAME for a9;
col OPEN_MODE for a10;
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST INSTANCE_MO
--------------- ---------------- --------- ------------ ----------------- ------------------ --------- -----------
2 CDELL2 rac2 OPEN ACTIVE PRIMARY_INSTANCE NORMAL REGULAR
1 CDELL1 rac1 OPEN ACTIVE PRIMARY_INSTANCE NORMAL REGULAR
SQL>
SQL> shut abort --> from rac1 & rac2
================================================================================================================
now register new clone database to cluster:
[oracle@rac1 ~]$ srvctl remove database -d CDELL
Remove the database CDELL? (y/[n]) y
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl config database
DELL
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl add database -d CDELL -o /u01/app/oracle/product/12.1.0/dbhome_1 -p '+DATA/CDELL/spfileCDELL.ora'
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl add instance -d CDELL -i CDELL1 -n rac1
[oracle@rac1 ~]$ srvctl status instance -d cdell -n rac1
Instance CDELL1 is not running on node rac1
[oracle@rac1 ~]$ srvctl add instance -d CDELL -i CDELL2 -n rac2
[oracle@rac1 ~]$ srvctl status instance -d cdell -n rac2
Instance CDELL2 is not running on node rac2
[oracle@rac1 ~]$ srvctl config database -d CDELL
Database unique name: CDELL
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/CDELL/spfileCDELL.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oinstall
Database instances: CDELL1,CDELL2
Configured nodes: rac1,rac2
Database is administrator managed
[oracle@rac1 ~]$ srvctl start database -d CDELL
[oracle@rac1 ~]$ sqlplus / as sysdba
set linesize 300;
col HOST_NAME for a9;
col OPEN_MODE for a10;
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,OPEN_MODE,LOG_MODE,FLASHBACK_ON,DATABASE_ROLE from gv$instance,gv$database;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS OPEN_MODE LOG_MODE FLASHBACK_ON DATABASE_ROLE
--------------- ---------------- --------- ------------ ----------------- ---------- ------------ ------------------ ----------------
1 CDELL1 rac1 OPEN ACTIVE READ WRITE ARCHIVELOG NO PRIMARY
1 CDELL1 rac1 OPEN ACTIVE READ WRITE ARCHIVELOG NO PRIMARY
2 CDELL2 rac2 OPEN ACTIVE READ WRITE ARCHIVELOG NO PRIMARY
2 CDELL2 rac2 OPEN ACTIVE READ WRITE ARCHIVELOG NO PRIMARY
SQL>
SQL>
Thanks for Reading
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux | DATABASE | RMAN | RAC | EBS |
| R1229 M7 Clone | ||||
| RAC DataGuard | Pluggable DB Clone | |||
| appsutil for DB | ||||
| JDK JRE upgrade | ||||
| Add EBS Node | ||||
No comments:
Post a Comment