Data Guard :: How To Delete Archive Log On Standby
Jun 18, 2011
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.
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]...
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 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
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.
On next month i will have plan to delete history data on my Primary database, but i still want to keep them in logical standby as the data warehouse. So, how can i skip delete statement from primary database apply to logical standby.
Oracle SQL apply only support for Skip DML but not for keep delete statement
(on 10g and 11g)SQL>exec dbms_logstdby.skip(statement => 'DELETE',schema_name => 'SCOTT', object_name => 'DEPT');BEGIN dbms_logstdby.skip(statement => 'DELETE',schema_name => 'SCOTT', object_name => 'DEPT'); END; *ERROR at line 1:ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'SKIP'ORA-06550: line 1, column 7:PL/SQL:
How 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 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
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 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.
We 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.
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
We have data archive scripts, these scripts move data for a date range to a different table. so the script has two parts first copy data from original table to archive table; and second delete copied rows from the original table. The first part is executing very fast but the deletion is taking too long i.e. around 2-3 hours. The customer analysed the delete query and are saying the script is not using index and is going into full table scan. but the predicate itself is the primary key,More info below
Plan hash value: 2798378986 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------| 0 | DELETE STATEMENT | | 2520 | 233K| 87 (2)| 00:00:02 || 1 | DELETE | MON_TXNS | | | | ||* 2 | HASH JOIN RIGHT SEMI | | 2520 | 233K| 87 (2)| 00:00:02 || 3 | INDEX FAST FULL SCAN| OTW_ID_TXN | 2520 | 15120 | 3 (0)| 00:00:01 || 4 | TABLE ACCESS FULL | MON_TXNS | 14260 | 1239K| 83 (0)| 00:00:02 |
------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
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 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.
