CONVERT DATABASE命令（一）

 

1,登陆数据库，检查数据库支持的可传输平台：

SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big

已选择17行。

2,下面可以执行CONVERT DATABASE命令了，这个命令要求源数据库和目标数据库的编码字符序一致。

SELECT tp.endian_format

FROM v$transportable_platform tp,

v$database d

WHERE tp.platform_name = d.platform_name;

3,且要求执行CONVERT DATABASE的源数据库处于只读状态：

SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup open read only
ORACLE 例程已经启动。

4,

RMAN> convert database
 new database tranexam
 skip offline
 transport script '/home/new/trans_script.sql'
 to platform 'Microsoft Windows IA (32-bit)'
 format '/home/new/%U_%T_%n_%N.dbf';

…

5,利用DBMS_FILE_TRANSFER包或ftp，完成数据文件和初始化参数文件的传输工作。

6,而且利用CONVERT DATABASE命令还得到了在目标数据库上加载打开数据库的脚本：/home/new/trans_script.sql，内容如下：

-- 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='/home/new/init_00lv5fo4_1_0_20101209_EXAMTRAN_DUMMY_TBS_0_0_0.dbf.ora'

CREATE CONTROLFILE REUSE SET DATABASE "EXAMTRAN" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 SIZE 50M,

  GROUP 2 SIZE 50M,

  GROUP 3 SIZE 50M

DATAFILE

  '/home/new/data_D-EXAM_I-1988230877_TS-SYSTEM_FNO-1_12lv5fo4_20101209_EXAMxxxx_SYSTEM.dbf',

  '/home/new/data_D-EXAM_I-1988230877_TS-UNDOTBS1_FNO-2_16lv5fup_20101209_EXAMxxxx_UNDOTBS1.dbf',  '/home/new/data_D-EXAM_I-1988230877_TS-SYSAUX_FNO-3_13lv5fo4_20101209_EXAMxxxx_SYSAUX.dbf',

  '/home/new/data_D-EXAM_I-1988230877_TS-USERS_FNO-4_15lv5ftg_20101209_EXAMxxxx_USERS.dbf',

  '/home/new/data_D-EXAM_I-1988230877_TS-EXAMPLE_FNO-5_14lv5fta_20101209_EXAMxxxx_EXAMPLE.dbf'

CHARACTER SET WE8ISO8859P1

;

 

-- Block change tracking was enabled, so re-enable it now.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE '/home/new/blockchange.file' REUSE;

 

-- 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

     SIZE 10485760  AUTOEXTEND ON NEXT 65536  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='/home/new/init_00lv5fo4_1_0_20101209_EXAMTRAN_DUMMY_TBS_0_0_0.dbf.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/home/new/init_00lv5fo4_1_0_20101209_EXAMTRAN_DUMMY_TBS_0_0_0.dbf.ora'

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

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

 

利用这个文件中给出的脚本就可以在目标服务器上打开数据库。

 

 

上一篇已经执行了CONVERT DATABASE的操作，且将数据文件和初始化参数文件都发送到指定的目标数据库上。

下面在目标服务器上打开数据库：

$ cd C:\app\Administrator\oradata\examtran
$ ls -l
total 2904968
7,
$ more init_00lv5fo4_1_0_20101209_EXAMTRAN_DUMMY_TBS_0_0_0.dbf.ora
# Please change the values of the following parameters:

 

  control_files            = "/home/new/cf_D-EXAMTRAN_id-1988230877_00lv5fo4_20101209_EXAMTRAN_DUMMY_TBS_0_0_0.dbf"

 

  db_create_file_dest      = "/home/new/oradata"

 

  db_recovery_file_dest    = "/home/new/flash_recovery_area"

 

  db_recovery_file_dest_size= 1073741824

 

  background_dump_dest     = "/home/new/bdump"

 

  user_dump_dest           = "/home/new/udump"

 

  core_dump_dest           = "/home/new/cdump"

 

  audit_file_dest          = "/home/new/adump"

 

  db_name                  = "EXAMTRAN"

 

 

 

# Please review the values of the following parameters:

 

  __shared_pool_size       = 75497472

__large_pool_size        = 4194304

 

  __java_pool_size         = 4194304

 

  __streams_pool_size      = 4194304

 

  __db_cache_size          = 75497472

 

  remote_login_passwordfile= "EXCLUSIVE"

 

  db_domain                = ""

 

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=examXDB)"

 

 

 

# The values of the following parameters are from source database:

 

  processes                = 150

 

  shared_pool_size         = 0

 

  large_pool_size          = 0

 

  java_pool_size           = 0

  streams_pool_size        = 0

 

  resource_manager_plan    = "SYSTEM_PLAN"

 

  sga_target               = 167772160

 

  db_block_size            = 8192

 

  db_cache_size            = 4194304

 

  compatible               = "10.2.0.1.0"

 

# log_archive_dest_1       = "LOCATION=/home/new"

 

  log_archive_format       = "%t_%s_%r.dbf"

 

  db_file_multiblock_read_count= 16

 

  undo_management          = "AUTO"

 

  undo_tablespace          = "UNDOTBS1"

 

  db_block_checking        = "TRUE"

 

  job_queue_processes      = 10

cursor_sharing           = "SIMILAR"

 

  open_cursors             = 300

 

  pga_aggregate_target     = 55574528

 

根据具体的需要修改上面的初始化参数，并利用这个初始化参数启动数据库，修改后的初始化参数如下：

$ more initEXAMTRAN.ora
# Please change the values of the following parameters:

 

  control_files            = "C:\app\Administrator\oradata\examtran\control01.ctl"

 

  db_create_file_dest      = "C:\app\Administrator\oradata"

 

 # db_recovery_file_dest    = "C:\app\Administrator\oradata\examtran"

 

