系统环境：
主数据库：
操作系统：REDHAT-AS4-U7
数据库：ORACLE10.2.0.4
主机名称：db-standby
ip地址：192.168.1.221
目录结构：
软件目录：/opt/oracle/product/10.2.0
数据文件目录：/home/oradatabase/datafile/racbydb
ORACLE_SID:racbydb
目标数据库：
操作系统：REDHAT-AS5
数据库：ORACLE10.2.0.4
主机名称：dmtzlk
ip地址：192.168.1.230
软件目录：/opt/oracle/product/10.2.0
数据文件目录：/u00/oracle/standbydb
ORACLE_SID:standbydb
第一步：手动准备目标数据库存放路径、配置信息
1、手动创建数据库信息目录
[oracle@dmtzlk arch]$ cd /opt
[oracle@dmtzlk opt]$ ls
oracle oraInventory ORCLfmap sun
[oracle@dmtzlk opt]$ cd oracle/
[oracle@dmtzlk oracle]$ ls
admin oradata oraInventory product
[oracle@dmtzlk oracle]$ cd admin/
[oracle@dmtzlk admin]$ pwd
/opt/oracle/admin
[oracle@dmtzlk admin]$ mkdir racbydb
[oracle@dmtzlk admin]$ cd racbydb
[oracle@dmtzlk admin]$ mkdir adump
[oracle@dmtzlk admin]$ mkdir bdump
[oracle@dmtzlk admin]$ mkdir cdump
[oracle@dmtzlk admin]$ mkdir dpdump
[oracle@dmtzlk admin]$ mkdir udump
[oracle@dmtzlk admin]$ mkdir pfile
[oracle@dmtzlk racbydb]$ ls
adump bdump cdump dpdump pfile udump
[oracle@dmtzlk racbydb]$ cd /home/oradatabase/datafile/
[oracle@dmtzlk admin]$ mkdir racbydb
[oracle@dmtzlk datafile]$ ls
racbydb
[oracle@dmtzlk datafile]$ cd
2、拷贝主数据库pfile文件到目标数据库，更改名称
登陆主数据库：
[oracle@db-standby ~]$ cd /opt/oracle/admin/standbydb/pfile/
[oracle@db-standby pfile]$ ls
init.ora.standbydb sqlnet.log
[oracle@db-standby pfile]$ scp init.ora.standbydb
password:
init.ora.standbydb 100% 2913 2.8KB/s 00:00
[oracle@db-standby pfile]$
登陆目标数据库更改pfile文件名称：
[oracle@dmtzlk ~]$ cd /opt/oracle/admin/racbydb/pfile/
[oracle@dmtzlk pfile]$ ls
init.ora.racbydb
[oracle@dmtzlk pfile]$ ls
init.ora.standbydb
[oracle@dmtzlk pfile]$ mv init.ora.standbydb ./init.ora.racbydb
[oracle@dmtzlk pfile]$ ls
init.ora.racbydb
3、修改pfile参数文件，如下：
原参数文件：
[oracle@dmtzlk pfile]$ cat init.ora.standbydb
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=/u00/arch'
log_archive_format=%t_%s_%r.dbf
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=sbydb
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/opt/oracle/admin/standbydb/bdump
core_dump_dest=/opt/oracle/admin/standbydb/cdump
user_dump_dest=/opt/oracle/admin/standbydb/udump
###########################################
# File Configuration
###########################################
control_files=("/u00/oracle/standbydb/control01.ctl",
"/u00/oracle/standbydb/control02.ctl", "/u00/oracle/standbydb/control03.ctl")
db_recovery_file_dest=/u00/flash
db_recovery_file_dest_size=2147483648
db_file_name_convert=('/u00/oracle/lizidb','/u00/oracle/standbydb')
log_file_name_convert=('/u00/oracle/lizidb','/u00/oracle/standbydb')
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.3.0
###########################################
# NLS
###########################################
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"
###########################################
# Processes and Sessions
###########################################
processes=150
sessions=170
###########################################
# SGA Memory
###########################################
sga_target=1073741824
###########################################
# Security and Auditing
###########################################
audit_file_dest=/opt/oracle/admin/standbydb/adump
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=standbydbXDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=357564416
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1
[oracle@dmtzlk pfile]$
修改后参数文件：
[oracle@dmtzlk pfile]$ cat init.ora.racbydb
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=/home/oradatabase/arch'
log_archive_format=%t_%s_%r.dbf
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=racbydb
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/opt/oracle/admin/racbydb/bdump
core_dump_dest=/opt/oracle/admin/racbydb/cdump
user_dump_dest=/opt/oracle/admin/racbydb/udump
###########################################
# File Configuration
###########################################
control_files=("/home/oradatabase/datafile/racbydb/control01.ctl",
"/home/oradatabase/datafile/racbydb/control02.ctl",
"/home/oradatabase/datafile/racbydb/control03.ctl")
db_recovery_file_dest=/home/oradatabase/flash
db_recovery_file_dest_size=2147483648
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.3.0
###########################################
# NLS
###########################################
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"
###########################################
# Processes and Sessions
###########################################
processes=150
sessions=170
###########################################
# SGA Memory
###########################################
sga_target=1073741824
###########################################
# Security and Auditing
###########################################
audit_file_dest=/opt/oracle/admin/racbydb/adump
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=standbydbXDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=357564416
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1
[oracle@dmtzlk pfile]$
蓝色标识部分为修改内容。
4、创建数据库密码文件，注意密码文件名称格式及sys密码要与主数据库相同：
orapwd file=/opt/oracle/product/10.2.0/dbs/orapwracbydb password=aaa entries=30
5、创建目标数据库的控制文件：
登陆主数据库执行控制文件trace备份命令：
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
这样，在udump文件夹中会生成创建trace文件的脚本，类似于如下：
[oracle@db-standby udump]$ cat standbydb_ora_24252.trc
/opt/oracle/admin/standbydb/udump/standbydb_ora_24252.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/10.2.0
System name: Linux
Node name: db-standby
Release: 2.6.9-78.EL
Version: #1 Wed Jul 9 15:26:38 EDT 2008
Machine: x86_64
Instance name: standbydb
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 24252, image: (TNS V1-V3)
*** ACTION NAME:() 2010-05-04 12:01:47.345
*** MODULE NAME:( (TNS V1-V3)) 2010-05-04 12:01:47.345
*** SERVICE NAME:(SYS$USERS) 2010-05-04 12:01:47.345
*** SESSION ID:(139.25454) 2010-05-04 12:01:47.345
*** 2010-05-04 12:01:47.345
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="sbydb"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u00/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED
NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- 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 "SBYDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u00/oracle/standbydb/redo01.log' SIZE 512M,
GROUP 2 '/u00/oracle/standbydb/redo02.log' SIZE 512M,
GROUP 3 '/u00/oracle/standbydb/redo03.log' SIZE 512M
-- STANDBY LOGFILE
DATAFILE
'/u00/oracle/standbydb/system01.dbf',
'/u00/oracle/standbydb/undotbs01.dbf',
'/u00/oracle/standbydb/sysaux01.dbf',
'/u00/oracle/standbydb/users01.dbf',
'/u00/oracle/standbydb/example01.dbf',
'/u00/oracle/standbydb/rman.dbf',
'/u00/oracle/standbydb/lizi.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u00/arch/1_1_711650606.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u00/arch/1_1_716749757.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u00/oracle/standbydb/temp01.dbf'
SIZE 210763776 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
[oracle@db-standby udump]$
复制如下一段脚本内容到目标数据库，保存
到/opt/oracle/admin/racbydb/create/create_control.sql脚本文件中：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SBYDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u00/oracle/standbydb/redo01.log' SIZE 512M,
GROUP 2 '/u00/oracle/standbydb/redo02.log' SIZE 512M,
GROUP 3 '/u00/oracle/standbydb/redo03.log' SIZE 512M
DATAFILE
'/u00/oracle/standbydb/system01.dbf',
'/u00/oracle/standbydb/undotbs01.dbf',
'/u00/oracle/standbydb/sysaux01.dbf',
'/u00/oracle/standbydb/users01.dbf',
'/u00/oracle/standbydb/example01.dbf',
'/u00/oracle/standbydb/rman.dbf',
'/u00/oracle/standbydb/lizi.dbf'
CHARACTER SET ZHS16GBK
;
修改脚本：
修改重建参数：将CREATE CONTROLFILE REUSE DATABASE 中的”REUSE”修改为”SET”
修改数据库名名称：将CREATE CONTROLFILE 中的”sbydb”修改为”racbydb”
修改每个数据文件和日志文件所在的路径，对应到目标数据库的路径
修改后如下：
[oracle@dmtzlk create]$ cat create_control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "RACBYDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oradatabase/datafile/racbydb/redo01.log' SIZE 512M,
GROUP 2 '/home/oradatabase/datafile/racbydb/redo02.log' SIZE 512M,
GROUP 3 '/home/oradatabase/datafile/racbydb/redo03.log' SIZE 512M
DATAFILE
'/home/oradatabase/datafile/racbydb/system01.dbf',
'/home/oradatabase/datafile/racbydb/undotbs01.dbf',
'/home/oradatabase/datafile/racbydb/sysaux01.dbf',
'/home/oradatabase/datafile/racbydb/users01.dbf',
'/home/oradatabase/datafile/racbydb/example01.dbf',
'/home/oradatabase/datafile/racbydb/rman.dbf',
'/home/oradatabase/datafile/racbydb/lizi.dbf'
CHARACTER SET ZHS16GBK
;
第二步、利用scp或者ftp拷贝主数据库数据文件及日志文件到目标数据库对应目录
注意：后来经实验证实，这一步要在创建控制文件之前拷贝！！
第三步、恢复目标数据库
1、创建目标数据库spfile件：
[oracle@dmtzlk racbydb]$ pwd
/home/oradatabase/datafile/racbydb
[oracle@dmtzlk racbydb]$ cd /opt/oracle/
[oracle@dmtzlk oracle]$ ls
admin oradata oraInventory product
[oracle@dmtzlk oracle]$ cd admin/
[oracle@dmtzlk admin]$ cd racbydb/pfile/
[oracle@dmtzlk pfile]$ ls
init.ora.racbydb
[oracle@dmtzlk pfile]$ pwd
/opt/oracle/admin/racbydb/pfile
[oracle@dmtzlk pfile]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 5 09:54:14 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from
pfile='/opt/oracle/admin/racbydb/pfile/init.ora.racbydb';
File created.
SQL> exit
Disconnected
2、创建目标数据库控制文件：
[oracle@dmtzlk pfile]$ cd ..
[oracle@dmtzlk racbydb]$ cd create/
[oracle@dmtzlk create]$ ls
create_control.sql
[oracle@dmtzlk create]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 5 09:56:03 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> @create_control.sql
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 264244808 bytes
Database Buffers 801112064 bytes
Redo Buffers 6295552 bytes
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dmtzlk create]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 5 10:26:42 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 264244808 bytes
Database Buffers 801112064 bytes
Redo Buffers 6295552 bytes
Database mounted.
Database opened.
SQL>
SQL>
注意：这一步中创建控制文件的脚本文件的语法结构一定要检查正确，注意修改过程中不
要出错，严格从参照trace备份生成的trace脚本中的结构，否则创建不成功。
2010.5.12
今天再次做了一次冷备份迁移，但在alter database open resetlogs;时候报错：
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: '/u01/oracle/app/testdb/system01.dbf'
仔细想想，经网上搜索找到原因，原来是我在原数据库生成create_control.sql脚本时候没有shutdown数据库，create完后，将其拷贝到目标数据库中，然后再进行数据文件的拷贝隔了一定期的时间，这时候数据文件中的scn已经改变，造成了前一之后控制文件同数据文件的scn不一致造成。
解决方法：
通过redo进行同步：
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 66602406 generated at 05/11/2010 16:33:38 needed for thread 1
ORA-00289: suggestion : /u05/oracle/app/testdb/arch/1_36_711650606.dbf
ORA-00280: change 66602406 for thread 1 is in sequence #36
Specify log: {=suggested | filename | AUTO | CANCEL}
/u04/oracle/app/testdb/redo01.log --这里是需要输入redo的路径
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/app/testdb/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 66602406 generated at 05/11/2010 16:33:38 needed for thread 1
ORA-00289: suggestion : /u05/oracle/app/testdb/arch/1_36_711650606.dbf
ORA-00280: change 66602406 for thread 1 is in sequence #36
Specify log: {=suggested | filename | AUTO | CANCEL}
/u04/oracle/app/testdb/redo02.log--这里是需要输入redo的路径
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1271564 bytes
Variable Size 264243444 bytes
Database Buffers 801112064 bytes
Redo Buffers 7114752 bytes
Database mounted.
Database opened.
SQL>
注意：输入redo路径时候可能需要多个redo
由于本次迁移是由64为迁到32位的架构上，所以需要数据库的降级，具体操作如下：
1、Startup upgrade
2、@@?/rdbms/admin/utlirp.sql;
3、Shutdown immediate
4、Startup
5、@@?/rdbms/admin/utlrp.sql;
6、Shutdown immediate
7、Startup
最主要的就是使用utlirp来把相关内容全部在32位平台下编译一遍。
