Java
恢復或重置 Oracle 數據庫（無閃回功能）
我在跑步
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production好像不是企業版，所以我不能使用閃回功能
SQL> ALTER DATABASE FLASHBACK ON; ALTER DATABASE FLASHBACK ON * ERROR at line 1: ORA-00439: feature not enabled: Flashback Database SQL> SELECT flashback_on, log_mode FROM v$database; FLASHBACK_ON |LOG_MODE -------------|------------- NO |NOARCHIVELOG如您所見，我的數據庫以 NOARCHIVELOG 模式執行，並且我的文件系統是 ext3
我必須對我的系統 + 另一個供應商進行 CRUD 測試。之後我想恢復到“恢復點”的狀態（測試前）。簡而言之，我不想將測試數據保存在我的數據庫中。
這裡有討論，但這是使用閃回功能。但是我的情況呢？
有適合我的解決方案嗎？
如果沒有歸檔日誌模式，並且根據數據庫的大小和停機時間，您只有兩個選項，即卷快照或 rman 冷備份。
Below is steps by using rman cold backup.C:\sql>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 24 22:31:10 2019 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select log_mode,flashback_on from v$database; LOG_MODE FLASHBACK_ON ------------ ------------------ NOARCHIVELOG NO SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\sql> rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 24 22:10:48 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA (DBID=1582999497, not open) RMAN> shutdown immediate; using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 1068937216 bytes Fixed Size 2182592 bytes Variable Size 583008832 bytes Database Buffers 469762048 bytes Redo Buffers 13983744 bytes RMAN> backup format 'G:\ora\%I_%t.bkup' database; Starting backup at 24-JAN-19 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=C:\ORA\ORADATA\ORA\BSTUFF01.DBF input datafile file number=00002 name=C:\ORA\ORADATA\ORA\SYSAUX01.DBF channel ORA_DISK_1: starting piece 1 at 24-JAN-19 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00007 name=C:\ORA\ORADATA\ORA\SOE.DBF input datafile file number=00003 name=C:\ORA\ORADATA\ORA\UNDOTBS01.DBF input datafile file number=00001 name=C:\ORA\ORADATA\ORA\SYSTEM01.DBF channel ORA_DISK_2: starting piece 1 at 24-JAN-19 channel ORA_DISK_3: starting full datafile backup set channel ORA_DISK_3: specifying datafile(s) in backup set input datafile file number=00004 name=C:\ORA\ORADATA\ORA\USERS01.DBF input datafile file number=00005 name=C:\ORA\ORADATA\ORA\EXAMPLE01.DBF input datafile file number=00008 name=C:\ORA\ORADATA\ORA\GGATE01.DBF channel ORA_DISK_3: starting piece 1 at 24-JAN-19 channel ORA_DISK_2: finished piece 1 at 24-JAN-19 piece handle=G:\ORA\1582999497_998431563.BKUP tag=TAG20190124T220602 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:05:36 channel ORA_DISK_1: finished piece 1 at 24-JAN-19 piece handle=G:\ORA\1582999497_998431562.BKUP tag=TAG20190124T220602 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:05:37 channel ORA_DISK_3: finished piece 1 at 24-JAN-19 piece handle=G:\ORA\1582999497_998431564.BKUP tag=TAG20190124T220602 comment=NONE channel ORA_DISK_3: backup set complete, elapsed time: 00:05:36 Finished backup at 24-JAN-19 Starting Control File and SPFILE Autobackup at 24-JAN-19 piece handle=C:\ORA\PRODUCT\11.2.0\DB_1\DATABASE\CF_C-1582999497-20190124-05 comment=NONE Finished Control File and SPFILE Autobackup at 24-JAN-19 RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 120 B F A DISK 24-JAN-19 1 1 NO TAG20190124T220602 121 B F A DISK 24-JAN-19 1 1 NO TAG20190124T220602 122 B F A DISK 24-JAN-19 1 1 NO TAG20190124T220602 123 B F A DISK 24-JAN-19 1 1 NO TAG20190124T221140 C:\sql>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 24 22:31:10 2019 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> create restore point pre_testing; -- create restore point before testing Restore point created. SQL> col name format A15 SQL> col scn format 999999999 SQL> select name,scn from v$restore_point; NAME SCN --------------- ---------- PRE_TESTING 26708339 SQL> conn tom/tom Connected. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- T TABLE ---create a new table and drop exiting table SQL> create table t2 as select * from t where 1 < 1; Table created. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- T TABLE T2 TABLE SQL> drop table t purge; Table dropped. SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- T2 TABLE SQL> conn scott/tiger Connected. SQL> select * from emp; 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 14 rows selected. SQL> insert into emp values(9999,'Mick','SUPERMAN',7839,sysdate,9999,1,10); 1 row created. SQL> commit; Commit complete. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 9999 Mick SUPERMAN 7839 2019-01-24 22:20:17 9999 1 10 -- new record inserted 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 15 rows selected. SQL> select count(*) from emp; COUNT(*) ---------- 15 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options -- now we revert back to original state C:\sql>rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 24 22:21:17 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA (DBID=1582999497) RMAN> shutdown immediate; using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 1068937216 bytes Fixed Size 2182592 bytes Variable Size 583008832 bytes Database Buffers 469762048 bytes Redo Buffers 13983744 bytes RMAN> restore database until restore point pre_testing; Starting restore at 24-JAN-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=96 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=189 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=221 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to C:\ORA\ORADATA\ORA\SYSTEM01.DBF channel ORA_DISK_1: restoring datafile 00003 to C:\ORA\ORADATA\ORA\UNDOTBS01.DBF channel ORA_DISK_1: restoring datafile 00007 to C:\ORA\ORADATA\ORA\SOE.DBF channel ORA_DISK_1: reading from backup piece G:\ORA\1582999497_998431563.BKUP channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00002 to C:\ORA\ORADATA\ORA\SYSAUX01.DBF channel ORA_DISK_2: restoring datafile 00006 to C:\ORA\ORADATA\ORA\BSTUFF01.DBF channel ORA_DISK_2: reading from backup piece G:\ORA\1582999497_998431562.BKUP channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00004 to C:\ORA\ORADATA\ORA\USERS01.DBF channel ORA_DISK_3: restoring datafile 00005 to C:\ORA\ORADATA\ORA\EXAMPLE01.DBF channel ORA_DISK_3: restoring datafile 00008 to C:\ORA\ORADATA\ORA\GGATE01.DBF channel ORA_DISK_3: reading from backup piece G:\ORA\1582999497_998431564.BKUP channel ORA_DISK_2: piece handle=G:\ORA\1582999497_998431562.BKUP tag=TAG20190124T220602 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:03:06 channel ORA_DISK_1: piece handle=G:\ORA\1582999497_998431563.BKUP tag=TAG20190124T220602 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:03:16 channel ORA_DISK_3: piece handle=G:\ORA\1582999497_998431564.BKUP tag=TAG20190124T220602 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:03:16 Finished restore at 24-JAN-19 RMAN> recover database until restore point pre_testing; Starting recover at 24-JAN-19 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 starting media recovery archived log for thread 1 with sequence 2 is already on disk as file C:\ORA\ORADATA\ORA\REDO02.LOG archived log file name=C:\ORA\ORADATA\ORA\REDO02.LOG thread=1 sequence=2 media recovery complete, elapsed time: 00:00:04 Finished recover at 24-JAN-19 RMAN> alter database open resetlogs; database opened RMAN> exit Recovery Manager complete. C:\sql>sqlplus scott/tiger SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 24 22:27:43 2019 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> select * from emp; 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 14 rows selected. SQL> conn tom/tom SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- T TABLE SQL> drop restore point pre_testing; Restore point dropped.編輯：您可以跳過創建還原點，只需使用 rman 還原和恢復數據庫