DUPLICATING RAC TO ORACLE STANDALONE (RESTART)
Source Server
Oracle 19.3 RAC database
DB NAME -> NCDB
Destination Server
Oracle 19.3 Restart – Only Oracle software installed.
DB NAME -> STDB
Prerequites and Notes before starts
=> UR=A entry is required on Destination DB Side ,As to avoid below error
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
=> Password for SYS has to be identical on Both side . Target and Destination Both ,to avoid below error
RMAN-05614: Passwords for target and auxiliary connections must be the same when using active duplicate
=> Auxiliary channel must be allocated in restore command to avoid below error.
RMAN-05503: at least one auxiliary channel must be allocated to execute this command
=> Static Listener entry required on Destination side in listener.ora file owned by grid user ,to avoid below error.
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
STEP 1-> add tns entry of Destination server on SOURCE sides as below.
[oracle@ace2oraclen1 tmp]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle_home/app/19c/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
NCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ace2orac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NCDB)
)
)
STDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDB)
)
)
STEP 2->Add below entry of Source database on Destination Side.
NCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NCDB)
)
)
STDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDB)
(UR=A)
)
)
Here please note we are using VIP of Source database instead of SCAN Name
And we have appeneded UR=A for Destination server tns
STEP-3 => Create pfile for Destination server on Source and edit the same.
SQL> create pfile='/tmp/pfileSTDB.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ace2oraclen1 ~]$ vi /tmp/pfileSTDB.ora
[oracle@ace2oraclen1 ~]$ cat /tmp/pfileSTDB.ora
*.audit_file_dest='/oracle_home/app/oracle/admin/STDB/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='+DATA/STDB/CONTROLFILE/current.1'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='STDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=3256m
*.diagnostic_dest='/oracle_home/app/oracle'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1075m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3224m
STDB.thread=1
STDB.undo_tablespace='UNDOTBS1'
STEP -4 => Create password file for Destination database as below on Source server
[oracle@ace2oraclen1 oracle]$ orapwd file=orapwSTDB
Enter password for SYS:
STEP -4-a => Copy pfile and password file from Source Server to Destination server .
[oracle@ace2oraclen1 tmp]$ scp pfileSTDB.ora oracle@192.168.56.120:/tmp
oracle@192.168.56.120's password:
pfileSTDB.ora 100% 589 1.2MB/s 00:00
[oracle@ace2oraclen1 tmp]$
[oracle@ace2oraclen1 oracle]$ scp orapwSTDB oracle@192.168.56.120:/tmp
oracle@192.168.56.120's password:
orapwSTDB 100% 6144 8.9MB/s 00:00
[oracle@ace2oraclen1 oracle]$
STEP-5=> Go onto Destination database ,set variables as below
[oracle@ace2oracledb oracle_home]$ . ~/.bash_profile
[oracle@ace2oracledb oracle_home]$ echo $ORACLE_SID
STDB
[oracle@ace2oracledb oracle_home]$ echo $ORACLE_HOME
/oracle_home/app/19c/oracle
[oracle@ace2oracledb oracle_home]$
STEP-6=> On Destination server Mount database
[oracle@ace2oracledb tmp]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 16:47:53 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/pfileSTDB.ora';
ORACLE instance started.
Total System Global Area 3388995104 bytes
Fixed Size 8902176 bytes
Variable Size 671088640 bytes
Database Buffers 2701131776 bytes
Redo Buffers 7872512 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STEP 7=> Now test all connectivity from Destination server.
[oracle@ace2oracledb tmp]$ sqlplus sys/******@NCDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 16:52:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ace2oracledb tmp]$
[oracle@ace2oracledb oracle_home]$ sqlplus sys/*****@STDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 16:57:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STEP-8=> Add below entry in listener.ora file and restart listener.
[grid@ace2oracledb ~]$ cd $ORACLE_HOME/network/admin
[grid@ace2oracledb admin]$ cat listener.ora
#Backup file is /grid_home/app/oracle/crsdata/ace2oracledb/output/listener.ora.bak.ace2oracledb.grid line added by Agent
# listener.ora Network Configuration File: /grid_home/app/19c/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ace2oracledb.vbox.lab)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent
SID_LIST_LISTENER=
(SID_DESC=
(GLOBAL_DBNAME=STDB)
(SID_NAME=STDB)
(ORACLE_HOME=/oracle_home/app/19c/oracle)
)
[grid@ace2oracledb admin]$
[grid@ace2oracledb admin]$ lsnrctl stop
[grid@ace2oracledb admin]$ lsnrctl start
STEP-9 => Create rman script file to start restore and recovery as below.
[oracle@ace2oracledb oracle_home]$ cat rman_duplicate.rcv
run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
allocate channel ch7 device type disk;
allocate auxiliary channel c8 device type disk ;
allocate auxiliary channel c9 device type disk ;
duplicate database to STDB from active database nofilenamecheck;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
release channel c8;
release channel c9;
}
[oracle@ace2oracledb oracle_home]$
STEP-10=> Execute command as below and wait for final exit.
nohup rman target sys/*****@NCDB nocatalog auxiliary sys/****@STDB cmdfile=rman_duplicate.rcv log=rman_duplicate_final1.log
STEP-11 => Verify database
SQL> select name,open_mode,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
STDB READ WRITE PRIMARY
SQL>
SQL>
SQL>
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_mode string READ-WRITE
instance_name string STDB
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string STDB
SQL>
SQL>
SQL>
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDB
Step-12 => Cleanup database as below.
SQL> alter database disable thread 2;
Database altered.
SQL> select group# from v$log where THREAD#=2;
GROUP#
----------
3
4
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
STEP -13 => Create spfile to Shared location as below .
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle_home/app/19c/oracle/db
s/spfileSTDB.ora
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.
SQL> create spfile='+DATA/spfile.ora' from pfile='/tmp/pfile.ora';
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STEP-14 => Point spfile to new location as below and restart database and verify spfile.
[oracle@ace2oracledb oracle_home]$ cd $ORACLE_HOME/dbs
[oracle@ace2oracledb dbs]$ cat initSTDB.ora
spfile='+DATA/spfile.ora'
[oracle@ace2oracledb dbs]$
[oracle@ace2oracledb dbs]$
[oracle@ace2oracledb dbs]$ ls -lrt
total 19604
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle asmadmin 24 May 7 17:23 lkSTDB
-rw-r-----. 1 oracle oinstall 6144 May 7 17:24 orapwSTDB
-rw-r-----. 1 oracle asmadmin 20037632 May 7 17:24 snapcf_STDB.f
-rw-r-----. 1 oracle asmadmin 8704 May 7 17:24 spfileSTDB.ora
-rw-rw----. 1 oracle asmadmin 1544 May 7 17:33 hc_STDB.dat
-rw-r--r--. 1 oracle oinstall 26 May 7 17:34 initSTDB.ora
[oracle@ace2oracledb dbs]$
[oracle@ace2oracledb dbs]$
[oracle@ace2oracledb dbs]$ mv spfileSTDB.ora spfileSTDB.ora_BKP
STEP-15 =>verify spfile location after DB RESTART
[oracle@ace2oracledb dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 17:35:46 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3388995104 bytes
Fixed Size 8902176 bytes
Variable Size 671088640 bytes
Database Buffers 2701131776 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfile.ora
SQL>
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! SETUP COMPLETE !!!!!!!!!!CHEERS !
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444
