oracle instance 迁移 （window - linux）
从windows 向linux下oracle instance数据迁移
Step1：
上传：oracleproduct10.2.0oradatasheng 下的全部数据文件到/u02/
上传：oracleproduct10.2.0admin 下的全部数据文件到/u01/pp/oracle/admin
上传：$ORACLE_HOMEdbs 下的spfile 到/u01/pp/oracle/product/10.2/db_1/dbs
Step2:
export ORACLE_SID=sheng
sqlplus / as sysdba
create pfile from spfile
修改pfile:
*.audit_file_dest='D:oracleproduct10.2.0/admin/SHENG/adump'
*.background_dump_dest='D:oracleproduct10.2.0/admin/SHENG/bdump'
*.control_files='D:oracleproduct10.2.0oradataSHENGcontrol01.ctl','D:oracleproduct10.2.0oradataSHENGcontrol02.ctl','D:oracleproduct10.2.0oradataSHENGcontrol03.ctl'
*.core_dump_dest='D:oracleproduct10.2.0/admin/SHENG/cdump'
*.db_recovery_file_dest='D:oracleproduct10.2.0/flash_recovery_area'
*.user_dump_dest='D:oracleproduct10.2.0/admin/SHENG/udump'
把里面的路径修改成linux 里的路径：
*.audit_file_dest='/u01/pp/oracle/admin/sheng/adump'
*.background_dump_dest='/u01/pp/oracle/admin/sheng/bdump'
*.control_files='/u01/pp/oracle/admin/sheng/control01.ctl','/u01/pp/oracle/admin/sheng/control02.ctl','/u01/pp/oracle/admin/sheng/control03.ctl'
*.core_dump_dest='/u01/pp/oracle/admin/sheng/cdump'
*.db_name='sheng'
*.user_dump_dest='/u01/pp/oracle/admin/sheng/udump'
好，下面启动一下：
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
报是control 文件不对的问题。
下面看一下 control 文件的内容：
[oracle@aoracle sheng]$ strings control01.ctl
}|{z
+fSHENG
*SHENG
*SHENG
sheng
sheng
*TMK
*TMK
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO03.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO02.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO01.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGTEMP01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO03.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO02.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO01.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGTEMP01.DBF
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SYSTEM
UNDOTBS1
SYSAUX
USERS
-----------
发现还是原来的内容，
tail -f alert.log 内容
startup nomount
发现
还是启动：
System parameters with non-default values:
processes = 150
__shared_pool_size = 79691776
__large_pool_size = 0
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 272629760
control_files = /u01/pp/oracle/admin/sheng/control01.ctl, /u01/pp/oracle/admin/sheng/control02.ctl, /u01/pp/oracle/admin/sheng/control03.ctl
db_block_size = 8192
修改好pfile 后启动成功：
SQL> alter database mount;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF SYSTEM
D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF ONLINE
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF ONLINE
D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF ONLINE
发现显示的还是windows 上的路径。
好，下面从新建ctontrol 文件：
利用trace 文件来建：
SQL> alter database backup controlfile to trace;
Database altered.
查看当前的trc 文件：
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/pp/oracle/admin/sheng/udump/sheng_ora_13809.trc
SQL>
好，下面打开tac 文件：
-- Set #1. NORESETLOGS case
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHENG" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATASHENGREDO01.LOG' SIZE 50M,
GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATASHENGREDO02.LOG' SIZE 50M,
GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATASHENGREDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF',
'D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF',
'D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF',
'D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF'
CHARACTER SET ZHS16GBK
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
的内容路径替换掉。
修改如下：
注意：最好把reuse 改成：set 像下面一样：NOARCHIVELOG 改成 ARCHIVELOG
SQL> CREATE CONTROLFILE set DATABASE "SHENG" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/sheng/REDO01.LOG' SIZE 50M,
9 GROUP 2 '/u02/sheng/REDO02.LOG' SIZE 50M,
10 GROUP 3 '/u02/sheng/REDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/sheng/SYSTEM01.DBF',
14 '/u02/sheng/UNDOTBS01.DBF',
15 '/u02/sheng/SYSAUX01.DBF',
16 '/u02/sheng/USERS01.DBF'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/sheng/SYSTEM01.DBF'
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> col name format a30;
SQL> select name ,status from v$datafile;
NAME STATUS
------------------------------ -------
/u02/sheng/SYSTEM01.DBF SYSTEM
/u02/sheng/UNDOTBS01.DBF RECOVER
/u02/sheng/SYSAUX01.DBF RECOVER
/u02/sheng/USERS01.DBF RECOVER
SQL>
---------------
下面尝试用control 文件全部损坏的办法恢复：
在pfile 中加：_allow_resetlogs_corruption=true
create spfile from pfile;
尝试把数据库重启：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
报错，看一下alert日志：
alter database mount
Wed Dec 14 14:20:43 2011
Setting recovery target incarnation to 1
Wed Dec 14 14:20:43 2011
Successful mount of redo thread 1, with mount id 1777402871
Wed Dec 14 14:20:43 2011
Database mounted in Exclusive Mode
Completed: alter database mount
Wed Dec 14 14:22:57 2011
alter database open resetlogs
Wed Dec 14 14:22:57 2011
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 4982391
Wed Dec 14 14:23:15 2011
Setting recovery target incarnation to 2
Wed Dec 14 14:23:15 2011
Assigning activation ID 1777402871 (0x69f103f7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=6585
Wed Dec 14 14:23:15 2011
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=6587
Wed Dec 14 14:23:16 2011
ARC0: STARTING ARCH PROCESSES
Wed Dec 14 14:23:16 2011
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Wed Dec 14 14:23:16 2011
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: /u02/sheng/REDO03.LOG
Successful open of redo thread 1
Wed Dec 14 14:23:16 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 14 14:23:16 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=6589
Wed Dec 14 14:23:16 2011
SMON: enabling cache recovery
Wed Dec 14 14:23:18 2011
Errors in file /u01/pp/oracle/admin/sheng/udump/sheng_ora_6467.trc:
ORA-00600: internal error code, arguments: [2662], [0], [4982397], [0], [4982439], [4194313], [], []
Wed Dec 14 14:23:19 2011
Errors in file /u01/pp/oracle/admin/sheng/udump/sheng_ora_6467.trc:
ORA-00600: internal error code, arguments: [2662], [0], [4982397], [0], [4982439], [4194313], [], []
Wed Dec 14 14:23:19 2011
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 6467
ORA-1092 signalled during: alter database open resetlogs...
下面修改下面参数：
*.undo_management=MANUAL
*.undo_tablespace=SYSTEM
下面启动，报/u02/sheng/system01.dbf' need recover
好，下面进行recover:
sql>recover datafile 1
midiea recovered
同样的道理，recover datafile 2
把其他的全部recover
select name,status from v$datafile 可以看到在mount 状态下全部都online了。
重新启动：
alter database open,
数据库open 一会，又宕了。看一下alert 日志：
Wed Dec 14 14:42:06 2011
Errors in file /u01/pp/oracle/admin/sheng/bdump/sheng_dbw0_6980.trc:
ORA-00600: internal error code, arguments: [4194], [10], [28], [], [], [], [], []
Wed Dec 14 14:42:07 2011
Errors in file /u01/pp/oracle/admin/sheng/bdump/sheng_dbw0_6980.trc:
ORA-00600: internal error code, arguments: [4194], [10], [28], [], [], [], [], []
DBW0: terminating instance due to error 471
Instance terminated by DBW0, pid = 6980
好，下面重新建undo数据datafile:
以前碰到解决ora-00600的问题多是redo 和undo 的不一致造成的。
下面重新建一下：
drop tablespace undotbs1
create tablespace undo undotbs2 datafile '/u02/sheng/undotbs02.dbf' size 100m;
问题解决后，看一下alert 日志：
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Wed Dec 14 15:14:12 2011
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 14 15:14:13 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=9042
QMNC started with pid=19, OS id=9044
Wed Dec 14 15:14:14 2011
Completed: alter database open
发现警告中有个temp 没有显示建，下面建一下：
SQL> select name,status from v$datafile
NAME STATUS
---------------------------------------- -------
/u02/sheng/SYSTEM01.DBF SYSTEM
/u02/sheng/SYSAUX01.DBF ONLINE
/u02/sheng/USERS01.DBF ONLINE
/u02/sheng/UNDOTBS02.dbf ONLINE
SQL> select name from v$tablespace;
NAME
----------------------------------------
SYSTEM
SYSAUX
USERS
TEMP
UNDOTBS2
查看temp file 的情况，是不能用 v$datafile 的，而要用 v$tempfile:
SQL> select name,status from v$tempfile;
no rows selected
SQL> alter tablespace temp add tempfile '/u02/sheng/temp01.dbf' size 50m autoextend on next 5m maxsize unlimited;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
----------------------------------------
/u02/sheng/temp01.dbf
Step1：
上传：oracleproduct10.2.0oradatasheng 下的全部数据文件到/u02/
上传：oracleproduct10.2.0admin 下的全部数据文件到/u01/pp/oracle/admin
上传：$ORACLE_HOMEdbs 下的spfile 到/u01/pp/oracle/product/10.2/db_1/dbs
Step2:
export ORACLE_SID=sheng
sqlplus / as sysdba
create pfile from spfile
修改pfile:
*.audit_file_dest='D:oracleproduct10.2.0/admin/SHENG/adump'
*.background_dump_dest='D:oracleproduct10.2.0/admin/SHENG/bdump'
*.control_files='D:oracleproduct10.2.0oradataSHENGcontrol01.ctl','D:oracleproduct10.2.0oradataSHENGcontrol02.ctl','D:oracleproduct10.2.0oradataSHENGcontrol03.ctl'
*.core_dump_dest='D:oracleproduct10.2.0/admin/SHENG/cdump'
*.db_recovery_file_dest='D:oracleproduct10.2.0/flash_recovery_area'
*.user_dump_dest='D:oracleproduct10.2.0/admin/SHENG/udump'
把里面的路径修改成linux 里的路径：
*.audit_file_dest='/u01/pp/oracle/admin/sheng/adump'
*.background_dump_dest='/u01/pp/oracle/admin/sheng/bdump'
*.control_files='/u01/pp/oracle/admin/sheng/control01.ctl','/u01/pp/oracle/admin/sheng/control02.ctl','/u01/pp/oracle/admin/sheng/control03.ctl'
*.core_dump_dest='/u01/pp/oracle/admin/sheng/cdump'
*.db_name='sheng'
*.user_dump_dest='/u01/pp/oracle/admin/sheng/udump'
好，下面启动一下：
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
报是control 文件不对的问题。
下面看一下 control 文件的内容：
[oracle@aoracle sheng]$ strings control01.ctl
}|{z
+fSHENG
*SHENG
*SHENG
sheng
sheng
*TMK
*TMK
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO03.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO02.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO01.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGTEMP01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO03.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO02.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGREDO01.LOG
D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0ORADATASHENGTEMP01.DBF
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SYSTEM
UNDOTBS1
SYSAUX
USERS
-----------
发现还是原来的内容，
tail -f alert.log 内容
startup nomount
发现
还是启动：
System parameters with non-default values:
processes = 150
__shared_pool_size = 79691776
__large_pool_size = 0
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 272629760
control_files = /u01/pp/oracle/admin/sheng/control01.ctl, /u01/pp/oracle/admin/sheng/control02.ctl, /u01/pp/oracle/admin/sheng/control03.ctl
db_block_size = 8192
修改好pfile 后启动成功：
SQL> alter database mount;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF SYSTEM
D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF ONLINE
D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF ONLINE
D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF ONLINE
发现显示的还是windows 上的路径。
好，下面从新建ctontrol 文件：
利用trace 文件来建：
SQL> alter database backup controlfile to trace;
Database altered.
查看当前的trc 文件：
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/pp/oracle/admin/sheng/udump/sheng_ora_13809.trc
SQL>
好，下面打开tac 文件：
-- Set #1. NORESETLOGS case
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHENG" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATASHENGREDO01.LOG' SIZE 50M,
GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATASHENGREDO02.LOG' SIZE 50M,
GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATASHENGREDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:ORACLEPRODUCT10.2.0ORADATASHENGSYSTEM01.DBF',
'D:ORACLEPRODUCT10.2.0ORADATASHENGUNDOTBS01.DBF',
'D:ORACLEPRODUCT10.2.0ORADATASHENGSYSAUX01.DBF',
'D:ORACLEPRODUCT10.2.0ORADATASHENGUSERS01.DBF'
CHARACTER SET ZHS16GBK
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
的内容路径替换掉。
修改如下：
注意：最好把reuse 改成：set 像下面一样：NOARCHIVELOG 改成 ARCHIVELOG
SQL> CREATE CONTROLFILE set DATABASE "SHENG" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/sheng/REDO01.LOG' SIZE 50M,
9 GROUP 2 '/u02/sheng/REDO02.LOG' SIZE 50M,
10 GROUP 3 '/u02/sheng/REDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/sheng/SYSTEM01.DBF',
14 '/u02/sheng/UNDOTBS01.DBF',
15 '/u02/sheng/SYSAUX01.DBF',
16 '/u02/sheng/USERS01.DBF'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/sheng/SYSTEM01.DBF'
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> col name format a30;
SQL> select name ,status from v$datafile;
NAME STATUS
------------------------------ -------
/u02/sheng/SYSTEM01.DBF SYSTEM
/u02/sheng/UNDOTBS01.DBF RECOVER
/u02/sheng/SYSAUX01.DBF RECOVER
/u02/sheng/USERS01.DBF RECOVER
SQL>
---------------
下面尝试用control 文件全部损坏的办法恢复：
在pfile 中加：_allow_resetlogs_corruption=true
create spfile from pfile;
尝试把数据库重启：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
报错，看一下alert日志：
alter database mount
Wed Dec 14 14:20:43 2011
Setting recovery target incarnation to 1
Wed Dec 14 14:20:43 2011
Successful mount of redo thread 1, with mount id 1777402871
Wed Dec 14 14:20:43 2011
Database mounted in Exclusive Mode
Completed: alter database mount
Wed Dec 14 14:22:57 2011
alter database open resetlogs
Wed Dec 14 14:22:57 2011
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 4982391
Wed Dec 14 14:23:15 2011
Setting recovery target incarnation to 2
Wed Dec 14 14:23:15 2011
Assigning activation ID 1777402871 (0x69f103f7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=6585
Wed Dec 14 14:23:15 2011
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=6587
Wed Dec 14 14:23:16 2011
ARC0: STARTING ARCH PROCESSES
Wed Dec 14 14:23:16 2011
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Wed Dec 14 14:23:16 2011
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: /u02/sheng/REDO03.LOG
Successful open of redo thread 1
Wed Dec 14 14:23:16 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 14 14:23:16 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=6589
Wed Dec 14 14:23:16 2011
SMON: enabling cache recovery
Wed Dec 14 14:23:18 2011
Errors in file /u01/pp/oracle/admin/sheng/udump/sheng_ora_6467.trc:
ORA-00600: internal error code, arguments: [2662], [0], [4982397], [0], [4982439], [4194313], [], []
Wed Dec 14 14:23:19 2011
Errors in file /u01/pp/oracle/admin/sheng/udump/sheng_ora_6467.trc:
ORA-00600: internal error code, arguments: [2662], [0], [4982397], [0], [4982439], [4194313], [], []
Wed Dec 14 14:23:19 2011
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 6467
ORA-1092 signalled during: alter database open resetlogs...
下面修改下面参数：
*.undo_management=MANUAL
*.undo_tablespace=SYSTEM
下面启动，报/u02/sheng/system01.dbf' need recover
好，下面进行recover:
sql>recover datafile 1
midiea recovered
同样的道理，recover datafile 2
把其他的全部recover
select name,status from v$datafile 可以看到在mount 状态下全部都online了。
重新启动：
alter database open,
数据库open 一会，又宕了。看一下alert 日志：
Wed Dec 14 14:42:06 2011
Errors in file /u01/pp/oracle/admin/sheng/bdump/sheng_dbw0_6980.trc:
ORA-00600: internal error code, arguments: [4194], [10], [28], [], [], [], [], []
Wed Dec 14 14:42:07 2011
Errors in file /u01/pp/oracle/admin/sheng/bdump/sheng_dbw0_6980.trc:
ORA-00600: internal error code, arguments: [4194], [10], [28], [], [], [], [], []
DBW0: terminating instance due to error 471
Instance terminated by DBW0, pid = 6980
好，下面重新建undo数据datafile:
以前碰到解决ora-00600的问题多是redo 和undo 的不一致造成的。
下面重新建一下：
drop tablespace undotbs1
create tablespace undo undotbs2 datafile '/u02/sheng/undotbs02.dbf' size 100m;
问题解决后，看一下alert 日志：
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Wed Dec 14 15:14:12 2011
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 14 15:14:13 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=9042
QMNC started with pid=19, OS id=9044
Wed Dec 14 15:14:14 2011
Completed: alter database open
发现警告中有个temp 没有显示建，下面建一下：
SQL> select name,status from v$datafile
NAME STATUS
---------------------------------------- -------
/u02/sheng/SYSTEM01.DBF SYSTEM
/u02/sheng/SYSAUX01.DBF ONLINE
/u02/sheng/USERS01.DBF ONLINE
/u02/sheng/UNDOTBS02.dbf ONLINE
SQL> select name from v$tablespace;
NAME
----------------------------------------
SYSTEM
SYSAUX
USERS
TEMP
UNDOTBS2
查看temp file 的情况，是不能用 v$datafile 的，而要用 v$tempfile:
SQL> select name,status from v$tempfile;
no rows selected
SQL> alter tablespace temp add tempfile '/u02/sheng/temp01.dbf' size 50m autoextend on next 5m maxsize unlimited;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
----------------------------------------
/u02/sheng/temp01.dbf