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基本使用