Creating a Physical Standby Database on Oracle Version 19.6.0.0.0
PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.
Basic high level steps:
1. Check on Primary DB
the FORCE_LOGGING is enabled.
2. Primary DB need to
be in archivelog mode.
3. Create pfile from
PRIMARY DB and copy to the STANDBY DB.
4. Modify required
parameter for PRIMARY DB.
5. Copy password file
from PRIMARY DB to STANDBY DB.
6. Edit required
parameter on pfile on STANDBY DB and Create from the pfile spfile.
7. Standby DB need to
be in nomount stage to create STANDBY DB.
8. Size of redo on
standby must be same as primary.
9. Create the same
number of Standby Redo Logfile for both production and standby databases.
10. Configure LISTENER
and entry in TNSNAMES.ORA for both PRIMARY DB and STANDBY DB.
11. Start Listener on
STANDBY DB and check the connection to PRIMARYDB. For example tnsping etc.
12. Prepare RMAN
script and Check the RMAN connection from STANDBY DB. Whereby PRIMARY_DB=TARGET
and STANDBY_DB=AUXILIARY.
13. Enable recovery
process MRP.
14. Check Standby is
in sync.
15. If required any
scrips, cronjob etc. on both side.
Following are the Environment:
Hostname : edu01.th.com
Primary DB :
EDUDB01
DB VERSION :
Version 19.6.0.0.0
DB Home Path :
/oracle19c/product/19.6.0.0/db
OS:
: Red Hat Enterprise Linux Server
release 7.7
Hostname : edu02.th.com
Standby DB :
EDUDB02
DB VERSION :
Version 19.6.0.0.0
DB Home Path :
/oracle19c/product/19.6.0.0/db
OS:
: Red Hat Enterprise Linux
Server release 7.7
NOTE: You can keep same UNIQUE NAME and DB NAME on Primary.
PRIMARY:
DB UNIQUE NAME=EDUDB
DB NAME=EDUDB
STANDBY:
DB UNIQUE NAME=EDUDB02
DB NAME=EDUDB
On PrimaryDB:
1. Check Archive log mode enable and force logging enabled.
SQL> archive log
list;
Database log
mode No Archive Mode
Automatic
archival Disabled
Archive
destination
/oracle19c/product/19.6.0.0/db/dbs/arch <<=====
I am going to change the Archive destination as well as.
Oldest online log
sequence 24
Current log
sequence 26
# This parameter log_archive_dest_1 can be
changed online.
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=spfile;
SQL> show
parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_1
string
LOCATION=USE_DB_RECOVERY_FILE_DEST
...
...
SQL>shutdown
immediate;
SQL>startup mount;
SQL>alter database
archivelog;
SQL> alter
database open;
SQL>archive log
list;
-- Automatic archival Enabled
2. Enable Force Logging on primary.
SQL> select FORCE_LOGGING from v$database;
SQL> ALTER DATABASE FORCE LOGGING;
3. Change required parameter on Primary DB
LOG_ARCHIVE_DEST_2 = is valid only for the primary role.
This destination transmits redo data to the remote physical standby destination
FAL_SERVER= Add both standby server, that oracle knows from
where to get arch files in case of switchover.
FAL_SERVER=EDUDB02
FAL_CLIENT= This is always the primary server. The parameter is
ignored when the DB is in Primary mode.
FAL_CLIENT=EDUDB01
Db_name=edudb # Same
across all DB’s
Db_unique_name=EDUDB01 # unique on each databases
SQL> show parameter DB_UNIQUE_NAME;
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
db_unique_name
string EDUDB01
SQL> show
parameter db_name
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
db_name
string edudb
alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(EDUDB01, EDUDB02)' SCOPE=both;;
alter system set
LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(all_logfiles,all_roles)
DB_UNIQUE_NAME=EDUDB01' SCOPE=both;
alter system set
LOG_ARCHIVE_DEST_2='SERVICE=EDUDB02 LGWR async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=EDUDB02';
alter system set
log_archive_dest_state_2=defer/enable;
You
can increase the parameter value based on your requirement. I am going to keep
as it is.
log_archive_max_processes=4
alter system set log_archive_max_processes=16;
<<== If you need to increase the value.
alter system set
FAL_SERVER=EDUDB02;# standbydb
alter system set
FAL_CLIENT=EDUDB01; # it's a primary self
ALTER SYSTEM SET
STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
alter system set
db_file_name_convert='+DATA','+DATA' scope=spfile;
alter system set
LOG_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;
4.
You can create multiple service_name, if needed.
alter system set service_names='edudb01','edudb','edudb02' scope = both;
5.
NOTE: Size of redo on standby must be same as
primary. You can find the size of redo log as follow.
Create the same number of SRLs for both
production and standby databases. The SRLs should exist on both production and
standby databases.
The Size should match on both side of the
ONLINE LOGFILES.
set linesize 200
col MEMBER format a50
select
lg.group#,lg.thread#,sequence#,lg.bytes/1024/1024
"sizeMB",lg.archived,lg.status,le.type,le.member from v$log
lg,v$logfile le
where
lg.group#=le.group#
/
You can create standby redo log file on primary DB as follow.
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 11 '+DATA' size 200m;
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 12 '+DATA' size 200m;
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 13 '+DATA' size 200m;
Check the online and standby redolog file status.
SQL> col MEMBER format a50
select
lg.group#,lg.thread#,sequence#,lg.bytes/1024/1024
"sizeMB",lg.archived,lg.status,le.type,le.member from v$log
lg,v$logfile le where lg.group#=le.group#
/
GROUP#
THREAD# SEQUENCE# sizeMB ARC STATUS TYPE MEMBER
---------- ----------
---------- ---------- --- ---------------- -------
--------------------------------------------------
3 1 24 200 NO
INACTIVE ONLINE +DATA/EDUDB/ONLINELOG/group_3.264.1034096339
2
1 26 200 NO
CURRENT ONLINE +DATA/EDUDB/ONLINELOG/group_2.263.1034096339
1 1 25 200 NO
INACTIVE ONLINE +DATA/EDUDB/ONLINELOG/group_1.262.1034096339
SQL> select
sg.group#,sg.thread#,sg.sequence#,sg.bytes/1024/1024
"sizeMB",sg.archived,sg.status,le.type,le.member
from V$STANDBY_LOG
sg,v$logfile le
where
le.group#=sg.group#
/
GROUP# THREAD#
SEQUENCE# sizeMB ARC
STATUS TYPE MEMBER
---------- ---------- ---------- ---------- --- ---------- -------
--------------------------------------------------
11 0 0 200 YES UNASSIGNED STANDBY
+DATA/EDUDB/ONLINELOG/redo11.log
12 0 0 200 YES UNASSIGNED STANDBY
+DATA/EDUDB/ONLINELOG/redo12.log
13 0 0 200 YES UNASSIGNED STANDBY
+DATA/EDUDB/ONLINELOG/redo13.log
6.
Establish the connectivity,
Create net services on both primary and standby
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
EDUDB01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
edu01.th.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = edudb01)
)
)
EDUDB02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
edu02.th.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = edudb02)
)
)
IMPORTENT:
Add following line in the listener.ora file, if you see tns connection
issue with service_name
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=edudb)
(ORACLE_HOME=/oracle19c/product/19.6.0.0/db)
(SID_NAME=EDUDB01)
)
)
7. Create pfile from spfile.
SQL> create
pfile='/oracle19c/tmp/initEDUDB01.ora' from spfile;
File created.
scp initEDUDB01.ora to the standbyDB
scp orapwEDUDB01 file to the standby and rename it mv orapwEDUDB01 orapwEDUDB02
On StandbyDB:
1.
copy the initfile to the standby DB, which we created erlier on primary
DB.
scp initEDUDB01.ora from primaryDB to standbyDB and edit the required
parameters.
2.
LOG_ARCHIVE_DEST_STATE_2 Is ignored; valid
only when “EDUDB02” is running in a primary role.
db_name=EDUDB
db_unique_name=EDUDB02
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
standby_file_management=AUTO
FAL_SERVER=EDUDB01; # other server
FAL_CLIENT=EDUDB02; # it's a server DB self
alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(EDUDB01, EDUDB02)';
alter system set
LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=EDUDB02';
alter system set
LOG_ARCHIVE_DEST_2='SERVICE=EDUDB01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=EDUDB01';
alter system set
log_archive_dest_state_2=defer/enable;
alter system set
DB_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;
alter system set
LOG_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;
You may need set also the following
parameter. Otherwise, you could see the following error.
ORACLE error from target database:
ORA-38757: Database must be mounted and
not open to FLASHBACK.
Check the value first:
SELECT name, value FROM v$parameter WHERE
name LIKE 'db_recovery%';
change the
value
ALTER SYSTEM SET
db_recovery_file_dest='/oracle19c/flashback' SCOPE=BOTH;
ALTER SYSTEM SET
db_recovery_file_dest_size=2g SCOPE=BOTH;
3.
on standby:
startup nomount pfile='/oracle19c/product/19.6.0.0/db/dbs/initEDUDB01.ora';
create spfile='/oracle19c/product/19.6.0.0/db/dbs/spfileEDUDB02.ora' from memory;
shutdown immediate;
startup nomount;
show parameter spfile;
As DB:
srvctl add database -d ${ORACLE_SID} -o /oracle19/dbbase/db -p '/oracle19/dbbase/db/dbs/spfile'"${ORACLE_SID}"'.ora' -pwfile '/oracle19/dbbase/db/dbs/orapw'"${ORACLE_SID}"''
4. Create passwd file:
NOTE:
If auxiliary database has following error:
RMAN-04006: error from auxiliary database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
No worry, because the DB is not open.
The blocked status is
being set by the instance, indicating that the database is not able to accept
connections.
This status can be
caused by the instance not being mounted or being mounted in a restricted
state.
Make sure, that the orapwd
file is created. The file must present on both side.
-- orapwd file='$ORACLE_HOME/dbs/orapwEDUDB01' entries=10 force=y
password='xxxxx' force=y format=12.2
orapwd
file=$ORACLE_HOME/dbs/orapwEDUDB01 password=oracle format=12 force=y
5.
Before you start RMAN script make sure
that connections works.
tnsping EDUDB01
tnsping EDUDB02
sqlplus
sys/password@EDUDB01 as sysdba
sqlplus
sys/password@EDUDB02 as sysdba
rman target
sys/password@EDUDB01 auxiliary sys/password@EDUDB02
rman target
sys/password@EDUDB01 auxiliary /
From Standby DB:
[oracle@edu02 ~]$ rman target sys/oracle@EDUDB01
auxiliary sys/oracle@EDUDB02
Recovery Manager:
Release 19.0.0.0.0 - Production on Mon Mar 16 10:08:21 2020
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle and/or its affiliates. All
rights reserved.
connected to target
database: EDUDB (DBID=3803574607)
connected to
auxiliary database: EDUDB (not mounted)
From Primary DB:
[oracle@edu01 admin]$
rman target sys/oracle@EDUDB02 auxiliary sys/oracle@EDUDB02
Recovery Manager:
Release 19.0.0.0.0 - Production on Fri Mar 13 17:24:58 2020
Version 19.6.0.0.0
Copyright (c) 1982,
2019, Oracle and/or its affiliates. All
rights reserved.
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in TARGET
database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in TARGET database is
not current
connected to target database: EDUDB (not mounted)
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in AUXILIARY
database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in AUXILIARY database
is not current
connected to auxiliary database: EDUDB (not mounted)
6.
[oracle@edu02 ~]$ rman target sys/oracle@EDUDB01
auxiliary sys/oracle@EDUDB02
Recovery Manager:
Release 19.0.0.0.0 - Production on Mon Mar 16 14:36:24 2020
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle and/or its affiliates. All
rights reserved.
connected to target
database: EDUDB (DBID=3803574607)
connected to
auxiliary database: EDUDB (not mounted)
RMAN> duplicate target
database for standby from active database nofilenamecheck;
-- This is log output.
Starting Duplicate Db at 16-MAR-20
using target database control file instead
of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=265 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format
'/oracle19c/product/19.6.0.0/db/dbs/orapwEDUDB02' ;
}
executing Memory Script
Starting backup at 16-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device
type=DISK
Finished backup at 16-MAR-20
duplicating Online logs to Oracle Managed
File (OMF) location
duplicating Datafiles to Oracle Managed
File (OMF) location
contents of Memory Script:
{
sql clone "alter system set
control_files =
''+DATA/EDUDB02/CONTROLFILE/current.260.1035210993''
comment=
''Set by RMAN'' scope=spfile";
restore clone from service
'EDUDB01' standby controlfile;
}
executing Memory Script
sql statement: alter system set control_files =
''+DATA/EDUDB02/CONTROLFILE/current.260.1035210993'' comment= ''Set by
RMAN'' scope=spfile
Starting restore at 16-MAR-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: restoring control
file
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
output file
name=+DATA/EDUDB02/CONTROLFILE/current.261.1035210995
Finished restore at 16-MAR-20
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby
database
contents of Memory Script:
{
set newname for clone tempfile 1
to new;
set newname for clone tempfile 2
to new;
set newname for clone tempfile 3
to new;
switch clone tempfile all;
set newname for clone datafile 1
to new;
set newname for clone datafile 3
to new;
set newname for clone datafile 4
to new;
set newname for clone datafile 5
to new;
set newname for clone datafile 6
to new;
set newname for clone datafile 7
to new;
set newname for clone datafile 8
to new;
set newname for clone datafile 9
to new;
set newname for clone datafile 10
to new;
set newname for clone datafile 11
to new;
set newname for clone datafile 12
to new;
restore
from nonsparse from service
'EDUDB01'
clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-MAR-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00001 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00003 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00004 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00005 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00006 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00007 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00008 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00009 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00010 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00011 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: using network
backup set from service EDUDB01
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00012 to +DATA
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
Finished restore at 16-MAR-20
sql statement: alter system archive log
current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=15
STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/system.266.1035211001
datafile 3 switched to datafile copy
input datafile copy RECID=16
STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/sysaux.267.1035211003
datafile 4 switched to datafile copy
input datafile copy RECID=17
STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/undotbs1.259.1035211007
datafile 5 switched to datafile copy
input datafile copy RECID=18
STAMP=1035211016 file name=+DATA/EDUDB02/9FF6693B4B0B13A0E0531F4614ACC2BF/DATAFILE/system.258.1035211007
datafile 6 switched to datafile copy
input datafile copy RECID=19
STAMP=1035211016 file
name=+DATA/EDUDB02/9FF6693B4B0B13A0E0531F4614ACC2BF/DATAFILE/sysaux.268.1035211009
datafile 7 switched to datafile copy
input datafile copy RECID=20
STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/users.265.1035211009
datafile 8 switched to datafile copy
input datafile copy RECID=21
STAMP=1035211016 file
name=+DATA/EDUDB02/9FF6693B4B0B13A0E0531F4614ACC2BF/DATAFILE/undotbs1.263.1035211011
datafile 9 switched to datafile copy
input datafile copy RECID=22
STAMP=1035211016 file
name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/system.270.1035211013
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=1035211016
file
name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/sysaux.262.1035211013
datafile 11 switched to datafile copy
input datafile copy RECID=24
STAMP=1035211016 file
name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/undotbs1.264.1035211015
datafile 12 switched to datafile copy
input datafile copy RECID=25
STAMP=1035211016 file
name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/users.272.1035211015
Finished Duplicate Db at 16-MAR-20
6.
Enable the log_archive_dest_2 on primary DB, which we have set as defer
earlier.
7.
SQL> ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select
name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
---------
---------------- --------------------
EDUDB PHYSICAL STANDBY MOUNTED
8:
You can test whether the lofile shipping to the Physical DB
SQL> SELECT
PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
---------
------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0
0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 49 0 0
RFS IDLE 1 0 0 0
RFS IDLE 1 49
21945 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
PROCESS STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
---------
------------ ---------- ---------- ---------- ----------
RFS IDLE 0 0 0 0
12 rows selected.
Execute few times following commands on primary DB.
alter system switch
logfile；
alter system switch
logfile;
Check on Stand by DB again.
SQL> SELECT
PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
---------
------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0
0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0
0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 51 0 0
RFS IDLE 1 0 0 0
RFS IDLE 1 51 24 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
PROCESS STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
---------
------------ ---------- ---------- ---------- ----------
RFS IDLE 0 0 0 0
12 rows selected.
-- On standby DB
SQL> SELECT
ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD#
,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#
,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# =
APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence
Applied
----------
---------------------- ---------------------
1 50 50
Completed.
