Oracle12.1閃回功能
shutdown immediate;
startup mount;
archive log list;
alter database archivelog;
alter system set db_flashback_retention_target=600; ---20小時
alter system set db_recovery_file_dest_size=50G;
alter system set undo_retention=36000; -------10小時
alter database flashback on;
alter database open;
select FLASHBACK_ON from V$DATABASE;
create restore point JTITSM_2017005231900;
select current_scn from v$database;
rman target /
list restore point all;'
恢復:
shutdown immediate;
startup mount;
flashback database to restore point JTITSM_2017005231900; -----注意不加引號
或者:
falshback database to scn **********;
alter database open resetlogs;
注意:系統變更完成後記得關閉閃回功能。
shutdown immediate
startup mount;
alter database flashback off;
alter database open;
show pdbs;
實測:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL>
SQL>
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FLASHBAKPDB MOUNTED
SQL> alter pluggable database FLASHBAKPDB open;
Pluggable database altered.
SQL> alter pluggable database FLASHBAKPDB save state;
Pluggable database altered.
SQL>
SQL>
SQL> ^H
SP2-0042: unknown command " - rest of line ignored.
SQL>
SQL> create table flash_before as select * from dba_data_files;
Table created.
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> create table flash_before as select * from dba_data_files;
Table created.
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> create restore point flash_point;
Restore point created.
SQL> select FLASHBACK_ON from V$DATABASE.FLASHBACK_ON;
select FLASHBACK_ON from V$DATABASE.FLASHBACK_ON
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select FLASHBACK_ON from V$DATABASE;
FLASHBACK_ON
------------------
YES
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1745361
SQL> exit
RMAN> list restore point all;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
1745334 2017/05/23 19:09:38 FLASH_POINT
RMAN> exit
SQL> create table flash_after as select * from dba_users;
Table created.
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> create table flash_after as select * from dba_users;
Table created.
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
startup mount;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
ORACLE instance started.
Total System Global Area 3674210304 bytes
Fixed Size 2930848 bytes
Variable Size 838862688 bytes
Database Buffers 2818572288 bytes
Redo Buffers 13844480 bytes
Database mounted.
SQL> SQL>
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL> select count(*) from flash_after;
COUNT(*)
----------
35
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> select count(*) from flash_after;
COUNT(*)
----------
36
SQL> show con_name
CON_NAME
------------------------------
FLASHBAKPDB
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
startup mount;
ORACLE instance shut down.
SQL>
ORACLE instance started.
Total System Global Area 3674210304 bytes
Fixed Size 2930848 bytes
Variable Size 838862688 bytes
Database Buffers 2818572288 bytes
Redo Buffers 13844480 bytes
Database mounted.
SQL> SQL>
SQL>
SQL>
SQL>
SQL> flashback database to restore point 'FLASH_POINT';
flashback database to restore point 'FLASH_POINT'
*
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> flashback database to restore point FLASH_POINT;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FLASHBAKPDB READ WRITE NO
SQL> select count(*) from flash_after;
select count(*) from flash_after
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from flash_before;
COUNT(*)
----------
4
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> select count(*) from flash_after;
select count(*) from flash_after
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from flash_before;
COUNT(*)
----------
3
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL>
SQL>
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FLASHBAKPDB MOUNTED
SQL> alter pluggable database FLASHBAKPDB open;
Pluggable database altered.
SQL> alter pluggable database FLASHBAKPDB save state;
Pluggable database altered.
SQL>
SQL>
SQL> ^H
SP2-0042: unknown command " - rest of line ignored.
SQL>
SQL> create table flash_before as select * from dba_data_files;
Table created.
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> create table flash_before as select * from dba_data_files;
Table created.
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> create restore point flash_point;
Restore point created.
SQL> select FLASHBACK_ON from V$DATABASE.FLASHBACK_ON;
select FLASHBACK_ON from V$DATABASE.FLASHBACK_ON
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select FLASHBACK_ON from V$DATABASE;
FLASHBACK_ON
------------------
YES
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1745361
SQL> exit
RMAN> list restore point all;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
1745334 2017/05/23 19:09:38 FLASH_POINT
RMAN> exit
SQL> create table flash_after as select * from dba_users;
Table created.
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> create table flash_after as select * from dba_users;
Table created.
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
startup mount;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
ORACLE instance started.
Total System Global Area 3674210304 bytes
Fixed Size 2930848 bytes
Variable Size 838862688 bytes
Database Buffers 2818572288 bytes
Redo Buffers 13844480 bytes
Database mounted.
SQL> SQL>
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL> select count(*) from flash_after;
COUNT(*)
----------
35
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> select count(*) from flash_after;
COUNT(*)
----------
36
SQL> show con_name
CON_NAME
------------------------------
FLASHBAKPDB
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
startup mount;
ORACLE instance shut down.
SQL>
ORACLE instance started.
Total System Global Area 3674210304 bytes
Fixed Size 2930848 bytes
Variable Size 838862688 bytes
Database Buffers 2818572288 bytes
Redo Buffers 13844480 bytes
Database mounted.
SQL> SQL>
SQL>
SQL>
SQL>
SQL> flashback database to restore point 'FLASH_POINT';
flashback database to restore point 'FLASH_POINT'
*
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> flashback database to restore point FLASH_POINT;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FLASHBAKPDB READ WRITE NO
SQL> select count(*) from flash_after;
select count(*) from flash_after
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from flash_before;
COUNT(*)
----------
4
SQL> alter session set container=FLASHBAKPDB;
Session altered.
SQL> select count(*) from flash_after;
select count(*) from flash_after
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from flash_before;
COUNT(*)
----------
3
SQL>
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28612416/viewspace-2139755/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 閃回技術 概覽 資料庫閃回功能Oracle資料庫
- 開啟oracle的flashback閃回功能Oracle
- Flashback_oracle閃回功能的使用Oracle
- Orcale利用閃回功能恢復資料
- 開啟資料庫的閃回功能:資料庫
- Flashback Drop閃回刪除功能實踐
- 啟用Flashback Database閃回資料庫功能(閃回區滿解決辦法 )Database資料庫
- 閃回表、閃回查詢
- 閃回(關於閃回查詢)
- 閃回刪除、閃回查詢
- 閃回技術二:閃回表
- Oracle 11g開啟閃回功能FlashbackOracle
- 【Flashback】Flashback Drop閃回刪除功能實踐
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- 閃回(關於閃回資料庫)資料庫
- 基本閃回查詢和閃回表
- 閃回技術一:閃回查詢
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- 【Flashback】啟用Flashback Database閃回資料庫功能Database資料庫
- 【Flashback】Flashback Database閃回資料庫功能實踐Database資料庫
- DM7閃回與閃回查詢
- 閃回查詢之閃回版本查詢
- 閃回查詢之閃回表查詢
- (f)--閃回恢復區---實踐2---閃回表(閃回DML部分資料會用到閃回查詢)
- oracle閃回Oracle
- oracle 閃回Oracle
- Oracle閃回技術之閃回資料庫Oracle資料庫
- Oracle資料庫的閃回查詢功能簡介Oracle資料庫
- [Flashback]開啟資料庫閃回資料庫功能資料庫
- [閃回特性之閃回版本查詢]Flashback Version Query
- oracle 閃回 flashbackOracle
- oracle閃回特性Oracle
- 閃回查詢
- Oracle閃回功能恢復偶然丟失的資料(轉)Oracle
- Oracle閃回技術 概覽 應用程式開發功能Oracle
- 用Oracle閃回功能恢復偶然丟失的資料Oracle
- 開閃回及閃迴歸檔
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