DG Issues
Space issue - Unable to create files on DG due to space issue
1. alter database recover managed standby database cancel;
2. alter system set standby_file_management='manual' scope=memory;
3. alter database create datafile '/local/UNNAMED.dbf' as '/local2/index_40.dbf';
4. alter system set standby_file_management='auto' scope=memory;
5. recover managed standby database disconnect from session;
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT SCN_TO_TIMESTAMP(above_scn) FROM dual;
How to setup DG with incremental backup -
1. Findout current SCN from DG
col current_scn for 99999999999999999999999999999999999999999999
select current_scn from v$database;
1234
2. Set profile on Primary and go to the rman prompt - rman target
3. Take backup of the primary database from this SCN
RMAN > backup incremental from scn 1234 database format '/tmp/Mybackup%U' tar 'FOR STANDBY'
or backup datafile 2 format '/tmp/Mybackup%U' tar 'FOR STANDBY'
or -
RMAN> run {
allocate channel c1 type disk format '/u01/oraback/%U.rmb';
backup incremental from scn 1301571 database;
3. Create control file for Standby database..
alter database create standby controlfile as '/tmp/standby.ctl';
3. Copy those files (standby control file and backup of the db / dbfile) to DR -
ON DG -
4. Catalog those backup file -- See note below for various catalog options
catalog start with '/tmp/Mybackup%U';
=
to uncatalog -change Datafilecopy '/u01/dbs/snapcf_f' uncatalog;
change controlfilecopy '/controlfile/current.3975.856614439' uncatalog;
==
5. Recover database / datafile
rman > Recover database noredo;
-- set newname for datafile if the location of the datafile is changed
rman > run { set newname for datafile 2 to '/NEWLOCATION/abc.dbf';
restore datafile; }
6.Switch datafile all;
rman> switch datafile all;
7. shutdown standby db and startup the databas at nomount state
shutdown immediate;
startup nomount;
8. Copy new control file created from primary using rman
rman > restore standby controlfile from '/tmp/standby.ctl';
9. Mount the standby database
alter database mount standby database;
10.Put database into contineous recovery.
alter database recover managed standby database disconnect;
11. Check MRP process is started or not
select process, thread#, sequence#, status from v$managed_standby
12. Go to primary and enable Arch Dest 2 to re-initiate the archive log transfer and switch log file
alter system set log_archive_dest_state_2='enable';
13. Do above for all nodes from RAC instances
14. Go to Dataguard and check the archive logs are getting applied or not
select process, thread#, sequence#, status from v$managed_standby;
15.. You should be able to see MRP and RFS processes here...
16. Check a DG gap from standby
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') as_of_date, to_char(TIMESTAMP,'MM/DD/YY HH24:MI:SS') Applied_time , round((sysdate - TIMESTAMP)*24 ,2) hrs_gap from v$recovery_progress where item = 'Last Applied Redo' and start_time=(select max(start_time) from v$recovery_progress);
check status of DG -
Select DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE, DESTINATION, APPLIED_SEQ#, SYNCHRONIZATION_STATUS from V$ARCHIVE_DEST_STATUS;
==========================
Delete old RMAN backup -
rman > DELETE BACKUP device type disk completed before 'sysdate-1';
==========================================================================================
Check Errors
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
============================================================================================
- Catalog specific backup piece.
CATALOG BACKUPPIECE '/backup/MYSID/01dmsbj4_1_1.bcp';
- Catalog all files and the contents of directories which
- begin with the pattern "/backup/MYSID/arch".
CATALOG START WITH '/backup/MYSID/arch';
- Catalog all files in the current recovery area.
CATALOG RECOVERY AREA NOPROMPT;
- Catalog all files in the current recovery area.
- This is an exact synonym of the previous command.
CATALOG DB_RECOVERY_FILE_DEST NOPROMPT;
============================================================================================
DR Switch Steps -
1. Ensure the DR is in sync with Primary. If not Syncup with primary. Ensure Tnsnames have been set properly. Do a Test and Check.
Ensure Init ora file.
2. Disable all Cronjob that would interfere (both at Prod & DR) - Block further Job submission (At Primary)
select owner , job_name,start_date,end_date,enable from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS";
select * from dba_jobs_running;
alter system set job_queue_processes=0;
execute dbms_scheduler.disable(job_name);
3. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database
SELECT SWTCHOVER_STATUS FROM V$DATABASE;
A value of TO STANDBY or SESSIONS ACTIVE (requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.
4. Shutdown Other RAC Instances except One Instance.
5. ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
6. Ensure Standby has received all the Archivelog & Redo.
7. At stby, Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role.
If neither of these values is returned, verify that redo apply is active and that redo transport is configured and working properly.
Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.
8. At stby ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
9. At Stby, ALTER DATABSE OPEN
Shut Immediate
Check & Enable log Archive_dest_2 to Old Production & enable startup
10. Restart the new Standby (Old Production)
Shut immediate
Startup Mount
11. Enable dba Jobs,
Enable Cron jobs,
Enable dbms_scheduler
---------------------------------------------------------------------------------------------------------------------------------------
Real Time Apply
Data Guard Log Apply Services can automatically apply information from redo logs to standby databases, keeping data synchronized with the primary database. In general, log apply services wait for the full archived redo log to arrive to the Oracle instance host before applying to the standby database.
Redo data is received by the remote file server process (RFS) on the standby system, where the RFS process writes the redo data to either archived redo logs or optionally to a standby redo log.
However, if a standby redo log is used, real time apply can be enabled, which allows Data Guard to recover redo data from the current standby redo log as it is being filled by the RFS process. This facility of real time apply is a new feature introduced in Oracle database 10g.
It has the benefit of quicker switchover, instantly updating results after a physical Oracle instance is changed to read-only with up-to-date reporting from a logical standby database.
The ALTER DATABASE statement can be used to enable the real-time apply feature. For example, in physical standby databases, issue the statement,
ALTER DATABASE RECOVER MANAGED Oracle instance USING CURRENT LOGFILE;
For logical standby databases, issue the statement,
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To determine if real time apply is enabled, the recovery_mode column in the v$archive_dest_status view can be queried. It will display MANAGED REAL TIME APPLY when real time apply is enabled.
select DEST_ID, DEST_NAME, RECOVERY_MODE from v$archive_dest_status;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1. Create restore point -
CREATE RESTORE POINT before_upgrade;OR -CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
2. Check the restore points from the db -
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;
3. You can also use the following query to view only the guaranteed restore points: For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
4. DROP restore point -
SQL> DROP RESTORE POINT before_app_upgrade;
Normal Restore Points -- A label for an SCN or time. For commands that support an SCN or time, you can often specify a restore point. Normal restore points exist in the circular list and can be overwritten in the control file. However, if the restore point pertains to an archival backup, then it will be preserved in the recovery catalog.
If the db_recovery_dest_file_size has been allocated less space then what is required for the flashback transaction logs as dictated by the db_flashback_retention_target parameter, then the flashback logs are deleted to free up space for the additional logs that are being generated.
Alert log will show also that flashback logs are being deleted
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc8494_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc86n3_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc8963_.flb
Flashback then MAY fail
SQL> flashback database to scn 1580000;
flashback database to scn 1580000
ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK.
Guaranteed Restore Points -- A restore point for which the database is guaranteed to retain the flashback logs for an Oracle Flashback Database operation. Unlike a normal restore point, a guaranteed restore point does not age out of the control file and must be explicitly dropped. Guaranteed restore points utilize space in the flash recovery area, which must be defined.
With guaranteed restore points, logs are not deleted, but if the space allocated for the flashback logs is not adequate, the database may hang until more space is allocated
The parameter db_recovery_file_dest_size is dynamic and can be increased on the fly to allocate more space for the flashback logs.
This query should be run when we have enabled a guaranteed restore point - the STORAGE_SIZE column will show the disk space that is currently being used by the flashback logs and this should be compared with the db_recovery_file_dest_size value so as to ensure that we have allocated sufficient space for the flashback logs.
Using V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE
The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help you determine whether you have allocated enough space for your flash recovery area.
Query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES-------------- ----------- ---------- ----------------- ---------------/mydisk/rcva 5368709120 109240320 256000 28
Query the V$FLASH_RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES------------ ------------------ ------------------------- ---------------ONLINELOG 2 0 22CONTROLFILE 0 0 0 ARCHIVELOG 4.05 2.01 31FLASHBACKLOG .08 0 1BACKUPPIECE 3.94 3.86 8IMAGECOPY 15.64 10.43 66
.================================================================================================
TO open db in a read / write mode with DG config -
SNAPSHOT STANDBY
-------------------
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made while in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, though it works just the same if it is.
If you are using RAC, turn off all but one of the RAC instances. On the remaining node shutdown and mount.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Ensure managed recovery is not active.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
Convert the standby to a snapshot standby.
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
Once converted it can be verified by the following query.
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
You can now treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
If you are using RAC, turn off all but one of the RAC instances. On single up node:
select name, TIME from v$restore_point; --(*)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT ABC; -- flashback database to restore point pre_test; --(as shown in above 1st - *** step- select name, time from v$restore_point;)
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
The standby is once again in managed recovery and archivelog shipping is resumed.
Notice that flashback database is still not enabled.
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
Start remaining nodes if applicable.
STARTUP MOUNT;
SELECT PROCESS, thread#, sequence#, STATUS FROM V$MANAGED_STANDBY;
| LAG time | SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like '%apply%lag%'; |
| Force standby to redo apply synchrnozation | ALTER SESSION SYNC WITH PRIMARY; |
| stop redo apply | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; |
| Real time apply | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION |
1) Start REDO apply process:
SQL> alter database recover managed standby database disconnect from session;
OR
SQL> alter database recover managed standby database nodelay disconnect parallel 8;
2) Verification.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
To check archive gap:
SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;
for RAC
SELECT thread#, low_sequence#, high_sequence# from gv$archive_gap;
To stop redo apply:
alter database recover managed standby database cancel;
3) Check alert log files and verify that you did not receive any error.
4) Switch some logfiles on the Primary and check if the same are getting applied to the standby.
on Primary:
SQL> alter system switch logfile;
on standby:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
==================================================================================================================================
New 11.2 Features Common to Redo Apply and SQL Apply
As of Oracle Database 11g Release 2 (11.2.0.2), Oracle Data Guard is fully integrated with Oracle Real Application Clusters One Node (Oracle RAC One Node).
A Data Guard configuration can now consist of a primary database and up to 30 standby databases.
The FAL_CLIENT database initialization parameter is no longer required.
The default archive destination used by the Oracle Automatic Storage Management (Oracle ASM) feature and the fast recovery area feature has changed from LOG_ARCHIVE_DEST_10 to LOG_ARCHIVE_DEST_1.
Redo transport compression is no longer limited to compressing redo data only when a redo gap is being resolved. When compression is enabled for a destination, all redo data sent to that destination is compressed.
The new ALTER SYSTEM FLUSH REDO SQL statement can be used at failover time to flush unsent redo from a mounted primary database to a standby database, thereby allowing a zero data loss failover to be performed even if the primary database is not running in a zero data loss data protection mode. See Section 8.2.2 for more information.
New 11.2 Features Specific to Redo Apply
You can configure apply lag tolerance in a real-time query environment by using the new STANDBY_MAX_DATA_DELAY parameter.
You can use the new ALTER SESSION SYNC WITH PRIMARY SQL statement to ensure that a suitably configured physical standby database is synchronized with the primary database as of the time the statement is issued.
The V$DATAGUARD_STATS view has been enhanced to a greater degree of accuracy in many of its columns, including apply lag and transport lag.
You can view a histogram of apply lag values on the physical standby. To do so, query the new V$STANDBY_EVENT_HISTOGRAM view.
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.
======================================================================================================
Start Managed Standby Recovery
During managed recovery the transfer of archivelogs is controlled by the servers without user intervention.
- Copy all archive logs from the primary to the standby server. This is the only time you should need to do this.
- From sqlplus do the following.SQL> CONNECT sys/password AS SYSDBASQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora;SQL> ALTER DATABASE MOUNT STANDBY DATABASE;SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Protect Primary Database
Now that Data Guard is configured and running the primary database can be prevented from applying updates unless the update has been sent to at least one standby location. Connect to the primary database and execute the following.
ALTER DATABASE SET STANDBY DATABASE PROTECTED;
Cancel Managed Standby Recovery
To stop managed standby recovery do the following.
- SQL> -- Cancel protected mode on primarySQL> CONNECT sys/password@primary1 AS SYSDBASQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;SQL> SQL> -- Cancel recovery if necessarySQL> RECOVER MANAGED STANDBY DATABASE CANCEL;SQL> CONNECT sys/password@standby1 AS SYSDBASQL> ALTER DATABASE OPEN READ ONLY;
The database can subsequently be switched back to recovery mode as follows.
- SQL> -- Startup managed recoverySQL> CONNECT sys/password@standby1 AS SYSDBASQL> SHUTDOWN IMMEDIATESQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.oraSQL> ALTER DATABASE MOUNT STANDBY DATABASE;SQL> -- Protect primary databaseSQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;SQL> CONNECT sys/password@primary1 AS SYSDBA
Activating A Standby Database
If the primary database is not available the standby database can be activated as a primary database using the following statements.
- SQL> -- Cancel recovery if necessarySQL> RECOVER MANAGED STANDBY DATABASE CANCEL;SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.
- -- Convert primary database to standbyCONNECT sys/change_on_install@prim1 AS SYSDBAALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;-- Shutdown primary database-- Mount old primary database as standby databaseSHUTDOWN IMMEDIATE;STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.oraALTER DATABASE MOUNT STANDBY DATABASE;-- Convert standby database to primaryALTER DATABASE RECOVER MANAGED STANDBY DATABASE;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;CONNECT sys/change_on_install@stby1 AS SYSDBA -- Shutdown standby database SHUTDOWN IMMEDIATE;STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora-- Open old standby database as primary
This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.
Database Failover
Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database.
- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;ALTER DATABASE ACTIVATE STANDBY DATABASE;
This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.
Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.
Automatic Archive Gap Detection
Gaps in the sequence of archive logs can be created when changes are applied to the primary database while the standby database is unavailable. In Oracle8i the archive redo logs associated with these gaps had to be identified using the V$ARCHIVE_GAP view and copied manually to the standby server before managed recovery could be initiated again. In Oracle9i most of these gap sequences can be resolved automatically. The following parameters must be added to the standby init.ora file where the values indicate net services names.
- FAL_SERVER = 'primary_db1'FAL_CLIENT = 'standby_db1'
The FAL server is normally the primary database, but can be another standby database. Once the standby database is placed in managed recovery mode it will automatically check for gap sequences. If it finds any it will request the appropriate files from the primary database via the FAL server. If the gap sequences cannot be resolved the files have to be recovered manually.
Background Managed Recovery
In Oracle8i managed recovery caused the user session to hang until the process was stopped by the user. This type of recovery is still available along with a background recovery that spawns a new background process and frees the user session
- -- User session hangsALTER DATABASE RECOVER MANAGED STANDBY DATABASE;-- User session releasedALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Delayed Redo Application
Application of the archived redo logs to the standby database can be delayed using the DELAY keyword. If a rogue statement significantly damages the primary database the DBA can choose to switch to the standby database, which will be in a state prior to this action
- -- Delay application of archived redo logs by 30 minutes.ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;-- Return to no delay (Default).
| Steps to perform for Rolling forward a standby database using RMAN Incremental Backup. (Doc ID 836986.1) |
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Note:
If you use 'DataGuard Broker', then it should be stopped before starting with step 1)
and then started again when all steps finished.
1) On the standby database, stop the managed recovery process (MRP)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2) On the STANDBY DATABASE, find the SCN which will be used for the incremental backup at the primary database:
"You need to use the 'lowest SCN' from the the 3 queries below"
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
3164433
SQL> select min(fhscn) from x$kcvfh;
MIN(FHSCN)
3162298
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY' ;
MIN(F.FHSCN)
3162298
- From the above you need to backup from SCN 3162298
"You need to use the 'lowest SCN' from the the 3 queries, which here is -> SCN: 3162298 "
3) In RMAN, connect to the PRIMARY database and create an incremental backup from the SCN derived in the previous step:
RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4) Transfer all backup sets created on the primary system to the standby system.
scp /tmp/ForStandby_* standby:/tmp
On the STANDBY catalog the backuppieces:
RMAN> CATALOG START WITH '/tmp/ForStandby';
using target database control file instead of recovery catalog searching for all files that match the pattern /tmp/ForStandby
List of Files Unknown to the Database
=========================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
===========
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
5) Recover the STANDBY database with the cataloged incremental backup:
RMAN> RECOVER DATABASE NOREDO;
starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09
6) In RMAN, connect to the PRIMARY database and create a standby control file backup:
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
7) Copy the standby control file backup to the STANDBY system.
scp /tmp/ForStandbyCTRL.bck standby:/tmp
8) We now need to refresh the standby controlfile from primary controlfile (for standby) backup. However, since the datafile names are likely different than primary, let's save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if any discrepancy. So, run below query from Standby and save results for further use.
spool datafile_names_step8.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off
9) From RMAN, connect to STANDBY database and restore the standby control file:
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
Starting restore at 03-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+DATA/mystd/controlfile/current.257.688583989
Finished restore at 03-JUN-09
10) Shut down the STANDBY database and startup mount:
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
11) Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
List of Files Unknown to the Database
=========================
File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
===========
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
If any datafiles have been added to Primary AFTER scn 3162298 they will also have to be restored to the standby host (see Note 1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary) and cataloged as shown above before doing the switch.
To determine if any files have been added to Primary since the standby current scn:
SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298
If the above query returns with 0 zero rows, you can switch the datafiles. This will rename the datafiles to its correct name at the standby site:
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
Note:
a) At this point, you can compare the query output from step 8) for any discrepancy (other than newly added datafiles) by running the same query as in Step 8) to ensure we have all the datafiles added in standby.
b) run the queries from step 2 again to confirm that the incremental apply has moved the datafiles forward. The SCN should now be bigger than its initial values.
12) If the STANDBY database needs to be configured for FLASHBACK run the following commands:
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
13) On standby database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
14) On the STANDBY database, start the MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
=================================================================================================================================================
Covert DG if opened in READ WRITE MODE --
Step 1 - In Standby database
A ) Set up a flash recovery area.
If Flash Recovery Area ( FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';
B ) Cancel Redo Apply and create a guaranteed restore point.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE;
To Confirim the details of restore point and its scn and time stamp run
SQL> select NAME,SCN,TIME from v$restore_point;
NAME SCN TIME
-------------------------------------------------- ------------- ------------------------------
STANDBY_FLASHBACK_TESTING 22607810 12-APR-09 01.10.21.000000000 P
Step 2 - In Primary Database
A) On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database.
When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
B ) Defer log archive destinations pointing to the standby that will be activated.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
Step 3 - In Standby database
A ) Activate the physical standby database:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Once its done you can check the controlfile status will be changed from Standby to Current
SQL> select CONTROLFILE_TYPE from v$database;
CONTROL
CURRENT
B) Then open the database.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> ALTER DATABASE OPEN;
Step 4 - In Standby database
Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database Any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.
For example :
SQL> create table testing ( col1 varchar2 (100));
Table created.
SQL> insert into testing values ( 'testing for flashback on standby database');
1 row created.
SQL> commit;
Commit complete.
Step 5 - In standby database
A ) Revert the active standby database back to Physical standby database
A1. Mount the database.
A2. Flashback the database to restore point.
SQL> STARTUP MOUNT FORCE;
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1290208 bytes
Variable Size 159383584 bytes
Database Buffers 125829120 bytes
Redo Buffers 2904064 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;
You can confirm the same by checking the controlfile status. It will be now backup controlfile
SQL> select controlfile_type from v$database;
CONTROL
BACKUP
B ) Convert to Standby database
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> select controlfile_type from v$database;
CONTROL
STANDBY
Step 6 - In standby database
A ) Put the standby database in managed recovery mode.Let archive gap resolution fetch all missing archived redo log
files and allow Redo Apply to apply the gap.
Step 7 - In Primary database
A ) Re-enable archiving to the physical standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
Step 8 - In Standby database
A ) Open the database in Read only mode and ensure that all the transaction done in active mode are no more
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> select * from testing;
select * from testing
*
ERROR at line 1:
ORA-00942: table or view does not exist
B ) Drop the restore point
SQL> STARTUP FORCE MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> DROP RESTORE POINT Standby_flashback_testing ;
Setup a Dataguard Broker -
| Steps to create broker configuration are: 1. Set the parameter dg_broker_config_file1 and dg_broker_config_file2 on all instances of primary and standby. ### These file will get created once we reate confi and add the db to the DGMGRL Default location of broker configuration file is $ORACLE_HOME/dbs or $ORACLE_HOME/database. In case if you want broker configuration files in non default location, set these parameters. In case of RAC database, set broker configuration file location to shared location and same value on all the instances. ALTER SYSTEM SET dg_broker_config_file1 = '\U01\oradata\dr1mfg.dat' scope=both sid='*'; ALTER SYSTEM SET dg_broker_config_file2 = '\U01\oradata\dr2mfg.dat' scope=both sid='*'; OR in case of ASM file system use: ### These file will get created once we reate confi and add the db to the DGMGRL ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/mfg/dr1mfg.dat' scope=both sid='*'; ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/mfg/dr2mfg.dat' scope=both sid='*'; If you want the broker configuration files in default location, then you can ignore this step. 2. Enable broker on both primary and standby: SQL>ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*'; 3. Connect to DGMGRL on primary: (from instance one in case of RAC) $dgmgrl DGMGRL for Linux: Version 10.2.0.3.0 - Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> CONNECT sys/<password>; Connected. 4. Create configuration on primary: DGMGRL> CREATE CONFIGURATION 'PRODCONF' AS PRIMARY DATABASE IS 'PROD10G' CONNECT IDENTIFIER IS PROD10G; Configuration "PRODCONF" created with primary database "PROD10G" 5. Add standby in the configuration: DGMGRL> ADD DATABASE 'STDBY10G' AS CONNECT IDENTIFIER IS STDBY10G MAINTAINED AS PHYSICAL; Database "STDBY10G" added 6. Enable Configuration: DGMGRL> ENABLE CONFIGURATION; Enabled. Use the SHOW command to verify that the configuration and its databases were successfully enabled and brought online: DGMGRL> SHOW CONFIGURATION; Configuration Name: PRODCONF Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: PROD10G - Primary database STDBY10G - Physical standby database Current status for "PRODCONF" SUCCESS Note : In this note I have used PROD10G for primary and STDBY10G for standby. Please change these name as per your configuration while running the commands. In case you notice any warnings or errors reported when running show configuration or show database verbose 'db unique name here', you may have to check the drc logs located in background_dump_dest (10g) or diagnostic_dest (11g) DGMGRL>show database 'standby' InconsistentProperties; Check the non default port used in connect string of both primary and standby(tnsnames.ora). Solution Change the broker variable to the correct port number. In 10G, DGMGRL> edit database <standby> set property LocalListenerAddress='<Listener address>'; Also set the same in local_listener parameter in case if it is not updated, SQL> alter system set local_listener='<value which is LocalListenerAddress>'; In 11G, DGMGRL> edit database <standby> set property StaticConnectIdentifier='<Static Address>'; For example, DGMGRL> edit database <standby> set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=North_Sales.example.com)(PORT=2840)) (CONNECT_DATA=(SERVICE_NAME=North_Sales_DGMGRL.example.com) (INSTANCE_NAME=north_sales1)(SERVER=DEDICATED)))'; SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=North_Sales.example.com)(PORT=2840)))'; NOTE : If the above didn't help please enable trace on standby (restart standby) and check with Support, event = "16664 trace name errorstack forever, level 3" DGMGRL> EDIT DATABASE <> SET STATE = APPLY-OFF; DGMGRL> EDIT DATABASE EC1 SET PROPERTY LogShipping = OFF; DGMGRL> show instance verbose abc1 on database dg112i_stb; DGMGRL> edit instance abc1 on database abc_prm set PROPERTY StaticConnectIdentifier =' (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=North_Sales.example.com)(PORT=2840))'; DGMGRL> delete instance abc1 on database abc_prm; DGMGRL> SHOW FAST_START FAILOVER; DGMGRL> ENABLE FAST_START FAILOVER CONDITION 55623; DGMGRL> ENABLE FAST_START FAILOVER; DGMGRL> START OBSERVER; DGMGRL> DGMGRL> DGMGRL> help create Creates a broker configuration Syntax: CREATE CONFIGURATION <configuration name> AS PRIMARY DATABASE IS <database name> CONNECT IDENTIFIER IS <connect identifier>; DGMGRL> help add Adds a standby database to the broker configuration Syntax: ADD DATABASE <database name> [AS CONNECT IDENTIFIER IS <connect identifier>] [MAINTAINED AS {PHYSICAL|LOGICAL}]; DGMGRL> help edit Edits a configuration, database, or instance Syntax: EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance}; EDIT CONFIGURATION SET PROPERTY <property name> = <value>; EDIT DATABASE <database name> SET PROPERTY <property name> = <value>; EDIT DATABASE <database name> RENAME TO <new database name>; EDIT DATABASE <database name> SET STATE = <state> [WITH APPLY INSTANCE = <instance name>]; EDIT INSTANCE <instance name> [ON DATABASE <database name>] SET AUTO PFILE [ = {<initialization file path>|OFF} ]; EDIT INSTANCE <instance name> [ON DATABASE <database name>] SET PROPERTY <property name> = <value>; EDIT INSTANCE * ON DATABASE <database name> SET PROPERTY <property name> = <value>; |
Oracle 11gR2 - Active Data Guard
Active Data Guard allows a standby database to be opened for read-only access whilst redo is still being applied. For some applications Active Data Guard can represent a more efficient use of Oracle licenses on the standby database. However, this benefit is offset to a certain extent by the fact that Active Data Guard is available on Enterprise Edition only and is cost option which must be licensed on both the primary and standby database.
Several of my customers are currently using Active Data Guard; in general they are very happy with it. A few others have discovered that it is very easy to inadvertently enable Active Data Guard. This is not desirable or advisable as Oracle have instigated licence audits with a large number of UK customers over the past couple of years.
To determine whether a standby database is using Active Data Guard use the following query:
>SELECT database_role, open_mode FROM v$database;
>SQL> SELECT database_role, open_mode FROM v$database;DATABASE_ROLE OPEN_MODEPHYSICAL STANDBY READ ONLY WITH APPLY---------------- --------------------If you start a database in SQL*Plus using the STARTUP command and then invoke managed recovery, the Active Data Guard will be enabled.For example:
$ sqlplus / as sysdbaSQL> STARTUPORACLE instance started.Total System Global Area 6497189888 bytesFixed Size 2238672 bytesDatabase Buffers 3103784960 bytesVariable Size 3372222256 bytesDatabase openedRedo Buffers 18944000 bytes Database mountedDATABASE_ROLE OPEN_MODESQL> SELECT database_role, open_mode FROM v$database; ---------------- --------------------USING CURRENT LOGFILE WITH SESSION SHUTDOWN;PHYSICAL STANDBY READ ONLY SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE---------------- --------------------SQL> SELECT database_role, open_mode FROM v$database; DATABASE_ROLE OPEN_MODE PHYSICAL STANDBY READ ONLY WITH APPLY>However, if the database is started in SQL*Plus using the STARTUP MOUNT command and then managed recovery is invoked, Active Data Guard will not be enabled.
$ sqlplus / as sysdbaSQL> STARTUP MOUNT\nORACLE instance started.Total System Global Area 6497189888 bytesVariable Size 3372222256 bytesFixed Size 2238672 bytes Database Buffers 3103784960 bytesSQL> SELECT database_role, open_mode FROM v$database;Redo Buffers 18944000 bytes Database mounted Database openedPHYSICAL STANDBY MOUNTEDDATABASE_ROLE OPEN_MODE ---------------- --------------------USING CURRENT LOGFILE WITH SESSION SHUTDOWN;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE SQL> SELECT database_role, open_mode FROM v$database;In the database has been started in SQL*Plus using STARTUP MOUNT and the database is subsequently opened read only, then invoking managed recovery willDATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED enable Active Data Guard. For example:oracle@server14]$ sqlplus / as sysdba
SQL> STARTUP MOUNTORACLE instance started.Total System Global Area 6497189888 bytesFixed Size 2238672 bytesDatabase Buffers 3103784960 bytesVariable Size 3372222256 bytesDatabase openedRedo Buffers 18944000 bytes Database mountedDATABASE_ROLE OPEN_MODESQL> SELECT database_role, open_mode FROM v$database; ---------------- --------------------SQL> SELECT database_role, open_mode FROM v$database;PHYSICAL STANDBY MOUNTED SQL> ALTER DATABASE OPEN READ ONLY; DATABASE_ROLE OPEN_MODESQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE---------------- -------------------- PHYSICAL STANDBY READ ONLY USING CURRENT LOGFILE\nWITH SESSION SHUTDOWN;PHYSICAL STANDBY READ ONLY WITH APPLYSQL> SELECT database_role, open_mode FROM v$database; DATABASE_ROLE OPEN_MODE---------------- --------------------
Of course not all databases are started using SQL*Plus.
If you start the database using SRVCTL then the default open mode can be specified in the OCR.
You can check the default open mode for a database using SRVCTL CONFIG DATABASE. For example if the database is called PROD:
oracle@server14]$ srvctl config database -d PRODDatabase unique name: PROD Database name: PRODOracle home: /u01/app/oracle/product/11.2.0/dbhome_1Oracle user: oracle Spfile: +DATA1/PROD/spfilePROD.ora Domain:**Start options: open**Server pools: PRODStop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Disk Groups: DATA1, FRA1In the above example, if the PROD database is started using SRVCTL then the database will be opened in read-only mode. For example:Mount point paths: Services: Type: SINGLE Database is administrator managed[oracle@server14]$ srvctl start database -d PROD
[oracle@server14]$ sqlplus / as sysdbaSQL> SELECT database_role, open_mode FROM v$database;DATABASE_ROLE OPEN_MODEPHYSICAL STANDBY READ ONLY---------------- --------------------USING CURRENT LOGFILE WITH SESSION SHUTDOWN;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE---------------- --------------------SQL> SELECT database_role, open_mode FROM v$database; DATABASE_ROLE OPEN_MODEPHYSICAL STANDBY READ ONLY WITH APPLY
The default start mode can be modified in the OCR using the SRVCTL MODIFY DATABASE command.
For example:
oracle@server14]$ srvctl modify database -d PROD -s mount</span>
The database configuration is updated as follows:
oracle@server14]$ srvctl config database -d PRODDatabase unique name: PROD Database name: PRODOracle home: /u01/app/oracle/product/11.2.0/dbhome_1Oracle user: oracle Spfile: +DATA1/PROD/spfilePROD.ora Domain: **Start options: mount**Server pools: PRODStop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Disk Groups: DATA1, FRA1Type: SINGLE\nDatabase is administrator managedMount point paths:Services:
When the default start mode is set to mount, Active Data Guard will not be enabled when managed recovery is invoked. For example:
[oracle@server14]$ srvctl start database -d PROD[oracle@server14]$ sqlplus / as sysdbaSQL> SELECT database_role, open_mode FROM v$database;DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTEDUSING CURRENT LOGFILE WITH SESSION SHUTDOWN;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE SQL> SELECT database_role, open_mode FROM v$database; DATABASE_ROLE OPEN_MODEPHYSICAL STANDBY MOUNTED---------------- --------------------
You can also specify the start mode as a parameter to the SRVCTL START DATABASE command
For example:
[oracle@server14] srvctl start database -d PROD -o open[oracle@server14] srvctl start database -d PROD -o mount
No comments:
Post a Comment