第5章 閃回資料庫Understanding the Flashback Database
第5章 閃回資料庫Understanding the Flashback Database
配置閃回恢復區
sql>alter system set db_recovery_file_dest_size = 6g scope=both;
sql>alter system set db_recovery_file_dest='c:\oracle\flash_recovery_area\ora101t';
sql>alter system set db_recovery_file_dest_size=2000m;
aql>alter system set db_recovery_file_dest='';
配置閃回恢復區
sql>alter system set db_recovery_file_dest_size = 6g scope=both;
sql>alter system set db_recovery_file_dest='c:\oracle\flash_recovery_area\ora101t';
sql>alter system set db_recovery_file_dest_size=2000m;
aql>alter system set db_recovery_file_dest='';
使用閃回恢復區Using the Flash Recovery Area
Let's walk through performing an automated disk-based backup and recovery:
1.First, connect to the target database:
command>set ORACLE_SID=ora101rc
command>rman
rman>connect target
2.Next, perform. a backup of the target database:
rman>backup as copy database;
3. Finally, query the V$RECOVERY_FILE_DEST to determine if the SPACE_USED column value has increased.
sql>select * from v$recovery_file_dest;
Let's walk through performing an automated disk-based backup and recovery:
1.First, connect to the target database:
command>set ORACLE_SID=ora101rc
command>rman
rman>connect target
2.Next, perform. a backup of the target database:
rman>backup as copy database;
3. Finally, query the V$RECOVERY_FILE_DEST to determine if the SPACE_USED column value has increased.
sql>select * from v$recovery_file_dest;
備份閃回恢復區
rman>backup recovery area;
rman>backup recovery files;
rman>backup recovery area;
rman>backup recovery files;
配置閃回恢復區
1. Start the database in MOUNT mode:
sql>connect / as sysdba
sql>startup mount
2. Set the DB_FLASHBACK_RETENTION_TARGET parameter to the desired value. This value can be set as an initialization parameter if you're not using the SPFILE. This value is in minutes, which equates to three days:
SQL> alter system set db_flashback_retention_target=4320;
3. Enable the flashback capability:
sql>alter database flashback on;
4. Now the database can be opened for normal use:
sql>alter database open;
1. Start the database in MOUNT mode:
sql>connect / as sysdba
sql>startup mount
2. Set the DB_FLASHBACK_RETENTION_TARGET parameter to the desired value. This value can be set as an initialization parameter if you're not using the SPFILE. This value is in minutes, which equates to three days:
SQL> alter system set db_flashback_retention_target=4320;
3. Enable the flashback capability:
sql>alter database flashback on;
4. Now the database can be opened for normal use:
sql>alter database open;
在RMAN中使用閃回資料庫
(example演示了閃回某個時刻(能閃回的最舊scn),但我在這中間曾經resetlogs也幾次,也更改了控制檔案,能成嗎,以後測試,估計要先恢復控制檔案)
1. First, query the V$FLASHBACK_DATABASE_LOG view to retrieve the OLDEST_FLASHBACK_SCN:
command>sqlplus /nolog
sql>connect / as sysdba
sql>select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
2. Next, shut down and start the database in MOUNT mode:
sql>shutdown
sql>startup mount
3. Next, issue the Flashback Database recovery from RMAN:
command>rman
rman>connect target
rman>flashback database to scn=689316;
4. Finally, open the database with the RESETLOGS option,
sql>alter database open resetlogs;
(example演示了閃回某個時刻(能閃回的最舊scn),但我在這中間曾經resetlogs也幾次,也更改了控制檔案,能成嗎,以後測試,估計要先恢復控制檔案)
1. First, query the V$FLASHBACK_DATABASE_LOG view to retrieve the OLDEST_FLASHBACK_SCN:
command>sqlplus /nolog
sql>connect / as sysdba
sql>select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
2. Next, shut down and start the database in MOUNT mode:
sql>shutdown
sql>startup mount
3. Next, issue the Flashback Database recovery from RMAN:
command>rman
rman>connect target
rman>flashback database to scn=689316;
4. Finally, open the database with the RESETLOGS option,
sql>alter database open resetlogs;
監視閃回資料庫
sql>select flashback_on from v$database;
sql>select oldest_flashback_scn,oldest_flashback_time,retention_target,estimated_flashback_size from v$flashback_database_log;
sql>select * from v$flashback_database_stat;
sql>select flashback_on from v$database;
sql>select oldest_flashback_scn,oldest_flashback_time,retention_target,estimated_flashback_size from v$flashback_database_log;
sql>select * from v$flashback_database_stat;
EM使用閃回資料庫(略)
第6章 從使用者錯誤恢復Recovering from User Errors
使用閃回丟棄
sql>flashback table t1 to before drop;
瞭解回收站
sql>select * from t1;
sql>drop table t1;
sql>show recyclebin;
sql>select object_name as recycle_name ,original_name,object_name from recyclebin;
BIN$HbesBq6flungQAB/AQBm7A==$0 TB01
sql>select * from "BIN$0ZVR8xxxxxxxxxxxxxxx"
sql>flashback table "BIN$0Zxxxxxxxxxxxxxxx" to before drop rename to t2;
sql>select * from t2;
sql>show recyclebin;
回收站和空間利用
sql>purge table "BIN$0ZVR8eDEQbK4sxxxxxxxxxxx==$0"
sql>purge tablespace users user test;
The PURGE RECYCLEBIN command is used to purge your own Recycle Bin.
The PURGE DBA_RECYCLEBIN command removes all objects from all users' Recycle Bins.
sql>purge recyclebin;
sql>connect / as sysdba;
sql>purge dba_recyclebin;
閃回丟棄和回收站的侷限性(略)
通過EM實現閃回丟棄表
使用閃回丟棄
sql>flashback table t1 to before drop;
瞭解回收站
sql>select * from t1;
sql>drop table t1;
sql>show recyclebin;
sql>select object_name as recycle_name ,original_name,object_name from recyclebin;
BIN$HbesBq6flungQAB/AQBm7A==$0 TB01
sql>select * from "BIN$0ZVR8xxxxxxxxxxxxxxx"
sql>flashback table "BIN$0Zxxxxxxxxxxxxxxx" to before drop rename to t2;
sql>select * from t2;
sql>show recyclebin;
回收站和空間利用
sql>purge table "BIN$0ZVR8eDEQbK4sxxxxxxxxxxx==$0"
sql>purge tablespace users user test;
The PURGE RECYCLEBIN command is used to purge your own Recycle Bin.
The PURGE DBA_RECYCLEBIN command removes all objects from all users' Recycle Bins.
sql>purge recyclebin;
sql>connect / as sysdba;
sql>purge dba_recyclebin;
閃回丟棄和回收站的侷限性(略)
通過EM實現閃回丟棄表
使用閃回版本查詢Using Flashback Versions Query
1.
sql>update t1 set salary=18000 where employee='JONES';
sql>commit;
sql>update t1 set salary=21000 where employee='JONES';
sql>commit;
sql>update t1 set salary=25000 where employee='JONES';
sql>commit;
2.顯示不同時刻的value
sql>select salary from t1 versions between scn minvalue and maxvalue where employee = 'JONES';
sql>select salary from t1
versions between timestamp
to_timestamp('2004-10-26 11:37:01','YYYY-MM-DD HH:MI:SS') and
to_timestamp('2004-10-26 11:43:01','YYYY-MM-DD HH:MI:SS')
where employee = 'JONES';
sql>select current_scn,scn_to_timestamp(current_scn) from v$database;
There are several new pseudocolumns that help you work with the Flashback Versions
Query:
VERSIONS_STARTTIME
VERSIONS_STARTSCN
VERSIONS_ENDTIME
VERSIONS_ENDSCN
VERSIONS_XID
VERSIONS_OPERATION
1.
sql>update t1 set salary=18000 where employee='JONES';
sql>commit;
sql>update t1 set salary=21000 where employee='JONES';
sql>commit;
sql>update t1 set salary=25000 where employee='JONES';
sql>commit;
2.顯示不同時刻的value
sql>select salary from t1 versions between scn minvalue and maxvalue where employee = 'JONES';
sql>select salary from t1
versions between timestamp
to_timestamp('2004-10-26 11:37:01','YYYY-MM-DD HH:MI:SS') and
to_timestamp('2004-10-26 11:43:01','YYYY-MM-DD HH:MI:SS')
where employee = 'JONES';
sql>select current_scn,scn_to_timestamp(current_scn) from v$database;
There are several new pseudocolumns that help you work with the Flashback Versions
Query:
VERSIONS_STARTTIME
VERSIONS_STARTSCN
VERSIONS_ENDTIME
VERSIONS_ENDSCN
VERSIONS_XID
VERSIONS_OPERATION
SQL>select to_char(versions_starttime,'DD-MON HH:MI') "START DATE",
to_char (versions_endtime, 'DD-MON HH:MI') "END DATE",
versions_xid,
versions_operation,
employee,
salary
from test.t1
versions between scn
minvalue and maxvalue
where employee = 'JONES'
to_char (versions_endtime, 'DD-MON HH:MI') "END DATE",
versions_xid,
versions_operation,
employee,
salary
from test.t1
versions between scn
minvalue and maxvalue
where employee = 'JONES'
select to_char(versions_starttime,'DD-MON HH:MI:SS') "START DATE",
to_char (versions_endtime, 'DD-MON HH:MI:SS') "END DATE",
versions_xid,
versions_operation,
salary
from t1
versions between scn
minvalue and maxvalue
where employee_id =197;
to_char (versions_endtime, 'DD-MON HH:MI:SS') "END DATE",
versions_xid,
versions_operation,
salary
from t1
versions between scn
minvalue and maxvalue
where employee_id =197;
使用閃回事務查詢 Using Flashback Transaction Query
The Flashback Transaction Query is designed to be a diagnostic tool to help identify changes made to the database at the transaction level.
The Flashback Transaction Query is based on undo data 。
Using the FLASHBACK_TRANSACTION_QUERY view can help identify the table and operation that is performed against the table. This view can be large, so it is helpful to use a filter like the transaction identifier, which is in the column XID. The XID value was identified in the previous
The privilege required to use the Flashback Transaction Query is the system privilege FLASHBACK ANY TABLE. (用sysdba管理吧?)
The Flashback Transaction Query is designed to be a diagnostic tool to help identify changes made to the database at the transaction level.
The Flashback Transaction Query is based on undo data 。
Using the FLASHBACK_TRANSACTION_QUERY view can help identify the table and operation that is performed against the table. This view can be large, so it is helpful to use a filter like the transaction identifier, which is in the column XID. The XID value was identified in the previous
The privilege required to use the Flashback Transaction Query is the system privilege FLASHBACK ANY TABLE. (用sysdba管理吧?)
Flashback Versions Query example.
SQL>SELECT table_name,operation,undo_sql from flashback_transaction_query
where xid='020018001F030000';
sql>select table_name,operation,undo_sql from flashback_transaction_query
where start_timestamp >= to_timestamp('2004-10-26 06:45:00','YYYY-MM-DD HH:MI:SS')
and table_owner='TEST';
SQL>SELECT table_name,operation,undo_sql from flashback_transaction_query
where xid='020018001F030000';
sql>select table_name,operation,undo_sql from flashback_transaction_query
where start_timestamp >= to_timestamp('2004-10-26 06:45:00','YYYY-MM-DD HH:MI:SS')
and table_owner='TEST';
使用閃回表Using Flashback Table
Flashback Table is a Flashback Technology that allows you to recover a table or set tables to a specific point-in-time without performing an incomplete recovery.
There are two main clauses that are used with the Flashback Table:
The TO SCN clause can recover the Flashback Table to a certain SCN.
The TO TIMESTAMP clause can recover the Flashback Table to a certain point-in-time.
Let's walk through performing a Flashback Table with SCN:
1.Enable ROW MOVEMENT on table T1:
sql>alter table t1 enable row movement;
2.Retrieve the current SCN before you modify the table:
sql>select current_scn from v$database; 771511
3.
sql>update t1 set salary=50000 where employee = 'JONES';
sql>commit;
4.
sql>flashback table t1 to scn 771511;
5.
sql>select * from t1 where employee='JONES';
OK已恢復
Flashback Table is a Flashback Technology that allows you to recover a table or set tables to a specific point-in-time without performing an incomplete recovery.
There are two main clauses that are used with the Flashback Table:
The TO SCN clause can recover the Flashback Table to a certain SCN.
The TO TIMESTAMP clause can recover the Flashback Table to a certain point-in-time.
Let's walk through performing a Flashback Table with SCN:
1.Enable ROW MOVEMENT on table T1:
sql>alter table t1 enable row movement;
2.Retrieve the current SCN before you modify the table:
sql>select current_scn from v$database; 771511
3.
sql>update t1 set salary=50000 where employee = 'JONES';
sql>commit;
4.
sql>flashback table t1 to scn 771511;
5.
sql>select * from t1 where employee='JONES';
OK已恢復
Let's walk through performing a Flashback Table with SCN: 測試時間點閃回表
FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');
Triggers are disabled by default during the Flashback Table process.
sql>flashback table table_name to scn 771551 enable triggers;
sql>flashback table table_name to scn 771551 enable triggers;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/235507/viewspace-555203/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- flashback query閃回資料
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- Oracle 12.2新特性: PDB級閃回資料庫(Flashback PDB)Oracle資料庫
- 【Flashback】Flashback Drop閃回刪除功能實驗
- Oracle資料庫閃回Oracle資料庫
- Oracle閃回技術--Flashback Version QueryOracle
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- Flashback Drop閃回刪除功能實踐
- Oracle 閃回資料庫測試Oracle資料庫
- 詳解oracle資料庫閃回Oracle資料庫
- dg_閃回資料庫實驗資料庫
- Oracle資料庫閃回區空間不足Oracle資料庫
- 在Oracle DG Standby庫上啟用flashback database功能OracleDatabase
- oracle 10g flashback databaseOracle 10gDatabase
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- 第 67 期 Go database/sql 資料庫連線池分析GoDatabaseSQL資料庫
- 騰訊基於全時態資料庫技術的資料閃回資料庫
- database資料庫的資料結構Database資料庫資料結構
- my2sql資料閃回SQL
- GeminiDB Cassandra介面新特性FLASHBACK釋出:任意時間點秒級閃回
- [20210722]ORA-38760與flashback database.txtDatabase
- 工具分享丨資料閃回工具MyFlash
- Orcale利用閃回功能恢復資料
- 資料庫分片(Database Sharding)詳解資料庫Database
- 3. 資料庫 database & Eloquent ORM資料庫DatabaseORM
- Flashback database必須要有之前的archivelog嗎?DatabaseHive
- Android資料庫檢視庫---Android-Debug-DatabaseAndroid資料庫Database
- 2.4.1 使用 CREATE DATABASE 子句建立資料庫Database資料庫
- 使用RMAN複製資料庫 active database資料庫Database
- mysql database manual(mysql資料庫手冊)MySqlDatabase資料庫
- 國產資料庫OushuDB(Database)簡介資料庫Database
- 利用binlog2sql閃回丟失資料SQL
- 2.4 使用 CREATE DATABASE 語句建立資料庫Database資料庫
- Part I Basic Database Administration (資料庫基本管理)Database資料庫
- 4、MySQL建立資料庫(CREATE DATABASE語句)MySql資料庫Database
- MySQL資料庫出現 Ignoring query to other databaseMySql資料庫Database
- DM7閃回與閃回查詢