12c闪回 oracle_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>