1. 将Solaris的oracle数据库归档日志关闭，并将数据库以只读方式启动
ssh [email protected]
su - oracle
sqlplus / as sysdba
shutdown immediate;
startup mount;
archive log list;
alter database noarchivelog;
shutdown normal;
startup mount;
alter database open read only;
等dbf数据文件上传完成后，需要恢复oracle数据库的归档日志模式
startup mount;
alter database archivelog;
alter database open;
archive log list;
alter system switch logfile;
2. 查询需要做convert转换的表空间
su - oracle
sqlplus / as sysdba
select FILE_NAME "Datafiles requiring Conversion" from DBA_DATA_FILES
where TABLESPACE_NAME in (select distinct TABLESPACE_NAME
from DBA_ROLLBACK_SEGS);
3. 在Solaris下生成oracle数据库的control trace文件
su - oracle
sqlplus / as sysdba
SQL>alter database backup controlfile to trace;
SQL>quit
查看已生成的trace文件
ls -ltr /soft/oracle/product/10.2/admin/testdb/udump
从trace文件中取如下代码，并按照AIX生产环境修改好所有路径，备用：
--STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORADB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 11685
LOGFILE
GROUP 5 (
'/oradata2/oradb2/redo5a.log',
'/oradata2/oradb2/redo5b.log'
) SIZE 500M,
GROUP 6 (
'/oradata2/oradb2/redo6a.log',
'/oradata2/oradb2/redo6b.log'
) SIZE 500M,
GROUP 7 (
'/oradata2/oradb2/redo7a.log',
'/oradata2/oradb2/redo7b.log'
) SIZE 500M,
GROUP 8 (
'/oradata2/oradb2/redo8a.log',
'/oradata2/oradb2/redo8b.log'
) SIZE 500M,
GROUP 9 (
'/oradata2/oradb2/redo9a.log',
'/oradata2/oradb2/redo9b.log'
) SIZE 500M
-- STANDBY LOGFILE
DATAFILE
'/oradata2/oradb2/system01.dbf',
'/oradata2/oradb2/undotbs01.dbf',
'/oradata2/oradb2/sysaux01.dbf',
'/oradata2/oradb2/users01.dbf',
'/oradata2/oradb2/example01.dbf',
'/oradata2/oradb2/PC8_USERS.dbf',
'/oradata2/oradb2/tsd_stage_one_1.dbf',
'/oradata2/oradb2/tivoliorts.dbf',
'/oradata2/oradb2/tsd_stage_two_1.dbf',
'/oradata2/oradb2/tsd_hub_data_1.dbf',
'/oradata2/oradb2/tsd_ods_data_1.dbf',
'/oradata2/oradb2/tsd_hub_idx_1.dbf',
'/oradata2/oradb2/tsd_ods_idx_1.dbf',
'/oradata2/oradb2/tsd_helper_1.dbf',
'/oradata2/oradb2/tsd_ods_data_2.dbf',
'/oradata2/oradb2/callcenter.dbf',
'/oradata2/oradb2/repository.dbf',
'/oradata2/oradb2/tsd_stage_error_data_1.dbf',
'/oradata2/oradb2/tsd_helper_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_data_1.dbf',
'/oradata2/oradb2/tsd_tmp_ods_idx_1.dbf',
'/oradata2/oradb2/tsd_ods_idx_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_data_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_idx_2.dbf',
'/oradata2/oradb2/tsd_ods_old_data_1.dbf',
'/oradata2/oradb2/tsd_hub_data_2.dbf',
'/oradata2/oradb2/tsd_stage_one_2.dbf',
'/oradata2/oradb2/tsd_ods_data_3.dbf',
'/oradata2/oradb2/tsd_hub_idx_2.dbf',
'/oradata2/oradb2/tsd_ods_data_4.dbf',
'/oradata2/oradb2/tsd_stage_two_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_data_3.dbf'
CHARACTER SET UTF8
;
4. 在Solaris环境下生成oracle数据库的pfile配置信息
su - oracle
sqlplus / as sysdba
SQL>create pfile = '/export/home/oracle/initoradb2.ora' from spfile;
SQL>quit
5. 将pfile文件/export/home/oracle/initoradb2.ora上传到AIX生产环境的相应目录
su - oracle
scp initoradb2.ora [email protected]:/soft/oracle/product/10.2.0/dbs
6. AIX生产环境下编辑/soft/oracle/product/10.2.0/dbs/initoradb2.ora文件
这里主要是更改路径信息，以匹配AIX生产环境的路径情况，修改后的内容如下：
oradb2.__db_cache_size=989855744
oradb2.__java_pool_size=16777216
oradb2.__large_pool_size=16777216
oradb2.__shared_pool_size=1006632960
oradb2.__streams_pool_size=33554432
*._kgl_large_heap_warning_threshold=8388608
*.audit_file_dest='/soft/oracle/admin/oradb2/adump'
*.background_dump_dest='/soft/oracle/admin/oradb2/bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=15
*.control_files='/oradata2/oradb2/control01.ctl','/oradata2/oradb2/control02.ctl','/oradata2/oradb2/control03.ctl'
*.core_dump_dest='/soft/oracle/admin/oradb2/cdump'
*.db_block_size=8192
*.db_domain='oradb2.hqdomain.com.cn'
*.db_file_multiblock_read_count=16
*.db_name='oradb'
*.db_recovery_file_dest_size=69793218560
*.db_recovery_file_dest='/oradata2/flash_recovery_area/ORADB2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)'
*.job_queue_processes=400
*.nls_date_format='YYYY-MM-DD:HH24:MI:SS'
*.nls_territory='AMERICA'
*.nls_timestamp_tz_format='YYYY-MM-DD hh24:mi:ss.ff'
*.open_cursors=300
*.pga_aggregate_target=1310720000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=TRUE
*.service_names='oradb2.hqdomain.com.cn'
*.sga_max_size=2194304000#internally adjusted
*.sga_target=2074210304
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/soft/oracle/admin/oradb2/udump'
7. 创建AIX生产环境上oradb数据库所需路径
su - oracle
mkdir -p /soft/oracle/admin/oradb2/adump
mkdir -p /soft/oracle/admin/oradb2/bdump
mkdir -p /soft/oracle/admin/oradb2/cdump
mkdir -p /soft/oracle/admin/oradb2/udump
创建用于存放oracle数据文件的文件系统/oradata2, 并更改用户属组
chown -R oracle:oinstall /oradata2
chmod 775 /oradata2
继续创建oracle数据库所需的下面路径
su - oracle
mkdir -p /oradata2/oradb2
mkdir -p /oradata2/backup
mkdir -p /oradata2/backup/oradb2_dump
mkdir -p /oradata2/flash_recovery_area/ORADB2
8. 以nomount方式启动AIX生产环境上的oradb数据库
su - oracle
sqlplus / as sysdba
startup nomount pfile='/soft/oracle/product/10.2.0/dbs/initoradb2.ora'
9. 确保Solaris下oracle数据库的dbf数据文件都已经上传到AIX生产环境的相应路径下
获取Solaris环境下oracle数据库oradb的datafile(不包括redofile, tempfile)
下面的操作要提前完成
将Solaris下oradb数据库除temp*.dbf以外的32个dbf数据文件从Solaris环境下上传到AIX生产环境的/oradata2/oradb2路径下
将AIX生产环境上/oradata2/oradb2下的system01.dbf, undotbs01.dbf文件移动到AIX生产环境的/oradata2/sourcedata路径下
10. 使用rman convert database功能对SYSTEM, UNDO表空间进行转换
su - oracle
rman target /
CONVERT DATAFILE '/oradata2/sourcedata/system01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)'
FORMAT '/oradata2/oradb2/system01.dbf';
CONVERT DATAFILE '/oradata2/sourcedata/undotbs01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)'
FORMAT '/oradata2/oradb2/undotbs01.dbf';
11. AIX生产环境下创建control file
su - oracle
sqlplus / as sysdba
拷贝下面内容，并执行
CREATE CONTROLFILE REUSE DATABASE "ORADB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 11685
LOGFILE
GROUP 5 (
'/oradata2/oradb2/redo5a.log',
'/oradata2/oradb2/redo5b.log'
) SIZE 500M,
GROUP 6 (
'/oradata2/oradb2/redo6a.log',
'/oradata2/oradb2/redo6b.log'
) SIZE 500M,
GROUP 7 (
'/oradata2/oradb2/redo7a.log',
'/oradata2/oradb2/redo7b.log'
) SIZE 500M,
GROUP 8 (
'/oradata2/oradb2/redo8a.log',
'/oradata2/oradb2/redo8b.log'
) SIZE 500M,
GROUP 9 (
'/oradata2/oradb2/redo9a.log',
'/oradata2/oradb2/redo9b.log'
) SIZE 500M
-- STANDBY LOGFILE
DATAFILE
'/oradata2/oradb2/system01.dbf',
'/oradata2/oradb2/undotbs01.dbf',
'/oradata2/oradb2/sysaux01.dbf',
'/oradata2/oradb2/users01.dbf',
'/oradata2/oradb2/example01.dbf',
'/oradata2/oradb2/PC8_USERS.dbf',
'/oradata2/oradb2/tsd_stage_one_1.dbf',
'/oradata2/oradb2/tivoliorts.dbf',
'/oradata2/oradb2/tsd_stage_two_1.dbf',
'/oradata2/oradb2/tsd_hub_data_1.dbf',
'/oradata2/oradb2/tsd_ods_data_1.dbf',
'/oradata2/oradb2/tsd_hub_idx_1.dbf',
'/oradata2/oradb2/tsd_ods_idx_1.dbf',
'/oradata2/oradb2/tsd_helper_1.dbf',
'/oradata2/oradb2/tsd_ods_data_2.dbf',
'/oradata2/oradb2/callcenter.dbf',
'/oradata2/oradb2/repository.dbf',
'/oradata2/oradb2/tsd_stage_error_data_1.dbf',
'/oradata2/oradb2/tsd_helper_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_data_1.dbf',
'/oradata2/oradb2/tsd_tmp_ods_idx_1.dbf',
'/oradata2/oradb2/tsd_ods_idx_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_data_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_idx_2.dbf',
'/oradata2/oradb2/tsd_ods_old_data_1.dbf',
'/oradata2/oradb2/tsd_hub_data_2.dbf',
'/oradata2/oradb2/tsd_stage_one_2.dbf',
'/oradata2/oradb2/tsd_ods_data_3.dbf',
'/oradata2/oradb2/tsd_hub_idx_2.dbf',
'/oradata2/oradb2/tsd_ods_data_4.dbf',
'/oradata2/oradb2/tsd_stage_two_2.dbf',
'/oradata2/oradb2/tsd_tmp_ods_data_3.dbf'
CHARACTER SET UTF8
;
12. 打开AIX生产环境oradb数据库，并给TEMP表空间新增tempfile
su - oracle
sqlplus / as sysdba
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/oradb2/temp01.dbf' size 2048m autoextend on;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/oradb2/temp02.dbf' size 2048m autoextend on;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/oradb2/temp03.dbf' size 2048m autoextend on;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/oradb2/temp04.dbf' size 2048m autoextend on;
13. 执行UTLIRP and UTLRP以完成数据库转换，处理该过程中的报错
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP UPGRADE;
SQL> @ ?/rdbms/admin/utlirp
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
create spfile from pfile;
shutdown immediate;
startup mount;
alter database open;
alter system set sga_max_size=12g scope=spfile;
shutdown immediate;
startup mount;
alter database open;
alter system set sga_target=10g scope=both;
alter system set pga_aggregate_target=4G scope=both;
SQL> @ ?/rdbms/admin/utlrp
select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;
14. AIX生产环境下配置oracle数据库sys用户的密码
su - oracle
orapwd file='/soft/oracle/product/10.2.0/dbs/orapworadb2' password=sys entries=5 force=y
15. 配置listener.ora和tnsnames.ora
从Solaris环境中将listener.ora, tnsnames.ora两个文件拷贝到AIX生产环境的$ORACLE_HOME/network/admin下
ssh [email protected]
scp $ORACLE_HOME/network/admin/listener.ora [email protected]:/soft/oracle/product/10.2.0/network/admin
scp $ORACLE_HOME/network/admin/tnsnames.ora [email protected]:/soft/oracle/product/10.2.0/network/admin
编辑AIX生产环境下listener.ora, tnsnames.ora这两个文件，主要是更改IP地址以及ORACLE_HOME路径等
启动监听，并测试tnsping是否正常
lsnrctl start
16. 重新配置AIX生产环境oracle数据库的DIRECTORY
create or replace directory EXP as '/oradata2/backup';
//这个临时用一下，最终需要将192.168.9.98:/oradbarch文件系统mount到AIX生产环境上的/home/etluser/backup
drop directory INFOUSER_BACKUP;
drop directory DATA_PUMP_DIR;
create or replace directory QUEST_SOO_UDUMP_DIR as '/soft/oracle/admin/oradb2/udump/';
create or replace directory QUEST_SOO_BDUMP_DIR as '/soft/oracle/admin/oradb2/bdump/';
create or replace directory QUEST_SOO_ADUMP_DIR as '/soft/oracle/admin/oradb2/adump/';
create or replace directory QUEST_SOO_CDUMP_DIR as '/soft/oracle/admin/oradb2/cdump/';
create or replace directory SUBDIR as '/soft/oracle/product/10.2.0/demo/schema/order_entry/2002/Sep';
create or replace directory XMLDIR as '/soft/oracle/product/10.2.0/demo/schema/order_entry/';
create or replace directory MEDIA_DIR as '/soft/oracle/product/10.2.0/demo/schema/product_media/';
create or replace directory LOG_FILE_DIR as '/soft/oracle/product/10.2.0/demo/schema/log/';
create or replace directory DATA_FILE_DIR as '/soft/oracle/product/10.2.0/demo/schema/sales_history/';
17. 删除AIX生产环境oracle数据库pc86_domain用户下的所有对象，并且重置pc86_domain用户的密码
su - oracle
sqlplus / as sysdba
alter user pc86_domain identified by "pc86_domain";
pc86_domain用户下对象删除完以后，可以通过下面命令检查是否还有其他对象存在
select * from dba_objects where owner='PC8_DOMAIN';
sqlplus pc86_domain/pc86_domain@oradb2
purge recyclebin
18. 开启AIX生产环境oracle数据库的归档日志
startup mount;
alter database archivelog;
alter database open;
archive log list;
alter system switch logfile;