RMAN之CONVERT整庫遷移

rman中的convert命令同樣可以遷移資料檔案，表空間和整個資料庫，此處演示整庫遷移的過程：

檢視一下支援convert有哪些平臺：

SQL> select PLATFORM_NAME,ENDIAN_FORMAT from v$DB_TRANSPORTABLE_PLATFORM;

PLATFORM_NAME ENDIAN_FORMAT

---------------------------------------- ----------------------------------------

Microsoft Windows IA (32-bit) Little

Linux IA (32-bit) Little

HP Tru64 UNIX Little

Linux IA (64-bit) Little

HP Open VMS Little

Microsoft Windows IA (64-bit) Little

Linux 64-bit for AMD Little

Microsoft Windows 64-bit for AMD Little

Solaris Operating System (x86) Little

9 rows selected.

將源庫置於READ ONLY

SQL> startup open read only;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 100665588 bytes

Database Buffers 62914560 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

檢查是否支援跨平臺傳輸：

SQL> ed

1 declare

2 db_ready boolean;

3 begin

4 db_ready := dbms_tdb.check_db('Linux IA (32-bit)', 2);

5 if(db_ready) then

6 dbms_output.put_line('true');

7 else

8 dbms_output.put_line('false');

9 end if;

10* end;

SQL> set serverout on;

SQL> /

true

PL/SQL procedure successfully completed.

返回true視為正確

檢查是否存在外部物件：

SQL> declare

2 external boolean;

3 begin

4 external := dbms_tdb.check_external;

5 end;

6 /

The following external tables exist in the database:

SH.SALES_TRANSACTIONS_EXT

The following directories exist in the database:

SYS.EXPDP_DIR2, SYS.EXPDP_DIR, SYS.DATA_PUMP_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR,

SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR

The following BFILEs exist in the database:

PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

以上輸出的物件將不會傳輸到新的資料庫，如果有必要就需DBA手工的將這些物件建立到新的資料庫中

執行整庫轉換：

RMAN> convert database new database 'orcl2'

2> transport script '/u01/rman_convert/whole_db_script.sql'

3> to platform 'Linux IA (32-bit)'

4> db_file_name_convert '/u01/app/oracle/oradata/orcl' '/u01/app/oracle/oradata/orcl2'

5> ;

.

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04

Run SQL script /u01/rman_convert/whole_db_script.sql on the target platform to create database

Edit init.ora file /u01/app/oracle/10.2.0/db_1/dbs/init_00p5armv_1_0.ora. This PFILE will be used to create the database on the target platform

To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform

To change the internal database identifier, use DBNEWID Utility

Finished backup at 09-APR-14

建立資料檔案和trace檔案的儲存路徑：

[oracle@localhost ~]$ mkdir -p /u01/app/oracle/oradata/orcl2

[oracle@localhost ~]$ mkdir -p /u01/app/oracle/admin/orcl2/{a,b,c,u}dump

將所以的轉換檔案和執行指令碼複製到目標資料庫：

[oracle@localhost ~]$ scp 10.37.100.100:/u01/app/oracle/oradata/orcl2/* /u01/app/oracle/oradata/orcl2/

oracle@10.37.100.100's password:

example01.dbf 100% 100MB 16.7MB/s 00:06

sysaux01.dbf 100% 330MB 13.8MB/s 00:24

system01.dbf 100% 500MB 11.4MB/s 00:44

tts01.dbf 100% 20MB 20.0MB/s 00:01

tts02.dbf 100% 20MB 20.0MB/s 00:00

undotbs001.dbf 100% 100MB 14.3MB/s 00:07

users01.dbf 100% 183MB 15.2MB/s 00:12

[oracle@localhost ~]$ scp 10.37.100.100:/u01/rman_convert/* /u01/rman_convert/

oracle@10.37.100.100's password:

whole_db_script.sql 100% 2853 2.8KB/s 00:00

[oracle@localhost ~]$ scp 10.37.100.100:/u01/app/oracle/10.2.0/db_1/dbs/init_00p5armv_1_0.ora /u01/rman_convert/

oracle@10.37.100.100's password:

init_00p5armv_1_0.ora 100% 1540 1.5KB/s 00:00

修改cupy過來的引數檔案：

[oracle@localhost ~]$ cp /u01/rman_convert/init_00p5armv_1_0.ora /u01/rman_convert/init_orcl2.ora

[oracle@localhost ~]$ vi /u01/rman_convert/init_orcl2.ora

將相關路徑修改成自己的配置：

主要的有以下幾個：

control_files = "/u01/app/oracle/oradata/orcl2/control01.ctl","/u01/app/oracle/oradata/orcl2/control02.ctl","/u01/app/oracle/oradata/orcl2/control03.ctl"

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area/orcl2"

db_recovery_file_dest_size = 5368709120

background_dump_dest = "/u01/app/oracle/admin/orcl2/bdump"

user_dump_dest = "/u01/app/oracle/admin/orcl2/udump"

core_dump_dest = "/u01/app/oracle/admin/orcl2/cdump"

audit_file_dest = "/u01/app/oracle/admin/orcl2/adump"

db_name = "ORCL2"

修改需要執行的指令碼：（也可按照指令碼一步一步去修改執行）

-- 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 PFILE='/u01/rman_convert/init_orcl2.ora'

CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl2/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oracle/oradata/orcl2/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oracle/oradata/orcl2/redo03' SIZE 50M

DATAFILE

'/u01/app/oracle/oradata/orcl2/system01.dbf',

'/u01/app/oracle/oradata/orcl2/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl2/users01.dbf',

'/u01/app/oracle/oradata/orcl2/example01.dbf',

'/u01/app/oracle/oradata/orcl2/tts01.dbf',

'/u01/app/oracle/oradata/orcl2/tts02.dbf',

'/u01/app/oracle/oradata/orcl2/undotbs001.dbf'

CHARACTER SET WE8ISO8859P1

;

-- 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 '/u01/app/oracle/10.2.0/db_1/dbs/temp01.dbf'

SIZE 22020096 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

--

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt * Your database has been created successfully!

prompt * There are many things to think about for the new database. Here

prompt * is a checklist to help you stay on track:

prompt * 1. You may want to redefine the location of the directory objects.

prompt * 2. You may want to change the internal database identifier (DBID)

prompt * or the global database name for this database. Use the

prompt * NEWDBID Utility (nid).

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

STARTUP UPGRADE PFILE='/u01/rman_convert/init_orcl2.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/u01/rman_convert/init_orcl2.ora'

-- The following step will recompile all PL/SQL modules.

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

修改指令碼相對操作簡單，但要認真，不然很出錯後差錯就麻煩了

設定新的環境變數，進入SQL*plus命令列

[oracle@localhost ~]$ export ORACLE_SID=orcl2

[oracle@localhost ~]$ sqlplus / as sysdba

執行修改後的指令碼：

SQL> @/u01/rman_convert/whole_db_script.sql

期間會重啟幾次資料庫，等待時間較長，要有耐心，尤其是這個時間點：

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

SQL>

SQL> DECLARE

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

6 /

執行完成後測試結果：

SQL> conn scott/oracle
Connected.
SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST

此時資料庫複製已經完成，需要的話別忘了去建立前面檢查時不能簽約的外部物件，配置口令檔案和監聽等。

RMAN之CONVERT整庫遷移

相關文章