Can we use Flashback database If noarchivelog exists
Can we use flashback database log after switching from archivelog mode to noarchivelog mode and back to archivelog mode?
According to this below document reference
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV583
We cannot, because when we turn off flashback database, you lose the flashback logs unless they are guaranteed
If they guaranteed, We can’t alter no archivelog mode
Let us see some example :
Step 1: Ensure Flashback ON
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
Step 2:
SQL> conn flashown/flashown; Connected. SQL> create table employee as select * from scott.emp; Table created. SQL> update employee set sal=sal+50; 14 rows updated. SQL> commit; Commit complete. SQL> create restore point azar; Restore point created. SQL> update employee set sal=sal+50; 14 rows updated. SQL> commit; SQL>select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 19-DEC-10 09.53.55.726000 AM +03:00
Step 3: alter noarchivelog mode
SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1: ORA-38774: cannot disable media recovery - flashback database is enabled SQL> alter database flashback off; Database altered. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> update flashown.employee set sal=sal+50; 14 rows updated. SQL> commit; Commit complete.
Step 4: Again back to archivelog mode & Recover flashback database But we can’t.
SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database flashback on; Database altered. SQL> flashback database to restore point azar; flashback database to restore point azar * ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK. SQL> flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS'); flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS') * ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK.
Step 5: Create Guarantee Restore point
SQL> show parameter db_flashback_retention_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> create restore point azardb guarantee flashback database; Restore point created. SQL> alter database open; Database altered. SQL> select sal from flashown.employee; SAL ---------- 955 1755 1405 3130 1405 3005 2605 3255 5155 1655 1255 SAL ---------- 1105 3155 1455 14 rows selected. SQL> select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 19-DEC-10 10.11.43.716000 AM +03:00 SQL> update flashown.employee set sal=sal-5; 14 rows updated. SQL> commit; Commit complete. SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database flashback off; Database altered. SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1: ORA-38781: cannot disable media recovery - have guaranteed restore points SQL> alter database flashback on; Database altered. SQL> flashback database to timestamp to_date('19-DEC-10 10.11.44','DD-MM-YY HH24:MI:SS'); Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> select sal from flashown.employee; SAL ---------- 955 1755 1405 3130 1405 3005 2605 3255 5155 1655 1255 SAL ---------- 1105 3155 1455 14 rows selected. SQL>
So we can’t alter noarchivelog mode While Guarantee restore point exists in DB.
And the If flashback database off, we can’t recover flashback database using flashback logs.