-- 检查环境
-- 检查归档模式是否开启
-- 如果未开启,启动数据库到 mount 状态下, 执行 alter database archivelog; 来开启数据库归档
-- 检查闪回目录和容量大小的设定
-- 如果未开启,运行 alter system set db_recovery_file_dest_size = 值; 和 alter system set db_recovery_file_dest = '目录名称';
-- 检查闪回数据库
-- 如果 FLASHBACK_ON 参数不是 YES 状态, 需要在 mount 状态下执行 alter database flashback on;
-- 如果 FORCE_logging 参数不是 YES 状态, 直接执行 alter database force logging;
-- 创建测试表
-- 查询表,并且,删除一定的数据,做测试准备
-- 启动数据库到 mount 状态
-- 以时间戳的方式闪回数据库
-- 启动数据库到 read only 模式,验证数据库恢复情况
-- 查询 scn 和时间戳的方法
-- scn恢复的命令
-- 时间戳恢复命令
-- 检查归档模式是否开启
-- 如果未开启,启动数据库到 mount 状态下, 执行 alter database archivelog; 来开启数据库归档
点击(此处)折叠或打开
-
SYS@RAC1> archive log list
-
Database log mode Archive Mode
-
Automatic archival Enabled
-
Archive destination USE_DB_RECOVERY_FILE_DEST
-
Oldest online log sequence 7
-
Next log sequence to archive 9
- Current log sequence 9
-- 如果未开启,运行 alter system set db_recovery_file_dest_size = 值; 和 alter system set db_recovery_file_dest = '目录名称';
点击(此处)折叠或打开
-
SYS@RAC1> show parameter db_recovery_file
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
db_recovery_file_dest string /u01/app/oracle/flash_recovery
-
_area
- db_recovery_file_dest_size big integer 4G
-- 如果 FLASHBACK_ON 参数不是 YES 状态, 需要在 mount 状态下执行 alter database flashback on;
-- 如果 FORCE_logging 参数不是 YES 状态, 直接执行 alter database force logging;
点击(此处)折叠或打开
-
SYS@RAC1> select flashback_on, force_logging from v$database;
-
-
FLASHBACK_ON FOR
-
------------------ ---
- YES YES
点击(此处)折叠或打开
-
SYS@RAC1> create table t as select * from scott.emp;
-
- Table created.
点击(此处)折叠或打开
-
SYS@RAC1> select * from t;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
-
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
-
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
-
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
-
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
-
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
-
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
-
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
-
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
-
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
-
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
-
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
-
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
-
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
-
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
-
SYS@RAC1> select sysdate from dual;
-
-
SYSDATE
-
-------------------
-
2016-08-10 11:08:05
-
-
SYS@RAC1> delete t where deptno = 10;
-
-
3 rows deleted.
-
-
SYS@RAC1> select * from t;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
-
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
-
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
-
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
-
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
-
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
-
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
-
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
-
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
-
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
-
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
- 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
点击(此处)折叠或打开
-
SYS@RAC1> shutdown immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SYS@RAC1> startup mount exclusive;
-
ORACLE instance started.
-
-
Total System Global Area 830930944 bytes
-
Fixed Size 2257800 bytes
-
Variable Size 524291192 bytes
-
Database Buffers 301989888 bytes
-
Redo Buffers 2392064 bytes
- Database mounted.
点击(此处)折叠或打开
-
SYS@RAC1> flashback database to timestamp(to_date('2016-08-10 11:07:56', 'yyyy-mm-dd hh24:mi:ss'));
-
- Flashback complete.
点击(此处)折叠或打开
-
SYS@RAC1> alter database open read only;
-
-
Database altered.
-
-
SYS@RAC1> select * from t;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
-
7369 SMITH CLERK 7902 17-DEC-80 800 20
-
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
-
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
-
7566 JONES MANAGER 7839 02-APR-81 2975 20
-
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
-
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
-
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
-
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
-
7839 KING PRESIDENT 17-NOV-81 5000 10
-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
-
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
-
7900 JAMES CLERK 7698 03-DEC-81 950 30
-
7902 FORD ANALYST 7566 03-DEC-81 3000 20
-
7934 MILLER CLERK 7782 23-JAN-82 1300 10
-
- 14 rows selected.
-- 查询 scn 和时间戳的方法
点击(此处)折叠或打开
-
SYS@RAC1> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-
-
Session altered.
-
-
SYS@RAC1> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
-
-
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-
-------------------- -------------------
- 1072909 2016-08-10 09:41:39
点击(此处)折叠或打开
- FLASHBACK DATABASE TO SCN_NUMBER;
点击(此处)折叠或打开
-
FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('DATE','格式'));
- FLASHBACK DATABASE TO TIMESTAMP(系统时间-回滚时间)/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31369239/viewspace-2123266/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
