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

SHUTDOWN IMMEDIATE

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

@@ ?/rdbms/admin/utlirp.sql

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

$ 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

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"

__large_pool_size = 4194304

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

cursor_sharing = "SIMILAR"

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

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

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

MAXDATAFILES 100

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

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

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

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

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

SQL> Rem ===========================================================================

SQL> Rem END utlip.sql

DOC> utlirp.sql completed successfully. All PL/SQL objects in the

DOC> database have been invalidated.

DOC> Shut down and restart the database in normal mode and run utlrp.sql to

DOC> recompile invalid objects.

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

DOC>######################################################################

DOC> error if the user running this script is not SYS. Disconnect

DOC> and reconnect with AS SYSDBA.

no rows selected

.DOC>#######################################################################

SQL> Rem *********************************************************************

SQL> Rem END catupgrd.sql

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

SQL> Rem

SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $

SQL> Rem utlrp.sql

SQL> Rem NAME

SQL> Rem utlrp.sql - Recompile invalid objects

SQL> Rem DESCRIPTION

SQL> Rem This script recompiles invalid objects in the database.

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 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 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 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 BEGIN utlrp.sql

SQL> Rem END utlrp.sql

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

Variable Size 243271704 bytes

Database Buffers 352321536 bytes

Database mounted.

Database opened.

SQL> alter database datafile 'MISSING00004' offline drop;

Database altered.

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

CONVERT DATABASE命令

继续阅读

(int),Convert.ToInt32(),Int32.Parse(),Int32.TryParse()的用法总结

Sql中将datetime转换成字符串的方法(CONVERT)

你的动漫脸真的很好看#midjourrney##chatgpt到底有多牛##mid#'mhavingalotoftrou

O神解析：曼联专栏关于曼联股东以及股权两篇专栏文章，专栏文章我会放在置顶楼。阿文解析：格雷泽股份详情：在这一点上我要非常

记录学习phantomjs+echarts-convert.js 生成图表图片过程

打鸟的安排，尼康NIKKORZ180-600mmVR大炮价格亲民，Hk售价16680港币，8月上市。镜头重量不到4斤，连

C++ convert between string and CString (LPWSTR)SummaryC++ convert from string to LPCWSTRC++ convert from LPCWSTR to stringReference

好消息！VMware P2V工具（converter）又有新版更新了

字符串转换成整型

glance50 [XCTF MISC] ——ImageMagick的convert和montage命令解题记录

mb_convert_encoding()函数

Sql语句转换格式函数Cast、Convert

mysql cast()与convert() 函数

好女人、好男人都是被另一半抛弃的对象。Goodwoman。好女人都是被另一半抛弃的对象。Goodmenarealways

SQL日期函数convert

Flutter中编解码Convert基本使用