oracle 備用恢復

傳輸資料庫的第一步是將源資料庫置於READ ONLY模式：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount   
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  1262044 bytes
Variable Size             385879588 bytes
Database Buffers         1207959552 bytes
Redo Buffers               15511552 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

第二步利用dbms_tdb包檢查資料庫能否被傳輸以及列出外部表和DIRECTORY等無法傳輸的物件資訊（測試案例採用Linux遷移至Windows平臺）：

[php]

SQL> set serveroutput on
SQL> declare
  2      db_ready boolean;
  3    begin
  4      /* db_ready is ignored, but with SERVEROUTPUT set to ON any
  5       * conditions preventing transport will be output to console */
  6      db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',
  7          dbms_tdb.skip_none);
  8    end;
  9  /

PL/SQL procedure successfully completed.

SQL>  declare
  2       external boolean;
  3    begin
  4      /* value of external is ignored, but with SERVEROUTPUT set to ON
  5       * dbms_tdb.check_external displays report of external objects
  6       * on console */
  7      external := dbms_tdb.check_external;
  8    end;
  9  
10  /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
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.

環境檢查完畢後就可以利用RMAN的convert database命令在源資料庫進行轉換（convert database也可以在目標資料庫進行，具體可以參考文件）：

[php]

SQL> host rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 15:23:16 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: DBTEST (DBID=1018019231)

RMAN> CONVERT DATABASE NEW DATABASE 'newdb'
2>         transport script '/home/oracle/liuyun/transportdb.sql'
3>         to platform 'Microsoft Windows IA (32-bit)'
4>         db_file_name_convert '/opt/oracle/oradata/dbtest/' '/home/oracle/liuyun/';

Starting convert at 28-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=/opt/oracle/oradata/dbtest/system01.dbf
converted datafile=/home/oracle/liuyun/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=/opt/oracle/oradata/dbtest/sysaux01.dbf
converted datafile=/home/oracle/liuyun/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/opt/oracle/oradata/dbtest/example01.dbf
converted datafile=/home/oracle/liuyun/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=/opt/oracle/oradata/dbtest/undotbs01.dbf
converted datafile=/home/oracle/liuyun/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/opt/oracle/oradata/dbtest/users01.dbf
converted datafile=/home/oracle/liuyun/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /home/oracle/liuyun/transportdb.sql on the target platform to create database
Edit init.ora file /opt/oracle/product/10.2/dbs/init_00ildsr9_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 28-JUN-07
..
[/php]

在這一步中new database表示目標資料庫的名字，transport script指定生成建庫指令碼的位置，to platform表示目標資料庫的平臺，db_file_name_convert指定源資料庫資料檔案所在的位置以及轉換後資料檔案存放的目錄。生成的PFILE為/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora，可以根據目標資料庫的情況編輯，編輯後的內容如下：

newdb.__db_cache_size=12582912
newdb.__java_pool_size=4194304
newdb.__large_pool_size=4194304
newdb.__shared_pool_size=58720256
newdb.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\newdb\adump'
*.background_dump_dest='D:\oracle\admin\newdb\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\oradata\newdb\control01.ctl'
*.core_dump_dest='D:\oracle\admin\newdb\cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='newdb'
*.db_recovery_file_dest_size=1
*.db_recovery_file_dest=''
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=d:\oracle\oradata\newdb\archive'
*.log_archive_dest_2='service=standby'
*.log_archive_dest_state_2='DEFER'
*.nls_language='AMERICAN'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.query_rewrite_integrity='STALE_TOLERATED'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=80M
*.star_transformation_enabled='FALSE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\newdb\udump'






生成的transportdb.sql內容如下，可以根據目標資料庫的情況更改：

[php]
-- 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='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-47_T-1_A-625156386_00ildsr9'  SIZE 50M,
  GROUP 2 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-45_T-1_A-625156386_00ildsr9'  SIZE 50M,
  GROUP 3 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-46_T-1_A-625156386_00ildsr9'  SIZE 50M
DATAFILE
  '/home/oracle/liuyunsystem01.dbf',
  '/home/oracle/liuyunundotbs01.dbf',
  '/home/oracle/liuyunsysaux01.dbf',
  '/home/oracle/liuyunusers01.dbf',
  '/home/oracle/liuyunexample01.dbf'
CHARACTER SET ZHS16GBK;

-- 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 '/opt/oracle/product/10.2/dbs/data_D-NEWDB_I-1018019231_TS-TEMP_FNO-1_00ildsr9'
     SIZE 20971520  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='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

..

[/php]

更改後的指令碼內容如下：

STARTUP NOMOUNT PFILE='d:\init.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'd:\oracle\oradata\newdb\redo01.log'  SIZE 5M,
  GROUP 2 'd:\oracle\oradata\newdb\redo02.log'  SIZE 5M
DATAFILE
  'd:\oracle\oradata\newdb\system01.dbf',
  'd:\oracle\oradata\newdb\undotbs01.dbf',
  'd:\oracle\oradata\newdb\sysaux01.dbf',
  'd:\oracle\oradata\newdb\users01.dbf',
  'd:\oracle\oradata\newdb\example01.dbf'
CHARACTER SET ZHS16GBK;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE 'd:\oracle\oradata\temp01.dbf'
     SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='d:\init.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='d:\init.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;\

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-1129295/，如需轉載，請註明出處，否則將追究法律責任。