*assuming you database is no in archivelog mode else skip this task*
ENABLE ARCHIVELOG MODE
----------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
CREATE A DIRECTORY FOR FLASHBACK AREA
-------------------------------------
UNIX> mkdir -p /sbcdump/01/oradumps/EMINUS/flashback_area
CONFIGURE FLASHBACK
-------------------
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='/sbcdump/01/oradumps/EMINUS/flashback_area' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.
SQL> shutdown immediate;
TURN FLASHBACK DATABASE ON
--------------------------
SQL> alter database mount;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
ADD DATAFILE
------------
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set numwidth 30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
------------------------------ --------------------
9205819379813 09-aug-2009 16:59:32
SQL> @/home/casenaem/ts_details -- this is my script to check on a tablespace
Enter value for tablespace_name: USERS
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 500 499.6875 499.6875 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
SQL> alter tablespace users
2 add datafile '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10m;
Tablespace altered.
SQL> @/home/casenaem/ts_details
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 510 499.6875 509.375 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf 10 AVAILABLE
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
------------------------------
9205819381790
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------------ ---------
9205819379813 09-AUG-09
FLASHBACK THE DATABASE TO THE OLDEST_FLASHBACK_SCN
--------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 9205819379813;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> @/home/casenaem/ts_details
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---
USERS 500 499.6875 499.6875 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ------------------------------ ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
Note:
As you can see that oracle will delete the file from dictionary,controlfile and the physical file
*Now if you want to recover the database before you executed flashback database*
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> recover database;
ORA-00279: change 9205819379814 generated at 08/09/2009 16:40:19 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc
ORA-00280: change 9205819379814 for thread 1 is in sequence #17
Specify log: {
AUTO
ORA-00279: change 9205819380249 generated at 08/09/2009 17:09:55 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000018_0694319678.arc
ORA-00280: change 9205819380249 for thread 1 is in sequence #18
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc' no longer needed for this recovery
.......
........
..........
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf'
ORA-01112: media recovery not started
SQL> select count(1) from v$datafile_header where fuzzy = 'YES';
COUNT(1)
------------------------------
5
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
6 rows selected.
SQL> !ls -l /sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf: No such file or directory
SQL> !ls -l /sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006: No such file or directory
SQL> alter database create datafile '/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006' as '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10M;
Database altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
6 rows selected.
SQL> recover database;
ORA-00279: change 9205819381724 generated at 08/09/2009 17:41:53 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc
ORA-00280: change 9205819381724 for thread 1 is in sequence #22
Specify log: {
aUTO
ORA-00279: change 9205819381766 generated at 08/09/2009 17:42:51 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000023_0694319678.arc
ORA-00280: change 9205819381766 for thread 1 is in sequence #23
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc' no longer needed for this recovery
....
......
.......
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
Note: Since Oracle has removed the physical file when I did a flashback you need to create it yourself in order for the recovery to conitnue
No comments:
Post a Comment