Step by Step on how to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE from On-premises to Oracle Cloud DBCS
On Prem Database Name : BEE
On Prem DB Version: 11.2.0.4
On Prem Primary db_unique_name : BEE
On DBCS Standby db_unique_name : BEE_CL
On DBCS DB Version: 11.2.0.4
Steps:
1) On the On-Prem Database
Get size of DB
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
2) Create a new DBCS system on OCI
Make sure to give DB name as BEE and Unique name suffix as CL
Once the DBCS system is created delete the files created by the system
Connect as ops user on DBCS node and switch to oracle user.
sqlplus '/as sysdba'
set heading off linesize 999 pagesize 0 feedback off trimspool on
spool /tmp/files.lst
select 'asmcmd rm '||name from v$datafile
union all
select 'asmcmd rm '||name from v$tempfile
union all
select 'asmcmd rm '||member from v$logfile; 2 3 4 5
asmcmd rm +DATA/bee_cl/datafile/system.259.1122836169
asmcmd rm +DATA/bee_cl/datafile/sysaux.260.1122836169
asmcmd rm +DATA/bee_cl/datafile/undotbs1.261.1122836169
asmcmd rm +DATA/bee_cl/datafile/users.264.1122836471
asmcmd rm +DATA/bee_cl/tempfile/temp.262.1122836237
asmcmd rm +RECO/bee_cl/onlinelog/group_3.259.1122836231
asmcmd rm +RECO/bee_cl/onlinelog/group_2.258.1122836227
asmcmd rm +RECO/bee_cl/onlinelog/group_1.257.1122836221
spool off
create pfile='/tmp/BEE_CL.pfile' from spfile;
exit;
3) Take backup for configuration on DBCS
[oracle@fundbcl ~]$ srvctl config database -d BEE_CL >/tmp/BEE_CL.config
[oracle@fundbcl ~]$ cat /tmp/BEE_CL.config
Database unique name: BEE_CL
Database name: BEE
Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_1
Oracle user: oracle
Spfile: +DATA/BEE_CL/spfileBEE.ora
Domain: gold1vcn.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: BEE_CL
Database instance: BEE
Disk Groups: DATA,RECO
Mount point paths:
Services:
Type: SINGLE
Database is administrator managed
4) Stop Database on DBCS
srvctl stop database -d BEE_CL -o immediate
As grid user (sudo from opc user to grid user) Edit /tmp/files.lst created previously to remove any unneeded lines from sqlplus. Leaving all lines beginning with ‘asmcmd'.
[grid@fundbcl ~]$ . files.lst
5) Check the parameter and make sure it is set to below values on primary on-prem server
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
While creating the standby database we use the active database files i.e., this command will be useful in creating the physical standby database using active database files over the network.
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
SQL> alter system set compatible='11.2.0.4' scope=spfile;
System altered.
As the DBCS system will have 19c grid so compatible parameter is set to 11.2.0.4
6) Prepare the on-premises database to be the primary database
- Ensure that the database is in archivelog mode .
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
b. Enable force logging
SQL> ALTER DATABASE FORCE LOGGING;
SQL> select force_logging from v$database;
FOR
---
YES
c. Create standby redologs
SQL> select group#,thread#,bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 1000
2 1 1000
3 1 1024
Make sure standby redo log files are of the maximum size of the logs.
alter database add standby logfile '/u01/oracle/oradata/log01s.dbf' size 1024M;
alter database add standby logfile '/u01/oracle/oradata/log02s.dbf' size 1024M;
alter database add standby logfile '/u01/oracle/oradata/log03s.dbf' size 1024M;
alter database add standby logfile '/u01/oracle/oradata/log04s.dbf' size 1024M;
Validate from v$standby_log.
Select * from v$standby_log;
d. Modify the primary initialization parameter for dataguard on primary,
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEE,BEE_CL)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEE';
System altered
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=BEE_CL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=BEE_CL';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> alter system set FAL_SERVER=BEE;
System altered.
SQL> alter system set FAL_CLIENT=BEE_CL;
System altered.
alter system set DB_FILE_NAME_CONVERT='/u01/oracle/oradata','+DATA','/u01/oracle/oradata2','+DATA' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/oracle/oradata','+RECO','/u01/oracle/oradata2','+RECO' scope=spfile;
Restart database as the compatible parameter has been changed.
7) Ensure that the sql*net connectivity is working fine
As the grid user, add the following entry to the listener.ora on both the cloud DBaaS .The listener.ora resides in $GRID_HOME/network/admin.
Insert a static entry for in the listener.ora file of the standby system.
SID_LIST_LISTENER =
(SID_LIST = (SID_DESC =
(GLOBAL_DBNAME = BEE_CL) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = BEE)
))
TNSNAMES.ORA for the Primary and Standby should have BOTH entries
BEE_CL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=fundbcl.gold1vcn.oraclevcn.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=BEE_CL)
)
)
BEE=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=funebsdb.lab)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=BEE)
(INSTANCE_NAME=BEE)
)
)
Check with the SQL*Net configuration using the following commands on the Primary AND Standby
tnsping BEE
tnsping BEE_CL
Make sure connectivity between cloud DBCS and on-prem DB server. If required put the entries in /etc/hosts
8) Setup the standby database
- Copy the password file from the primary $ORACLE_HOME/dbs to the standby database $ORACLE_HOME/dbs.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
To create password file at primary site
orapwd file=orapwBEE password=oracle123
b. Create a initialization parameter with only one parameter DB_NAME on standby database $ORACLE_HOME/dbs. If file exit move to backup and create a new one as below.
vi initBEE.ora
DB_NAME=BEE
DB_UNIQUE_NAME=BEE_CL
DB_BLOCK_SIZE=8192
c. Create the necessary directories in the standby location to place database files and trace files
Like archive location
Diag Location
d. Start in DBCS with below command
srvctl start database -d BEE_CL -o nomount
SQL> startup nomount pfile=initBEE.ora;
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2252016 bytes
Variable Size 180355856 bytes
Database Buffers 50331648 bytes
Redo Buffers 5095424 bytes
NOTE : Use either PFILE or SPFILE
# Addtl. comment
# If DUPLICATE without TARGET connection is used you cannot use SPFILE
# else getting
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
e. Verify if the connection 'AS SYSDBA' is working
sqlplus /nolog
connect sys/oracle123@BEE AS SYSDBA
connect sys/oracle123@BEE_CL AS SYSDBA
9 ) On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)
Create a rman script as below. If you want change parameter as per your environment.
vi rman_duplicate.cmd
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database
for standby
from active database
spfile
parameter_value_convert 'BEE','BEE_CL'
set db_unique_name='BEE_CL'
set db_file_name_convert='/u01/oracle/oradata','+DATA','/u01/oracle/oradata2','+DATA'
set log_file_name_convert='/u01/oracle/oradata','+RECO','/u01/oracle/oradata2','+RECO'
set diagnostic_dest='/u01/app/oracle'
set utl_file_dir='/usr/tmp'
set log_archive_dest_1='LOCATION=/u01/app/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEE_CL'
set control_files='+DATA','+RECO'
set log_archive_max_processes='5'
set fal_client='BEE'
set fal_server=‘BEE_CL'
set local_listener='BEE_CL'
set standby_file_management='MANUAL'
set log_archive_config='dg_config=(BEE,BEE_CL)'
set log_archive_dest_2='service=BEE ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=BEE'
;
}
Connect to RMAN and run script prepared earlier.
rman target sys/oracle123@BEE auxiliary sys/oracle123@BEE_CL
RMAN>@rman_duplicate.cmd
10) Start managed recovery (MRP)
Once the RMAN is completed, we will start MRP
Connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.
SQL> select name from v$database;
NAME
---------
BEE
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
BEE MOUNTED
SQL> alter database recover managed standby database disconnect from session;
On Primary run
alter system set log_archive_dest_state_2=ENABLE;
11) Validation
Use below queries to validate
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
select max(sequence#) from v$log_history;
select name,open_mode,database_role,current_scn from v$database;
select group#,dbid,thread#,status from V$standby_log;
select destination,status,error,target,dest_name from v$archive_dest where dest_id=2;
DESTINATION
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
TARGET
-------
DEST_NAME
--------------------------------------------------------------------------------
BEE_CL
VALID
STANDBY
LOG_ARCHIVE_DEST_2
Post a Comment
Post a Comment