java convert ut_CONVERT DATABASE命令

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.

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