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;

10-09 16:20