#  db_recovery_file_dest_size= 1073741824

 

  background_dump_dest     = "C:\app\Administrator\product\11.2.0\dbhome_1\admin\examtran\bdump"

 

  user_dump_dest           = "C:\app\Administrator\product\11.2.0\dbhome_1\admin\examtran\udump"

 

  core_dump_dest           = "C:\app\Administrator\product\11.2.0\dbhome_1\admin\examtran\cdump"

 

  audit_file_dest          = "C:\app\Administrator\product\11.2.0\dbhome_1\admin\examtran\adump"

 

  db_name                  = "EXAMTRAN"

 

 

 

# Please review the values of the following parameters:

 

  __shared_pool_size       = 75497472

 

  __large_pool_size        = 4194304

 

  __java_pool_size         = 4194304

 

  __streams_pool_size      = 4194304

 

  __db_cache_size          = 75497472

 

  remote_login_passwordfile= "EXCLUSIVE"

 

  db_domain                = ""

 

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=examXDB)"

 

 

 

# The values of the following parameters are from source database:

 

  processes                = 150

 

  shared_pool_size         = 0

 

  large_pool_size          = 0

 

  java_pool_size           = 0

 

  streams_pool_size        = 0

 

  resource_manager_plan    = "SYSTEM_PLAN"

 

  sga_target               = 167772160

 

  db_block_size            = 8192

 

  db_cache_size            = 4194304

 

  compatible               = "10.2.0.1.0"

 

 log_archive_dest_1       = "LOCATION=C:\app\Administrator\oradata\examtran"

 

  log_archive_format       = "%t_%s_%r.dbf"

 

  db_file_multiblock_read_count= 16

 

  undo_management          = "AUTO"

 

  undo_tablespace          = "UNDOTBS1"

 

  db_block_checking        = "TRUE"

 

  job_queue_processes      = 10

 

  cursor_sharing           = "SIMILAR"

 

  open_cursors             = 300

 

  pga_aggregate_target     = 55574528

8,创建数据库dump文件所需的目录：

C:\app\Administrator\product\11.2.0\dbhome_1\admin\examtran

mkdir bdump cdump adump udump

9,下面启动数据库：

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 6月 29 16:37:13 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE=C:\app\Administrator\oradata\examtran\initEXAMTRAN.ora

ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 2074600 bytes
Variable Size 167774232 bytes
Database Buffers 427819008 bytes
Redo Buffers 6311936 bytes
SQL> CREATE SPFILE FROM PFILE = ' C:\app\Administrator\oradata\examtran\initEXAMTRAN.ora';

File created.

SQL> STARTUP FORCE NOMOUNT
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 2074600 bytes
Variable Size 167774232 bytes
Database Buffers 427819008 bytes
Redo Buffers 6311936 bytes
-- 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='C:\app\Administrator\oradata\examtran\initEXAMTRAN.ora'

CREATE CONTROLFILE REUSE SET DATABASE "EXAMTRAN" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 SIZE 50M,

  GROUP 2 SIZE 50M,

  GROUP 3 SIZE 50M

DATAFILE

  'C:\app\Administrator\oradata\examtran\SYSTEM.dbf',

  'C:\app\Administrator\oradata\examtran\UNDOTBS1.dbf',

  'C:\app\Administrator\oradata\examtran\SYSAUX.dbf',

  'C:\app\Administrator\oradata\examtran\USERS.dbf',

  'C:\app\Administrator\oradata\examtran\EXAMPLE.dbf'

CHARACTER SET WE8ISO8859P1

;

 

-- Block change tracking was enabled, so re-enable it now.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE 'C:\app\Administrator\oradata\examtran\blockchange.file' REUSE;

 

-- 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

     SIZE 10485760  AUTOEXTEND ON NEXT 65536  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='C:\app\Administrator\oradata\examtran\initEXAMTRAN.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='C:\app\Administrator\oradata\examtran\initEXAMTRAN.ora'

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

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

 

 

 

由于数据库的从32位变为64位，且版本都发生了变化，需要执行下面的脚本：

SQL> @?/rdbms/admin/utlip.sql

5139 rows updated.


Commit complete.

.
.
.
System altered.

SQL> @?/rdbms/admin/utlirp.sql
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if there the database was not opened in UPGRADE mode
DOC>
DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC> re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
.
.
.
SQL> Rem ===========================================================================
SQL> Rem END utlip.sql
SQL> Rem ===========================================================================
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

SQL> @?/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

.
.
.DOC>#######################################################################
DOC>#
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************

SQL> @?/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL*PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
SQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem gviswana 06/25/02 - Add documentation
SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem rburns 11/12/01 - validate all components after compiles
SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
SQL> Rem rburns 09/29/01 - use 9.2.0
SQL> Rem rburns 09/20/01 - add check for CATPROC valid
SQL> Rem rburns 07/06/01 - get version from instance view
SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem skabraha 09/25/00 - validate is now a keyword
SQL> Rem kosinski 06/14/00 - Persistent parameters
SQL> Rem skabraha 06/05/00 - validate tables also
SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem rshaikh 09/22/99 - quote name for recompile
SQL> Rem ncramesh 08/04/98 - change for sqlplus
SQL> Rem usundara 06/03/98 - merge from 8.0.5
SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
SQL> Rem Mark Ramacher (mramache) was the original
SQL> Rem author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
.
.
.
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 2074600 bytes
Variable Size 243271704 bytes
Database Buffers 352321536 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> alter database datafile 'MISSING00004' offline drop;

Database altered.

至此，目标服务器上的数据库顺利打开。