Active Data Guard Feature Of 11g To Run Live Reports On Standby
Sep 26, 2012
We have 10g physical standby set in our environment and we are migrating to 11g now. We want to use the active data guard feature of 11g to run the live reports on standby rather production. Questions I have is:
1) On our current 10g standby environment, we use db_name=cusms which is exactly matching with the production database name. I don't see we are using database_unique name on our standby. But I have read several blogs where everyone talks about using db_unique name on standby and db_name can be exactly matching with production on 11g. I wanted to know, is db_unique name a new requirement to have on 11g? can I go ahead and not use db_unique name and just have db_name exactly matching with production? What are the implications of doing so? The reason we want to stick to this is in-case of failover we want the database name to be the same. But I want to hear your thoughts on this:
2)While building standby, I did noticed few things and want your clarifications:
a.On standby database, should I mount instance using pfile or spfile or it doesn't matter?
b. Lets say if I use either spfile or pfile, can I just have db_unique name in that file and just start the instance in no mount and do the duplicate from rman?
c.As soon as my duplicate target database for standby from active database got finished, I usually exit the rman session and go to sqlplus and shutdown the standby instance. (Is this ok to do)
d.Then I start the standby instance with startup (mount and open the database) this should open the standby database in read only mode. Following I issue alter database recover managed standby database using current logfile disconnect to put the database in recovery mode. (any steps missing here)
e.Then go to primary and do few log switches and come back to standby to see if the primary changes moved to secondary or not.
But what I have observed is:
a. When I do the duplicate it runs successful. But during the course of duplicate, primary system generates few archives which are not shipped or applied on standby. When I go to standby to recover the database, it says media recovery needed and ask for archives files. I need to manually move this files from primary to standby to apply. Isn/t this automatically taken care?
b. I also noticed after I can not open the standby database in read only mode after the duplicate command. While trying to open, it says database media recovery needed. What's the best procedure to open the database in read only mode immediately?
c. On my standby init.ora lets say if I use db_unique name, where would my control file be place? Will oracle create controlfile from primary and put it on my standby database and put an update an entry into my pfile or spfile?
We are planning to create physical standby database for 4-node RAC primary to a 4-node RAC standby using Active database duplicate with ASM and OMF
1. while using active database duplicate, do we need to set cluster_database=FALSE in spfile clause and create standby,next change it back to TRUE at standby. Or we don't need to touch it. And also do we need to include instance_number parameter 2. We are using OMF, can we use log_file_name_convert parameter in spfile clause of duplicate. Documentation says log_file_name_convert cannot be used if standby is going to have OMF. Because we have 5 groups for each thread, which are on 5 different disk groups. When standby is created all the logs are created in only one disk group pointed by db_create_online_file_dest_1. 3. To enable force logging at primary do we need to shutdown the database?
creating the standby database from Active database using RMAN and getting the below issue after i executed the duplicate command.
Version of Database:11g(11.2.0.1.0) Operating System:Linux 5 Error: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/21/2012 17:26:52 RMAN-03015: error occurred in stored script Memory Script RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [code]....
provide any work arounds to proceed further in creating the standby database.
I have dataguard configuration operating in maximum availability mode with a local standby db (A - lgwr sync not using real time apply) and a remote standby db (B - lgwr async). I then simualted a crash of my primary database with batch jobs running. Since the stby db A is in lgwr sync option ,all the commited data in the current online redo log has been transmitted to stby A and is present in its stby redo log (Group 2).How do I apply this stby redo log to the remote stby db.
Tried the following methods.
1.ftp the stby redo log to the remote db and tried to regiter it, got an error that it is not completely archived.
2.issued the recover standby database command and supplied the stby redo log when it asked for the sequence in the stby redo, got an error saying there is corruption in a block(tried this option multiple times ended up with the same result.)
1.create a primary database 2.duplicate a physical standby database; 3.turn on flashback on both databases. 4.record SCN xxx on physical standby database. 5.convert physical standby to logical standby (using keep identity statement) 6.flashback to logical standby to xxx 7.convert logical standby to physical standby 8.using real time apply I got errors: Fast Parallel Media Recovery enabledManaged Standby Recovery starting Real Time ApplyMRP0:
Background Media Recovery waiting for new incarnation during transient logical upgrade procedure
Errors in file /home ora/ app/ oracle/ diag/ rdbms/ ora11gr1dg/ora11gr1dg/trace/ora11gr1dg_mrp0_10120.trc:ORA-19906: recovery target incarnation changed during recoveryManaged Standby Recovery not using Real Time ApplyErrors in file /home/ ora/app/ oracle/diag/ rdbms/ ora11gr1dg/ ora11gr1dg/ trace/ora11gr 1dg_mrp0_ 10120.trc:ORA-19906: recovery target incarnation changed during recovery
Errors appears every 10 seconds. Seems MPR0 is waiting for new incarnation for a long time. So am I.Standby database incarnation:
List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time-------1 1 ORA11GR1 3853851354 CURRENT 1 08/09/2013 01:02:182 2 ORA11GR1 3853851354 ORPHAN 2127877 08/28/2013 19:22:01 BGV
Statspack has been configured for Active Dataguard on Primary database.We got an spike of Buffer busy waits for about 5 min in Active Dataguard, this was causing worse Application SQL's response time during this 5 min window.Below is what i got from statspack report for one hour
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 18611 21-Feb-13 22:00:02 236 2.2 End Snap: 18613 21-Feb-13 23:00:02 237 2.1 Elapsed: 60.00 (mins) [code]...
Why there could sudden spike of demand on UNDO data in Active Data Guard ?
GROUP# THREAD# SEQUENCE# sizeMB ARC STATUS T ---------- ---------- ---------- ---------- --- ---------------- - 1 1 115 512 YES CLEARING O 2 1 116 512 YES CURRENT O 6 1 114 512 YES CLEARING O 4 1 112 512 YES CLEARING O 5 1 113 512 YES CLEARING O 3 1 111 512 YES CLEARING O
6 rows selected. [code]...
Above result is from standby database.I am trying to understand the concept of standby redo log.My stadby database protection_mode is MAXIMUM PERFORMANCE.
I have created dataguard between two instances hosted in two different servers. I have done switchover from primary to standby & then again standby to primary with DGMGRL utility.
1- When I done switchover to primary & trying to open standby database in readonly mode it was giving below error-
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
Why this error was coming ?
2- As I was not able to open standby database in read only mode, I have executed below command-
alter database recover managed standby database disconnect from session;
which return an error as-
ORA-01153: an incompatible media recovery is active
After this I have executed-
alter database recover managed standby database cancel;
which executed successfully.
& then after standby database open in read only mode.
Why above error was coming ?
3- After this I had tried to check the checkpoint_change# & controlfile_change# from primary database and result shows both number were different.
i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..
Primary alert log
Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated))) VERSION INFORMATION: TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
1) scn differs wrt primary in standby (i checked, 1day difference), how to make scn same?
2)i created a table in primary, its not refelecting in standby, (below i ve pasted alertlog entries)
ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Errors in file d:oracle11gappadministratordiag dbmsstandstand racestand_dbw0_6916.trc: ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: 'D:ORACLE11GAPPADMINISTRATORORADATASTANDSYSAUX01.DBF'
[code]....
3)wen i try to open standby database in read only mode gives below error..
ERROR at line 1: ORA-16004: backup database requires recovery ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: 'D:ORACLE11GAPPADMINISTRATORORADATASTANDSYSTEM01.DBF'
I have to implement Physical standby using same SID. parameters required to set on Primary and standby. Also what entries are required to do in TNS file. Recently we have faced hardware failure.
My clients want to print the output of reports of size 25*12 and 15*12 in 11g and I am able to do the printing but the page break required for each page is not coming properly and because of this they cannoy identify which is the 1st and 2nd paper..
But in 6i D2K, there is the Live Previewer through which I can print without any page break issues.
My query is what is the alternative for Live previewer in Oracle Reports 11g so that i can make it right.
i have attended an interview there i got a question like--->There are 100 archives generated in standby in that a series of five archives are missing and those are not available in the primary then how will you do.....
I want to get the execute plan of a sql in standby database(read only),but failed,how can i do?
SQL> explain plan for select count(1) from hxl.tb_objects; explain plan for select count(1) from hxl.tb_objects * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 2 ORA-16000: database open for read-only access
I have an issue whereby our standby instance was down for a couple of days and when it came back online we found an archivelog file was missing, so recovery only happened up to a point. It appears that the archivelog was deleted therefore we are unable to continue with this standby. We are now planning to rebuild the standby database. As far as i know all of the steps originally performed need not be performed to complete this task.
1. Shutdown standby 2. Delete all datafiles & redo logs 3. Copy over datafiles to respective directories 4. Copy online logs and archivelogs to standby. 5. Create a control file for standby database 6. Startup mount standby database 7. start redo apply
We are planning to setup a data guard (Maximum performance configuration ) between two Oracle 9i databases on two different servers.
The archive logs on the primary servers are deleted via a RMAN job bases on a policy , just wondering how I should delete the archive logs that are shipped to the standby.
Is putting a cron job on the standby to delete archive logs that are say 2 days old the proper approach or is there a built in data guard option that would some how allow archive logs that are no longer needed or are two days old deleted automatically.
SQL> ALTER DATABASE OPEN READ ONLY; ALTER DATABASE OPEN READ ONLY * ERROR at line 1: ORA-16004: backup database requires recovery ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/app/oracle/oradata/PBLSTAN/system01.dbf'
I want to know that what will happen when I will resize database file from 32G to 5G on primary server or add file to tablespace. Will standby server do the same automatically or I have to to something with standby server?
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=demodb' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
System altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database open read only; alter database open read only * ERROR at line 1: ORA-16004: backup database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u002/app/OraDB10g/stdemo/oradata/system01.dbf'
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH * ERROR at line 1:
I've a primary database and a physical standby.Logs are shipping perfectly from primary to standby.The logs that are applied on the standby are getting stored in a mount point(LINUX-/opt2) which consumes more space(160 GB). Will that be right if I go ahead and delete them?
My rman configurations in primary is:
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 4; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON;
We recently configured data guard in test machine.Archives not applied in physical standby.Where i need to start investigation?
Primary SQL> select THREAD#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 301 [code]...
Today, I am learning dataguard 11.2.0.1. The docs said:A. Monitoring Standby DB Application of redo logs may be monitored by issuing the SQL command:
SQL> select client_dbid, process, sequence#, status from v$managed_standby;
In the primary database, the sequence# with the status “WRITING” would refer to the current redo log sequence#In the standby database, if the status of the process MRPO is “APPLYING LOG” and the status of the process LGWR is “IDLE”, then application of redo logs is up to dateI see that that MRPO at standby is "WAIT_FOR_GAP". How do I resolve this gap.