Flashback Data Archive?
Aug 27, 2012I heard about flaskback data archive. can i get some short example to understand in practical way
View 11 Replies
ADVERTISEMENT
Server Administration :: Flashback Data Archive
Aug 23, 2011I 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.
Backup Flashback Data Archive And Restore On New Database?
Oct 14, 2012Is there anyway to backup Flashback Data Archive (FBDA) data and can be restore on new database. I cannot find Oracle's document or any document explain about backing up this data.
View 6 Replies View RelatedServer Administration :: Why Archive Should Be Turned ON Before Turning On Flashback
Aug 5, 2010I have read it in books that flashback uses undo data to create the flashback data or to flashback the database to a time in the past.Then, what is the role of archive files in flashback operation. Why it is mandatory to turn on archiving before turning on flashback. Also, if you remove the latest archive files, you can NOT flashback the data to a time in past (Oracle complains of missing archive files).
View 8 Replies View RelatedRAC/ASM Clusterware Installation :: Configuration Of Archive Logs And Backup Using Flashback
Jun 8, 2012As per Client requirement, i need to configure archive log and backups in flashback.
View 3 Replies View RelatedData Guard :: Flashback And Fast-Start Failover
Jun 18, 2012My Data Guard 's Enviroment:
Primary: 11.1.0.7 RAC 2 Nodes
Standby: 11.1.0.7 Single Instance
I want to implement Fast-Start Failover at my enviroment. Reading requirements, I found that Oracle Flashback is needed (I don't use Flashback on my databases).
Best Practices recommends 60 min (1 hr) for DB_FLASHBACK_RETENTION_TARGET if I just want to use Flashback to fast-start failover.
My questions:
What 's better, setup up flash recovery area on ASM or O.S. filesystem?
What is the recommended size for flash recovery area?
Backup & Recovery :: How To Recover Before 15 Minutes Table Data From Flashback
Mar 23, 2012I delete some of the data from a table unfortunately and make commit on that on my production server.
getting back my all data of this table only of last 15 minutes.
Data Guard :: Flashback Snapshot Standby Database Or New Primary Database After Failover?
Aug 30, 2013If flashback is enable in physical standby database 1. If we failover at 11AM can I flash back NEW primary database to 6 AM ? 2. if I convert physically standby database to snapshot standby database at 11AM , Can I flashback snapshot standby database to 6 AM and do some works on it (DML operations) then converting the snapshot standby database into physical standby database ?
View 1 Replies View RelatedData Guard :: Archive Gap Script
Mar 6, 2012I 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.
How To Archive Data On Partitioned Table
Jul 7, 2011I 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.
Data Guard :: Archive Log Deletion
Mar 8, 2013I 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.
Data Guard :: Detecting Archive Log Gaps?
Nov 17, 2012I 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?
Data Guard :: How To Delete Archive Log On Standby
Jun 18, 2011We 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.
Data Guard :: Archive Copied Onto DR But Not Applied?
Jul 2, 2011We 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"
Data Guard :: Archive Not Applied On Standby
Mar 25, 2013We 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]...
Data Guard :: Archive Log Cannot Send To Standby
Feb 15, 2012The 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)
How To Calculate Sizes Of Archive / Redo In Data Warehouse DB
May 24, 2011Before 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?
Data Guard :: Confirm Particular Archive Log File Applied Or Not?
Aug 5, 2011I 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.
Data Guard :: App Archive Logs Which Come From Primary Database?
Feb 16, 2012If my standby database is read only mode,does it can app the archive logs which come from primary database?
View 2 Replies View RelatedData Guard :: Setup/ Archive Destination Configuration
Mar 28, 2011We 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.
Data Guard :: Redo Archive Logs Missing
Apr 23, 2013While 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?
Data Guard :: Transfer Of Archive Log On Standby Database
Dec 9, 2010I 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.
Data Guard :: Archive Logs Are Being Shipped But Not Applied
Jun 27, 2013Currently 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
Data Guard :: Difficulty In Detecting Archive Gap On Physical Standby
Dec 29, 2012which 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;
Data Guard :: Archive To Complete Recovery On Standby Database
Oct 22, 2013i 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]....
Data Guard :: Find Archive Log Gap From Primary Database Site?
Mar 23, 2013I 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?
Data Guard :: RMAN Archive Log Backup With Delete Input Command
Mar 17, 2012I 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.
Archive Old Data Which Has Stored Before 2012 And Can Be Restored For 2011 Or 2010 Needed?
Jan 29, 2013I have a database in which two table contains millions of data and the whole database size is getting more bigger. is there any option to archive the old data which has stored before 2012 and can be restored when the data required for 2011 or 2010 needed.
View 4 Replies View RelatedData Guard :: Applying Incremental Backup On Standby To Resolve Archive Log Gap?
Nov 6, 2013I 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]....
Data Archive Script Is Taking Too Long To Delete A Large Table
Aug 8, 2013We 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
CREATE TABLE "APP"."MON_TXNS" ( "ID_TXN" NUMBER(12,0) NOT NULL ENABLE, "BOL_IS_CANCELLED" VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, "ID_PAYER" NUMBER(12,0), "ID_PAYER_PI" NUMBER(12,0), "ID_PAYEE" NUMBER(12,0), "ID_PAYEE_PI" NUMBER(12,0), "ID_CURRENCY" CHAR(3 BYTE) NOT NULL ENABLE, "STR_TEXT" VARCHAR2(60 CHAR), "DAT_MERCHANT_TIMESTAMP" DATE, "STR_MERCHANT_ORDER_ID" VARCHAR2(30 BYTE), "DAT_EXPIRATION" DATE, "DAT_CREATION" DATE, "STR_USER_CREATION" VARCHAR2(30 CHAR), "DAT_LAST_UPDATE"
[Code]...
Data is first moved to table in schema3.OTW. and then we are deleting all the rows in otw from original table. below is the explain plan for delete
SQL> explain plan for 2 delete from schema1.mon_txns where id_txn in (select id_txn from schema3.OTW);
Explained. SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------
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):
---------------------------------------------------