oracle用户管理的完全恢复1：在NOARCHIVELOG 模式下执行恢复
在NOARCHIVELOG 模式下，必须还原以下数据库文件：
a.所有数据文件（一个损坏，也要恢复所有文件，保持状态一致性）
b.控制文件
也可以还原以下文件：
a.重做日志文件
b.口令文件
c.参数文件
场景描述：非归档模式，将备份数据还原至其他磁盘（本实验以目录代替）
1.查看环境：
1 -bash-3.00$ sqlplus /nolog 2 3 SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 21 16:26:40 2013 4 5 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 6 7 SQL> conn /as sysdba 8 SQL> 9 SQL> archive log list; 10 Database log mode Archive Mode 11 Automatic archival Enabled 12 Archive destination USE_DB_RECOVERY_FILE_DEST 13 Oldest online log sequence 1 14 Next log sequence to archive 2 15 Current log sequence 16 SQL>
2.将归档模式转换成非归档
1 SQL> shutdown immediate 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL> startup mount 6 ORACLE instance started. 7 8 Total System Global Area 289406976 bytes 9 Fixed Size 1279820 bytes 10 Variable Size 92276916 bytes 11 Database Buffers 192937984 bytes 12 Redo Buffers 2912256 bytes 13 Database mounted. 14 SQL> alter database noarchivelog ; 15 16 Database altered. 17 18 SQL> archive log list; 19 Database log mode No Archive Mode 20 Automatic archival Disabled 21 Archive destination USE_DB_RECOVERY_FILE_DEST 22 Oldest online log sequence 1 23 Current log sequence 2 24 SQL> alter database open; 25 26 Database altered. 27 28 SQL>
3.非归档模式全备份（冷备）
1 SQL> shutdown immediate 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL> ！ 6 bash-3.00$ pwd 7 /u01/backup 8 #备份数据文件，控制文件，日志文件 9 bash-3.00$ cp /u01/oradata/sun/*. . 10 11 bash-3.00$ 12 #备份参数文件，口令文件 13 bash-3.00$ cp /u01/oracle/dbs/spfilesun.ora . 14 bash-3.00$ cp /u01/oracle/dbs/orapwsun .
4.创建磁盘（以目录代替）
1 bash-3.00$ pwd 2 /u01/oradata 3 bash-3.00$ ls 4 sun 5 bash-3.00$ mkdir sunbak 6 bash-3.00$
5.模拟磁盘毁坏
1 bash-3.00$ pwd 2 /u01/oradata/sun 3 bash-3.00$ ls 4 control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf 5 control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf 6 bash-3.00$ rm -f *.dbf 7 bash-3.00$ rm -f *.ctl 8 bash-3.00$ rm -f *.log 9 bash-3.00$
6.开始恢复
6.1 将备份文件考到新磁盘(sunbak目录下)
1 bash-3.00$ pwd 2 /u01/backup 3 bash-3.00$ 4 bash-3.00$ ls 5 control01.ctl control03.ctl orapwsun redo02.log spfilesun.ora system01.dbf undotbs01.dbf 6 control02.ctl example01.dbf redo01.log redo03.log sysaux01.dbf temp01.dbf users01.dbf 7 bash-3.00$ cp * /u01/oradata/sunbak 8 bash-3.00$ 9 #文件spfilesun.ora与orapwsun没被损坏，可删掉 10 bash-3.00$rm -f /u01/oradata/sunbak/spfilesun.ora 11 bash-3.00$rm -f /u01/oradata/sunbak/orapwsun 12 bash-3.00$
6.2 因文件目录环境改变，必须修改参数文件
6.2.1 查看参数文件
1 bash-3.00$ pwd 2 /u01/oracle/dbs 3 bash-3.00$ strings spfilesun.ora |more 4 sun.__db_cache_size=192937984 5 sun.__java_pool_size=4194304 6 sun.__large_pool_size=4194304 7 sun.__shared_pool_size=83886080 8 sun.__streams_pool_size=0 9 *.audit_file_dest='/u01/admin/sun/adump' 10 *.background_dump_dest='/u01/admin/sun/bdump' 11 *.compatible='10.2.0.2.0' 12 #参数文件 13 *.control_files='/u01/oradata/sun/control01.ctl','/u01/oradata/sun/control02.ctl','/u01/oradata/sun/control03.ctl' 14 *.core_dump_dest='/u01/admin/sun/cdump' 15 *.db_block_size=8192 16 *.db_domain='' 17 *.db_file_multiblock_read_count=16 18 *.db_name='sun' 19 *.db_recovery_file_dest='/u01/flash_recovery_area' 20 *.db_recovery_file_dest_size=2147483648 21 *.dispatchers='(PROTOCOL=TCP) (SERVICE=sunXDB)' 22 *.job_queue_processes=10 23 *.log_archive_format='%t_%s_%r.dbf' 24 *.open_cursors=300 25 *.pga_aggregate_target=96468992 26 *.processes=150 27 *.remote_login_passwordfile='EXCLUSIVE' 28 *.sga_target=289406976 29 *.undo_management='AUTO' 30 *.undo_tablespace='UNDOTBS1' 31 *.user_dump_dest='/u01/admin/sun/udump' 32 bash-3.00$
6.2.2 修改参数文件
1 bash-3.00$ sqlplus /nolog 2 3 SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 21 17:44:54 2013 4 5 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 6 7 SQL> conn /as sysdba 8 Connected to an idle instance. 9 #创建pfile文件，文件名为initsun.ora 10 SQL> create pfile from spfile; 11 12 File created. 13 14 SQL> ！ 15 #开始修改控制文件目录 16 bash-3.00$ vi initsun.ora 17 "initsun.ora" 27 lines, 926 characters 18 sun.__db_cache_size=192937984 19 sun.__java_pool_size=4194304 20 sun.__large_pool_size=4194304 21 sun.__shared_pool_size=83886080 22 sun.__streams_pool_size=0 23 *.audit_file_dest='/u01/admin/sun/adump' 24 *.background_dump_dest='/u01/admin/sun/bdump' 25 *.compatible='10.2.0.2.0' 26 #修改处 27 #*.control_files='/u01/oradata/sun/control01.ctl','/u01/oradata/sun/control02.ctl','/u01/oradata/sun/control03.ctl' 28 *.control_files='/u01/oradata/sunbak/control01.ctl','/u01/oradata/sunbak/control02.ctl','/u01/oradata/sunbak/control03.ctl' 29 *.core_dump_dest='/u01/admin/sun/cdump' 30 *.db_block_size=8192 31 *.db_domain='' 32 *.db_file_multiblock_read_count=16 33 *.db_name='sun' 34 *.db_recovery_file_dest='/u01/flash_recovery_area' 35 *.db_recovery_file_dest_size=2147483648 36 *.dispatchers='(PROTOCOL=TCP) (SERVICE=sunXDB)' 37 *.job_queue_processes=10 38 *.log_archive_format='%t_%s_%r.dbf' 39 *.open_cursors=300 40 *.pga_aggregate_target=96468992 41 *.processes=150 42 *.remote_login_passwordfile='EXCLUSIVE' 43 *.sga_target=289406976 44 *.undo_management='AUTO' 45 *.undo_tablespace='UNDOTBS1' 46 *.user_dump_dest='/u01/admin/sun/udump' 47 48 bash-3.00$
6.2.3 通过pfile文件创建新的spfile文件
1 SQL> create spfile from pfile; 2 3 File created. 4 5 SQL> ！ 6 #查看修改后的spfile文件 7 bash-3.00$ strings spfilesun.ora|more 8 sun.__db_cache_size=192937984 9 sun.__java_pool_size=4194304 10 sun.__large_pool_size=4194304 11 sun.__shared_pool_size=83886080 12 sun.__streams_pool_size=0 13 *.audit_file_dest='/u01/admin/sun/adump' 14 *.background_dump_dest='/u01/admin/sun/bdump' 15 *.compatible='10.2.0.2.0' 16 *.control_files='/u01/oradata/sunbak/control01.ctl','/u01/oradata/sunbak/control02.ctl','/u01/oradata/sunbak/control03.ctl' 17 *.core_dump_dest='/u01/admin/sun/cdump' 18 *.db_block_size=8192 19 *.db_domain='' 20 *.db_file_multiblock_read_count=16 21 *.db_name='sun' 22 *.db_recovery_file_dest='/u01/flash_recovery_area' 23 *.db_recovery_file_dest_size=2147483648 24 *.dispatchers='(PROTOCOL=TCP) (SERVICE=sunXDB)' 25 *.job_queue_processes=10 26 *.log_archive_format='%t_%s_%r.dbf' 27 *.open_cursors=300 28 *.pga_aggregate_target=96468992 29 *.processes=150 30 *.remote_login_passwordfile='EXCLUSIVE' 31 *.sga_target=289406976 32 *.undo_management='AUTO' 33 *.undo_tablespace='UNDOTBS1' 34 *.user_dump_dest='/u01/admin/sun/udump' 35 bash-3.00$
6.3 修改控制文件
6.3.1 查看控制文件
1 bash-3.00$ cd /u01/oradata/sunbak 2 bash-3.00$ strings control01.ctl | more 3 }|{z 4 rSUN 5 /SUN 6 oki# 7 /SUN 8 oki# 9 yki# 10 yki# 11 #参数文件中的目录休要修改 12 /u01/oradata/sun/redo03.log 13 /u01/oradata/sun/redo02.log 14 /u01/oradata/sun/redo01.log 15 /u01/oradata/sun/users01.dbf 16 /u01/oradata/sun/sysaux01.dbf 17 /u01/oradata/sun/undotbs01.dbf 18 /u01/oradata/sun/system01.dbf 19 /u01/oradata/sun/temp01.dbf 20 /u01/oradata/sun/example01.dbf 21 /u01/oradata/sun/redo03.log 22 /u01/oradata/sun/redo02.log 23 /u01/oradata/sun/redo01.log 24 /u01/oradata/sun/users01.dbf 25 /u01/oradata/sun/sysaux01.dbf 26 /u01/oradata/sun/undotbs01.dbf 27 /u01/oradata/sun/system01.dbf 28 /u01/oradata/sun/temp01.dbf 29 /u01/oradata/sun/example01.dbf 30 SYSTEM 31 UNDOTBS1 32 SYSAUX 33 USERS 34 TEMP 35 PQf/ 36 UNNAMED_INSTANCE_2 37 --More--
6.3.2 修改控制文件
6.3.2.1 此时只能将数据库开启到mount状态。
1 bash-3.00$ sqlplus /nolog 2 3 SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 21 18:05:56 2013 4 5 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 6 7 SQL> conn /as sysdba 8 Connected to an idle instance. 9 SQL> startup mount 10 ORACLE instance started. 11 12 Total System Global Area 289406976 bytes 13 Fixed Size 1279820 bytes 14 Variable Size 92276916 bytes 15 Database Buffers 192937984 bytes 16 Redo Buffers 2912256 bytes 17 Database mounted. 18 SQL>
6.3.2.2 修改数据文件，临时文件，日志文件目录（此处最好用脚本完成更改）
1 #数据文件 2 SQL> desc v$datafile 3 Name Null? Type 4 ----------------------------------------- -------- ---------------------------- 5 FILE# NUMBER 6 CREATION_CHANGE# NUMBER 7 CREATION_TIME DATE 8 TS# NUMBER 9 RFILE# NUMBER 10 STATUS VARCHAR2(7) 11 ENABLED VARCHAR2(10) 12 CHECKPOINT_CHANGE# NUMBER 13 CHECKPOINT_TIME DATE 14 UNRECOVERABLE_CHANGE# NUMBER 15 UNRECOVERABLE_TIME DATE 16 LAST_CHANGE# NUMBER 17 LAST_TIME DATE 18 OFFLINE_CHANGE# NUMBER 19 ONLINE_CHANGE# NUMBER 20 ONLINE_TIME DATE 21 BYTES NUMBER 22 BLOCKS NUMBER 23 CREATE_BYTES NUMBER 24 BLOCK_SIZE NUMBER 25 NAME VARCHAR2(513) 26 PLUGGED_IN NUMBER 27 BLOCK1_OFFSET NUMBER 28 AUX_NAME VARCHAR2(513) 29 FIRST_NONLOGGED_SCN NUMBER 30 FIRST_NONLOGGED_TIME DATE 31 32 SQL> col name format a40 33 SQL> select file#,name from v$datafile; 34 35 FILE# NAME 36 ---------- ---------------------------------------- 37 1 /u01/oradata/sun/system01.dbf 38 2 /u01/oradata/sun/undotbs01.dbf 39 3 /u01/oradata/sun/sysaux01.dbf 40 4 /u01/oradata/sun/users01.dbf 41 5 /u01/oradata/sun/example01.dbf 42 43 SQL> alter database rename file '/u01/oradata/sun/system01.dbf' to '/u01/oradata/sunbak/system01.dbf'; 44 45 Database altered. 46 47 SQL> alter database rename file '/u01/oradata/sun/system01.dbf' to '/u01/oradata/sunbak/system01.dbf'; 48 49 Database altered. 50 51 SQL> alter database rename file '/u01/oradata/sun/undotbs01.dbf' to '/u01/oradata/sunbak/undotbs01.dbf'; 52 53 Database altered. 54 55 SQL> alter database rename file '/u01/oradata/sun/sysaux01.dbf' to '/u01/oradata/sunbak/sysaux01.dbf'; 56 57 Database altered. 58 59 SQL> alter database rename file '/u01/oradata/sun/users01.dbf' to '/u01/oradata/sunbak/users01.dbf'; 60 61 Database altered. 62 63 SQL> alter database rename file '/u01/oradata/sun/example01.dbf' to '/u01/oradata/sunbak/example01.dbf'; 64 65 Database altered. 66 67 SQL> select file#,name from v$datafile; 68 69 FILE# NAME 70 ---------- ---------------------------------------- 71 1 /u01/oradata/sunbak/system01.dbf 72 2 /u01/oradata/sunbak/undotbs01.dbf 73 3 /u01/oradata/sunbak/sysaux01.dbf 74 4 /u01/oradata/sunbak/users01.dbf 75 5 /u01/oradata/sunbak/example01.dbf 76 77 SQL> 78 79 #临时文件 80 SQL> desc v$tempfile 81 Name Null? Type 82 ----------------------------------------- -------- ---------------------------- 83 FILE# NUMBER 84 CREATION_CHANGE# NUMBER 85 CREATION_TIME DATE 86 TS# NUMBER 87 RFILE# NUMBER 88 STATUS VARCHAR2(7) 89 ENABLED VARCHAR2(10) 90 BYTES NUMBER 91 BLOCKS NUMBER 92 CREATE_BYTES NUMBER 93 BLOCK_SIZE NUMBER 94 NAME VARCHAR2(513) 95 96 SQL> select file#,name from v$tempfile; 97 98 FILE# NAME 99 ---------- ---------------------------------------- 100 1 /u01/oradata/sun/temp01.dbf 101 102 103 SQL> alter database rename file '/u01/oradata/sun/temp01.dbf' to '/u01/oradata/sunbak/temp01.dbf'; 104 105 Database altered. 106 107 SQL> select file#,name from v$tempfile; 108 109 FILE# NAME 110 ---------- ---------------------------------------- 111 1 /u01/oradata/sunbak/temp01.dbf 112 113 SQL> 114 115 #日志文件 116 SQL> desc v$logfile 117 Name Null? Type 118 ----------------------------------------- -------- ---------------------------- 119 GROUP# NUMBER 120 STATUS VARCHAR2(7) 121 TYPE VARCHAR2(7) 122 MEMBER VARCHAR2(513) 123 IS_RECOVERY_DEST_FILE VARCHAR2(3) 124 125 SQL> col member format a40 126 SQL> select group#,member from v$logfile; 127 128 GROUP# MEMBER 129 ---------- ---------------------------------------- 130 3 /u01/oradata/sun/redo03.log 131 2 /u01/oradata/sun/redo02.log 132 1 /u01/oradata/sun/redo01.log 133 134 SQL> alter database rename file '/u01/oradata/sun/redo03.log' to '/u01/oradata/sunbak/redo03.log'; 135 136 Database altered. 137 138 SQL> alter database rename file '/u01/oradata/sun/redo02.log' to '/u01/oradata/sunbak/redo02.log'; 139 140 Database altered. 141 142 SQL> alter database rename file '/u01/oradata/sun/redo01.log' to '/u01/oradata/sunbak/redo01.log'; 143 144 Database altered. 145 146 SQL> select group#,member from v$logfile; 147 148 GROUP# MEMBER 149 ---------- ---------------------------------------- 150 3 /u01/oradata/sunbak/redo03.log 151 2 /u01/oradata/sunbak/redo02.log 152 1 /u01/oradata/sunbak/redo01.log 153 154 SQL> 155 156 #整体查看 157 SQL> select file#,name from v$datafile; 158 159 FILE# NAME 160 ---------- ---------------------------------------- 161 1 /u01/oradata/sunbak/system01.dbf 162 2 /u01/oradata/sunbak/undotbs01.dbf 163 3 /u01/oradata/sunbak/sysaux01.dbf 164 4 /u01/oradata/sunbak/users01.dbf 165 5 /u01/oradata/sunbak/example01.dbf 166 SQL> select file#,name from v$tempfile; 167 168 FILE# NAME 169 ---------- ---------------------------------------- 170 1 /u01/oradata/sunbak/temp01.dbf 171 172 SQL> select group#,member from v$logfile; 173 174 GROUP# MEMBER 175 ---------- ---------------------------------------- 176 3 /u01/oradata/sunbak/redo03.log 177 2 /u01/oradata/sunbak/redo02.log 178 1 /u01/oradata/sunbak/redo01.log 179 180 SQL>
7.开启数据库
1 SQL> alter database open; 2 3 Database altered. 4 5 SQL>
8.验证
1 SQL> select * from hr.departments; 2 3 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID 4 ------------- ------------------------------ ---------- ----------- 5 10 Administration 200 1700 6 20 Marketing 201 1800 7 30 Purchasing 114 1700 8 40 Human Resources 203 2400 9 50 Shipping 121 1500 10 60 IT 103 1400 11 70 Public Relations 204 2700 12 80 Sales 145 2500 13 90 Executive 100 1700 14 100 Finance 108 1700 15 110 Accounting 205 1700 16 17 SQL>
