CONVERT DATABASE命令（二）

最新推荐文章于 2023-03-27 11:49:16 发布

ckawt40802

最新推荐文章于 2023-03-27 11:49:16 发布

阅读量133

点赞数

RMAN新增了CONVERT命令，在前面的文章中已经介绍了如何利用CONVERT命令在不同平台间转换表空间。对于相同字节序的数据库，Oracle还提供了CONVERT DATABASE的命令。

Oracle10g新增CONVERT语法：http://yangtingkun.itpub.net/post/468/483871

利用CONVERT实现跨平台表空间迁移：http://yangtingkun.itpub.net/post/468/483949

这一篇介绍在目标数据库上打开数据库的过程。

CONVERT DATABASE命令（一）：http://yangtingkun.itpub.net/post/468/487880

前两天帮junsansi解决了CONVERT DATABASE数据库过程中出现的问题，正好这几天打算测试CONVERT DATABASE的内容，于是就仿照他的例子，做了一个完整的测试。

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

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

[oracle@yans1 ytktran]$ cd /data/oradata/ytktran/
[oracle@yans1 ytktran]$ ls -l
total 2904968
-rw-r--r-- 1 oracle oinstall 104865792 Jun 29 15:40 EXAMPLE01.DBF
-rw-r--r-- 1 oracle oinstall 1696 Jun 29 15:54 INIT_00KIQ9S4_1_0.ORA
-rw-r--r-- 1 oracle oinstall 880812032 Jun 29 15:42 MGMT.DBF
-rw-r--r-- 1 oracle oinstall 104865792 Jun 29 15:42 MGMT_ECM_DEPOT1.DBF
-rw-r--r-- 1 oracle oinstall 367009792 Jun 29 15:43 SYSAUX01.DBF
-rw-r--r-- 1 oracle oinstall 597696512 Jun 29 15:45 SYSTEM01.DBF
-rw-r--r-- 1 oracle oinstall 1056768 Jun 29 15:45 TEST01.DBF
-rw-r--r-- 1 oracle oinstall 676339712 Jun 29 15:46 UNDOTBS01.DBF
-rw-r--r-- 1 oracle oinstall 134225920 Jun 29 15:49 YANGTK01.DBF
-rw-r--r-- 1 oracle oinstall 104865792 Jun 29 15:53 YANGTK02.DBF
[oracle@yans1 ytktran]$ more INIT_00KIQ9S4_1_0.ORA
# Please change the values of the following parameters:

control_files            = "E:\ORACLE\ORACLE1020\DATABASE\CF_D-YTKTRAN_ID-3695768905_00KIQ9S4"
db_recovery_file_dest    = "E:\ORACLE\ORACLE1020\DATABASE\flash_recovery_area"
db_recovery_file_dest_size= 2147483648
audit_file_dest          = "E:\ORACLE\ORACLE1020\DATABASE\ADUMP"
background_dump_dest     = "E:\ORACLE\ORACLE1020\DATABASE\BDUMP"
user_dump_dest           = "E:\ORACLE\ORACLE1020\DATABASE\UDUMP"
core_dump_dest           = "E:\ORACLE\ORACLE1020\DATABASE\CDUMP"
db_name                  = "YTKTRAN"

# Please review the values of the following parameters:

__shared_pool_size       = 209715200
__large_pool_size        = 4194304
__java_pool_size         = 4194304
__streams_pool_size      = 4194304
__db_cache_size          = 373293056
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = "ytk_thinkpad"
dispatchers              = "(PROTOCOL=TCP) (SERVICE=ytk102XDB)"
# The values of the following parameters are from source database:

processes                = 150
sga_target               = 603979776
db_block_size            = 8192
db_2k_cache_size         = 0
compatible               = "10.2.0.1.0"
# log_archive_dest_1       = "LOCATION=E:\ORACLE\ORADATA\YTK102\ARCHIVE"
db_file_multiblock_read_count= 16
undo_management          = "AUTO"
undo_tablespace          = "UNDOTBS1"
session_cached_cursors   = 200
job_queue_processes      = 10
audit_trail              = "NONE"
open_cursors             = 300
pga_aggregate_target     = 200278016
aq_tm_processes          = 1

[oracle@yans1 ytktran]$ export ORACLE_SID=YTKTRAN

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

[oracle@yans1 ytktran]$ more initytktran.ora
control_files            = "/data/oradata/ytktran/control01.ctl"
db_recovery_file_dest    = "/data/oradata/ytktran"
db_recovery_file_dest_size= 2147483648
audit_file_dest          = "/opt/ora10g/admin/ytktran/adump"
background_dump_dest     = "/opt/ora10g/admin/ytktran/bdump"
user_dump_dest           = "/opt/ora10g/admin/ytktran/udump"
core_dump_dest           = "/opt/ora10g/admin/ytktran/cdump"
db_name                  = "YTKTRAN"
remote_login_passwordfile= "EXCLUSIVE"
sga_target               = 603979776
db_block_size            = 8192
compatible               = "10.2.0.1.0"
log_archive_dest_1       = "LOCATION=/data/oradata/ytktran/archivelog"
undo_management          = "AUTO"
undo_tablespace          = "UNDOTBS1"
job_queue_processes      = 10
audit_trail              = "NONE"
open_cursors             = 300
pga_aggregate_target     = 200278016

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

[oracle@yans1 ~]$ cd $ORACLE_BASE
[oracle@yans1 ora10g]$ cd admin
[oracle@yans1 admin]$ pwd
/opt/ora10g/admin
[oracle@yans1 admin]$ mkdir ytktran
[oracle@yans1 admin]$ cd ytktran
[oracle@yans1 ytktran]$ mkdir bdump cdump adump udump

下面启动数据库：

[oracle@yans1 ytktran]$ sqlplus "/ as sysdba"

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE=/data/oradata/ytktran/initytktran.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 = '/data/oradata/ytktran/initytktran.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
SQL> CREATE CONTROLFILE REUSE SET DATABASE "YTKTRAN" RESETLOGS FORCE LOGGING ARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 292
7 LOGFILE
8    GROUP 1 '/data/oradata/ytktran/redo01.log' SIZE 50M,
9    GROUP 2 '/data/oradata/ytktran/redo02.log' SIZE 50M,
10    GROUP 3 '/data/oradata/ytktran/redo03.log' SIZE 50M
11 DATAFILE
12    '/data/oradata/ytktran/SYSTEM01.DBF',
13    '/data/oradata/ytktran/UNDOTBS01.DBF',
14    '/data/oradata/ytktran/SYSAUX01.DBF',
15    '/data/oradata/ytktran/EXAMPLE01.DBF',
16    '/data/oradata/ytktran/YANGTK01.DBF',
17    '/data/oradata/ytktran/MGMT.DBF',
18    '/data/oradata/ytktran/YANGTK02.DBF',
19    '/data/oradata/ytktran/TEST01.DBF',
20    '/data/oradata/ytktran/MGMT_ECM_DEPOT1.DBF'
21 CHARACTER SET ZHS16GBK
22 ;

Control file created.

SQL> alter database open resetlogs upgrade;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oradata/ytktran/temp01.dbf'
2 SIZE 1024M AUTOEXTEND OFF;

Tablespace altered.

由于数据库的从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.

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-609946/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-609946/