User Management Complete Recovery in ARCHIVELOG Mode
User Management Complete Recovery in ARCHIVELOG Mode
准备工作,创建一个app1表空间。在archivelog mode下进行一冷备和对app1_01.dbf空间进行热备
[sql]- [Oracle@oracle11gR2 ~]$ sqlplus /nolog
- idle> conn /as sysdba
- Connected.
- sys@DENVER> archive log list
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /u01/admin/denver/archdest
- Oldest online log sequence 1
- Next log sequence to archive 2
- Current log sequence 2
- sys@DENVER> CREATE TABLESPACE app1 DATAFILE '/u01/oradata/md/app1_01.dbf' SIZE 10M extent management local uniform segment space management auto;
- Tablespace created.
- sys@DENVER> CREATE USER user1 IDENTIFIED BY user1 DEFAULT TABLESPACE app1;
- User created.
- sys@DENVER> GRANT connect, resource TO user1;
- Grant succeeded.
- sys@DENVER> conn user1/user1
- Connected.
- user1@DENVER> CREATE TABLE t(id int, name varchar2(10));
- Table created.
- user1@DENVER> INSERT INTO t VALUES(0,'bbk');
- 1 row created.
- user1@DENVER> COMMIT;
- Commit complete.
- user1@DENVER> SELECT * FROM t;
- ID NAME
- ---------- ----------
- 0 bbk
- user1@DENVER> conn /as sysdba
- Connected.
- sys@DENVER> @coldbackup
- sys@DENVER> conn user1/user1
- Connected.
- user1@DENVER> set heading on
- user1@DENVER> select * from t;
- ID NAME
- ---------- ----------
- 0 bbk
[sql]
- sys@DENVER> conn user1/user1
- Connected.
- user1@DENVER> SELECT * FROM t;
- ID NAME
- ---------- ----------
- 0 bbk
- user1@DENVER> set feedback on
- user1@DENVER> INSERT INTO t VALUES(1,'bbk');
- 1 row created.
- user1@DENVER> COMMIT;
- Commit complete.
- user1@DENVER> SELECT * FROM t;
- ID NAME
- ---------- ----------
- 0 bbk
- 1 bbk
- 2 rows selected.
- user1@DENVER> conn /as sysdba
- Connected.
- sys@DENVER> ALTER TABLESPACE app1 BEGIN BACKUP;
- Tablespace altered.
- sys@DENVER> !cp /u01/oradata/md/app1_01.dbf /tmp/backup/hot
- sys@DENVER> !ls -l /tmp/backup/hot
- total 10268
- -rw-r----- 1 oracle oinstall 10493952 Jan 6 09:09 app1_01.dbf
- sys@DENVER> ALTER TABLESPACE app1 END BACKUP;
- Tablespace altered.
第一种情况:Complete Recovery of a Closed Database
Closed database reocvery is used for :
1. System tablespace datafiles
2. Rollback segment datafiles
3. Whole database
Steps: shut down the instance -----> Restore dtafile -----> Apply archvied log and redo log files -------> Open the databse
以下实验模拟了数据库的损坏:[sql]
- sys@DENVER> conn /as sysdba
- Connected.
- sys@DENVER> conn user1/user1
- Connected.
- user1@DENVER> SELECT * FROM t;
- ID NAME
- ---------- ----------
- 0 bbk
- 1 bbk
- 2 rows selected.
- user1@DENVER> INSERT INTO t VALUES(2,'bbk');
- 1 row created.
- user1@DENVER> COMMIT;
- Commit complete.
- user1@DENVER> SELECT * FROM t;
- ID NAME
- ---------- ----------
- 0 bbk
- 1 bbk
- 2 bbk
- 3 rows selected.
- user1@DENVER> conn /as sysdba
- Connected.
- sys@DENVER> !ls -l /u01/admin/denver/archdest
- total 10752
- -rw-r----- 1 oracle oinstall 1778688 Jan 6 07:43 1_1_771835295.arc
- -rw-r----- 1 oracle oinstall 1638400 Jan 6 09:11 1_2_771835295.arc
- -rw-r----- 1 oracle oinstall 1536 Jan 6 09:11 1_3_771835295.arc
- -rw-r----- 1 oracle oinstall 1536 Jan 6 09:11 1_4_771835295.arc
- sys@DENVER> ALTER SYSTEM SWITCH LOGFILE;
- System altered.
- sys@DENVER> ALTER SYSTEM SWITCH LOGFILE;
- System altered.
- sys@DENVER> ALTER SYSTEM SWITCH LOGFILE;
- System altered.
- sys@DENVER> !ls -l /u01/admin/denver/archdest
- total 11340
- -rw-r----- 1 oracle oinstall 1778688 Jan 6 07:43 1_1_771835295.arc
- -rw-r----- 1 oracle oinstall 1638400 Jan 6 09:11 1_2_771835295.arc
- -rw-r----- 1 oracle oinstall 1536 Jan 6 09:11 1_3_771835295.arc
- -rw-r----- 1 oracle oinstall 1536 Jan 6 09:11 1_4_771835295.arc
- -rw-r----- 1 oracle oinstall 571904 Jan 6 09:13 1_5_771835295.arc
- -rw-r----- 1 oracle oinstall 1024 Jan 6 09:13 1_6_771835295.arc
- -rw-r----- 1 oracle oinstall 5632 Jan 6 09:13 1_7_771835295.arc
- sys@DENVER> !rm -f /u01/oradata/md/*.dbf
- > !ls -l /u01/oradata/md/
- total 163444
- -rw-r----- 1 oracle oinstall 9846784 Jan 6 09:22 control01.ctl
- drwx------ 2 oracle oinstall 16384 Jan 6 05:18 lost+found
- -rw-r----- 1 oracle oinstall 52429312 Jan 6 09:13 redo01.log
- -rw-r----- 1 oracle oinstall 52429312 Jan 6 09:22 redo02.log
- -rw-r----- 1 oracle oinstall 52429312 Jan 6 09:13 redo03.log
- sys@DENVER> shutdown immediate
- ORA-01116: error in opening database file 2
- ORA-01110: data file 2: '/u01/oradata/md/sysaux01.dbf'
- ORA-27041: unable to open file
- Linux Error: 2: No such file or directory
- Additional information: 3
- sys@DENVER> shutdown abort
- ORACLE instance shut down.
- sys@DENVER> !cp /tmp/backup/cold/*.dbf /u01/oradata/md
- sys@DENVER> !ls -l /u01/oradata/md
- total 2250172
- -rw-r----- 1 oracle oinstall 10493952 Jan 6 09:23 app1_01.dbf
- -rw-r----- 1 oracle oinstall 9846784 Jan 6 09:22 control01.ctl
- -rw-r----- 1 oracle oinstall 104865792 Jan 6 09:23 example01.dbf
- drwx------ 2 oracle oinstall 16384 Jan 6 05:18 lost+found
- -rw-r----- 1 oracle oinstall 536879104 Jan 6 09:24 perfstat_01.dbf
- -rw-r----- 1 oracle oinstall 52429312 Jan 6 09:13 redo01.log
- -rw-r----- 1 oracle oinstall 52429312 Jan 6 09:22 redo02.log
- -rw-r----- 1 oracle oinstall 52429312 Jan 6 09:13 redo03.log
- -rw-r----- 1 oracle oinstall 566239232 Jan 6 09:25 sysaux01.dbf
- -rw-r----- 1 oracle oinstall 765468672 Jan 6 09:25 system01.dbf
- -rw-r----- 1 oracle oinstall 30416896 Jan 6 09:25 temp01.dbf
- -rw-r----- 1 oracle oinstall 20979712 Jan 6 09:25 tools01.dbf
- -rw-r----- 1 oracle oinstall 89137152 Jan 6 09:26 undotbs01.dbf
- -rw-r----- 1 oracle oinstall 10493952 Jan 6 09:26 user02.dbf
- -rw-r----- 1 oracle oinstall 11804672 Jan 6 09:26 users01.dbf
- sys@DENVER> startup
- ORACLE instance started.
- Total System Global Area 422670336 bytes
- Fixed Size 1336960 bytes
- Variable Size 314575232 bytes
- Database Buffers 100663296 bytes
- Redo Buffers 6094848 bytes
- Database mounted.
- ORA-01113: file 1 needs media recovery
- ORA-01110: data file 1: '/u01/oradata/md/system01.dbf'
- sys@DENVER> col error format a18
- sys@DENVER> select * from v$recover_file;
- FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
- ---------- ------- ------- ------------------ ---------- ---------
- 1 ONLINE ONLINE 1303743 06-JAN-12
- 2 ONLINE ONLINE 1303743 06-JAN-12
- 3 ONLINE ONLINE 1303743 06-JAN-12
- 4 ONLINE ONLINE 1303743 06-JAN-12
- 5 ONLINE ONLINE 1303743 06-JAN-12
- 6 ONLINE ONLINE 1303743 06-JAN-12
- 7 ONLINE ONLINE 1303743 06-JAN-12
- 8 ONLINE ONLINE 1303743 06-JAN-12
- 9 ONLINE ONLINE 1303743 06-JAN-12
- 9 rows selected.
- sys@DENVER> set autorecovery off
- sys@DENVER> col name format a40
- sys@DENVER> select file#, ts#, name from v$datafile;
- FILE# TS# NAME
- ---------- ---------- ----------------------------------------
- 1 0 /u01/oradata/md/system01.dbf
- 2 1 /u01/oradata/md/sysaux01.dbf
- 3 2 /u01/oradata/md/undotbs01.dbf
- 4 4 /u01/oradata/md/users01.dbf
- 5 6 /u01/oradata/md/example01.dbf
- 6 7 /u01/oradata/md/perfstat_01.dbf
- 7 4 /u01/oradata/md/user02.dbf
- 8 8 /u01/oradata/md/tools01.dbf
- 9 9 /u01/oradata/md/app1_01.dbf
- 9 rows selected.
- sys@DENVER> recover datafile 1;
- ORA-00279: change 1303743 generated at 01/06/2012 08:58:36 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_2_771835295.arc
- ORA-00280: change 1303743 for thread 1 is in sequence #2
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 1304127 generated at 01/06/2012 09:11:19 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_3_771835295.arc
- ORA-00280: change 1304127 for thread 1 is in sequence #3
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 1304133 generated at 01/06/2012 09:11:22 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_4_771835295.arc
- ORA-00280: change 1304133 for thread 1 is in sequence #4
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 1304137 generated at 01/06/2012 09:11:24 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_5_771835295.arc
- ORA-00280: change 1304137 for thread 1 is in sequence #5
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- Log applied.
- Media recovery complete.
- sys@DENVER> select * from v$recover_file;
- FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
- ---------- ------- ------- ------------------ ---------- ---------
- 2 ONLINE ONLINE 1303743 06-JAN-12
- 3 ONLINE ONLINE 1303743 06-JAN-12
- 4 ONLINE ONLINE 1303743 06-JAN-12
- 5 ONLINE ONLINE 1303743 06-JAN-12
- 6 ONLINE ONLINE 1303743 06-JAN-12
- 7 ONLINE ONLINE 1303743 06-JAN-12
- 8 ONLINE ONLINE 1303743 06-JAN-12
- 9 ONLINE ONLINE 1303743 06-JAN-12
- 8 rows selected.
- sys@DENVER> recover datafile '/u01/oradata/md/sysaux01.dbf';
- ORA-00279: change 1303743 generated at 01/06/2012 08:58:36 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_2_771835295.arc
- ORA-00280: change 1303743 for thread 1 is in sequence #2
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 1304127 generated at 01/06/2012 09:11:19 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_3_771835295.arc
- ORA-00280: change 1304127 for thread 1 is in sequence #3
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 1304133 generated at 01/06/2012 09:11:22 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_4_771835295.arc
- ORA-00280: change 1304133 for thread 1 is in sequence #4
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00279: change 1304137 generated at 01/06/2012 09:11:24 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_5_771835295.arc
- ORA-00280: change 1304137 for thread 1 is in sequence #5
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- Log applied.
- Media recovery complete.
- sys@DENVER> set autorecover on
- SP2-0735: unknown SET option beginning "autorecove..."
- sys@DENVER> set autorecovery on
- sys@DENVER> recover database;
- ORA-00279: change 1303743 generated at 01/06/2012 08:58:36 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_2_771835295.arc
- ORA-00280: change 1303743 for thread 1 is in sequence #2
- ORA-00279: change 1304127 generated at 01/06/2012 09:11:19 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_3_771835295.arc
- ORA-00280: change 1304127 for thread 1 is in sequence #3
- ORA-00279: change 1304133 generated at 01/06/2012 09:11:22 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_4_771835295.arc
- ORA-00280: change 1304133 for thread 1 is in sequence #4
- ORA-00279: change 1304137 generated at 01/06/2012 09:11:24 needed for thread 1
- ORA-00289: suggestion : /u01/admin/denver/archdest/1_5_771835295.arc
- ORA-00280: change 1304137 for thread 1 is in sequence #5
- Log applied.
- Media recovery complete.
- sys@DENVER> select * from v$recover_file;
- no rows selected
- sys@DENVER> alter database open;
- Database altered.
- sys@DENVER> select * from user1.t;
- ID NAME
- ---------- ----------------------------------------
- 0 bbk
- 1 bbk
- 2 bbk
- 3 rows selected.
- sys@DENVER> clear scr
|
评论暂时关闭