Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
—————- ————
jrd OPEN
SQL> select NAME,FLASHBACK_ON from v$database;
NAME FLASHBACK_ON
——— ——————
JRD NO
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> show parameter undo_retention
– FLASHBACK LOGS AND ARCHIVELOGS IN $ORACLE_BASE/fast_recovery_area
SQL> show parameter undo_retention
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 900
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
———————————— ———– ——————————
db_flashback_retention_target integer 1440
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 2750M
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest_size big integer 2750M
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/11.2.0.3/db_1/dbs/spfilejrd.ora
SQL> show parameter undo_management
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
–CHANGING THE LOCATION, FORMAT, SIZE, RETETION OF FLASHBACK LOGS – ARCHIVELOGS (OPTIONALLY).
ALTER SYSTEM SET log_archive_dest_1=”LOCATION=/u06/backups/archivelogs/jrd OPTIONAL REOPEN=300″ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format=”archJRD_%t_%r_%s.arc” SCOPE=SPFILE;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=”/u06/backups/fast_recovery_area” SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest_size=300G SCOPE=SPFILE;
–How to enable Flashback Feature on a Oracle Database.
SQL> shut immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
–The Database must be in Archivelogs mode, to use flasback technology.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> SELECT LOG_MODE, NAME FROM V$DATABASE;
LOG_MODE NAME
———— ———
ARCHIVELOG JRD
SQL> select NAME, FLASHBACK_ON from v$database;
NAME FLASHBACK_ON
——— ——————
JRD YES
–generating archivelogs
SQL> alter system switch logfile;
[/u01/app/oracle/fast_recovery_area/JRD/archivelog/2012_06_17]$ls -ltr
-rw-r—– 1 oracle oinstall 22849536 Jun 17 23:56 o1_mf_1_5_7xwr43jh_.arc
– Flashback Query
SQL> conn scott/****
SQL> CREATE TABLE JRDTEST ( OBJECTID NUMBER NOT NULL, ALIAS VARCHAR2 (20)) TABLESPACE USERS;
SQL> ALTER SESSION SET NLS_DATE_FORMAT=’DD/MM/YYYY HH24:MI:SS’;
SQL> SELECT TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’DD
——————-
18-06-2012 00:00:58
SQL> insert into JRDTEST values(101,’TABLE’);
SQL> insert into JRDTEST values(102,’TABLE’);
SQL> insert into JRDTEST values(103,’TABLE’);
SQL> insert into JRDTEST values(104,’TABLE’);
SQL> select * from JRDTEST;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
SQL> commit;
–Delete some data and commit
SQL> delete from JRDTEST where OBJECTID in(103,104);
SQL> commit;
SQL> select * from JRDTEST;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
SQL> SELECT TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’) from dual; –time
TO_CHAR(SYSDATE,’DD
——————-
18-06-2012 00:04:33
– Insert some more data and commit
SQL> insert into JRDTEST values(105,’VIEW’);
SQL> insert into JRDTEST values(106,’VIEW’);
SQL> insert into JRDTEST values(107,’VIEW’);
SQL> commit;
SQL> SELECT TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’DD
——————-
18-06-2012 00:06:03
–identifying transactions
SQL> set linesize 512
SQL> col versions_starttime for a25
SQL> col versions_endtime for a25
SQL> col versions_xid for a45
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation, objectid
from JRDTEST versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;
–errors with date format
–query these records using timestamp before actually recovering them.
SQL> SELECT * FROM JRDTEST AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:04:15′,’DD-MM-YYYY HH:MI:SS’);
ERROR at line 1:
ORA-01849: hour must be between 1 and 12
SQL> SELECT * FROM JRDTEST AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’);
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
–18-06-2012 00:03:30 ==> at this time until i delete two registers
–18-06-2012 00:04:33 ==> at this time i delete two registers (103, 140)
–inserting back these records to the original table
SQL> INSERT INTO JRDTEST (SELECT * FROM JRDTEST AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’));
SQL> select * from JRDTEST;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
105 VIEW
106 VIEW
107 VIEW
–> 101 TABLE
–> 102 TABLE
–> 103 TABLE
–> 104 TABLE
–As dba user.
–verifying the table for flasback
select OWNER,TABLE_NAME,STATUS,PARTITIONED,ROW_MOVEMENT
from dba_tables
where TABLE_NAME like ‘%JRDTEST%’;
OWNER TABLE_NAME STATUS PAR ROW_MOVE
—————————— —————————— ——– — ——–
SCOTT JRDTEST VALID NO DISABLED
grant flashback on scott.JRDTEST to scott;
–As scott user
alter table scott.JRDTEST enable row movement;
SQL> SELECT * FROM JRDTEST AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’);
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
–Flash back the table till that time stamp
SQL> SELECT * FROM JRDTEST AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 02:03:30′,’DD-MM-YYYY HH24:MI:SS’);
SQL> SELECT * FROM JRDTEST order by 1;
OBJECTID ALIAS
———- ——————–
101 TABLE
101 TABLE
102 TABLE
102 TABLE
103 TABLE
104 TABLE
105 VIEW
106 VIEW
107 VIEW
9 rows selected.
SQL> FLASHBACK TABLE JRDTEST TO TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’);
Flashback complete.
SQL> SELECT * FROM JRDTEST order by 1;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
–Undo a DROP TABLE Operation using FLASH BACK
SQL> create table JRDTEST_drop as select * from JRDTEST;
Table created.
SQL> select * from JRDTEST_drop;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
SQL> drop table JRDTEST_drop;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME
—————————— ——————————–
BIN$wu7OSqOSD1TgQ4LOqMDVgw==$0 JRDTEST_DROP
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
JRDTEST_DROP BIN$wu7OSqOSD1TgQ4LOqMDVgw==$0 TABLE 2012-06-20:23:20:25
–verifying that Dropped table contains all rows.
SQL> SELECT * FROM “BIN$wu7OSqOSD1TgQ4LOqMDVgw==$0″;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
– Flash back the table to it’s before drop state.
SQL> select OWNER, TABLE_NAME from all_tables where table_name = ‘JRDTEST_DROP’ and owner like ‘SCO%’;
no rows selected
SQL> FLASHBACK TABLE scott.JRDTEST_drop TO BEFORE DROP;
Flashback complete.
SQL> show recyclebin;
SQL>
SQL> select OWNER, TABLE_NAME from all_tables where table_name = ‘JRDTEST_DROP’ and owner like ‘SCO%’;
OWNER TABLE_NAME
—————————— ——————————
SCOTT JRDTEST_DROP
SQL> select TABLE_NAME, STATUS, DROPPED from all_tables where TABLE_NAME like’%JRDTEST_DROP%’;
TABLE_NAME STATUS DRO
—————————— ——– —
JRDTEST_DROP VALID NO
–ALSO WE CAN RENAME THE TABLE REMOVED
SQL> SELECT * FROM JRDTEST order by 1;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
SQL> drop table JRDTEST;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
JRDTEST BIN$wu7OSqOUD1TgQ4LOqMDVgw==$0 TABLE 2012-06-20:23:31:47
SQL> FLASHBACK TABLE “BIN$wu7OSqOUD1TgQ4LOqMDVgw==$0″ TO BEFORE DROP
RENAME TO JRDTEST1;
Flashback complete.
–TESTING
SQL> show recyclebin;
SQL>
SQL> SELECT * FROM JRDTEST;
SELECT * FROM JRDTEST order by 1
*
ERROR at line 1:
ORA-00942: table or view does not exist ok
SQL> SELECT * FROM JRDTEST1;
OBJECTID ALIAS
———- ——————–
101 TABLE
102 TABLE
103 TABLE
104 TABLE
–Freeing Space in the Recycle Bin
SQL> PURGE TABLE JRDTEST; –alright, because i haven’t anything in the recycle bin
PURGE TABLE JRDTEST
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN ok
SQL> PURGE TABLE “BIN$wu7OSqOUD1TgQ4LOqMDVgw==$0″;
SQL> PURGE TABLESPACE USER; – - All deleted objects of this tablespace.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> PURGE DBA_RECYCLEBIN;
–ORACLE FLASHBACK DATABASE (Point-In-Time Recovery)
–AS SYSDBA
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
————
OPEN
SQL> select NAME,FLASHBACK_ON from v$database;
NAME FLASHBACK_ON
——— ——————
JRD YES
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> show parameter undo_retention
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 900
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
———————————— ———– ——————————
db_flashback_retention_target integer 1440
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 2750M
–Determining the Current Flashback Database Window
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASH
——————– ——————–
861065 17-JUN-2012 11:53:27
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASH
——————– ——————–
861065 17-JUN-2012 23:53:27
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN date= 20-jun-2012 20:51:20
———–
916953
–Execute the FLASHBACK DATABASE Command from RMAN
[oracle@localhost ~]$ rman target /
RMAN> FLASHBACK DATABASE TO TIME “TO_DATE(’17-06-2012 23:53:27′,’DD-MM-YYYY HH24:MI:SS’)”;
–another method
RMAN> flashback database to scn 916953; –ORA-38743: Time/SCN is in the future of the database. ok
RMAN> flashback database to scn 861065;
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASH
——————– ——————–
861065 17-JUN-2012 23:53:27
SQL> SELECT CURRENT_SCN, RESETLOGS_TIME from v$database;
CURRENT_SCN RESETLOGS_TIME
———– ——————-
866011 22/06/2012 22:23:07
–VERIFYING
SQL> conn scott/jrd;
Connected.
SQL> SELECT * FROM JRDTEST1;
SELECT * FROM JRDTEST1
*
ERROR at line 1:
ORA-00942: table or view does not exist –ok
Không có nhận xét nào:
Đăng nhận xét