168
Introduction
This article, will cover Oracle 19c restart data-guard physical standby configuration, but before we start, you need to have two oracle 19c restart servers one as Primary server and one as Standby server. the Standby Database will be the target server where we will replicate the database there.
Please check my youtube channel and my github, to see how you can install oracle 19c restart, besides that, to save time of reinstall standby server, you can clone the primary and change the hostname of Cloned server, check my youtube channel and github to see the way of doing that.
System Overview
In this demo I will show you how to configure oracle 19c restart physical standby database, but before we start we need to have an overview for the current system we will work on. below table show the overview of the systems.
| Env Details | Primary DB | Standby DB |
|---|---|---|
| DB Unique Name | prod | prod_stby |
| DB Name | prod | prod |
| DB Role | Primary | standby |
| Server IP | 10.10.20.130 | 10.10.20.131 |
| DB Version | 19.3c | 19.3c |
| OS Version | OLE 8.3 | OLE 8.3 |
Steps Overview
- Pre-Config steps
- Add hostname to hosts file for both (Primary & Standby).
- Add tnsname to tnsnames.ora for both (Primary & Standby).
- Create Backup Dir grant permissions for both (Primary & Standby).
- Install rlwrap
- Primary steps
- Enable Archive log mode .
- Enable force logging.
- Set parameter log_archive_config.
- Set parameter fal_server & fal_client.
- Set parameter standby_file_management to auto.
- Set log_file_name_convert.
- Set db_file_name_convert.
- Set pdb_file_name_convert.
- Create standby redo log files .
- Create Password file.
- Create user with table and insert sample data for testing.
- Collect files and take backup to /backup directory.
- tar the directory and shipped it to the standby database.
- Standby steps
- Un-Tar backup file.
- Edit pfile using vi/vim.
- Create Standby instance and Mount database.
- Restore database.
- Recover Database.
- Create DB grid service.
- Configure Broker
- Create Broker Data file for both (PRIMARY,STANDBY).
- Start Broker on both (primary & standby).
- Create configuration using gdmgrl command.
- Switchover
- Validate switchover.
- switchover to prod_stby.
- switchback to prod.
- Automate Standby archive Logs Deletion
- Create RMAN script.
- Test RMAN script.
- Crontab script.
Configuration Steps (Short)
Pre-Config Steps
-
Add hostname to hosts file for both (Primary & Standby)
# as root vim /etc/hosts # add below records 10.10.20.130 primary.oradomain primary 10.10.20.131 standby.oradomain standby ping primary ping standby
-
Add tnsname to tnsnames.ora for both (Primary & Standby)
# as Oracle user vim $ORACLE_HOME/network/admin/tnsnames.ora cat $ORACLE_HOME/network/admin/tnsnames.ora # ping the service names tnsping prodpdb1_p tnsping prod tnsping prod_stby
-
Create Backup Dir grant permissions for both (Primary & Standby)
# as root or sudo user mkdir -p /backup/prod chown -R oracle:oinstall /backup chmod -R 775 /backup # Using grid user # on Primary asmcmd mkdir +DATA/PROD/DGCONF/ asmcmd mkdir +FRA/PROD/DGCONF/ asmcmd mkdir +FRA/PROD/arch #on Standby asmcmd mkdir +DATA/PROD_STBY/ asmcmd mkdir +DATA/PROD_STBY/prodpdb1 asmcmd mkdir +DATA/PROD_STBY/pdbseed asmcmd mkdir +DATA/PROD_STBY/DGCONF/ asmcmd mkdir +FRA/PROD_STBY/ asmcmd mkdir +FRA/PROD_STBY/arch asmcmd mkdir +FRA/PROD_STBY/DGCONF/
-
rlwrap
rpm -Uvh ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/8.1.2020-04-22/Everything/x86_64/Packages/r/rlwrap-0.43-5.el8.x86_64.rpm
Primary Steps
- Enable Archive log mode
show pdbs
alter pluggable database prodpdb1 open;
select log_mode from v$database;
archive log list
shutdown immediate;
startup mount;
alter database archivelog;
archive log list
show parameter log_archive_dest_1
alter system set log_archive_dest_1='LOCATION=+FRA/PROD/arch';
alter database open;
show parameter log_archive_dest_1
archive log list
- Enable force logging
select force_logging from v$database;
alter database force logging;
select force_logging from v$database;
- Set parameter log_archive_config
show parameter log_archive_conf
alter system set log_archive_config='DG_CONFIG=(PROD,PROD_STBY)';
show parameter log_archive_conf
- Set parameter fal_server & fal_client
show parameter fal_
ALTER system SET fal_server=PROD_STBY;
ALTER system SET fal_client=PROD;
show parameter fal_
- Set parameter standby_file_management to auto
show parameter file_manag
alter system set standby_file_management=AUTO;
show parameter file_manag
- Set log_file_name_convert
show parameter convert
alter system set log_file_name_convert = '/PROD_STBY/','/PROD/' scope=spfile;
- Set db_file_name_convert
alter system set db_file_name_convert = '/PROD_STBY/','/PROD/' scope=spfile;
- Set pdb_file_name_convert
alter system set pdb_file_name_convert = '/PROD_STBY/','/PROD/' scope=spfile;
shutdown immediate;
startup;
show parameter convert
- Create standby redo log files
-- check redolog files and groups
col member for a45
set lines 200
select group#, type, member from v$logfile;
-- check the size
col REDO_SIZE for a10
select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024||' MB' as REDO_SIZE ,MEMBERS,STATUS from v$log;
-- create standby redo logs
ALTER DATABASE ADD STANDBY LOGFILE Group 4 ('+DATA','+FRA') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE Group 5 ('+DATA','+FRA') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE Group 6 ('+DATA','+FRA') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE Group 7 ('+DATA','+FRA') size 200M;
set lines 200
select group#, type, member from v$logfile;
- Create Password file
# using oracle user
orapwd file=+DATA/PROD/orapwPROD dbuniquename=prod password=password1 force=y format=12
- Create user with table and insert sample data for testing
rlwrap sqlplus sys@prodpdb1_p as sysdba
show pdbs
alter pluggable database prodpdb1 open;
create user test_user identified by test123;
alter user test_user quota 100M on users;
grant connect,resource to test_user;
connect test_user/test123@prodpdb1_p
create table test (id number(10),name varchar2(35));
insert into test values (1,'names');
/
/
/
/
commit;
select * from test;
alter system switch logfile;
alter system archive log current;
- Collect files and take backup to /backup directory
# get pfile from spfile (Oracle user)
create pfile='/backup/pfilePROD.ora' from spfile;
! ls -l /backup
# get passwd file from ASM (Grid User)
asmcmd pwget --dbuniquename prod
asmcmd pwcopy +DATA/PROD/orapwprod /backup/orapwprod
ls -l /backup/ora*
# Configure RMAN and take backup
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/prod/%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/prod/%U';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
# Run block
run {
backup database;
backup archivelog all;
backup current controlfile for standby format '/backup/prod/stby_ctl_%d_%u_%s';
}
# or run them individually
backup database;
backup archivelog all;
backup current controlfile for standby format '/backup/prod/stby_ctl_%d_%u_%s';
list backup of controlfile;
# Or you can duplicate Database using
RMAN> run {
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='prod_stby' COMMENT 'Is standby' SET db_file_name_convert='/prod/','/prod_stby/' SET log_file_name_convert='/prod/','/prod_stby/';
}
- Tar the directory and shipped it to the standby database.
# oracle user
cd /backup
tar -czvf backup.tar.gz *
scp backup.tar.gz oracle@standby:~
Standby Steps
-
Un-Tar backup file
# as oracle user tar -xvf backup.tar.gz -C /backup -
Edit pfile using vi/vim
# Edit pfile vim /backup/pfilePROD.ora # change below parameters to be same as below *.db_file_name_convert='/PROD/','/PROD_STBY/' *.log_file_name_convert='/PROD/','/PROD_STBY/' *.pdb_file_name_convert='/PROD/','/PROD_STBY/' *.db_unique_name='prod_stby' *.control_files='+DATA/PROD_STBY/CONTROLFILE/current.260.1073580523','+FRA/PROD_STBY/CONTROLFILE/current.256.1073580523' *.log_archive_dest_1='LOCATION=+FRA/PROD_STBY/arch' *.fal_client='PROD_STBY' *.fal_server='PROD' # create below directorys if not created # oracle base mkdir -p /u01/19c/oracle_base/admin/prod/adump
-
Create Standby instance
rlwrap sqlplus / as sysdba startup nomount pfile='/backup/pfilePROD.ora'; create spfile='+DATA/PROD_STBY/spfilePROD.ora' from pfile='/backup/pfilePROD.ora'; !vim $ORACLE_HOME/dbs/initprod.ora # add this to initprod.ora SPFILE='+DATA/PROD_STBY/spfilePROD.ora' show parameter spfile shu immediate startup nomount show parameter spfile show parameter fal_ show parameter convert show parameter control_files show parameter log_archive_dest_1 show parameter db_uniq select value from v$parameter where name='service_names';
-
Restore standby control file and mount database
ls /backup/prod/stby* restore controlfile from '/backup/prod/stby_ctl_PROD_0b0l5q0r_11'; alter database mount standby database; list backup;
-
Restore database
restore database; -
Recover database
catalog start with '+DATA/PROD_STBY'; NO switch database to copy; recover database;
-
Create Standby database service.
srvctl add database -d prod_stby -o $ORACLE_HOME -p '+DATA/PROD_STBY/spfilePROD.ora' -r PHYSICAL_STANDBY -s 'mount' -n standby -a 'DATA,FRA' srvctl config database -d prod_stby ## use oracle srvctl modify database -d prod_stby -i prod srvctl config database -d prod_stby srvctl stop database -d prod_stby -force srvctl start database -d prod_stby srvctl status database -d prod_stby ## use grid srvctl config database -d prod_stby asmcmd pwcopy --dbuniquename prod_stby /backup/orapwprod +data/prod_stby/orapwprod asmcmd pwget --dbuniquename prod_stby srvctl config database -d prod_stby # if needed you can modify service srvctl modify database -d prod_stby -pwfile +data/prod_stby/orapwprod # tnsping from both primary and standby # using grid check the listener status and serives srvctl stop listener srvctl start listener lsnrctl services lsnrctl status # you can also reload lsnrctl reload # using sql register the service name if the serivce not registered select value from v$parameter where name='service_names'; alter system register; alter session set container=prodpdb1; alter system register;
Configure Broker
-
Create Broker Data file for both (PRIMARY,STANDBY)
# using sqlplus on Primary oracle user rlwrap sqlplus / as sysdba show parameter dg_broker_config ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/PROD/DGCONF/dgb_conf_01.dat'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/PROD/DGCONF/dgb_conf_02.dat'; show parameter dg_broker_config # using sqlplus on standby oracle user show parameter dg_broker_config ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/PROD_STBY/DGCONF/dgb_conf_01.dat'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/PROD_STBY/DGCONF/dgb_conf_02.dat'; show parameter dg_broker_config
-
Start Broker on both (primary & standby)
ALTER SYSTEM SET dg_broker_start=true; -
Create configuration using gdmgrl command
# monitor the database # primary tail -f $ORACLE_BASE/diag/rdbms/prod/prod/trace/alert_prod.log #standby tail -f $ORACLE_BASE/diag/rdbms/prod_stby/prod/trace/alert_prod.log select name,open_mode,database_role from v$database; dgmgrl sys@prod CREATE CONFIGURATION PROD_DB_DG_CONFIG AS PRIMARY DATABASE IS prod CONNECT IDENTIFIER IS prod; ADD DATABASE prod_stby AS CONNECT IDENTIFIER IS prod_stby; show configuration enable configuration; show configuration show database prod_stby
Switchover
-
Use tail command to monitor
# primary tail -f $ORACLE_BASE/diag/rdbms/prod/prod/trace/alert_prod.log tail -f $ORACLE_BASE/diag/rdbms/prod/prod/trace/drcprod.log #standby tail -f $ORACLE_BASE/diag/rdbms/prod_stby/prod/trace/alert_prod.log tail -f $ORACLE_BASE/diag/rdbms/prod_/prod/trace/drcprod.log
-
Validate switchover
# using sql plus select low_sequence#, high_sequence# from v$archive_gap; # using broker show configuration; validate database prod; validate database prod_stby;
-
switchover to prod_stby
switchover to prod_stby; show configuration; # sqlplus on both nodes to check the db role select name,db_unique_name,database_role from v$database;
-
Validate switchover
validate database prod; show configuration;
-
switchback to prod
switchover to prod; show configuration; # sqlplus on both nodes to check the db role select name,db_unique_name,database_role from v$database;
Automate Standby archive Logs Deletion
-
Create RMAN script.
cd ~ mkdir -p scripts/logs cd scripts vim delete_arch.sh ## add below by copy paste to script file #!/bin/bash export ORACLE_SID=prod rman target / <<EOF run{ delete noprompt archivelog until time 'sysdate-0.5'; } exit EOF
-
Test RMAN script.
chmod u+x delete_arch.sh ./delete_arch.sh
-
Crontab script.
05 23 * * * . /home/oracle/.bash_profile; /home/oracle/scripts/delete_arch.sh 2>&1 > /home/oracle/scripts/delete_arch.$(date +\%Y\%m\%d-\%H\%M\%S).cron.log
Configuration Steps (Detailed)
System Overview
| Env Details | Primary DB | Standby DB |
|---|---|---|
| DB Unique Name | prod | prod_stby |
| DB Name | prod | prod |
| DB Role | Primary | standby |
| Server IP | 10.10.20.130 | 10.10.20.131 |
| DB Version | 19.3c | 19.3c |
| OS Version | OLE 8.3 | OEL 8.3 |
Pre-Config steps
- Add hostname to hosts file for both (Primary & Standby)
# as root
vim /etc/hosts
# add below records
10.10.20.130 primary.oradomain primary
10.10.20.131 standby.oradomain standby
# try to ping from both servers should be reachable.
ping primary
ping standby
- Add tnsname to tnsnames.ora for both (Primary & Standby)
vim $ORACLE_HOME/network/admin/tnsnames.ora
cat $ORACLE_HOME/network/admin/tnsnames.ora
scp $ORACLE_HOME/network/admin/tnsnames.ora oracle@standby:$ORACLE_HOME/network/admin/
# add this to primary tnsnames.ora
PRODPDB1_p =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prodpdb1)
)
)
PRODPDB1_s =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prodpdb1)
)
)
# add this to both primary & standby tnsnames.ora
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
PROD_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod_stby)
)
)
# ping the service names
tnsping prodpdb1_p
tnsping prod
tnsping prod_stby
- Create Backup Dir grant permissions for both (Primary & Standby)
# as root or sudo user
mkdir -p /backup/prod
chown -R oracle:oinstall /backup
chmod -R 775 /backup
# Using grid user
# on Primary
asmcmd mkdir +DATA/PROD/DGCONF/
asmcmd mkdir +FRA/PROD/DGCONF/
asmcmd mkdir +FRA/PROD/arch
#on Standby
asmcmd mkdir +DATA/PROD_STBY/
asmcmd mkdir +DATA/PROD_STBY/prodpdb1
asmcmd mkdir +DATA/PROD_STBY/pdbseed
asmcmd mkdir +FRA/PROD_STBY/
asmcmd mkdir +FRA/PROD_STBY/arch
asmcmd mkdir +DATA/PROD_STBY/DGCONF/
asmcmd mkdir +FRA/PROD_STBY/DGCONF/
-
rlwrap
rpm -Uvh ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/8.1.2020-04-22/Everything/x86_64/Packages/r/rlwrap-0.43-5.el8.x86_64.rpm Retrieving ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/8.1.2020-04-22/Everything/x86_64/Packages/r/rlwrap-0.43-5.el8.x86_64.rpm warning: /var/tmp/rpm-tmp.7i35A8: Header V3 RSA/SHA256 Signature, key ID 2f86d6a1: NOKEY Verifying... ################################# [100%] Preparing... ################################# [100%] Updating / installing... 1:rlwrap-0.43-5.el8 ################################# [100%]
Primary Steps
- Enable Archive log mode
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immdediate;
SP2-0717: illegal SHUTDOWN option
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2415918608 bytes
Fixed Size 9137680 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database archivelog;
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 12
Next log sequence to archive 14
Current log sequence 14
SQL> show parameter log_achive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19 string
SQL> alter system set log_archive_dest_1='LOCATION=+FRA/PROD/arch';
SQL> alter database open;
Database altered.
SQL> show parameter dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
log_archive_dest_1 string LOCATION=+FRA/PROD/arch
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
log_archive_dest_19 string
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA/prod/arch
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL>
- Enable force logging
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL>
- Set parameter log_archive_config
SQL> alter system set log_archive_config='DG_CONFIG=(PROD,PROD_STBY)';
System altered.
SQL>
SQL> show parameter log_archive_conf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(PROD,PROD_STBY)
SQL>
- Set parameter fal_server & fal_client
SQL> ALTER system SET fal_server=PROD_STBY;
System altered.
SQL> ALTER system SET fal_client=PROD;
System altered.
SQL> show parameter fal_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string PROD
fal_server string PROD_STBY
SQL>
- Set parameter standby_file_management to auto
SQL> show parameter file_manag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management=AUTO;
System altered.
SQL> show parameter file_manag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
- Set log_file_name_convert
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
pdb_file_name_convert string
SQL> alter system set log_file_name_convert = '/PROD_STBY/','/PROD/' scope=spfile;
System altered.
- Set db_file_name_convert
SQL> alter system set db_file_name_convert = '/PROD_STBY/','/PROD/' scope=spfile;
System altered.
- Set pdb_file_name_convert
SQL> alter system set pdb_file_name_convert = '/PROD_STBY/','/PROD/' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
- Create standby redo log files
-- check redolog files and groups
SQL> set lines 200
SQL> --set pages 200
SQL> select group#, type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ---------------------------------------------
3 ONLINE +DATA/PROD/ONLINELOG/group_3.263.1073580527
3 ONLINE +FRA/PROD/ONLINELOG/group_3.259.1073580527
2 ONLINE +DATA/PROD/ONLINELOG/group_2.262.1073580527
2 ONLINE +FRA/PROD/ONLINELOG/group_2.258.1073580527
1 ONLINE +DATA/PROD/ONLINELOG/group_1.261.1073580527
1 ONLINE +FRA/PROD/ONLINELOG/group_1.257.1073580527
6 rows selected.
SQL> ALTER DATABASE ADD STANDBY LOGFILE Group 4 ('+DATA','+FRA') size 200M;
Database altered.
SQL> select group#, type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ---------------------------------------------
3 ONLINE +DATA/PROD/ONLINELOG/group_3.263.1073580527
3 ONLINE +FRA/PROD/ONLINELOG/group_3.259.1073580527
2 ONLINE +DATA/PROD/ONLINELOG/group_2.262.1073580527
2 ONLINE +FRA/PROD/ONLINELOG/group_2.258.1073580527
1 ONLINE +DATA/PROD/ONLINELOG/group_1.261.1073580527
1 ONLINE +FRA/PROD/ONLINELOG/group_1.257.1073580527
4 STANDBY +DATA/PROD/ONLINELOG/group_4.275.1095431659
4 STANDBY +FRA/PROD/ONLINELOG/group_4.262.1095431659
8 rows selected.
SQL> ALTER DATABASE ADD STANDBY LOGFILE Group 5 ('+DATA','+FRA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE Group 6 ('+DATA','+FRA') size 200M;
Database altered.
SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024||' MB' as REDO_SIZE ,MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# REDO_SIZE MEMBERS STATUS
---------- ---------- ---------- ------------------------------------------- ---------- ----------------
1 1 16 200 MB 2 CURRENT
2 1 14 200 MB 2 INACTIVE
3 1 15 200 MB 2 INACTIVE
SQL> col REDO_SIZE for a10
SQL> /
GROUP# THREAD# SEQUENCE# REDO_SIZE MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
1 1 16 200 MB 2 CURRENT
2 1 14 200 MB 2 INACTIVE
3 1 15 200 MB 2 INACTIVE
SQL> select group#, type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ---------------------------------------------
3 ONLINE +DATA/PROD/ONLINELOG/group_3.263.1073580527
3 ONLINE +FRA/PROD/ONLINELOG/group_3.259.1073580527
2 ONLINE +DATA/PROD/ONLINELOG/group_2.262.1073580527
2 ONLINE +FRA/PROD/ONLINELOG/group_2.258.1073580527
1 ONLINE +DATA/PROD/ONLINELOG/group_1.261.1073580527
1 ONLINE +FRA/PROD/ONLINELOG/group_1.257.1073580527
4 STANDBY +DATA/PROD/ONLINELOG/group_4.275.1095431659
4 STANDBY +FRA/PROD/ONLINELOG/group_4.262.1095431659
5 STANDBY +DATA/PROD/ONLINELOG/group_5.276.1095431677
5 STANDBY +FRA/PROD/ONLINELOG/group_5.263.1095431677
6 STANDBY +DATA/PROD/ONLINELOG/group_6.277.1095431683
GROUP# TYPE MEMBER
---------- ------- ---------------------------------------------
6 STANDBY +FRA/PROD/ONLINELOG/group_6.264.1095431683
12 rows selected.
SQL>
- Create Password file
# using oracle user
orapwd file=+DATA/PROD/orapwPROD dbuniquename=prod password=password1 force=y format=12
- Create user with table and insert sample data for testing
[oracle@primary ~]$ rlwrap sqlplus sys@prodpdb1_p as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 1 11:57:10 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PRODPDB1 READ WRITE
SQL> create user test_user identified by testuser123;
User created.
SQL> alter user test_user quota 100M on system;
User altered.
SQL> alter user test_user quota 100M on users;
User altered.
SQL> grant connect,resource to test_user;
Grant succeeded.
SQL> connect test_user@prodpdb1_p
Enter password:
Connected.
SQL> create table test (id number(10),name varchar2(35));
Table created.
SQL> insert into test values (1,'test_name');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- -----------------------------------
1 test_name
SQL>
- Collect files and take backup to /backup directory
# get pfile from spfile (Oracle user)
SQL> create pfile='/backup/pfilePROD.ora' from spfile;
File created.
SQL> ! ls -l /backup
total 4
-rw-r--r--. 1 oracle asmadmin 1448 Feb 1 12:23 pfilePROD.ora
SQL>
# get passwd file from ASM (Grid User)
[grid@primary ~]$ asmcmd pwget --dbuniquename prod
+DATA/PROD/orapwprod
[grid@primary ~]$ asmcmd pwcopy +DATA/PROD/orapwprod /backup/orapwprod
copying +DATA/PROD/orapwprod -> /backup/orapwprod
[grid@primary ~]$
# Configure RMAN and take backup
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/prod/%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/prod/%U';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
# run them as run block
run {
backup database;
backup archivelog all;
backup current controlfile for standby format '/backup/prod/stby_ctl_%d_%u_%s';
}
# or run them individually
RMAN> backup database;
Starting backup at 01-FEB-2022 12:37:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=145 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=408 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
---snipped---
channel ORA_DISK_1: starting piece 1 at 01-FEB-2022 12:37:50
channel ORA_DISK_2: finished piece 1 at 01-FEB-2022 12:37:50
piece handle=/backup/prod/060kob0l_1_1 tag=TAG20220201T123714 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_3: finished piece 1 at 01-FEB-2022 12:37:51
piece handle=/backup/prod/070kob0m_1_1 tag=TAG20220201T123714 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_1: finished piece 1 at 01-FEB-2022 12:37:52
piece handle=/backup/prod/080kob0u_1_1 tag=TAG20220201T123714 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01-FEB-2022 12:37:52
Starting Control File and SPFILE Autobackup at 01-FEB-2022 12:37:53
piece handle=/backup/prod/c-485418859-20220201-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-FEB-2022 12:37:58
RMAN> backup archivelog all;
Starting backup at 01-FEB-2022 12:38:22
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
----snipped----
piece handle=/backup/prod/0b0kob1v_1_1 tag=TAG20220201T123823 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 01-FEB-2022 12:38:26
Starting Control File and SPFILE Autobackup at 01-FEB-2022 12:38:26
piece handle=/backup/prod/c-485418859-20220201-01 comment=NONE
Finished Control File and SPFILE Autobackup at 01-FEB-2022 12:38:27
RMAN> backup current controlfile for standby format '/backup/prod/stby_ctl_%d_%u_%s';
Starting backup at 01-FEB-2022 12:38:50
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 01-FEB-2022 12:38:51
channel ORA_DISK_1: finished piece 1 at 01-FEB-2022 12:38:52
piece handle=/backup/prod/stby_ctl_PROD_0d0kob2q_13 tag=TAG20220201T123850 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-FEB-2022 12:38:52
Starting Control File and SPFILE Autobackup at 01-FEB-2022 12:38:52
piece handle=/backup/prod/c-485418859-20220201-02 comment=NONE
Finished Control File and SPFILE Autobackup at 01-FEB-2022 12:38:53
RMAN>
# Or you can duplicate Database using
RMAN> run {
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='prod_stby' COMMENT 'Is standby' SET db_file_name_convert='/prod/','/prod_stby/' SET log_file_name_convert='/prod/','/prod_stby/';
}
- Tar the directory and shipped it to the standby database.
[oracle@primary backup]$ tar -czvf backup.tar.gz *
orapwprod
pfilePROD.ora
prod/
prod/020l4d7o_1_1
prod/040l4d7p_1_1
prod/030l4d7p_1_1
prod/050l4d80_1_1
prod/060l4d84_1_1
prod/070l4d86_1_1
prod/080l4d8d_1_1
prod/stby_ctl_PROD_090l4d8i_9
prod/c-485418859-20220206-01
[oracle@primary ~]$
[oracle@primary backup]$ scp backup.tar.gz oracle@standby:~
oracle@standby's password:
backup.tar.gz 100% 450MB 97.3MB/s 00:04
[oracle@primary backup]$
Standby Steps
-
Un-Tar backup file
# as oracle user tar -xvf backup.tar.gz -C /backup -
Edit pfile using vi/vim
# Edit pfile vim /backup/pfilePROD.ora # change below parameters to be same as below *.db_file_name_convert='/PROD/','/PROD_STBY/' *.log_file_name_convert='/PROD/','/PROD_STBY/' *.pdb_file_name_convert='/PROD/','/PROD_STBY/' *.db_unique_name='prod_stby' *.control_files='+DATA/PROD_STBY/CONTROLFILE/current.260.1073580523','+FRA/PROD_STBY/CONTROLFILE/current.256.1073580523' *.log_archive_dest_1='LOCATION=+FRA/PROD_STBY/arch' *.fal_client='PROD_STBY' *.fal_server='PROD' # create below directorys if not created # oracle base mkdir -p $ORACLE_BASE/admin/prod/adump
-
Create Standby instance
rlwrap sqlplus / as sysdba SQL> startup nomount pfile='/backup/pfilePROD.ora'; SQL> create spfile='+DATA/PROD_STBY/spfilePROD.ora' from pfile='/backup/pfilePROD.ora'; vim $ORACLE_HOME/dbs/initprod.ora # add this to initprod.ora SPFILE='+DATA/PROD_STBY/spfilePROD.ora' SQL> startup nomount ORACLE instance started. Total System Global Area 2415918608 bytes Fixed Size 9137680 bytes Variable Size 520093696 bytes Database Buffers 1879048192 bytes Redo Buffers 7639040 bytes SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string PROD_STBY fal_server string PROD SQL> show parameter convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /PROD/, /PROD_STBY/ log_file_name_convert string /PROD/, /PROD_STBY/ pdb_file_name_convert string /PROD/, /PROD_STBY/ SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- --------------------------- control_files string +DATA/PROD_STBY/CONTROLFILE/current.260.1073580523, +FRA/PRO D_STBY/CONTROLFILE/current.256.1073580523 SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=+FRA/PROD_STBY/arch SQL> show parameter db_uniq NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string prod_stby SQL>
-
Restore standby control file and mount database
RMAN> restore controlfile from '/backup/prod/stby_ctl_PROD_0d0kob2q_13'; Starting restore at 05-FEB-2022 06:18:43 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=136 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=+DATA/PROD_STBY/CONTROLFILE/current.260.1095833925 output file name=+FRA/PROD_STBY/CONTROLFILE/current.256.1095833925 Finished restore at 05-FEB-2022 06:18:45 RMAN> RMAN> alter database mount standby database; Statement processed RMAN> list backup;
-
Restore database
RMAN> restore database; Starting restore at 05-FEB-2022 06:54:12 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: restoring datafile 00001 input datafile copy RECID=17 STAMP=1095835920 file name=+DATA/PROD_STBY/DATAFILE/system.259.1095835915 destination for restore of datafile 00001: +DATA/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to +DATA/MUST_RENAME_THIS_DATAFILE_3.4294967295.4294967295 channel ORA_DISK_1: restoring datafile 00004 to +DATA/MUST_RENAME_THIS_DATAFILE_4.4294967295.4294967295 channel ORA_DISK_1: reading from backup piece /backup/prod/030koavr_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00009 to +DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295 channel ORA_DISK_2: restoring datafile 00011 to +DATA/MUST_RENAME_THIS_DATAFILE_11.4294967295.4294967295 channel ORA_DISK_2: reading from backup piece /backup/prod/040koavs_1_1 channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00001 to +DATA/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295 channel ORA_DISK_3: restoring datafile 00007 to +DATA/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295 channel ORA_DISK_3: reading from backup piece /backup/prod/020koavr_1_1 channel ORA_DISK_1: piece handle=/backup/prod/030koavr_1_1 tag=TAG20220201T123714 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to +DATA/MUST_RENAME_THIS_DATAFILE_5.4294967295.4294967295 channel ORA_DISK_1: reading from backup piece /backup/prod/050kob0l_1_1 channel ORA_DISK_2: piece handle=/backup/prod/040koavs_1_1 tag=TAG20220201T123714 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:03 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00006 to +DATA/MUST_RENAME_THIS_DATAFILE_6.4294967295.4294967295 channel ORA_DISK_2: reading from backup piece /backup/prod/070kob0m_1_1 channel ORA_DISK_3: piece handle=/backup/prod/020koavr_1_1 tag=TAG20220201T123714 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:00:03 channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00010 to +DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295 channel ORA_DISK_3: restoring datafile 00012 to +DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295 channel ORA_DISK_3: reading from backup piece /backup/prod/060kob0l_1_1 channel ORA_DISK_1: piece handle=/backup/prod/050kob0l_1_1 tag=TAG20220201T123714 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to +DATA/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295 channel ORA_DISK_1: reading from backup piece /backup/prod/080kob0u_1_1 channel ORA_DISK_2: piece handle=/backup/prod/070kob0m_1_1 tag=TAG20220201T123714 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 channel ORA_DISK_3: piece handle=/backup/prod/060kob0l_1_1 tag=TAG20220201T123714 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: piece handle=/backup/prod/080kob0u_1_1 tag=TAG20220201T123714 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 05-FEB-2022 06:54:20
-
Recover database
RMAN> catalog start with '+DATA/PROD_STBY'; searching for all files that match the pattern +DATA/PROD_STBY List of Files Unknown to the Database ===================================== File Name: +DATA/PROD_STBY/spfileprod.ora Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... no files cataloged List of Files Which Were Not Cataloged ======================================= File Name: +DATA/PROD_STBY/spfileprod.ora RMAN-07518: Reason: Foreign database file DBID: 0 Database Name: RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/PROD_STBY/DATAFILE/system.262.1095836055" datafile 3 switched to datafile copy "+DATA/PROD_STBY/DATAFILE/sysaux.256.1095836055" datafile 4 switched to datafile copy "+DATA/PROD_STBY/DATAFILE/undotbs1.265.1095836055" datafile 5 switched to datafile copy "+DATA/PROD_STBY/C342D1D0D2E07ACDE05382140A0AE26E/DATAFILE/system.268.1095836059" datafile 6 switched to datafile copy "+DATA/PROD_STBY/C342D1D0D2E07ACDE05382140A0AE26E/DATAFILE/sysaux.273.1095836059" datafile 7 switched to datafile copy "+DATA/PROD_STBY/DATAFILE/users.264.1095836055" datafile 8 switched to datafile copy "+DATA/PROD_STBY/C342D1D0D2E07ACDE05382140A0AE26E/DATAFILE/undotbs1.277.1095836059" datafile 9 switched to datafile copy "+DATA/PROD_STBY/C342F3B7488D84D9E05382140A0A2185/DATAFILE/system.266.1095836055" datafile 10 switched to datafile copy "+DATA/PROD_STBY/C342F3B7488D84D9E05382140A0A2185/DATAFILE/sysaux.279.1095836059" datafile 11 switched to datafile copy "+DATA/PROD_STBY/C342F3B7488D84D9E05382140A0A2185/DATAFILE/undotbs1.259.1095836055" datafile 12 switched to datafile copy "+DATA/PROD_STBY/C342F3B7488D84D9E05382140A0A2185/DATAFILE/users.278.1095836059" RMAN> recover database; Starting recover at 05-FEB-2022 06:59:14 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=17 channel ORA_DISK_1: reading from backup piece /backup/prod/0b0kob1v_1_1 channel ORA_DISK_1: piece handle=/backup/prod/0b0kob1v_1_1 tag=TAG20220201T123823 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=+FRA/prod_stby/arch/1_17_1073580526.dbf thread=1 sequence=17 media recovery complete, elapsed time: 00:00:01 Finished recover at 05-FEB-2022 06:59:17 RMAN>
-
Create Standby database service.
srvctl add database -d prod_stby -o $ORACLE_HOME -p '+DATA/PROD_STBY/spfilePROD.ora' -r PHYSICAL_STANDBY -s 'mount' -n standby -a 'DATA,FRA' -pwfile '+DATA/PROD_STBY/orapwprod' [oracle@standby ~]$ srvctl config database -d prod_stby Database unique name: prod_stby Database name: standby Oracle home: /u01/19c/oracle_base/oracle/db_home Oracle user: oracle Spfile: +DATA/PROD_STBY/spfilePROD.ora Password file: +DATA/PROD_STBY/orapwprod Domain: Start options: mount Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Disk Groups: DATA,FRA Services: OSDBA group: OSOPER group: Database instance: prodstby ## use grid asmcmd pwcopy --dbuniquename prod_stby /backup/orapwprod +data/prod_stby/orapwprod -f ## use oracle [oracle@standby ~]$ srvctl modify database -d prod_stby -i prod [oracle@standby ~]$ srvctl config database -d prod_stby Database unique name: prod_stby Database name: standby Oracle home: /u01/19c/oracle_base/oracle/db_home Oracle user: oracle Spfile: +DATA/PROD_STBY/spfilePROD.ora Password file: +DATA/PROD_STBY/orapwprod Domain: Start options: mount Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Disk Groups: DATA,FRA Services: OSDBA group: OSOPER group: Database instance: prod [oracle@standby ~]$ [oracle@standby ~]$ srvctl stop database -d prod_stby -force [oracle@standby ~]$ srvctl start database -d prod_stby [oracle@standby ~]$ srvctl status database -d prod_stby Database is running. [oracle@standby ~]$ # using grid check the listener status and serives lsnrctl services lsnrctl status lsnrctl reload srvctl stop listener srvctl start listner # using sql register the service name SQL> select value from v$parameter where name='service_names'; VALUE --------------------------------------------------------------------- prod_stby SQL> alter system register; System altered. SQL> alter session set container=prodpdb1; Session altered. SQL> alter system register; System altered. SQL> exit
Configure Broker
-
Create Broker Data file for both (PRIMARY,STANDBY)
# using sqlplus on Primary SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/PROD/DGCONF/dgb_conf_01.dat'; System altered. SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/PROD/DGCONF/dgb_conf_02.dat'; System altered. SQL> SQL> show parameter dg_broker_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------- dg_broker_config_file1 string +DATA/PROD/DGCONF/dgb_conf_01.dat dg_broker_config_file2 string +FRA/PROD/DGCONF/dgb_conf_02.dat SQL> # Using sqlplus on Standby SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/PROD_STBY/DGCONF/dgb_conf_01.dat'; System altered. SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/PROD_STBY/DGCONF/dgb_conf_02.dat'; System altered. SQL> SQL> show parameter dg_broker_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string +DATA/PROD_STBY/DGCONF/dgb_conf_01.dat dg_broker_config_file2 string +FRA/PROD_STBY/DGCONF/dgb_conf_02.dat SQL>
-
Start Broker on both (primary & standby)
SQL> ALTER SYSTEM SET dg_broker_start=true; System altered. SQL>
-
Create configuration using gdmgrl command
select name,open_mode,database_role from v$database; dgmgrl sys@prod DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Feb 5 08:44:42 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Password: Connected to "prod" Connected as SYSDBA. DGMGRL> DGMGRL> CREATE CONFIGURATION PROD_DB_DG_CONFIG AS PRIMARY DATABASE IS prod CONNECT IDENTIFIER IS prod; Configuration "prod_db_dg_config" created with primary database "prod" DGMGRL> DGMGRL> ADD DATABASE prod_stby AS CONNECT IDENTIFIER IS prod_stby; Database "prod_stby" added DGMGRL> DGMGRL> DGMGRL> show configuration Configuration - prod_db_dg_config Protection Mode: MaxPerformance Members: prod - Primary database prod_stby - Physical standby database Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL> DGMGRL> enable configuration; Enabled. DGMGRL> DGMGRL> show configuration Configuration - prod_db_dg_config Protection Mode: MaxPerformance Members: prod - Primary database prod_stby - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 59 seconds ago) DGMGRL> DGMGRL> show database prod_stby Database - prod_stby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 152.00 KByte/s Real Time Query: OFF Instance(s): prod Database Status: SUCCESS DGMGRL>
Switchover
To monitor both node you need to run below command:
# primary
tail -f $ORACLE_BASE/diag/rdbms/prod/prod/trace/alert_prod.log
#standby
tail -f $ORACLE_BASE/diag/rdbms/prod_stby/prod/trace/alert_prod.log
-
Validate switchover
DGMGRL> validate database prod; Database Role: Primary database Ready for Switchover: Yes Flashback Database Status: prod: Off Managed by Clusterware: prod: YES DGMGRL> validate database prod_stby; Database Role: Physical standby database Primary Database: prod Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: prod : Off prod_stby: Off Managed by Clusterware: prod : YES prod_stby: YES Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (prod) (prod_stby) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (prod_stby) (prod) 1 3 0 Insufficient SRLs Warning: standby redo logs not configured for thread 1 on prod DGMGRL> DGMGRL> show configuration Configuration - prod_db_dg_config Protection Mode: MaxPerformance Members: prod - Primary database prod_stby - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 56 seconds ago) DGMGRL>
-
switchover to prod_stby
DGMGRL> switchover to prod_stby; Performing switchover NOW, please wait... Operation requires a connection to database "prod_stby" Connecting ... Connected to "prod_stby" Connected as SYSDBA. New primary database "prod_stby" is opening... Oracle Clusterware is restarting database "prod" ... Connected to "prod" Connected to "prod" Switchover succeeded, new primary is "prod_stby" DGMGRL> show configuration; Configuration - prod_db_dg_config Protection Mode: MaxPerformance Members: prod_stby - Primary database prod - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 78 seconds ago) DGMGRL>
-
switchback to prod
DGMGRL> show configuration Configuration - prod_db_dg_config Protection Mode: MaxPerformance Members: prod_stby - Primary database prod - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 22 seconds ago) DGMGRL> DGMGRL> switchover to prod; Performing switchover NOW, please wait... Operation requires a connection to database "prod" Connecting ... Connected to "prod" Connected as SYSDBA. New primary database "prod" is opening... Oracle Clusterware is restarting database "prod_stby" ... Connected to "prod_stby" Connected to "prod_stby" Switchover succeeded, new primary is "prod" DGMGRL> DGMGRL> show configuration Configuration - prod_db_dg_config Protection Mode: MaxPerformance Members: prod - Primary database prod_stby - Physical standby database Warning: ORA-16854: apply lag could not be determined Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 55 seconds ago) DGMGRL>
Automate Standby archive Logs Deletion
-
Create RMAN script.
cd ~ mkdir -p scripts/logs cd scripts vim delete_arch.sh ## add below by copy paste to script file #!/bin/bash export ORACLE_SID=prod rman target / <<EOF run{ delete noprompt archivelog until time 'sysdate-0.5'; } exit EOF
-
Test RMAN script.
chmod +x delete_arch.sh ./delete_arch.sh
-
Crontab script.
05 23 * * * . /home/oracle/.bash_profile; /home/oracle/scripts/delete_arch.sh 2>&1 > /home/oracle/scripts/delete_arch.$(date +\%Y\%m\%d-\%H\%M\%S).cron.log
