1] Concepts
1.1 Dataguard DB Roles
1.2 [10G+] Real Time Apply
1.3 Protection Mode
There
are three protection modes for the primary database:
- Maximum
Availability: Transactions
on the primary do not commit until redo information has been written to
the online redo log and the standby redo logs of at least one standby
location. If no standby location is available, it acts in the same manner
as maximum performance mode until a standby becomes available again.
- Maximum
Performance:
Transactions on the primary commit as soon as redo information has been
written to the online redo log. Transfer of redo information to the
standby server is asynchronous, so it does not impact on performance of
the primary.
- Maximum
Protection:
Transactions on the primary do not commit until redo information has been
written to the online redo log and the standby redo logs of at least one
standby location. If not suitable standby location is available, the
primary database shuts down.
Sqlplus equivalent
- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;
2] Dataguard Management
2.1 Start & Stop
alter database mount standby database ;
alter database recover managed standby database
disconnect from session ;
alter database recover managed standby database cancel
;
select controlfile_type from v$database;
Provided you have configured standby redo logs, you can start real-time
apply using the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
CURRENT LOGFILE;
alter database recover managed standby database using
current logfile disconnect from session
2.2 Deactivate / Reactivate Archivelogs sending
·
SQLPLUS
SQL> alter system set
log_archive_dest_state_2='DEFER' ;
SQL> alter system set
log_archive_dest_state_2='ENABLE' ;
·
Broker
$ dgmgrl
DGMGRL
for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - 64bit Production
Copyright
(c) 2000, 2005, Oracle. All rights reserved.
Welcome
to DGMGRL, type "help" for information.
DGMGRL>
connect sys/<psw>
Connected.
DGMGRL> edit database '<PRIM_DB>' set
state='LOG-TRANSPORT-OFF';
Succeeded.
DGMGRL> edit database '<PRIM_DB>' set state='ONLINE'
;
Succeeded.
2.3 Read-Only
Log files are still being received when you put the physical standby
database in the read-only state. The broker stops Redo Apply from applying the
redo data to the standby database.
Succeeded.
ALTER DATABASE
OPEN READ ONLY
Mon May 3 16:53:25 2010
SMON: enabling
cache recovery
Mon May 3 16:53:26 2010
Database
Characterset is WE8MSWIN1252
replication_dependency_tracking
turned off (no async multimaster replication found)
Physical standby
database opened for read only access.
Mon May 3 16:53:29 2010
ALTER SYSTEM SET
service_names='SERV_BO' SCOPE=MEMORY;
Completed: ALTER
DATABASE OPEN READ ONLY
Get back to Recover :
DGMGRL> EDIT DATABASE 'STD12C' SET STATE='ONLINE';
Succeeded.
3] Dataguard Monitoring Sync
3.1 Synchronization
- Are
they in Sync ?
set lines 150 pages 5000
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM
V$MANAGED_STANDBY ;
- What’s
the delay
select max(sequence#) from v$log_history ;
- Data
age
-- date age
select next_time Data_Time from v$archived_log where next_change# =
( select max(next_change#) from v$log_history ) ;
Be careful to select current_scn from v$database ;
Hdr: 8391931 10.2.0.4 RDBMS
10.2.0.4 RECOVERY PRODID-5 PORTID-226Abstract: CURRENT_SCN FROM V$DATABASE IS
NOT SHOWING THE REAL TIME APPLY
*** 04/10/09 10:22 am ***
(CHG: FixBy->11.2)
Abstract: READABLE STANDBY:
SELECT SCN_TO_TIMESTAMP() FROM DUAL MAY RETURN WRONG RESULTS
CURRENT_SCN from v$database
not showing the correct value in physical standby DB [ID 811434.1]
Bug 6450228: SELECT
SCN_TO_TIMESTAMP RETURNS DIFFERENT VALUES ON PRIMARY AND STANDBY
with a as ( select current_scn,database_role from
v$database )
select a.database_role ,
trunc(scn_to_timestamp(a.current_scn)) "DATE_REFRESH"
,scn_to_timestamp(a.current_scn) from
dual , a ;
DATABASE_ROLE
DATE_REFR SCN_TO_TIMESTAMP(A.CURRENT_SCN)
-------------- ---------
---------------------------------------------------------------------------
PHYSICAL STANDBY 25-NOV-10 25-NOV-10
05.14.47.000000000 PM
with a as ( select current_scn,database_role from
v$database )
select a.database_role , decode(a.database_role,
'PRIMARY' , trunc(scn_to_timestamp(a.current_scn)) ,
'PHYSICAL STANDBY' ,
trunc(scn_to_timestamp(a.current_scn)-1) ) "DATE_REFRESH" ,
scn_to_timestamp(a.current_scn)
"REAL_DATA_TIMESTAMP"
from dual , a ;
DATABASE_ROLE
DATE_REFR REAL_DATA_TIMESTAMP
-------------- ---------
---------------------------------------------------------------------------
PHYSICAL STANDBY 24-NOV-10 25-NOV-10
05.14.47.000000000 PM
alter session set NLS_DATE_FORMAT='DD/MM/YY HH24:MI:SS' ;
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
3.2 [12C] Dataguard Synchronization
monitoring
Dataguard : Get the delay between
Primary and Secondary Instances in Seconds
if [[ $dgtype = 'PHYSICAL' || $dgtype = 'LOGICAL' ]] ; then
sqlplus -s '/ as sysdba' <<!
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD ON TRIMS ON TAB OFF pages 5000 LINES 150
prompt
prompt Instance avec Standby: Redo Application Delay in Seconds
select DIFF_SEC , case WHEN DIFF_SEC>120 THEN ' CRITICAL' WHEN DIFF_SEC>60 THEN ' WARNING' ELSE ' OK' END STATUS
from (
with P AS ( select CAST(scn_to_timestamp(current_scn) AS DATE) T1 from v$database ),
S AS ( select CAST(scn_to_timestamp(applied_scn) AS DATE) T2 from v$archive_dest where dest_id = 2 )
select ceil(24*60*60*(P.T1-S.T2)) DIFF_SEC from P , S ) ;
!
fi
Output
DIFF_SECSTATUS
--------------------
25
OK
4] Rebuild Standby DB after a hole of missing
Archivelogs
a/ stop dmon <PRIM_DB> <STAND_DB>
alter system
set dg_broker_start = FALSE ;
b/ save init <PRIM_DB> <STAND_DB>
sys@<PRIM_DB>>
create pfile = '/data/<PRIM_DB>/init<PRIM_DB>.ora_051010' from
spfile ;
create pfile
= '/data/<STAND_DB>/init<STAND_DB>.ora_051010' from spfile ;
c/ rman bup scp
input
archive log thread=1 sequence=870609 derniere arc
c'/ get standby to nomount mode
d/ restore for standby
Disk Busy%
KBPS TPS KB-Read KB-Writ
hdisk7 100.0
24.5K 74.0 3.0K
21.5K -> 72 GB /H ?
done
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/work/<PRIM_DB>/rman/db_<PRIM_DB>_s22_U0mlpm2tl_1_1
tag=TAG20101005T090241
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 02:41:16
channel
ORA_AUX_DISK_2: restored backup piece 1
piece
handle=/work/<PRIM_DB>/rman/db_<PRIM_DB>_s21_U0llpm2tk_1_1
tag=TAG20101005T090241
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 02:58:09
d'/ activate archivelogs transmission
from Primary
sys@<PRIM_DB>>
alter system set log_archive_dest_state_2 = 'ENABLE' ;
=> FAL is working to bring back the
missing arcs to <STAND_DB> host
e/ post-install ( restore init ? )
SQL>
select * from v$logfile ;
GROUP# STATUS TYPE
MEMBER IS_
---------- ------- -------
------------------------------ ---
1 ONLINE
/REDO_APP2/<STAND_DB>/log_01_1.rdo
NO
2
ONLINE /REDO_APP2/<STAND_DB>/log_02_1.rdo NO
3 ONLINE
/REDO_APP2/<STAND_DB>/log_03_1.rdo
NO
4 ONLINE
/REDO_APP2/<STAND_DB>/log_04_1.rdo
NO
Wed Oct 06
16:17:31 DFT 2010
Completed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session
Wed Oct 06
16:21:06 DFT 2010
Errors in
file /work/oracle/log/<STAND_DB>_arc0_1482828.trc:
ORA-16146:
standby destination control file enqueue unavailable
Wed Oct 06
16:22:36 DFT 2010
RFS[11]:
Archived Log: '/work/<PRIM_DB>/arch/A8707241593855296.arch'
...
OK
e'/ start dmon
SQL>
alter system set dg_broker_start=TRUE ;
f/ dgmgrl check config ok
SHOW
CONFIGURATION;
SHOW
DATABASE VERBOSE '<PRIM_DB>' ;
Intended State: LOG-TRANSPORT-OFF ( edit database '<PRIM_DB>'
set state='LOG-TRANSPORT-OFF'; )
-> edit database '<PRIM_DB>' set
state='ONLINE' ;
5] UPGRADE
5.1 Steps to upgrade Dataguard 10G DB from
10.2.0.3 to 10.2.0.5
|
#
|
PRI
|
STD
|
||
|
1
|
10.2.0.3
|
disable log shipping
to the standby site dgmgrl
DGMGRL> edit database 'PRDUN' set state='LOG-TRANSPORT-OFF'; |
10.2.0.3
|
|
|
2
|
10.2.0.3
|
|
10.2.0.3
|
Shutdown instance
|
|
3
|
10.2.0.3
|
Shutdown & apply
interim patchsets to the RDBMS binaries
|
10.2.0.3
|
|
|
3b
|
10.2.0.5
|
Upgrade/Patch the
RDBMS and dictionary objects ( catpatch )
|
10.2.0.3
|
|
|
3c
|
10.2.0.5
|
Start lsnr + instance
|
10.2.0.3
|
|
|
4
|
10.2.0.5
|
|
10.2.0.3
|
apply interim
patchsets to the RDBMS binaries
|
|
4c
|
10.2.0.5
|
|
10.2.0.5
|
Start database to
mount and restart the listener(s). -> broker start recover
|
|
5
|
10.2.0.5
|
re-enable log shipping
to the standby
DGMGRL> edit database 'PRDUN' set state='ONLINE'; |
10.2.0.5
|
|
|
5b
|
10.2.0.5
|
|
10.2.0.5
|
( RECOVER applique le
patch au dictionnaire OK )
|
5.2 Steps to apply a Bundle Patch on Dataguard
12C DB – with Shutdown
|
Step
|
PRIMARY
|
SECONDARY
|
|
1
|
|
Patch Binaries : shutdown instance , then start instance
|
|
2
|
Patch Binaries : shutdown instance , then start instance
|
|
|
3a
|
Apply patch in every database ( datapatch )
|
|
|
3b
|
Implicit : « Datapatch » gets replicated to the Standby
|
|
|
4
|
END
|
|
5.3 Steps
to apply a Bundle Patch on Dataguard DB – with just switchover
|
Step
|
PRIMARY
|
SECONDARY
|
|
1
|
|
Patch Binaries : shutdown instance , then start instance replication
|
|
2
|
Dataguard switchover :
invert Roles
|
|
|
New roles
|
SECONDARY
|
PRIMARY
|
|
3
|
Patch Binaries : shutdown instance , then start instance replication
|
|
|
4
|
|
Apply patch in every database ( datapatch )
|
|
5
|
Implicit : « Datapatch » gets replicated to the Standby
|
|
|
6
|
Dataguard switchover :
invert Roles
|
|
|
New roles
|
PRIMARY
|
SECONDARY
|
|
7
|
END
|
|
