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.
No comments yet
DBA 