I need a clarification on the below query.
I am having a Dataguard setup as Logical.
I am copying all the archive log files generated in the live server into the local server in order to apply into the Data Guard.
I am running a script for this process.
Question:How i can verify whether particular archive log file was applied into the data guard or not.
We have Oracle 10G dataguard configured,
DGMGRL -
Show configuration;
Shows status "SUCCESS"
Also
Archive log list
shows the proper archives in PROD as well as DR
But, when we check the Archive Applied ="YES" its shows result as "BLANK"
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]...
Currently I am at the point where the configuration has been completed, and I just need to sync the standby database to the primary one. I can see in the log files that the archive logs are being shipped, but they are not applied on the standby system.
If I run "recover standby database;" manually in sqlplus I can see that it is trying to apply an archive log which is way too old (ORA-00279: change 9656498443 generated at 04/29/2008 08:45:08 needed for thread 1). I
n the alert log I can also see this error: Warning: Recovery target destination is in a sibling branch of the controlfile checkpoint. Recovery will only recover changes to datafiles.
At this point I was thinking that the standby database might be on a different incarnation compared to the primary, but this is not the case, they are both in incarnation 6:6 6 MVF 4023175798 CURRENT 48493546257 13-06-21
I got a database on dataguard and my primary (db1) is shipping files to my standby (db2) with no problems.However, when I query:
select sequence#, status, applied from v$archived_log;
I see this:
SEQUENCE# S APP
---------- - ---
4 A YES
5 A YES
6 A YES
7 A YES
8 A YES
9 A YES
10 A YES
11 A YES
12 A YES
13 A YES
14 A YES
[code]....
So I did an alter system switch logfile on db1 then looked again and I can see new archived logs being applied.I thought all archived logs had to be applied on the standby since this is the very foundation of the standby database.Am I going to run in trouble later if I have a failover (unsynchronized database)
I have created a physical standby database and in it the logs are not getting applied.following is an extract of the standby alert log
Wed Sep 05 07:53:59 2012
Media Recovery Log /u01/oracle/oradata/ABC/archives/1_37638_765704228.arc
Error opening /u01/oracle/oradata/ABC/archives/1_37638_765704228.arc
Attempting refetch
Media Recovery Waiting for thread 1 sequence 37638
Fetching gap sequence in thread 1, gap sequence 37638-37643
Wed Sep 05 07:53:59 2012
RFS[46]: Assigned to RFS process 3081
RFS[46]: Allowing overwrite of partial archivelog for thread 1 sequence 37638
RFS[46]: Opened log for thread 1 sequence *37638* dbid 1723205832 branch 765704228
Wed Sep 05 07:55:34 2012
RFS[42]: Possible network disconnect with primary database
However, the archived files are getting copied to the standby server.I tried registering and recovering the logs but it also failed..Follows some of the information,
Primary
Oralce 11R2 EE
SQL> select max(sequence#) from v$log where archived='YES';
MAX(SEQUENCE#)
--------------
37668
[code]...
troubleshoot this and get the standby in sync.
Till now i was pretty sure that standby redologs are applied to database without any delay, but everything looks that the process is different. i did on primary server table and add some records to this table.
Next i switch to standby and open redologs (with hex editor) to look for this "create table" and "insert" commands. and i succeeded. so this is prove that standby log are supplied by primary database.
Next i cancelled recovery process and open database in read only mode and there was no records in this standby db, moreover there wasn't any table on standby. so i started to be confused, because i suspected that this table and it content will be on standby database.
Next i started recover process again for standby database and do some switch log on primary server. after that i back to standby, cancel recovery process and open database in read only mode. there was table and contents for it.
so my question is:
does standby redologs should be applied online to database or they are only applied after promoting standby database to primary database?
it looks like contents only from archivelog is applied to standby database. it is correct?
database is 9i.
I have a setup where i have one physical and logical standby from a primary database. In case of switch over between primary and physical database my logical apply gets stopped. Can a logical database be applied from a physical standby ?
View 5 Replies View RelatedHow to make the automatic deletion of all the archivelogs when it is applied successfully on the standby database? We have 1 primary database and 1 standby database using oracle physical dataguard.
In the primary database, we have daily backup job so that the applied archivelogs are deleted automatically, no problem
But in the standby database ( physical standby) I have to go there and delete manually because I don't issue backup on this database. Is there any solution for this problem.
I have a Sap Primry Database and also a standby db that was working perfectly.. We migrated the primary db from windows 2003 to windows 2008 and brought the primary db up.. I had to create a controlfile and do a system copy and had to reset the logs on the Primary.. All came up and when i checked the standby it was receiving the logs but after a month. i see that it was not applying the logs as I think because of the sequence number .. it stopped.
I did the ffg as per the attachement...My logs have been shipped across but not applied, But What worrries me is the log sequence number on my Primary
SQL> select max(sequence#),thread# from gv$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
27727 1
I have a DR setup with the following configuration
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
I dont want to backup the STDBY DB but I want the ARC files to be removed when applied so my flash area does not fill up. Is there some command(rman or not) that can fire off this policy?
I am working on Data Guard concepts. I have configured Data Guard which is working fine.
Now I am trying to work on script which can give me output on OS prompt if there are any archive gaps..
I tried to use few commands on a script archgap.sh as
archgap.sh
sqlplus "/ as sysdba"
select * from v$archive_gap;
I am trying to run above script on OS prompt as
ABC:/home/oracle>sh archgap.sh
but I am not getting output ... I want it a way that this script should run within interval of 5 min and give output on OS prompt.
I have a RAC system with DR set-up, this is a test environment and it doesn't have any backup, why DR is required but it exist. Since this is a test a lot or archives gets generated and deleting the archives has become a daily job for this server manually.
I want have a script to delete archive logs which is in non-ASM (i.e. filesystem) after ensuring that the archive log has been applied in standby database. If this can done only by RMAN.
I have a two node rac DB (prim and stdby) with two threads. I am trying to put together a script that will detect any gaps in the log files.
I know that MRPO only runs on one node. When I run the following query (see below) I can only see the sequence# for one of the threads.
select thread#, sequence#, process from gv$managed_standby;
THREAD# SEQUENCE# PROCESS
---------- ---------- ---------
1 119739 MRP0
...
...
I than query my PRIM DB and subract the value from my STBY DB to see the difference (gap)
select (sequence# - &scn) from v$log where thread# = &thrd and status ='CURRENT'
Can there be gaps in more than one thread? If so, how can I see the diffences?
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.
The archive log can not send to the standby database, how to do?
primary database spfile:
*.db_name=oracl
*.db_unique_name=oracl
[Code]....
Error 12170 received logging on to the standby
Error 12170 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'oraclbak'
Error 12170 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'oraclbak'
ORA-12170: TNS:Connect timeout occurred
*** 2012-02-15 08:31:50.678 60679 kcrr.c
PING[ARCq]: Heartbeat failed to connect to standby 'oraclbak'. Error is 12170.
*** 2012-02-15 08:31:50.680 58941 kcrr.c
kcrrfail: dest:2 err:12170 force:0 blast:1
kcrrwkx: nothing to do (end)
If my standby database is read only mode,does it can app the archive logs which come from primary database?
View 2 Replies View RelatedWe have a request to configure data guard for databases on the production server. Here is my situation:
We have a backup strategy in place where the backups are being taken on a regular basis. Archive logs are deleted as soon as they are backed up.
My question now is , is there a way of configuring the Data guard in such a way that there would be no change to existing backup strategy (RMAN) and still duplicate the archive logs to another destination and not delete the archive logs by RMAN backup process on that destination while the first destination is deleted.
We will be deleting the logs in the second archive destination using a script which checks if the logs were applied.
While Configuring Data Guard for ORacle 10g (10.2.0.4) 64 bits on Windows 2007 Server 64 bits.I got few questions
1. What is the Default mode of Standby Database?
2. Should we Always Start Physical Standby Database to Recover Missing Redo Archive Log?
SQL> startup mount;
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 2067496 bytes
Variable Size 163578840 bytes
Database Buffers 419430400 bytes
Redo Buffers 6320128 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
3. When there are missing Redo Log Archives e.g.
----On Standby Database--------
SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
2 ORDER BY RESETLOGS_ID,SEQUENCE#;
RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 15 A YES
812980008 16 A YES
812980008 17 A YES
812980008 18 A YES
[code]....
65 rows selected. Log 8, 9, 10, 11, 12, 13, 14, 15 are missing.
How to Apply / Recover These Logs on Standby Database?
I successfully created the standby database and the archive logs were properly moving on both the primary and the standby databases. For the proper transfer of the archive logs on the STANDBY database I used "FAL_CLIENT AND FAL_SERVER" in the pfile of the primary database specifying the location of the primary and the Standby respectively.
When I removed both the parameters from the pfile of the primary database still there was the transfer of the archive logs however there should not be "If I am not wrong" as I have removed both the parameters.
why there is still the transfer of the archive logs on the standby database.
which of the following views on the physical standby will us correct information on synchronization with Primary database?
For example, when I checked v$archived_gap it did not return any rows but the max(applied_seq#) on v$archive_dest_status was lagging far behind from the max(sequence#) on Primary database
select max(applied_seq#) from v$archive_dest_status where dest_id=2;
select max(sequence#) from v$archived_log where applied='YES';
select * from v$archive_gap;
i have found an issue regarding log archiving on dest1. yesterday one sequence number 76871 not archive to dest1.alert logfile content as follow. i configure standby and ship archive manually with window copy command. i need this archive to complete recovery on standby database.
Mon Oct 21 09:29:28 2013
ARC2: Completed archiving log# 3 seq# 76869
Mon Oct 21 09:39:28 2013
Thread 1 advanced to log sequence 76871
Current log# 2 seq# 76871 mem# 0: D:ORACLEORADATAORC1REDO02.LOG
[code]....
I am new to Oracle and a beginner in Data Guard.
1) How to find archive log gap from Primary Database site?
2) Do I need the DB_UNIQUE_NAME when setting the LOG_ARCHIVE_DEST_2 ? What is the purpose of DB_UNIQUE_NAME in LOG_ARCHIVE_DEST parameter?
3) If archivelog gap happens standby db goes out of sync with the primary database. What does out of sync actually mean?
4) Primary DB knows where to transport redo data based on the location mentioned in LOG_ARCHIVE_DEST_n of Primary DB. Am I correct in my understanding?
I have following query in Dataguard ..
If I want to take Rman archive log backup with delete input command , how the archive logs will be copied to standby database
For eg
I am taking archive backup as
RMAN>backup archivelog all delete input;
here consider few archives are not copied to standby database (due to nw issue) then how standby will receives these missing archives as those are deleted by rman backup at primary side.
I am not getting any document related to above query.
I have a Primary database and Standby database both in ASM. Recently my archive logs got deleted and i am trying to recover my standby database with an incremental backup based on scn from primary database. But i face the below error when i recover the standby database with the incremental backup taken in primary database.
RMAN> recover database noredo;Starting recover at 06-NOV-13using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=21 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +STDBY/11gdb/datafile/system.258.805921881destination for restore of datafile 00002: +STDBY/11gdb/datafile/sysaux.259.805921967destination for restore of datafile 00003: +STDBY/11gdb/datafile/undotbs1.260.805922023destination for restore of datafile 00004: +STDBY/11gdb/datafile
[code]....
I want to confirm the order of the data in results of two sql queries.
For example, in my table 5 columns are there.c1 to c5.
c1 c2 c3 c4 c5
--------------------------------------------------
test100 1 A 1-Jan-2006 2-Jan-2006
test100 2 B 3-jan-2006 4-Jan-2006
test101 1 A 01-Jan-2006 2-Jan-2006
test101 2 B 03-Jan-2006 4-Jan-2006
test101 3 c 05-Jan-2006 06-Jan-2006
In this table if you order by c2,
select c3
from tab1
where c1='test101'
order by c2;
c3
---------
A
B
C
If you order by c4,
select c1,c2,c3
from tab1
where c1='test101'
order by c4;
c3
------
A
B
C
Sometimes order of these data may change like. the result of the 2nd query may change (bcos of c4 date column changes)
C3
--
A
C
B
So,@ run time i want to check the order of these 2 results should match....if not,i need to display the corresponding c1 column.
How to understand the two parameters db_file_name_convert and log_file_name_convert,if there are missing in the parameter file of standdy database,how does oracle will do?
View 3 Replies View RelatedI want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below
1)shutdown primary and standby (stop managed recovery)
2)change db name in init.ora of primary and standby change database name control file location
3)create control file for primary from trace(script) make changes for db name and file locations
4)mount and open primary database
5)create standby control file
6) transfer standby control file to standby
7) mount stand by database and start manage recovery
If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all
i did everything writen but when i do *SQL>alter database recover managed standby database disconnect from session;*
i go and look in the standby database AlertLog file ,and thats whats writen
*ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: غير قادر على فتح الملف
O/S-Error: (OS 3) The system cannot find the path specified.
[code]....
strange thing that it realises the primary database in drive F and it goes to it but i dont understand what could be the reason of this ,although im doing this command while primary database is shutdown!
1) How to copy the ARCHIVED LOG FILE from Primary to DR location. Where if the DR is having the file already, how to SKIP that Particular Archived log file?
2) I have Checked with the FILE_TRANSFER DBMS package, but it gives the error if any file existing already.