Data Guard :: Detecting Archive Log Gaps?
Nov 17, 2012
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?
View 4 Replies
ADVERTISEMENT
Dec 29, 2012
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;
View 1 Replies
View Related
Mar 6, 2012
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.
View 2 Replies
View Related
Mar 8, 2013
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.
View 6 Replies
View Related
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.
View 1 Replies
View Related
Jul 2, 2011
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"
View 2 Replies
View Related
Mar 25, 2013
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]...
View 8 Replies
View Related
Feb 15, 2012
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)
View 6 Replies
View Related
Aug 5, 2011
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.
View 1 Replies
View Related
Feb 16, 2012
If my standby database is read only mode,does it can app the archive logs which come from primary database?
View 2 Replies
View Related
Mar 28, 2011
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.
View 2 Replies
View Related
Apr 23, 2013
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?
View 11 Replies
View Related
Dec 9, 2010
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.
View 3 Replies
View Related
Jun 27, 2013
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
View 5 Replies
View Related
Oct 22, 2013
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]....
View 7 Replies
View Related
Mar 23, 2013
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?
View 3 Replies
View Related
Mar 17, 2012
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.
View 7 Replies
View Related
Nov 6, 2013
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]....
View 4 Replies
View Related
Sep 24, 2011
I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.
i.e. :
CREATE TABLE TRY_F (DAT DATE);
INSERT ALL
INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY'))
SELECT * FROM DUAL;
I need a smart way of getting the dates that were missed in DAT.
View 4 Replies
View Related
Jul 5, 2011
We have physical data guard configured version (10.2.0.4). We are in need to upgrade primary & standby database to 11G R2. Can we perform rolling upgrade.
View 3 Replies
View Related
Oct 17, 2012
Controlling User Access to Tables in a Logical Standby Database can be controlled using the following command:
ALTER DATABASE GUARD STANDBY;
My simple question is: how can I know the current active Guard setting in the standby database?
Oracle 11g R2.
View 1 Replies
View Related
Jun 14, 2012
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[code]....
Requirements for the result set:
1. List the root division as column headers
2. Start date is March 15, 2012
3. End date is June 30, 2012
4. Do not skip or omit dates
5. The values under each column are percentages where completed = 'Y'
5a. Example, D5_Total = 277, D5_Completed(Y) = 254, D5_Percent(Y) = 254/277 = 92%
5b. Rounded to whole percentage like 92% and NOT 91.696%
Example of similar result set:
,D1,D12,D18,D2,D3,D4,D5
05/30/2012,0,0,0,0,0,0,0
06/01/2012,10,20,30,40,50,60,70
06/02/2012,12,22,32,42,52,62,72
06/03/2012,13,23,33,43,53,63,73
06/04/2012,14,24,34,44,54,64,74
[code]....
The result set will be used to create a line graph. The graph will show which root division is closer to reaching 100% before the deadline of June 30, 2012.
View 12 Replies
View Related
Jul 31, 2011
I am not sure if the problem is related to hidden characters but its my best guess so far. I am trying to enhance a part of the ERD by creating a lookup for a column one of the table that uses text (finite set of values).
CREATE TABLE N_AGREEMENT_STATUS
(
STATUS_ID NUMBER(2) PRIMARY KEY,
STATUS_NAME VARCHAR2(10 BYTE)
);
INSERT ALL
INTO N_AGREEMENT_STATUS VALUES (1, 'INACTIVE')
INTO N_AGREEMENT_STATUS VALUES (2, 'ACTIVE')
INTO N_AGREEMENT_STATUS VALUES (3, 'CLOSED')
INTO N_AGREEMENT_STATUS VALUES (4, 'CANCELLED')
SELECT * FROM DUAL;
when I try to update the source table no update takes place (0 records updated) if I used the following statement:
ALTER TABLE N_AGREEMENT ADD STATUS_ID NUMBER(2);
UPDATE N_AGREEMENT SET STATUS_ID =
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE N_AGREEMENT.STATUS = STATUS_NAME );
but it works fine only if I used:
UPDATE N_AGREEMENT SET STATUS_ID =
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE N_AGREEMENT.STATUS LIKE STATUS_NAME || '%'
);
The strange thing is that when I use:
SELECT N_AGREEMENT.STATUS, N_AGREEMENT.STATUS_ID
FROM N_AGREEMENT
WHERE N_AGREEMENT.STATUS = 'ACTIVE';
it returns correct results and all status = 'ACTIVE' appear correctly!
View 20 Replies
View Related
Jul 26, 2011
I have a form that i need to be able to detect when a field is has been updated. I will then do the appropriate update to the table.
I can't use the standard oracle forms data block for handling updates because my data selection is controlled by me via the when-new-block-instance.
The problem is when i select a value from an LOV and place the new data in the field. I don't know how to detect if the field is updated or not. I am currently using a loop to cycle through all the records, I have an if/else in the key_commit trigger that basically determines if there is an update or insert to be done to the table. The problem is that it seems if i select a value for one record that record goes through the INSERT procedure, (which is correct), but all the other records(not touched/changed) are going through the UPDATE procedure.
Is there a way in forms to check the status of a field? I know of system.form_status but that does not work for me.
View 16 Replies
View Related
Oct 20, 2010
If I'm looking on v$session - values in osuser column for some machines displayed with DOMAINNAME, and for other - without. Even same user (MYDOMAIN\ADMINISTRATOR) from 2 different machines displayed in 2 different ways on the same db:
MYDOMAIN\Administrator from machine A, and administrator from machine B.
Both machines are belongs to same windows domain. DB (oracle 10g) have no "DOMAIN" defined.
What make the difference? And can I somehow to affect this behaviour? I mean, can I define that all users will be displayed as DOMAINNAME\USERNAME or MACHINENAME\USERNAME(in case of no domain exists)?
View 8 Replies
View Related
Oct 20, 2010
If I'm looking on v$session - values in osuser column for some machines displayed with DOMAINNAME, and for other - without.Even same user (MYDOMAINADMINISTRATOR) from 2 different machines displayed in 2 different ways:
MYDOMAINAdministrator from machine A, and administrator from machine B.Both machines are belongs to same windows domain.
What make the difference?And can I somehow to affect this behaviour? I mean, can I define that all users will be displayed as DOMAINNAMEUSERNAME or MACHINENAMEUSERNAME(in case of no domain exists)?
View 6 Replies
View Related
Aug 27, 2012
I heard about flaskback data archive. can i get some short example to understand in practical way
View 11 Replies
View Related
Jul 7, 2011
I have a partitioned table that is streamed to another database. I need to archive data on that table. That is I need to add a partition and remove a partition.
If I make those changes to the source table, will it stream over to the destination table?
If not, can I ...
pause streaming make changes to source table make same changes to destination table sreenable streaming. I know making data changes to the destination table can screw up streams but not sure if that holds for ddl.
View 1 Replies
View Related
Aug 23, 2011
I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);
So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.
create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;
create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;
alter table teta_admin.t_prac flashback archive audit_flash_archive;
and everything works fine but on sys user.
i can query this table using "as of timestamp" clause
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')
but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.
interface user have such sys privs:
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO
and table privs:
SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT
what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.
View 9 Replies
View Related
May 24, 2011
Before I begin, I want to clarify that I am newbie in the administration of data warehouse.I need to know how to calculate the sizes of the archive and redo on data warehouse DB, in order to make an initial sizing of the BD on disks level.
Is there a formula to calculate the size?
View 1 Replies
View Related
