实验目的:
把64位 win2003 11.2g数据库 不完全恢复到32位的linux 5.5服务器上 .
把64位 win2003 11.2g数据库 不完全恢复到32位的linux 5.5服务器上 .
1.源库先做一个全备操作。
2.加入测试数据test1.t2 :
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 C:\app\Administrator\archive
最早的联机日志序列 3
下一个存档日志序列 5
当前日志序列 5
数据库日志模式 存档模式
自动存档 启用
存档终点 C:\app\Administrator\archive
最早的联机日志序列 3
下一个存档日志序列 5
当前日志序列 5
SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-12-10 17:29:13
-------------------
2010-12-10 17:29:13
SQL> create table test1.t2 (id number);
表已创建。
SQL> insert into test1.t2 select rownum from dba_users;
已创建39行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-12-10 17:30:06
-------------------
2010-12-10 17:30:06
SQL> update test1.t2 set id=id+10;
已更新39行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-12-10 17:30:31
-------------------
2010-12-10 17:30:31
--要恢复到这个时间点
SQL> truncate table test1.t1;
表被截断。
SQL> truncate table test1.t2;
表被截断。
3.关闭数据库。
SQL> shutdown immediate;
已经卸载数据库。
ORACLE 例程已经关闭。
4.拷贝备份和日志文件到目标服务器上。
[oracle@kmed dbs]$ ll /rmanbk/kmed11/
total 1165712
drwxr-xr-x 2 oracle oinstall 4096 Dec 11 09:35 archive
-rw-r--r-- 1 oracle oinstall 26984448 Dec 11 09:34 ARC_KMED11_1_1_1_737400317.ARH
-rw-r--r-- 1 oracle oinstall 4608 Dec 11 09:34 ARC_KMED11_3_1_1_737400412.ARH
-rw-r--r-- 1 oracle oinstall 9830400 Dec 11 09:34 CTL_C-682820449-20101210-00
-rw-r--r-- 1 oracle oinstall 1155678208 Dec 11 09:38 DB_KMED11_2_1_1_737400323.DBF
以下连接目标服务器上linux5.5 上操作:
5.编辑pfile文件:
[oracle@kmed dbs]$ vi initkmed11.ora
*.aq_tm_processes=2
*.audit_file_dest='/u02/app/admin/kmed11/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u02/oradata/kmed11/control01.ctl','/u02/oradata/kmed11/control02.ctl','/u02/oradata/kmed11/control03.ctl'#Restore Controlfile
*.core_dump_dest='/u02/app/diag/rdbms/kmed11/kmed11/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='kmed11' #Reset to original value by RMAN
*.diagnostic_dest='/u02/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=kmed11XDB)'
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_2='location=/u02/archive/kmed11'
*.log_buffer=2134016# log buffer update
*.memory_target=244M
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.open_cursors=300
*.open_links=4
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.parallel_max_servers=20
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=640K
*.skip_unusable_indexes=TRUE
*.streams_pool_size=12M
*.undo_retention=3600
*.undo_management='manual'
*.undo_tablespace='undotbs2'
*.utl_file_dir='*'
~
~~
"initkmed11.ora" [New] 33L, 1141C written
6.建立kmed11相关的目录:
[oracle@kmed dbs]$ mkdir -p /u02/archive/kmed11
[oracle@kmed dbs]$ mkdir -p /u02/app/diag/rdbms/kmed11/kmed11/cdump
[oracle@kmed dbs]$ ll /u02/oradata/kmed11
total 0
*.aq_tm_processes=2
*.audit_file_dest='/u02/app/admin/kmed11/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u02/oradata/kmed11/control01.ctl','/u02/oradata/kmed11/control02.ctl','/u02/oradata/kmed11/control03.ctl'#Restore Controlfile
*.core_dump_dest='/u02/app/diag/rdbms/kmed11/kmed11/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='kmed11' #Reset to original value by RMAN
*.diagnostic_dest='/u02/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=kmed11XDB)'
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_2='location=/u02/archive/kmed11'
*.log_buffer=2134016# log buffer update
*.memory_target=244M
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.open_cursors=300
*.open_links=4
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.parallel_max_servers=20
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=640K
*.skip_unusable_indexes=TRUE
*.streams_pool_size=12M
*.undo_retention=3600
*.undo_management='manual'
*.undo_tablespace='undotbs2'
*.utl_file_dir='*'
~
~~
"initkmed11.ora" [New] 33L, 1141C written
6.建立kmed11相关的目录:
[oracle@kmed dbs]$ mkdir -p /u02/archive/kmed11
[oracle@kmed dbs]$ mkdir -p /u02/app/diag/rdbms/kmed11/kmed11/cdump
[oracle@kmed dbs]$ ll /u02/oradata/kmed11
total 0
7.建立密码文件:
[oracle@kmed dbs]$ orapwd file=orapwkmed11 password=oracle entries=10
8.设置sid,启动到nomount目录下:
[oracle@kmed dbs]$ export ORACLE_SID=kmed11
[oracle@kmed dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 11 09:56:07 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
ORACLE instance started.
Total System Global Area 255270912 bytes
Fixed Size 1335836 bytes
Variable Size 197135844 bytes
Database Buffers 54525952 bytes
Redo Buffers 2273280 bytes
Fixed Size 1335836 bytes
Variable Size 197135844 bytes
Database Buffers 54525952 bytes
Redo Buffers 2273280 bytes
9.启动rman,restore控制文件。
[oracle@kmed dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 11 09:56:45 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: KMED11 (not mounted)
RMAN> restore controlfile from '/rmanbk/kmed11/CTL_C-682820449-20101210-00';
Starting restore at 11-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u02/oradata/kmed11/control01.ctl
output file name=/u02/oradata/kmed11/control02.ctl
output file name=/u02/oradata/kmed11/control03.ctl
Finished restore at 11-DEC-10
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u02/oradata/kmed11/control01.ctl
output file name=/u02/oradata/kmed11/control02.ctl
output file name=/u02/oradata/kmed11/control03.ctl
Finished restore at 11-DEC-10
10.启动到mount 状态。
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_1
SQL> col name format a40;
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 +DG/kmed11/system01.dbf SYSTEM
2 +DG/kmed11/sysaux01.dbf ONLINE
3 +DG/kmed11/undotbs01.dbf ONLINE
4 +DG/kmed11/users01.dbf ONLINE
5 +DG/kmed11/example01.dbf ONLINE
6 +DG/kmed11/test01.dbf ONLINE
7 +DG/kmed11/test02.dbf ONLINE
8 +DG/kmed11/test03.dbf ONLINE
---------- ---------------------------------------- -------
1 +DG/kmed11/system01.dbf SYSTEM
2 +DG/kmed11/sysaux01.dbf ONLINE
3 +DG/kmed11/undotbs01.dbf ONLINE
4 +DG/kmed11/users01.dbf ONLINE
5 +DG/kmed11/example01.dbf ONLINE
6 +DG/kmed11/test01.dbf ONLINE
7 +DG/kmed11/test02.dbf ONLINE
8 +DG/kmed11/test03.dbf ONLINE
11.在rman中指定备份文件的路径:
RMAN> catalog backuppiece '/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF';
cataloged backup piece
backup piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF RECID=4 STAMP=737460328
backup piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF RECID=4 STAMP=737460328
RMAN> catalog backuppiece '/rmanbk/kmed11/ARC_KMED11_1_1_1_737400317.ARH';
cataloged backup piece
backup piece handle=/rmanbk/kmed11/ARC_KMED11_1_1_1_737400317.ARH RECID=5 STAMP=737460377
backup piece handle=/rmanbk/kmed11/ARC_KMED11_1_1_1_737400317.ARH RECID=5 STAMP=737460377
RMAN> catalog backuppiece '/rmanbk/kmed11/ARC_KMED11_3_1_1_737400412.ARH';
cataloged backup piece
backup piece handle=/rmanbk/kmed11/ARC_KMED11_3_1_1_737400412.ARH RECID=6 STAMP=737460390
backup piece handle=/rmanbk/kmed11/ARC_KMED11_3_1_1_737400412.ARH RECID=6 STAMP=737460390
12.开始恢复文件:
RMAN> run{
2> set newname for datafile 1 to '/u02/oradata/kmed11/system01.dbf';
3> set newname for datafile 2 to '/u02/oradata/kmed11/sysaux01.dbf';
4> set newname for datafile 3 to '/u02/oradata/kmed11/undotbs01.dbf';
5> set newname for datafile 4 to '/u02/oradata/kmed11/users01.dbf';
6> set newname for datafile 5 to '/u02/oradata/kmed11/example01.dbf';
7> set newname for datafile 6 to '/u02/oradata/kmed11/test01.dbf';
8> restore tablespace system;
9> restore tablespace sysaux;
10> restore tablespace undotbs1;
11> restore tablespace users;
12> restore tablespace example;
13> restore tablespace test1;
14> switch datafile 1;
15> switch datafile 2;
16> switch datafile 3;
17> switch datafile 4;
18> switch datafile 5;
19> switch datafile 6;
20> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-DEC-10
using channel ORA_DISK_1
using channel ORA_DISK_1
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 /u02/oradata/kmed11/system01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 11-DEC-10
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/kmed11/system01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 11-DEC-10
Starting restore at 11-DEC-10
using channel ORA_DISK_1
using channel ORA_DISK_1
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 00002 to /u02/oradata/kmed11/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 11-DEC-10
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/kmed11/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 11-DEC-10
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 00003 to /u02/oradata/kmed11/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-DEC-10
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/kmed11/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-DEC-10
Starting restore at 11-DEC-10
using channel ORA_DISK_1
using channel ORA_DISK_1
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 00004 to /u02/oradata/kmed11/users01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-10
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/kmed11/users01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-10
Starting restore at 11-DEC-10
using channel ORA_DISK_1
using channel ORA_DISK_1
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 00005 to /u02/oradata/kmed11/example01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-DEC-10
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/kmed11/example01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-DEC-10
Starting restore at 11-DEC-10
using channel ORA_DISK_1
using channel ORA_DISK_1
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 00006 to /u02/oradata/kmed11/test01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-10
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u02/oradata/kmed11/test01.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: errors found reading piece handle=C:\APP\ORABK\DB_KMED11_2_1_1_737400323.DBF
channel ORA_DISK_1: failover to piece handle=/rmanbk/kmed11/DB_KMED11_2_1_1_737400323.DBF tag=TAG20101210T172523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-10
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=737460543 file name=/u02/oradata/kmed11/system01.dbf
input datafile copy RECID=2 STAMP=737460543 file name=/u02/oradata/kmed11/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=737460565 file name=/u02/oradata/kmed11/sysaux01.dbf
input datafile copy RECID=3 STAMP=737460565 file name=/u02/oradata/kmed11/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=737460733 file name=/u02/oradata/kmed11/undotbs01.dbf
input datafile copy RECID=8 STAMP=737460733 file name=/u02/oradata/kmed11/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=737460733 file name=/u02/oradata/kmed11/users01.dbf
input datafile copy RECID=9 STAMP=737460733 file name=/u02/oradata/kmed11/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=737460734 file name=/u02/oradata/kmed11/example01.dbf
input datafile copy RECID=10 STAMP=737460734 file name=/u02/oradata/kmed11/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=737460734 file name=/u02/oradata/kmed11/test01.dbf
input datafile copy RECID=11 STAMP=737460734 file name=/u02/oradata/kmed11/test01.dbf
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /u02/oradata/kmed11/system01.dbf SYSTEM
2 /u02/oradata/kmed11/sysaux01.dbf ONLINE
3 /u02/oradata/kmed11/undotbs01.dbf ONLINE
4 /u02/oradata/kmed11/users01.dbf ONLINE
5 /u02/oradata/kmed11/example01.dbf ONLINE
6 /u02/oradata/kmed11/test01.dbf ONLINE
7 +DG/kmed11/test02.dbf ONLINE
8 +DG/kmed11/test03.dbf ONLINE
---------- ---------------------------------------- -------
1 /u02/oradata/kmed11/system01.dbf SYSTEM
2 /u02/oradata/kmed11/sysaux01.dbf ONLINE
3 /u02/oradata/kmed11/undotbs01.dbf ONLINE
4 /u02/oradata/kmed11/users01.dbf ONLINE
5 /u02/oradata/kmed11/example01.dbf ONLINE
6 /u02/oradata/kmed11/test01.dbf ONLINE
7 +DG/kmed11/test02.dbf ONLINE
8 +DG/kmed11/test03.dbf ONLINE
8 rows selected.
13.修改其它表空间为offline:
SQL> alter database datafile 7,8 offline drop;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /u02/oradata/kmed11/system01.dbf SYSTEM
2 /u02/oradata/kmed11/sysaux01.dbf ONLINE
3 /u02/oradata/kmed11/undotbs01.dbf ONLINE
4 /u02/oradata/kmed11/users01.dbf ONLINE
5 /u02/oradata/kmed11/example01.dbf ONLINE
6 /u02/oradata/kmed11/test01.dbf ONLINE
7 +DG/kmed11/test02.dbf RECOVER
8 +DG/kmed11/test03.dbf RECOVER
---------- ---------------------------------------- -------
1 /u02/oradata/kmed11/system01.dbf SYSTEM
2 /u02/oradata/kmed11/sysaux01.dbf ONLINE
3 /u02/oradata/kmed11/undotbs01.dbf ONLINE
4 /u02/oradata/kmed11/users01.dbf ONLINE
5 /u02/oradata/kmed11/example01.dbf ONLINE
6 /u02/oradata/kmed11/test01.dbf ONLINE
7 +DG/kmed11/test02.dbf RECOVER
8 +DG/kmed11/test03.dbf RECOVER
8 rows selected.
14.rename logfile 文件:
SQL> col member format a30;
SQL> select * from v$logfile;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE +DG/kmed11/redo03.log NO
2 ONLINE +DG/kmed11/redo02.log NO
1 ONLINE +DG/kmed11/redo01.log NO
---------- ------- ------- ------------------------------ ---
3 ONLINE +DG/kmed11/redo03.log NO
2 ONLINE +DG/kmed11/redo02.log NO
1 ONLINE +DG/kmed11/redo01.log NO
SQL> alter database rename file '+DG/kmed11/redo03.log' to '/u02/oradata/kmed11/redo03.log';
Database altered.
SQL> alter database rename file '+DG/kmed11/redo02.log' to '/u02/oradata/kmed11/redo02.log';
Database altered.
SQL> alter database rename file '+DG/kmed11/redo01.log' to '/u02/oradata/kmed11/redo01.log';
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /u02/oradata/kmed11/redo03.log NO
2 ONLINE /u02/oradata/kmed11/redo02.log NO
1 ONLINE /u02/oradata/kmed11/redo01.log NO
---------- ------- ------- ------------------------------ ---
3 ONLINE /u02/oradata/kmed11/redo03.log NO
2 ONLINE /u02/oradata/kmed11/redo02.log NO
1 ONLINE /u02/oradata/kmed11/redo01.log NO
15.开始recover 不完全恢复。
SQL> recover database until time '2010-12-10 17:30:06' using backup controlfile;
ORA-00279: change 1022562 generated at 12/10/2010 17:25:25 needed for thread 1
ORA-00289: suggestion : /u02/archive/kmed11/1_8_737396517.dbf
ORA-00280: change 1022562 for thread 1 is in sequence #8
ORA-00279: change 1022562 generated at 12/10/2010 17:25:25 needed for thread 1
ORA-00289: suggestion : /u02/archive/kmed11/1_8_737396517.dbf
ORA-00280: change 1022562 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/rmanbk/kmed11/archive/ARC0000000008_0737396517.0001
ORA-00279: change 1022596 generated at 12/10/2010 17:26:51 needed for thread 1
ORA-00289: suggestion : /u02/archive/kmed11/1_9_737396517.dbf
ORA-00280: change 1022596 for thread 1 is in sequence #9
ORA-00278: log file '/rmanbk/kmed11/archive/ARC0000000008_0737396517.0001' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/rmanbk/kmed11/archive/ARC0000000009_0737396517.0001
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 1, block#
3098)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/u02/oradata/kmed11/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 456
ORA-00600: internal error code, arguments: [4502], [0], [], [], [], [], [], [],
[], [], [], []
ORA-01112: media recovery not started
网上查找资料后,也没有搞定这个问题。后来做了cancel不完全恢复。
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1022596 generated at 12/10/2010 17:26:51 needed for thread 1
ORA-00289: suggestion : /u02/archive/kmed11/1_9_737396517.dbf
ORA-00280: change 1022596 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
16.resetlog 方式打开数据库:
SQL> alter database open resetlogs;
SQL> alter database open resetlogs;
Database altered.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
以下是打开数据库后的相关问题的处理:
1.强制打开数据库后,alert日志文件中产生很多的‘ORA-06544: PL/SQL: internal error, arguments: [56327]’错误信息。
Sat Dec 11 11:10:37 2010
Errors in file /u02/app/diag/rdbms/kmed11/kmed11/trace/kmed11_cjq0_3463.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Errors in file /u02/app/diag/rdbms/kmed11/kmed11/trace/kmed11_cjq0_3463.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Sat Dec 11 11:10:47 2010
Errors in file /u02/app/diag/rdbms/kmed11/kmed11/trace/kmed11_cjq0_3463.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Errors in file /u02/app/diag/rdbms/kmed11/kmed11/trace/kmed11_cjq0_3463.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Errors in file /u02/app/diag/rdbms/kmed11/kmed11/trace/kmed11_cjq0_3463.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Sat Dec 11 11:10:47 2010
Errors in file /u02/app/diag/rdbms/kmed11/kmed11/trace/kmed11_cjq0_3463.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
创建临时表空间也提示出错。
SQL> create temporary tablespace temp2 tempfile '/u02/oradata/kmed11/temp02.dbf' size 10m;
create temporary tablespace temp2 tempfile '/u02/oradata/kmed11/temp02.dbf' size 10m
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56327]
ORA-00901: invalid CREATE command
create temporary tablespace temp2 tempfile '/u02/oradata/kmed11/temp02.dbf' size 10m
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56327]
ORA-00901: invalid CREATE command
网上查找资料后,好像是64位平台迁移到32位时,会显示这样的错误。
解决方式:
<1>.startup migrate ;
startup migrate表示降级,在9i,无论升级/降级 数据库都是startup migrate,
10g后增加了upgrade参数,升级可直接用startup upgrade,降级仍是startup migrate.
解决方式:
<1>.startup migrate ;
startup migrate表示降级,在9i,无论升级/降级 数据库都是startup migrate,
10g后增加了upgrade参数,升级可直接用startup upgrade,降级仍是startup migrate.
<2>.使用utlirp来把相关内容全部在32位平台下编译一遍。
开始操作:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
ORACLE instance started.
Total System Global Area 255270912 bytes
Fixed Size 1335836 bytes
Variable Size 197135844 bytes
Database Buffers 54525952 bytes
Redo Buffers 2273280 bytes
Database mounted.
Database opened.
Fixed Size 1335836 bytes
Variable Size 197135844 bytes
Database Buffers 54525952 bytes
Redo Buffers 2273280 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlirp.sql
....................
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
ORACLE instance started.
Total System Global Area 255270912 bytes
Fixed Size 1335836 bytes
Variable Size 197135844 bytes
Database Buffers 54525952 bytes
Redo Buffers 2273280 bytes
Database mounted.
Database opened.
Fixed Size 1335836 bytes
Variable Size 197135844 bytes
Database Buffers 54525952 bytes
Redo Buffers 2273280 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql
..........
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components; --这里用了很长时间。
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components; --这里用了很长时间。
Warning: XDB now invalid, could not find xdbconfig
ORDIM INVALID OBJECTS: CODE_SEQUENCE110_T - 6 - 13
ORDIM INVALID OBJECTS: CODE_SEQUENCE112_COLL - 6 - 13
ORDIM INVALID OBJECTS: CODE_SQ111_T - 6 - 13
ORDIM INVALID OBJECTS: ORD_DICOM_HEADER114_T - 6 - 13
ORDIM INVALID OBJECTS: MEDIA_STORAGE_SOP_CLASS116_T - 6 - 13
ORDIM INVALID OBJECTS: MEDIA_STORAGE_SOP_INSTA117_T - 6 - 13
ORDIM INVALID OBJECTS: TRANSFER_SYNTAX_UID118_T - 6 - 13
ORDIM INVALID OBJECTS: IMPLEMENTATION_CLASS_UI119_T - 6 - 13
ORDIM INVALID OBJECTS: IMPLEMENTATION_VERSION_120_T - 6 - 13
ORDIM INVALID OBJECTS: SOURCE_APPLICATION_ENTI121_T - 6 - 13
ORDIM INVALID OBJECTS: FILE_META_HEADER115_T - 6 - 13
ORDIM INVALID OBJECTS: NAME124_T - 6 - 13
ORDIM INVALID OBJECTS: ID125_T - 6 - 13
ORDIM INVALID OBJECTS: BIRTH_DATE126_T - 6 - 13
ORDIM INVALID OBJECTS: SEX127_T - 6 - 13
ORDIM INVALID OBJECTS: PATIENT123_T - 6 - 13
ORDIM INVALID OBJECTS: INSTANCE_UID129_T - 6 - 13
ORDIM INVALID OBJECTS: DATE130_T - 6 - 13
ORDIM INVALID OBJECTS: TIME132_T - 6 - 13
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-01403: no
data found
ORDIM INVALID OBJECTS: CODE_SEQUENCE110_T - 6 - 13
ORDIM INVALID OBJECTS: CODE_SEQUENCE112_COLL - 6 - 13
ORDIM INVALID OBJECTS: CODE_SQ111_T - 6 - 13
ORDIM INVALID OBJECTS: ORD_DICOM_HEADER114_T - 6 - 13
ORDIM INVALID OBJECTS: MEDIA_STORAGE_SOP_CLASS116_T - 6 - 13
ORDIM INVALID OBJECTS: MEDIA_STORAGE_SOP_INSTA117_T - 6 - 13
ORDIM INVALID OBJECTS: TRANSFER_SYNTAX_UID118_T - 6 - 13
ORDIM INVALID OBJECTS: IMPLEMENTATION_CLASS_UI119_T - 6 - 13
ORDIM INVALID OBJECTS: IMPLEMENTATION_VERSION_120_T - 6 - 13
ORDIM INVALID OBJECTS: SOURCE_APPLICATION_ENTI121_T - 6 - 13
ORDIM INVALID OBJECTS: FILE_META_HEADER115_T - 6 - 13
ORDIM INVALID OBJECTS: NAME124_T - 6 - 13
ORDIM INVALID OBJECTS: ID125_T - 6 - 13
ORDIM INVALID OBJECTS: BIRTH_DATE126_T - 6 - 13
ORDIM INVALID OBJECTS: SEX127_T - 6 - 13
ORDIM INVALID OBJECTS: PATIENT123_T - 6 - 13
ORDIM INVALID OBJECTS: INSTANCE_UID129_T - 6 - 13
ORDIM INVALID OBJECTS: DATE130_T - 6 - 13
ORDIM INVALID OBJECTS: TIME132_T - 6 - 13
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-01403: no
data found
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
完成后,日志中不再有ORA-00604的错误了。
2. 重建无效的temp文件:
SQL> col name format a40;
SQL> select name,status from v$tempfile;
SQL> select name,status from v$tempfile;
NAME STATUS
---------------------------------------- -------
+DG/kmed11/temp01.dbf OFFLINE
---------------------------------------- -------
+DG/kmed11/temp01.dbf OFFLINE
SQL> create temporary tablespace temp2 tempfile '/u02/oradata/kmed11/temp02.dbf' size 10m;
Tablespace created.
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> drop tablespace temp;
Tablespace dropped.
SQL> select name,status from v$tempfile;
NAME STATUS
---------------------------------------- -------
/u02/oradata/kmed11/temp02.dbf ONLINE
---------------------------------------- -------
/u02/oradata/kmed11/temp02.dbf ONLINE
3.删除无效的表空间:
SQL> select name,status from v$datafile;
NAME STATUS
---------------------------------------- -------
/u02/oradata/kmed11/system01.dbf SYSTEM
/u02/oradata/kmed11/sysaux01.dbf ONLINE
/u02/oradata/kmed11/undotbs01.dbf ONLINE
/u02/oradata/kmed11/users01.dbf ONLINE
/u02/oradata/kmed11/example01.dbf ONLINE
/u02/oradata/kmed11/test01.dbf ONLINE
+DG/kmed11/test02.dbf OFFLINE
+DG/kmed11/test03.dbf OFFLINE
---------------------------------------- -------
/u02/oradata/kmed11/system01.dbf SYSTEM
/u02/oradata/kmed11/sysaux01.dbf ONLINE
/u02/oradata/kmed11/undotbs01.dbf ONLINE
/u02/oradata/kmed11/users01.dbf ONLINE
/u02/oradata/kmed11/example01.dbf ONLINE
/u02/oradata/kmed11/test01.dbf ONLINE
+DG/kmed11/test02.dbf OFFLINE
+DG/kmed11/test03.dbf OFFLINE
8 rows selected.
SQL> drop tablespace test2 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace test3 including contents and datafiles;
Tablespace dropped.
SQL> select name,status from v$datafile;
NAME STATUS
---------------------------------------- -------
/u02/oradata/kmed11/system01.dbf SYSTEM
/u02/oradata/kmed11/sysaux01.dbf ONLINE
/u02/oradata/kmed11/undotbs01.dbf ONLINE
/u02/oradata/kmed11/users01.dbf ONLINE
/u02/oradata/kmed11/example01.dbf ONLINE
/u02/oradata/kmed11/test01.dbf ONLINE
---------------------------------------- -------
/u02/oradata/kmed11/system01.dbf SYSTEM
/u02/oradata/kmed11/sysaux01.dbf ONLINE
/u02/oradata/kmed11/undotbs01.dbf ONLINE
/u02/oradata/kmed11/users01.dbf ONLINE
/u02/oradata/kmed11/example01.dbf ONLINE
/u02/oradata/kmed11/test01.dbf ONLINE
6 rows selected.
不完全恢复完成。
本文转自 gjm008 51CTO博客,原文链接:http://blog.51cto.com/gaoshan/452959,如需转载请自行联系原作者