data guard new features
DESCRIPTION
11g Data Guard New FeaturesTRANSCRIPT
11g Data Guard New Features -- Fan Xiangrong
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Password file
Create password file on standby doesnrsquot work You need to copy password file from primary to standby
Redo Compress Shipping Add ldquocompression=enablerdquo to log_archive_dest_N
parameter log_archive_dest_3 = service=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=qadb27qaebaycom)(PORT=1799))(CONNECT_DATA=(SID=TEST2))) compression=enable reopen=5 lgwr async
For 11g R1 it only uses compression when resolving gaps If you set hidden parameter _REDO_TRANSPORT_COMPRESS_ALL=TRUE it will use compression for all redo transport
Redo Compress TestSQL to generate redo
create table test tablespace data01 as select from dba_objectsbegin for i in 18 loop insert into test select from test commit end loopend
Check ldquobytes received via SQLNet from clientrdquo statistics on standby
1st testCompression = enable 594M- 507M = 87MCompression = disable 959M- 595M = 364M
2st TestCompression = enable 1046M-959M = 87MCompression = disable 1410M-1047M=363M
Compression rate is 24 Different data can have different compress rate
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Password file
Create password file on standby doesnrsquot work You need to copy password file from primary to standby
Redo Compress Shipping Add ldquocompression=enablerdquo to log_archive_dest_N
parameter log_archive_dest_3 = service=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=qadb27qaebaycom)(PORT=1799))(CONNECT_DATA=(SID=TEST2))) compression=enable reopen=5 lgwr async
For 11g R1 it only uses compression when resolving gaps If you set hidden parameter _REDO_TRANSPORT_COMPRESS_ALL=TRUE it will use compression for all redo transport
Redo Compress TestSQL to generate redo
create table test tablespace data01 as select from dba_objectsbegin for i in 18 loop insert into test select from test commit end loopend
Check ldquobytes received via SQLNet from clientrdquo statistics on standby
1st testCompression = enable 594M- 507M = 87MCompression = disable 959M- 595M = 364M
2st TestCompression = enable 1046M-959M = 87MCompression = disable 1410M-1047M=363M
Compression rate is 24 Different data can have different compress rate
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Password file
Create password file on standby doesnrsquot work You need to copy password file from primary to standby
Redo Compress Shipping Add ldquocompression=enablerdquo to log_archive_dest_N
parameter log_archive_dest_3 = service=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=qadb27qaebaycom)(PORT=1799))(CONNECT_DATA=(SID=TEST2))) compression=enable reopen=5 lgwr async
For 11g R1 it only uses compression when resolving gaps If you set hidden parameter _REDO_TRANSPORT_COMPRESS_ALL=TRUE it will use compression for all redo transport
Redo Compress TestSQL to generate redo
create table test tablespace data01 as select from dba_objectsbegin for i in 18 loop insert into test select from test commit end loopend
Check ldquobytes received via SQLNet from clientrdquo statistics on standby
1st testCompression = enable 594M- 507M = 87MCompression = disable 959M- 595M = 364M
2st TestCompression = enable 1046M-959M = 87MCompression = disable 1410M-1047M=363M
Compression rate is 24 Different data can have different compress rate
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Password file
Create password file on standby doesnrsquot work You need to copy password file from primary to standby
Redo Compress Shipping Add ldquocompression=enablerdquo to log_archive_dest_N
parameter log_archive_dest_3 = service=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=qadb27qaebaycom)(PORT=1799))(CONNECT_DATA=(SID=TEST2))) compression=enable reopen=5 lgwr async
For 11g R1 it only uses compression when resolving gaps If you set hidden parameter _REDO_TRANSPORT_COMPRESS_ALL=TRUE it will use compression for all redo transport
Redo Compress TestSQL to generate redo
create table test tablespace data01 as select from dba_objectsbegin for i in 18 loop insert into test select from test commit end loopend
Check ldquobytes received via SQLNet from clientrdquo statistics on standby
1st testCompression = enable 594M- 507M = 87MCompression = disable 959M- 595M = 364M
2st TestCompression = enable 1046M-959M = 87MCompression = disable 1410M-1047M=363M
Compression rate is 24 Different data can have different compress rate
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Redo Compress Shipping Add ldquocompression=enablerdquo to log_archive_dest_N
parameter log_archive_dest_3 = service=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=qadb27qaebaycom)(PORT=1799))(CONNECT_DATA=(SID=TEST2))) compression=enable reopen=5 lgwr async
For 11g R1 it only uses compression when resolving gaps If you set hidden parameter _REDO_TRANSPORT_COMPRESS_ALL=TRUE it will use compression for all redo transport
Redo Compress TestSQL to generate redo
create table test tablespace data01 as select from dba_objectsbegin for i in 18 loop insert into test select from test commit end loopend
Check ldquobytes received via SQLNet from clientrdquo statistics on standby
1st testCompression = enable 594M- 507M = 87MCompression = disable 959M- 595M = 364M
2st TestCompression = enable 1046M-959M = 87MCompression = disable 1410M-1047M=363M
Compression rate is 24 Different data can have different compress rate
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Redo Compress TestSQL to generate redo
create table test tablespace data01 as select from dba_objectsbegin for i in 18 loop insert into test select from test commit end loopend
Check ldquobytes received via SQLNet from clientrdquo statistics on standby
1st testCompression = enable 594M- 507M = 87MCompression = disable 959M- 595M = 364M
2st TestCompression = enable 1046M-959M = 87MCompression = disable 1410M-1047M=363M
Compression rate is 24 Different data can have different compress rate
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
It needs License
Redo transport compression is a feature of the Oracle Advanced Compression option You must purchase a license for this option before using the redo transport compression feature
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Active Standby
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Simple Steps
bullSQLgt RECOVER MANAGED STANDBY DATABASE CANCEL104867310486731048673
Stop redo applybullSQLgt
ALTER DATABASE OPEN READ ONLY
Open the database read
only
bullSQLgt RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
Restart redo apply
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Disallow operations
Any DML (insert update delete) Any DDL Access of local sequences DMLs on local temporary tables
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Check Standby Apply Status V$databasecurrent_scn records the current apply SCN
SQLgt select scn_to_timestamp(current_scn) from v$database
SCN_TO_TIMESTAMP(CURRENT_SCN)---------------------------------------------------------------------------21-NOV-09 084243000000000 AM
Session level parameter STANDBY_MAX_DATA_DELAY(11g R2)
The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance measured in seconds for queries issued by non-administrative users to a physical standby database that is in real-time query mode
Alter session set STANDBY_MAX_DATA_DELAY = integer | NONE
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Snapshot database A snapshot standby database is a fully update-able standby
database that is created by converting a physical standby database into a snapshot standby database
Snapshot standby database receives and archives but does not apply the redo data
Redo data received from the primary database is applied automatically once it is converted back into a physical standby database
All local updates will be discarded when snapshot database is converted back to physical standby database
In 10g version we can do it manually
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
10g Manual Steps
httpdbmonitorvipqaebaycomdbblogmainphpname=Sub20doc200911222010g20put20standby20in20read-write20using20flashbackhtm
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Convert to Snapshot database Steps
If not already configured configure flash recovery area as given below
Set the size for recovery area Alter system set db_recovery_file_dest_size=ltsizegt
Set Flash recovery area (unset db_recovery_file_dest and set db_recovery_file_dest_1)Alter system set db_recovery_file_dest=ltpathgt
Bring the physical standby database to mount stage
Stop managed recovery if it is active
Convert to snapshot standby database
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Shutdown and startup
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Convert back to Physical Standby Steps
Shutdown the snapshot standby database Bring the database to the mount stage Issue the command
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shutdown the database and mount it Start the media recovery process
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Corrupted block replacement
A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode
A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database if LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n parameters are set on standby
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
RMAN Duplicate Standby
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
RMAN Duplicate Standby
Prepare standby system Setup listener Create an initora file with only db_name in it Create or copy a password file Startup nomount database Create spfile from pfile
Prepare primary system Create spfile from pfile
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
RMAN build scriptrman target syssystest_pri AUXILIARY syssystest_targetrun allocate channel prmy1 type diskallocate auxiliary channel stby1 type diskduplicate target database for standby from active databasespfileparameter_value_convert GBPGWDDSset db_unique_name=TESTset control_files=oracleDDSarchiveTESTcontrolctlset log_archive_dest_1=location=oracleDDSarchiveTESTarchiveset db_file_name_convert=oracleGBPGWdata01TESToracleDDSarchiveTESTsetlog_file_name_convert=oracleGBPGWdata01TESTredooracleDDSarchiveTEST
redolsquoset log_archive_dest_3=lsquorsquonofilenamecheck
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Agenda Common changes
Password file Redo compress shipping
Physical standby changes Active standby Snapshot database Corrupted block automatic replacement Use RMAN to build physical standby
Logical standby changes Dynamic parameters change Compressed table is supported by logical standby SQL Apply will execute parallel DDLs in parallel
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Dynamic parameters change
select from dba_logstdby_parametersSQLgt
NAME VALUE UNIT SETTING DYNAMIC------------------------------ -------------------- ---------- ---------- ----------MAX_SGA 30 MEGABYTE SYSTEM YESMAX_SERVERS 9 SYSTEM YESPREPARE_SERVERS 1 SYSTEM YESAPPLY_SERVERS 5 SYSTEM YESMAX_EVENTS_RECORDED 10000 SYSTEM YESRECORD_SKIP_ERRORS TRUE SYSTEM YESRECORD_SKIP_DDL TRUE SYSTEM YESRECORD_APPLIED_DDL FALSE SYSTEM YESRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM YESEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YESLOG_AUTO_DELETE TRUE SYSTEM YESLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YESPRESERVE_COMMIT_ORDER TRUE SYSTEM NO_EAGER_SIZE 1000 USER YES
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Compressed table is supported (112)
DSS table compression Create table hellip compress as selecthellip
OLTP table compression Alter table hellip compress for all operations
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Parallel DDL is supportedPrimary
SQLgt create index test1 on test(object_name) tablespace data01 parallel 4
Index created
Target
583 oracleqadb27 (P002) log buffer space 000 0 0 00 A 14 13 575 oracleqadb27 (P003) log buffer space 000 0 0 00 A 14 13 595 oracleqadb27 (P000) direct path read temp 10319291 3 0 00 A 14 14 601 oracleqadb27 (P001) direct path read temp 103147291 -1 0 00 A 14 13 576 oracleqadb27 (P004) PX Deq Execution Msg 26856652720 0 1 00 I 1 13 665 oracleqadb27 (P006) PX Deq Execution Msg 26856652720 0 1 00 I 2 13 574 oracleqadb27 (P005) PX Deq Execution Msg 26856652710 0 1 00 I 1 13 647 oracleqadb27 (P007) PX Deq Execution Msg 26856652710 0 1 00 I 1 13
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Others
Statspack on standby (4548481) Support different platforms (4134841) Big performance boost in Oracle 11G Up to
100 increase in redo apply performance
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-
Review Common changes
Password file Redo compress shipping
(_REDO_TRANSPORT_COMPRESS_ALL=TRUE for 11gR1) Physical standby changes
Active standby Snapshot database Corrupted block automatic replacement RMAN duplicate standby from active database
Logical standby changes Online modify logical standby parameters Compressed table is supported by logical standby (11g R2) SQL Apply will execute parallel DDLs in parallel
- 11g Data Guard New Features
- Agenda
- Agenda (2)
- Password file
- Redo Compress Shipping
- Redo Compress Test
- It needs License
- Agenda (3)
- Active Standby
- Simple Steps
- Disallow operations
- Check Standby Apply Status
- Snapshot database
- 10g Manual Steps
- Convert to Snapshot database Steps
- Convert back to Physical Standby Steps
- Corrupted block replacement
- RMAN Duplicate Standby
- RMAN Duplicate Standby (2)
- RMAN build script
- Agenda (4)
- Dynamic parameters change
- Compressed table is supported (112)
- Parallel DDL is supported
- Others
- Review
-