convert db
DESCRIPTION
DB cross platform migration exampleTRANSCRIPT
1) Run the convert command (in our case we ran it on source)
Script:convert databasetransport script '/u06/MIG/transport_mig.sql'new database 'mig'to platform 'AIX-Based Systems (64-bit)'parallelism 4format '/u06/MIGstage' -- give location where files will be kept on targetdb_file_name_convert '/u01/oracle/CRP1/db/apps_st/data','/u06/MIG','/u03/oracle/CRP1/db/apps_st/data','/u06/MIG';
select directory_path||'/'||location External_file_path from dba_directories a, dba_external_locations b where
a.directory_name=b.directory_name;
Take spfile backup and give 777 to spfile.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jul 16 03:02:53 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CRP1 (DBID=3550452063)
RMAN>
RMAN>
RMAN> convert database2> transport script '/u06/MIG/transport_mig.sql'new database 'mig'3> 4> to platform 'AIX-Based Systems (64-bit)'5> parallelism 46> format '/u06/MIGstage'7> db_file_name_convert '/u01/oracle/CRP1/db/apps_st/data','/u06/MIG','/u03/oracle/CRP1/db/apps_st/data','/u06/MIG';
Starting conversion at source at 16-JUL-10using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=2985 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=2979 device type=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: SID=2978 device type=DISKallocated channel: ORA_DISK_4channel ORA_DISK_4: SID=2977 device type=DISK
External table SYS.SYS_TZUV2_AFFECTED_REGIONS found in the database
Directory SYS.CSR_XML_TOP found in the databaseDirectory SYS.ODPDIR found in the databaseDirectory SYS.AW_DIR found in the databaseDirectory SYS.IDR_DIR found in the database
Directory SYS.XMLDIR found in the databaseDirectory SYS.ORACLE_OCM_CONFIG_DIR found in the databaseDirectory SYS.TIMEZDIF_DIR found in the databaseDirectory SYS.ECX_UTL_XSLT_DIR_OBJ found in the databaseDirectory SYS.ECX_UTL_LOG_DIR_OBJ found in the databaseDirectory SYS.DATA_PUMP_DIR found in the databaseDirectory SYS.APPS_DATA_FILE_DIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password filechannel ORA_DISK_1: starting datafile conversioninput datafile file number=00041 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_ind06.dbfchannel ORA_DISK_2: starting datafile conversioninput datafile file number=00012 name=/u03/oracle/CRP1/db/apps_st/data/undo01.dbfchannel ORA_DISK_3: starting datafile conversioninput datafile file number=00039 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_data05.dbfchannel ORA_DISK_4: starting datafile conversioninput datafile file number=00040 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_data06.dbfconverted datafile=/u06/MIG/undo01.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:06channel ORA_DISK_2: starting datafile conversioninput datafile file number=00042 name=/u01/oracle/CRP1/db/apps_st/data/sysaux02.dbfconverted datafile=/u06/MIG/a_txn_data05.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:01:14channel ORA_DISK_3: starting datafile conversioninput datafile file number=00023 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_data02.dbfconverted datafile=/u06/MIG/a_txn_ind06.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:17channel ORA_DISK_1: starting datafile conversioninput datafile file number=00025 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_ind01.dbfconverted datafile=/u06/MIG/a_txn_data06.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:01:17channel ORA_DISK_4: starting datafile conversioninput datafile file number=00019 name=/u03/oracle/CRP1/db/apps_st/data/a_ref01.dbfconverted datafile=/u06/MIG/sysaux02.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:18channel ORA_DISK_2: starting datafile conversioninput datafile file number=00020 name=/u03/oracle/CRP1/db/apps_st/data/a_ref02.dbfconverted datafile=/u06/MIG/a_txn_ind01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:13channel ORA_DISK_1: starting datafile conversioninput datafile file number=00022 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_data01.dbfconverted datafile=/u06/MIG/a_ref01.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:01:13channel ORA_DISK_4: starting datafile conversioninput datafile file number=00024 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_data03.dbfconverted datafile=/u06/MIG/a_txn_data02.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:01:33channel ORA_DISK_3: starting datafile conversioninput datafile file number=00026 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_ind02.dbfconverted datafile=/u06/MIG/a_ref02.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:19channel ORA_DISK_2: starting datafile conversioninput datafile file number=00027 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_ind03.dbfconverted datafile=/u06/MIG/a_txn_data01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:26channel ORA_DISK_1: starting datafile conversioninput datafile file number=00029 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_ind05.dbfconverted datafile=/u06/MIG/a_txn_data03.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:01:27channel ORA_DISK_4: starting datafile conversioninput datafile file number=00028 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_ind04.dbf
converted datafile=/u06/MIG/a_txn_ind02.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:01:12channel ORA_DISK_3: starting datafile conversioninput datafile file number=00009 name=/u01/oracle/CRP1/db/apps_st/data/system09.dbfconverted datafile=/u06/MIG/a_txn_ind05.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:17channel ORA_DISK_1: starting datafile conversioninput datafile file number=00008 name=/u01/oracle/CRP1/db/apps_st/data/system08.dbfconverted datafile=/u06/MIG/a_txn_ind03.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:33channel ORA_DISK_2: starting datafile conversioninput datafile file number=00047 name=/u03/oracle/CRP1/db/apps_st/data/custom02.dbfconverted datafile=/u06/MIG/a_txn_ind04.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:01:25channel ORA_DISK_4: starting datafile conversioninput datafile file number=00014 name=/u03/oracle/CRP1/db/apps_st/data/a_int01.dbfconverted datafile=/u06/MIG/system09.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:01:26channel ORA_DISK_3: starting datafile conversioninput datafile file number=00015 name=/u03/oracle/CRP1/db/apps_st/data/a_media01.dbfconverted datafile=/u06/MIG/a_int01.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:59channel ORA_DISK_4: starting datafile conversioninput datafile file number=00001 name=/u01/oracle/CRP1/db/apps_st/data/system01.dbfconverted datafile=/u06/MIG/custom02.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:09channel ORA_DISK_2: starting datafile conversioninput datafile file number=00006 name=/u01/oracle/CRP1/db/apps_st/data/system06.dbfconverted datafile=/u06/MIG/system08.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:14channel ORA_DISK_1: starting datafile conversioninput datafile file number=00013 name=/u03/oracle/CRP1/db/apps_st/data/a_archive01.dbfconverted datafile=/u06/MIG/a_media01.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:01:06channel ORA_DISK_3: starting datafile conversioninput datafile file number=00021 name=/u03/oracle/CRP1/db/apps_st/data/a_summ01.dbfconverted datafile=/u06/MIG/system01.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:56channel ORA_DISK_4: starting datafile conversioninput datafile file number=00004 name=/u01/oracle/CRP1/db/apps_st/data/system04.dbfconverted datafile=/u06/MIG/system06.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:56channel ORA_DISK_2: starting datafile conversioninput datafile file number=00010 name=/u01/oracle/CRP1/db/apps_st/data/system10.dbfconverted datafile=/u06/MIG/a_summ01.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:49channel ORA_DISK_3: starting datafile conversioninput datafile file number=00045 name=/u01/oracle/CRP1/db/apps_st/data/custom01.dbfconverted datafile=/u06/MIG/a_archive01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:00channel ORA_DISK_1: starting datafile conversioninput datafile file number=00002 name=/u01/oracle/CRP1/db/apps_st/data/system02.dbfconverted datafile=/u06/MIG/system04.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:47channel ORA_DISK_4: starting datafile conversioninput datafile file number=00046 name=/u01/oracle/CRP1/db/apps_st/data/sysaux03.dbfconverted datafile=/u06/MIG/system10.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:45channel ORA_DISK_2: starting datafile conversioninput datafile file number=00003 name=/u01/oracle/CRP1/db/apps_st/data/system03.dbfconverted datafile=/u06/MIG/custom01.dbf
channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:45channel ORA_DISK_3: starting datafile conversioninput datafile file number=00005 name=/u01/oracle/CRP1/db/apps_st/data/system05.dbfconverted datafile=/u06/MIG/system02.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:37channel ORA_DISK_1: starting datafile conversioninput datafile file number=00011 name=/u01/oracle/CRP1/db/apps_st/data/system11.dbfconverted datafile=/u06/MIG/system11.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45channel ORA_DISK_1: starting datafile conversioninput datafile file number=00007 name=/u01/oracle/CRP1/db/apps_st/data/system07.dbfconverted datafile=/u06/MIG/system03.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:46channel ORA_DISK_2: starting datafile conversioninput datafile file number=00038 name=/u01/oracle/CRP1/db/apps_st/data/a_txn_data04.dbfconverted datafile=/u06/MIG/system05.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:47channel ORA_DISK_3: starting datafile conversioninput datafile file number=00043 name=/u03/oracle/CRP1/db/apps_st/data/a_summ02.dbfconverted datafile=/u06/MIG/sysaux03.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:49channel ORA_DISK_4: starting datafile conversioninput datafile file number=00044 name=/u03/oracle/CRP1/db/apps_st/data/a_media02.dbfconverted datafile=/u06/MIG/a_txn_data04.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:26channel ORA_DISK_2: starting datafile conversioninput datafile file number=00017 name=/u03/oracle/CRP1/db/apps_st/data/a_queue01.dbfconverted datafile=/u06/MIG/a_summ02.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:26channel ORA_DISK_3: starting datafile conversioninput datafile file number=00018 name=/u03/oracle/CRP1/db/apps_st/data/a_queue02.dbfconverted datafile=/u06/MIG/a_media02.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:25channel ORA_DISK_4: starting datafile conversioninput datafile file number=00036 name=/u03/oracle/CRP1/db/apps_st/data/apps_ts_tools01.dbfconverted datafile=/u06/MIG/system07.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:53channel ORA_DISK_1: starting datafile conversioninput datafile file number=00035 name=/u01/oracle/CRP1/db/apps_st/data/sysaux01.dbfconverted datafile=/u06/MIG/a_queue02.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:26channel ORA_DISK_3: starting datafile conversioninput datafile file number=00016 name=/u03/oracle/CRP1/db/apps_st/data/a_nolog01.dbfconverted datafile=/u06/MIG/a_queue01.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:27channel ORA_DISK_2: starting datafile conversioninput datafile file number=00037 name=/u03/oracle/CRP1/db/apps_st/data/interim.dbfconverted datafile=/u06/MIG/interim.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:15channel ORA_DISK_2: starting datafile conversioninput datafile file number=00031 name=/u03/oracle/CRP1/db/apps_st/data/odm.dbfconverted datafile=/u06/MIG/a_nolog01.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:17channel ORA_DISK_3: starting datafile conversioninput datafile file number=00032 name=/u03/oracle/CRP1/db/apps_st/data/olap.dbfconverted datafile=/u06/MIG/apps_ts_tools01.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:43channel ORA_DISK_4: starting datafile conversioninput datafile file number=00034 name=/u03/oracle/CRP1/db/apps_st/data/portal01.dbfconverted datafile=/u06/MIG/odm.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile conversioninput datafile file number=00030 name=/u01/oracle/CRP1/db/apps_st/data/ctxd01.dbfconverted datafile=/u06/MIG/olap.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:08channel ORA_DISK_3: starting datafile conversioninput datafile file number=00033 name=/u03/oracle/CRP1/db/apps_st/data/owad01.dbfconverted datafile=/u06/MIG/portal01.dbfchannel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:09converted datafile=/u06/MIG/sysaux01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:27converted datafile=/u06/MIG/ctxd01.dbfchannel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01converted datafile=/u06/MIG/owad01.dbfchannel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:01Edit init.ora file /u06/init_MIGstage.ora. This PFILE will be used to create the database on the target platformRun SQL script /u06/MIG/transport_mig.sql on the target platform to create databaseTo recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platformTo change the internal database identifier, use DBNEWID UtilityFinished conversion at source at 16-JUL-10
Starting Control File and SPFILE Autobackup at 16-JUL-10piece handle=/u01/oracle/CRP1/db/tech_st/11.1.0/dbs/c-3550452063-20100716-02 comment=NONEFinished Control File and SPFILE Autobackup at 16-JUL-10
RMAN>
2) Transfer converted datafiles, init file and transport.sql to the target system.3) Export required variables on Target system
###################################################################################################
export ORACLE_HOME=/u01/oramig/MIG/db/tech_st/11.1.0
export ORACLE_SID=MIG
export LD_LIBRARY_PATH=/u01/oramig/MIG/db/tech_st/11.1.0/lib:/usr/dt/lib:/usr/openwin/lib:/u01/oramig/MIG/db/tech_st/11.1.0/ctx/lib:/u01/oramig/MIG/db/tech_st/11.1.0/lib32
export LIBPATH=/u01/oramig/MIG/db/tech_st/11.1.0/lib:/usr/dt/lib:/usr/openwin/lib:/u01/oramig/MIG/db/tech_st/11.1.0/ctx/lib:/u01/oramig/MIG/db/tech_st/11.1.0/lib32
export PATH=${PATH}:${ORACLE_HOME}:bin
export TNS_ADMIN=/u01/oramig/MIG/db/tech_st/11.1.0/network/admin/MIG_adcnadb
####################################################################################################
4) Change the initfile as per requirement (removed size parameters and reduced sga_target to 2G and pga to 512M)
-----------------------------------------------------------------------------------------------------
$ cat init_MIG.ora# Please change the values of the following parameters:
control_files = '/u01/oramig/MIG/db/apps_st/data/controlMIG1.ctl','/u01/oramig/MIG/db/apps_st/data/controlMIG2.ctl','/u01/oramig/MIG/db/apps_st/data/controlMIG3.ctl'
local_listener = 'MIG_LOCAL'
plsql_native_library_dir = '/u01/oramig/MIG/db/tech_st/11.1.0/plsql/nativelib'
db_name = "MIG"
# Please review the values of the following parameters:
log_archive_dest = "/u01/oramig/MIG/arch"
_system_trig_enabled = TRUE
_sort_elimination_cost_ratio= 5
_b_tree_bitmap_plans = FALSE
_fast_full_scan_enabled = FALSE
_index_join_enabled = FALSE
_sqlexec_progression_cost= 2147483647
_like_with_bind_as_equality= TRUE
_optimizer_autostats_job = FALSE
_trace_files_public = TRUE
# The values of the following parameters are from source database:
processes = 1500
sessions = 3000
timed_statistics = TRUE
event = "31151 trace name context forever, level 0x100"
nls_language = "american"
nls_territory = "america"
nls_sort = "binary"
nls_date_format = "DD-MON-RR"
nls_numeric_characters = ".,"
nls_comp = "binary"
nls_length_semantics = "BYTE"
sga_target = 2G
db_block_checksum = "TRUE"
db_block_size = 8192
compatible = "11.1.0"
log_buffer = 10485760
log_checkpoint_interval = 100000
log_checkpoint_timeout = 1200
db_files = 512
log_checkpoints_to_alert = TRUE
dml_locks = 10000
undo_management = "AUTO"
undo_tablespace = "APPS_UNDOTS1"
db_block_checking = "FALSE"
recyclebin = "OFF"
sec_case_sensitive_logon = FALSE
session_cached_cursors = 500
utl_file_dir = "/usr/tmp"
# utl_file_dir = "/usr/tmp"
# utl_file_dir = "/u03/edi/inbound"
# utl_file_dir = "/u03/edi/outbound"
# utl_file_dir = "/u01/oracle/CRP1/db/tech_st/11.1.0/appsutil/outbound/CRP1_dhdoe01"
# utl_file_dir = "/usr/tmp"
plsql_native_library_subdir_count= 149
plsql_code_type = "INTERPRETED"
plsql_optimize_level = 2
job_queue_processes = 2
cursor_sharing = "EXACT"
parallel_min_servers = 0
parallel_max_servers = 8
open_cursors = 600
query_rewrite_enabled = "true"
pga_aggregate_target = 512M
workarea_size_policy = "AUTO"
optimizer_secure_view_merging= FALSE
aq_tm_processes = 1
olap_page_pool_size = 4194304
diagnostic_dest = "/u01/oramig/MIG/db/tech_st/11.1.0/admin/MIG_adcnadb"
max_dump_file_size = "20480"
---------------------------------------------------------------------------------------------------------------
5) Change transport_mig.sql as per actual init and datafile location. Also change log file, temp etc
Copy all datafiles as per locations in the create controlfile part of the transport script.
#####################################################################################################$ more transport_mig.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='/u01/oramig/MIG/db/tech_st/11.1.0/dbs/init_MIG.ora'CREATE CONTROLFILE REUSE SET DATABASE "MIG" RESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 5 MAXDATAFILES 512 MAXINSTANCES 8 MAXLOGHISTORY 20157LOGFILE GROUP 1 ( '/u01/oramig/MIG/db/apps_st/data/log01a.log', '/u01/oramig/MIG/db/apps_st/data/log01b.log' ) SIZE 1024M, GROUP 2 ( '/u01/oramig/MIG/db/apps_st/data/log02a.log', '/u01/oramig/MIG/db/apps_st/data/log02b.log' ) SIZE 1024MDATAFILE '/u01/oramig/MIG/db/apps_st/data/system01.dbf', '/u01/oramig/MIG/db/apps_st/data/system02.dbf', '/u01/oramig/MIG/db/apps_st/data/system03.dbf', '/u01/oramig/MIG/db/apps_st/data/system04.dbf', '/u01/oramig/MIG/db/apps_st/data/system05.dbf', '/u01/oramig/MIG/db/apps_st/data/system06.dbf', '/u01/oramig/MIG/db/apps_st/data/system07.dbf', '/u01/oramig/MIG/db/apps_st/data/system08.dbf', '/u01/oramig/MIG/db/apps_st/data/system09.dbf', '/u01/oramig/MIG/db/apps_st/data/system10.dbf', '/u01/oramig/MIG/db/apps_st/data/system11.dbf', '/u01/oramig/MIG/db/apps_st/data/undo01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_archive01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_int01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_media01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_nolog01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_queue01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_queue02.dbf', '/u01/oramig/MIG/db/apps_st/data/a_ref01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_ref02.dbf', '/u01/oramig/MIG/db/apps_st/data/a_summ01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_data01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_data02.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_data03.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_ind01.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_ind02.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_ind03.dbf',
'/u01/oramig/MIG/db/apps_st/data/a_txn_ind04.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_ind05.dbf', '/u01/oramig/MIG/db/apps_st/data/ctxd01.dbf', '/u01/oramig/MIG/db/apps_st/data/odm.dbf', '/u01/oramig/MIG/db/apps_st/data/olap.dbf', '/u01/oramig/MIG/db/apps_st/data/owad01.dbf', '/u01/oramig/MIG/db/apps_st/data/portal01.dbf', '/u01/oramig/MIG/db/apps_st/data/sysaux01.dbf', '/u01/oramig/MIG/db/apps_st/data/apps_ts_tools01.dbf', '/u01/oramig/MIG/db/apps_st/data/interim.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_data04.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_data05.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_data06.dbf', '/u01/oramig/MIG/db/apps_st/data/a_txn_ind06.dbf', '/u01/oramig/MIG/db/apps_st/data/sysaux02.dbf', '/u01/oramig/MIG/db/apps_st/data/a_summ02.dbf', '/u01/oramig/MIG/db/apps_st/data/a_media02.dbf', '/u01/oramig/MIG/db/apps_st/data/custom01.dbf', '/u01/oramig/MIG/db/apps_st/data/sysaux03.dbf', '/u01/oramig/MIG/db/apps_st/data/custom02.dbf'CHARACTER SET UTF8;
-- 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 TEMP1 ADD TEMPFILE '/u01/oramig/MIG/db/apps_st/data/temp01.dbf' SIZE 2000M AUTOEXTEND OFF;ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/oramig/MIG/db/apps_st/data/temp02.dbf' SIZE 2000M AUTOEXTEND OFF;ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/oramig/MIG/db/apps_st/data/temp03.dbf' SIZE 1024M AUTOEXTEND OFF;-- End of tempfile additions.--
set echo offprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt * Your database has been created successfully!prompt * There are many things to think about for the new database. Hereprompt * 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 theprompt * NEWDBID Utility (nid).prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATESTARTUP UPGRADE PFILE='/u01/oramig/MIG/db/tech_st/11.1.0/dbs/init_MIG.ora'
@@ ?/rdbms/admin/utlirp.sqlSHUTDOWN IMMEDIATESTARTUP PFILE='/u01/oramig/MIG/db/tech_st/11.1.0/dbs/init_MIG.ora'-- The following step will recompile all PL/SQL modules.-- It may take serveral hours to complete.@@ ?/rdbms/admin/utlrp.sqlset feedback 6;
#################################################################################################
6) Prepare the listener.ora & tnsnames.ora (not required but since local listener is kept in init file, I decided to bring up the listener)
################################################################################################$ cat listener.ora## $Header: ad8ilsnr.ora 120.3 2007/12/18 10:13:38 sbandla ship $## ################################################################# This file is automatically generated by AutoConfig. It will be read and# overwritten. If you were instructed to edit this file, or if you are not# able to use the settings created by AutoConfig, refer to Metalink Note# 387859.1 for assistance.## ###############################################################
#
## Net8 definition for Database listener#
MIG = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = adcnadb.adc.nar.capgemini.com)(PORT = 1532)) ) )
SID_LIST_MIG = (SID_LIST = (SID_DESC = (ORACLE_HOME= /u01/oramig/MIG/db/tech_st/11.1.0) (SID_NAME = MIG) ) )
STARTUP_WAIT_TIME_MIG = 0
CONNECT_TIMEOUT_MIG = 10TRACE_LEVEL_MIG = OFF
LOG_DIRECTORY_MIG = /u01/oramig/MIG/db/tech_st/11.1.0/network/adminLOG_FILE_MIG = MIGTRACE_DIRECTORY_MIG = /u01/oramig/MIG/db/tech_st/11.1.0/network/adminTRACE_FILE_MIG = MIGADMIN_RESTRICTIONS_MIG = OFFSUBSCRIBE_FOR_NODE_DOWN_EVENT_MIG = OFF
IFILE=/u01/oramig/MIG/db/tech_st/11.1.0/network/admin/MIG_adcnadb/listener_ifile.ora########################################################################################
$ cat tnsnames.ora## $Header: ad8itns.ora 120.0 2005/06/20 15:31:47 appldev noship $## ################################################################# This file is automatically generated by AutoConfig. It will be read and# overwritten. If you were instructed to edit this file, or if you are not# able to use the settings created by AutoConfig, refer to Metalink Note# 387859.1 for assistance.## ###############################################################
#
## Net8 definition for the database#
MIG = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=adcnadb)(PORT=1532)) (CONNECT_DATA=(SID=MIG)) )
MIG_LOCAL = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=adcnadb)(PORT=1532)) )
MIG_REMOTE = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=adcnadb)(PORT=1532)) )
## Intermedia#extproc_connection_data = (DESCRIPTION= (ADDRESS_LIST = (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCMIG))
) (CONNECT_DATA= (SID=PLSExtProc) (PRESENTATION = RO) ) )
IFILE=/u01/oramig/MIG/db/tech_st/11.1.0/network/admin/MIG_adcnadb/MIG_adcnadb_ifile.ora
###############################################################################################
$ lsnrctl start MIG
LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production on 27-JUL-2010 06:51:29
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Starting /u01/oramig/MIG/db/tech_st/11.1.0/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - ProductionSystem parameter file is /u01/oramig/MIG/db/tech_st/11.1.0/network/admin/MIG_adcnadb/listener.oraLog messages written to /u01/oramig/MIG/db/tech_st/11.1.0/log/diag/tnslsnr/adcnadb/mig/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adcnadb.adc.nar.capgemini.com)(PORT=1532)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adcnadb.adc.nar.capgemini.com)(PORT=1532)))STATUS of the LISTENER------------------------Alias MIGVersion TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - ProductionStart Date 27-JUL-2010 06:51:30Uptime 0 days 0 hr. 0 min. 8 secTrace Level offSecurity ON: Local OS AuthenticationSNMP ONListener Parameter File /u01/oramig/MIG/db/tech_st/11.1.0/network/admin/MIG_adcnadb/listener.oraListener Log File /u01/oramig/MIG/db/tech_st/11.1.0/log/diag/tnslsnr/adcnadb/mig/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adcnadb.adc.nar.capgemini.com)(PORT=1532)))Services Summary...Service "MIG" has 1 instance(s). Instance "MIG", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully$ ps -ef|grep tns oramig 1155096 1 0 06:51:29 pts/2 0:00 /u01/oramig/MIG/db/tech_st/11.1.0/bin/tnslsnr MIG -inherit oramig 1224940 1188086 0 06:51:54 pts/2 0:00 grep tns$ pwd/u01/oramig/MIG/db/tech_st/11.1.0/dbs$ ls -ltrtotal 264-rw-r--r-- 1 oramig dba 12920 May 03 2001 initdw.ora-rw-r--r-- 1 oramig dba 2774 Sep 11 2007 init.ora
-rw-rw-r-- 1 oramig dba 19457 Nov 25 2008 initR1211BL.ora.10252008112451-rw-r--r-- 1 oramig dba 19721 Jan 14 2009 initR1211XB4.ora.015200914515-rw-rw---- 1 oramig dba 1552 Jan 15 2009 hc_DBUA0.dat-rw-r----- 1 oramig dba 6656 Jan 15 2009 spfileR1211XB4.ora.bak-rw-r--r-- 1 oramig dba 0 May 15 03:53 TRNG12_ifile.ora-rw-r----- 1 oramig dba 24 May 15 04:04 lkTRNG12-rw-r--r-- 1 oramig dba 19119 May 18 04:32 initTRNG12.ora-rw-r--r-- 1 oramig dba 19165 May 18 04:32 initTRNG12_noaq.ora-rw-r--r-- 1 oramig dba 0 May 18 04:32 TRNG12_adcnadb_ifile.ora-rw-rw---- 1 oramig dba 1544 Jul 16 03:32 hc_TRNG12.dat-rw-r--r-- 1 oramig dba 2899 Jul 27 06:23 init_MIG.ora-rw-r--r-- 1 oramig dba 4955 Jul 27 06:39 transport_mig.sql
7) Run the transport_mig.sql (spool before running -- I missed a part as I had not spooled)
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 27 06:52:05 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @transport_mig.sqlORACLE instance started.
Total System Global Area 2137886720 bytesFixed Size 2155464 bytesVariable Size 486542392 bytesDatabase Buffers 1627389952 bytesRedo Buffers 21798912 bytes
Control file created.
Database altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~* Your database has been created successfully!
* There are many things to think about for the new database. Here* is a checklist to help you stay on track:* 1. You may want to redefine the location of the directory objects.* 2. You may want to change the internal database identifier (DBID)* or the global database name for this database. Use the* NEWDBID Utility (nid).~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Database closed.Database dismounted.ORACLE instance shut down.ORACLE instance started.
Total System Global Area 2137886720 bytesFixed Size 2155464 bytesVariable Size 486542392 bytesDatabase Buffers 1627389952 bytesRedo Buffers 21798912 bytesDatabase mounted.Database opened.SQL>SQL> WHENEVER SQLERROR EXIT;SQL>SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC> The following statement will cause an "ORA-01722: invalid number"DOC> error if there the database was not opened in UPGRADE modeDOC>DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" andDOC> re-execute utlirp.sqlDOC>#######################################################################DOC>#######################################################################DOC>#SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance 2 WHERE status != 'OPEN MIGRATE';
no rows selected
SQL>SQL> RemSQL> Rem Store object numbers of all valid PL/SQL-based functional indexesSQL> RemSQL> DROP TABLE utlirp_enabled_func_indexes;
Table dropped.
SQL> CREATE TABLE utlirp_enabled_func_indexes AS 2 SELECT obj# FROM ind$ 3 WHERE bitand(property, 2048) != 0 AND bitand(flags, 1024) = 0;
Table created.
SQL>SQL> Rem invalidate all pl/sql modules and recompile standard and dbms_standard
SQL> @@utlipSQL> Rem Copyright (c) 1998, 2007, Oracle. All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem utlip.sql - UTiLity script to Invalidate Pl/sqlSQL> RemSQL> Rem DESCRIPTIONSQL> RemSQL> Rem *WARNING* *WARNING* *WARNING* *WARNING* *WARNING* *WARNING*SQL> RemSQL> Rem Do not run this script directly.SQL> RemSQL> Rem utlip.sql is automatically executed when required for databaseSQL> Rem upgrades.SQL> RemSQL> Rem Use utlirp.sql if you are looking to invalidate and recompileSQL> Rem PL/SQL for a 32-bit to 64-bit conversion. Use dbmsupgnv.sqlSQL> Rem to convert all PL/SQL to NATIVE or dbmsupgin.sql to convert allSQL> Rem PL/SQL to INTERPRETED.SQL> RemSQL> Rem *WARNING* *WARNING* *WARNING* *WARNING* *WARNING* *WARNING*SQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem gviswana 05/02/07 - Add warning messages; revert view Diana deleteSQL> Rem gviswana 06/10/06 - Delete Diana performance optimizationSQL> Rem gviswana 06/06/06 - Delete 11.x Diana for fine-grain depsSQL> Rem ssubrama 12/30/05 - bug 4882839 invalidate dbms_standard dependentsSQL> Rem gviswana 06/17/05 - Delete sequence DianaSQL> Rem weiwang 05/06/05 - invalidate rules engine objectsSQL> Rem ciyer 07/24/04 - selectively invalidate views and synonymsSQL> Rem jmuller 02/12/04 - Fix bug 3432304: commit even if no rows deletedSQL> Rem gviswana 08/28/03 - 3103287: Remove Diana deletions for PL/SQLSQL> Rem jmallory 08/18/03 - Hardcode dbms_dbupgrade_subnameSQL> Rem gviswana 06/23/03 - 2985184: Invalidate dependent viewsSQL> Rem kquinn 07/22/03 - 3009599: Handle remote dbms_standard caseSQL> Rem jmallory 06/09/03 - Fix null checkingSQL> Rem jmallory 03/31/03 - Exclude dbupgrade objectsSQL> Rem gviswana 04/16/03 - Move system parameter handling to utlirp.sqlSQL> Rem kmuthukk 02/03/03 - fix update performanceSQL> Rem nfolkert 12/23/02 - invalidate summary objectsSQL> Rem kmuthukk 10/22/02 - ncomp dlls in dbSQL> Rem gviswana 10/28/02 - Deferred synonym translationSQL> Rem rdecker 11/09/01 - remove CREATE library code FOR bug 1952368SQL> Rem gviswana 08/17/01 - Break up IDL_ deletes to avoid blowing rollbackSQL> Rem rburns 08/23/01 - bug 1950073 - add exit on errorSQL> Rem rburns 08/24/01 - add plitblmSQL> Rem rburns 07/26/01 - invalidate index types and operatorsSQL> Rem rxgovind 04/30/01 - interim fix for bug-1747462SQL> Rem gviswana 10/19/00 - Disable system triggers for Standard recompileSQL> Rem sbalaram 06/01/00 - Add prvthssq.sql after resolving Bug 1292760SQL> Rem thoang 05/26/00 - Do not invalidate earlier type versionsSQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.SQL> Rem rshaikh 09/22/99 - quote library namesSQL> Rem mjungerm 06/15/99 - add java shared data object typeSQL> Rem rshaikh 02/12/99 - dont delete java idl objects
SQL> Rem rshaikh 11/17/98 - remove obsolete commentsSQL> Rem rshaikh 10/30/98 - add slash after last truncate stmtSQL> Rem abrik 10/01/98 - just truncate idl_*$ tablesSQL> Rem rshaikh 10/14/98 - bug 491101: recreate librariesSQL> Rem ncramesh 08/04/98 - change for sqlplusSQL> Rem rshaikh 07/20/98 - add commitsSQL> Rem usundara 06/03/98 - merge from 8.0.5SQL> Rem usundara 04/29/98 - creation (split from utlirp)SQL> Rem Kannan Muthukkaruppan (kmuthukk) was the originalSQL> Rem author of this script.SQL>SQL> Rem ===========================================================================SQL> Rem BEGIN utlip.sqlSQL> Rem ===========================================================================SQL>SQL> Rem Exit immediately if Any failure in this scriptSQL> WHENEVER SQLERROR EXIT;SQL>SQL> -- Step (I)SQL> --SQL> -- First we invalidate all stored PL/SQL units (procs, fns, pkgs,SQL> -- types, triggers.)SQL> --SQL> -- The type# in the update statement below indicates the KGLSQL> -- type of the object. They have the following interpretation:SQL> -- 7 - pl/sql stored procedureSQL> -- 8 - pl/sql stored functionSQL> -- 9 - pl/sql pkg specSQL> -- 11 - pl/sql pkg bodySQL> -- 12 - triggerSQL> -- 13 - type specSQL> -- 14 - type bodySQL> -- 22 - librarySQL> -- 32 - indextypeSQL> -- 33 - operatorSQL> --SQL> -- Earlier type versions do not need to be invalidated since all pgmSQL> -- units reference latest type versions. There is no mechanisms toSQL> -- recompile earlier type versions anyway. They must be kept valid soSQL> -- we can get access to its TDO to handle image conversion from one typeSQL> -- version to another.SQL> -- All earlier type versions has the version name stored in obj$.subnameSQL> -- and the latest type version always has a null subname. We use thisSQL> -- fact to invalidate only the latest type version.SQL> update obj$ set status = 6 2 where ((type# in (7, 8, 9, 11, 12, 14, 22, 32, 33, 87)) or 3 (type# = 13 and subname is null)) 4 and ((subname is null) or (subname <> 'DBMS_DBUPGRADE_BABY')) 5 and status not in (5,6) 6 and linkname is null 7 and not exists (select 1 8 from type$ 9 where (bitand(properties, 16) = 16) 10 and toid = obj$.oid$) 11 /
97224 rows updated.
SQL> commit 2 /
Commit complete.
SQL>SQL> Rem Always invalidate MVs during upgrades/ downgradesSQL> update obj$ set status = 5 where type# = 42;
761 rows updated.
SQL> commit;
Commit complete.
SQL>SQL> UPDATE sys.obj$ SET status = 5 2 where obj# in 3 ((select obj# from obj$ where type# = 62 or type# = 46 or type# = 59) 4 union all 5 (select /*+ index (dependency$ i_dependency2) */ 6 d_obj# from dependency$ 7 connect by prior d_obj# = p_obj# 8 start with p_obj# in 9 (select obj# from obj$ where type# = 62 or type# = 46 or type# = 59))) 10 /
163 rows updated.
SQL> commit 2 /
Commit complete.
SQL>SQL> -- Invalidate all synonym dependents of dbms_standard. If not we will end upSQL> -- with a timestamp mismatch between dependency and objSQL>SQL> update obj$ set status=6 where obj# in 2 (select d_obj# from dependency$ 3 where p_obj# in (select obj# from obj$ where name='DBMS_STANDARD' and 4 type# in ( 9, 11) and owner#=0) 5 ) and type#=5 6 /
1 row updated.
SQL> commit 2 /
Commit complete.
SQL>SQL> alter system flush shared_pool 2 /
System altered.
SQL>SQL> --SQL> -- Step (II)SQL> --SQL> -- Delete Diana to force full recompile (rather than fast validation).SQL> -- Diana deletion is accomplished by changing the version number to makeSQL> -- rows invisible.SQL> --SQL> update idl_ub1$ set version = -version 2 where part = 0 and version >= 184549376 3 and obj# IN 4 (select obj# from obj$ o where status in (5, 6) and 5 type# in (7, 8, 9, 11, 12, 13, 14, 22, 32, 33, 87));
92754 rows updated.
SQL> update idl_ub2$ SET version = -version 2 where part = 0 and version >= 184549376 3 and obj# IN 4 (select obj# from obj$ o where status in (5, 6) and 5 type# in (7, 8, 9, 11, 12, 13, 14, 22, 32, 33, 87));
199461 rows updated.
SQL> update idl_sb4$ SET version = -version 2 where part = 0 and version >= 184549376 3 and obj# IN 4 (select obj# from obj$ o where status in (5, 6) and 5 type# in (7, 8, 9, 11, 12, 13, 14, 22, 32, 33, 87));
******************************This part was missed as I did not spool*********************************************
SQL> remSQL> Rem Copyright (c) 1991, 2006, Oracle. All rights reserved.SQL> Rem NAMESQL> Rem stdext.sql - Kernel extensions to package standardSQL> Rem DESCRIPTIONSQL> Rem Routines in this package do not need to be qualified by theSQL> Rem owner or package name, similar to the behaviour of packageSQL> Rem 'standard'. This package mostly contains utility routines forSQL> Rem triggers.SQL> Rem RETURNSSQL> RemSQL> Rem NOTESSQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem sagrawal 05/15/06 - sys_GetTriggerStateSQL> Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM
SQL> Rem ykunitom 04/04/01 - fix bug 1473966SQL> Rem weiwang 05/02/00 - add error functionsSQL> Rem najain 04/25/00 - add partition_pos functionSQL> Rem najain 04/14/00 - add sqltext attr. funcSQL> Rem weiwang 02/28/00 - fix bug 1209532SQL> Rem weiwang 08/02/99 - change name_list_t to ora_name_list_tSQL> Rem weiwang 02/09/99 - add isdropcolumn, isaltercolumnSQL> Rem weiwang 09/15/98 - add function des_encrypted_passwordSQL> Rem weiwang 06/11/98 - add function dictionary_obj_ownerSQL> Rem jkrishna 05/05/98 - support for error eventsSQL> Rem jkrishna 04/01/98 - adding system event calloutsSQL> Rem cbarclay 11/06/96 - remove kkxl*emSQL> Rem mmonajje 09/16/96 - Fixing bug 244014; Adding RESTRICT_REFERENCES prSQL> Rem ramkrish 06/28/96 - Add EMPTY_BLOB, EMPTY_CLOB routinesSQL> Rem hjakobss 10/16/94 - bug 151485 - remove sql_ddlSQL> Rem jwijaya 04/05/93 - merge changes from branch 1.3.312.1SQL> Rem jwijaya 03/26/93 - bug 157348SQL> Rem rkooi 11/28/92 - add 'keep' option to raise_application_errorSQL> Rem rkooi 10/25/92 - deal with null arg to raeSQL> Rem glumpkin 10/21/92 - Renamed from STDEXT.SQLSQL> Rem mmoore 09/24/92 - #(130568) add callback for commit commentSQL> Rem rkooi 09/02/92 - change pls_integer to binary_integerSQL> Rem mmoore 08/12/92 - override the savepoint command in standardSQL> Rem rkooi 06/10/92 - add 'do not change' commentSQL> Rem mmoore 04/14/92 - move begin_oltp to package transactionSQL> Rem rkooi 04/06/92 - merge changes from branch 1.7.300.1SQL> Rem rkooi 04/02/92 - split dbms_standard into separate pkgsSQL> Rem maporter 03/25/92 - renumber 8200 to 8175SQL> Rem rbamford 03/07/92 - add BEGIN_OLTP_TRANSACTIONSQL> Rem mroberts 02/21/92 - delete extra rollback_sv procedureSQL> Rem rkooi 02/17/92 - add set_role and related proceduresSQL> Rem mroberts 02/14/92 - add execute_ddl internalSQL> Rem mmoore 02/06/92 - change name of packageSQL> Rem mmoore 01/14/92 - add rollback_nrSQL> Rem mmoore 01/09/92 - speed upSQL> Rem rkooi 11/24/91 - rename to stdext.sql from stdext.plsSQL> Rem rkooi 08/26/91 - get rid of stack_application_errorSQL> Rem rkooi 05/08/91 - change name to standard_extensionSQL> Rem rkooi 05/02/91 - forgot skip_row procedureSQL> Rem mmoore 05/02/91 - move trigger icds to psd, use varchar2 inSQL> Rem rae/saeSQL> Rem rkooi 04/23/91 - add 'skip_row' procedure, commit etc.SQL> Rem procedures.SQL> Rem Moore 04/02/91 - fix typoSQL> Rem Moore 03/28/91 - add boolean trigger functionsSQL> Rem Kooi 03/17/91 - CreationSQL> Rem Kooi 03/12/91 - change name to standard_utilitiesSQL> Rem Kooi 02/26/91 - get rid of raise now that psdkse does itSQL> Rem Kooi 02/26/91 - CreationSQL> ------------------------------------------------------------------------------SQL>SQL> REM *****************************************************************SQL> REM THIS PACKAGE MUST NOT BE MODIFIED BY THE CUSTOMER. DOING SOSQL> REM COULD CAUSE INTERNAL ERRORS AND SECURITY VIOLATIONS IN THE RDBMS.SQL> REM *****************************************************************
SQL>SQL> create or replace package dbms_standard is 2 -- types 3 type ora_name_list_t is table of varchar2(64); 4 5 -- Trigger Operations 6 procedure raise_application_error(num binary_integer, msg varchar2, 7 keeperrorstack boolean default FALSE); 8 pragma interface (C, raise_application_error); -- 1 (see psdicd.c) 9 pragma restrict_references (raise_application_error, WNPS, RNPS, WNDS, RNDS); 10 function inserting return boolean; 11 pragma interface (C, inserting); -- 2 12 pragma restrict_references (inserting, WNPS, RNPS, WNDS); 13 function deleting return boolean; 14 pragma interface (C, deleting); -- 3 15 pragma restrict_references (deleting, WNPS, RNPS, WNDS); 16 function updating return boolean; 17 pragma interface (C, updating); -- 4 18 pragma restrict_references (updating, WNPS, RNPS, WNDS); 19 function updating (colnam varchar2) return boolean; 20 pragma interface (C, updating); -- 5 21 pragma restrict_references (updating, WNPS, RNPS, WNDS); 22 23 -- Transaction Commands 24 procedure commit; 25 pragma interface (C, commit); -- 6 26 procedure commit_cm(vc varchar2); 27 pragma interface (C, commit_cm); -- 7 28 procedure rollback_nr; 29 pragma interface (C, rollback_nr); -- 8 30 procedure rollback_sv(save_point varchar2); 31 pragma interface (C, rollback_sv); -- 9 32 procedure savepoint(save_point varchar2); 33 pragma interface (C, savepoint); -- 10 34 procedure set_transaction_use(vc varchar2); 35 pragma interface (C, set_transaction_use); -- 11 36 37 38 -- Functions supported for system events 39 -- Null or zero will be returned if called in inappropriate occasions 40 -- error functions only search for the top 5 errors in the error stack 41 42 function sysevent return varchar2 ; -- 12 43 pragma interface (C, sysevent); 44 pragma restrict_references (sysevent, WNPS, RNPS, WNDS); 45 function dictionary_obj_type return varchar2 ; -- 13 46 pragma interface (C, dictionary_obj_type); 47 pragma restrict_references (dictionary_obj_type, WNPS, RNPS, WNDS); 48 function dictionary_obj_owner return varchar2 ; -- 14 49 pragma interface (C, dictionary_obj_owner); 50 pragma restrict_references (dictionary_obj_owner, WNPS, RNPS, WNDS); 51 function dictionary_obj_name return varchar2 ; -- 15 52 pragma interface (C, dictionary_obj_name); 53 pragma restrict_references (dictionary_obj_name, WNPS, RNPS, WNDS); 54 function database_name return varchar2 ; -- 16
55 pragma interface (C, database_name); 56 pragma restrict_references (database_name, WNPS, RNPS, WNDS); 57 function instance_num return binary_integer ; -- 17 58 pragma interface (C, instance_num); 59 pragma restrict_references (instance_num, WNPS, RNPS, WNDS); 60 function login_user return varchar2 ; -- 18 61 pragma interface (C, login_user); 62 pragma restrict_references (login_user, WNPS, RNPS, WNDS); 63 function is_servererror (errno binary_integer) 64 return boolean ; -- 19 65 pragma interface (C, is_servererror); 66 pragma restrict_references (is_servererror, WNPS, RNPS, WNDS); 67 68 function server_error(position binary_integer) 69 return binary_integer ; -- 20 70 pragma interface (C, server_error); 71 pragma restrict_references (server_error, WNPS, RNPS, WNDS); 72 function des_encrypted_password(user varchar2 default null) return varchar2; -- 21 73 pragma interface (C, des_encrypted_password); 74 pragma restrict_references (des_encrypted_password, WNPS, RNPS, WNDS); 75 function is_alter_column (column_name varchar2) 76 return boolean ; -- 22 77 pragma interface (C, is_alter_column); 78 pragma restrict_references (is_alter_column, WNPS, RNPS, WNDS); 79 function is_drop_column (column_name varchar2) 80 return boolean ; -- 23 81 pragma interface (C, is_drop_column); 82 pragma restrict_references (is_drop_column, WNPS, RNPS, WNDS); 83 function grantee (user_list out ora_name_list_t) return binary_integer ; -- 24 84 pragma interface (C, grantee); 85 pragma restrict_references (grantee, WNPS, RNPS, WNDS); 86 function revokee (user_list out ora_name_list_t) return binary_integer ; -- 25 87 pragma interface (C, revokee); 88 pragma restrict_references (revokee, WNPS, RNPS, WNDS); 89 function privilege_list (priv_list out ora_name_list_t) 90 return binary_integer ; -- 26 91 pragma interface (C, privilege_list); 92 pragma restrict_references (privilege_list, WNPS, RNPS, WNDS); 93 function with_grant_option return boolean ; -- 27 94 pragma interface (C, with_grant_option); 95 pragma restrict_references (with_grant_option, WNPS, RNPS, WNDS); 96 function dictionary_obj_owner_list (owner_list out ora_name_list_t) 97 return binary_integer; -- 28 98 pragma interface (C, dictionary_obj_owner_list); 99 pragma restrict_references (dictionary_obj_owner_list, WNPS, RNPS, WNDS);100 function dictionary_obj_name_list (object_list out ora_name_list_t)101 return binary_integer; -- 29102 pragma interface (C, dictionary_obj_name_list);103 pragma restrict_references (dictionary_obj_name_list, WNPS, RNPS, WNDS);104 function is_creating_nested_table return boolean; -- 30105 pragma interface (C, is_creating_nested_table);106 pragma restrict_references (is_creating_nested_table, WNPS, RNPS, WNDS);107 function client_ip_address return varchar2; -- 31108 pragma interface (C, client_ip_address);109 pragma restrict_references (client_ip_address, WNPS, RNPS, WNDS);
110 function sql_txt (sql_text out ora_name_list_t) return binary_integer; -- 32111 pragma interface (C, sql_txt);112 pragma restrict_references (sql_txt, WNPS, RNPS, WNDS);113 function server_error_msg (position binary_integer) return varchar2; -- 33114 pragma interface (C, server_error_msg);115 pragma restrict_references (server_error_msg, WNPS, RNPS, WNDS);116 function server_error_depth return binary_integer; -- 34117 pragma interface (C, server_error_depth);118 pragma restrict_references (server_error_depth, WNPS, RNPS, WNDS);119 function server_error_num_params (position binary_integer)120 return binary_integer; -- 35121 pragma interface (C, server_error_num_params);122 pragma restrict_references (server_error_num_params, WNPS, RNPS, WNDS);123 function server_error_param(position binary_integer, param binary_integer)124 return varchar2; -- 36125 pragma interface (C, server_error_param);126 pragma restrict_references (server_error_param, WNPS, RNPS, WNDS);127 function partition_pos return binary_integer; -- 37128 pragma interface (C, partition_pos);129 pragma restrict_references (partition_pos, WNPS, RNPS, WNDS);130131 function sys_GetTriggerState return pls_integer;132 pragma interface (C, Sys_GetTriggerState); -- 38133 pragma restrict_references (Sys_GetTriggerState, wnds, RNDS);134 end;135 /
Package created.
SQL>SQL> create or replace public synonym dbms_standard for sys.dbms_standard 2 /
Synonym created.
SQL> grant execute on dbms_standard to public 2 /
Grant succeeded.
SQL>SQL> -- Step (III)SQL> --SQL> -- Invalidate views and synonyms which depend (directly or indirectly) onSQL> -- invalid objects.SQL> begin 2 loop 3 update obj$ o_outer set status = 6 4 where type# in (4, 5) 5 and status not in (5, 6) 6 and linkname is null 7 and ((subname is null) or (subname <> 'DBMS_DBUPGRADE_BABY')) 8 and exists (select o.obj# from obj$ o, dependency$ d 9 where d.d_obj# = o_outer.obj# 10 and d.p_obj# = o.obj#
11 and (bitand(d.property, 1) = 1) 12 and o.status > 1); 13 exit when sql%notfound; 14 end loop; 15 end; 16 /
PL/SQL procedure successfully completed.
SQL>SQL> commit;
Commit complete.
SQL>SQL> alter system flush shared_pool;
System altered.
SQL>SQL> -- Step (IV)SQL> --SQL> -- Delete Diana for tables, views, and sequencesSQL> --SQL> -- The DELETEs are coded in chunks using a PL/SQL loop to avoid runningSQL> -- into rollback segment limits.SQL> --SQL> begin 2 3 loop 4 delete from idl_ub1$ where 5 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6)) 6 and rownum < 5000; 7 exit when sql%rowcount = 0; 8 commit; 9 end loop; 10 11 -- 12 -- IDL_UB2$ must use dynamic SQL because its PIECE type is not 13 -- understood by PL/SQL. 14 -- 15 loop 16 execute immediate 17 'delete from idl_ub2$ where 18 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6)) 19 and rownum < 5000'; 20 exit when sql%rowcount = 0; 21 commit; 22 end loop; 23 24 -- 25 -- IDL_SB4$ must use dynamic SQL because its PIECE type is not 26 -- understood by PL/SQL. 27 -- 28 loop
29 execute immediate 30 'delete from idl_sb4$ where 31 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6)) 32 and rownum < 5000'; 33 exit when sql%rowcount = 0; 34 commit; 35 end loop; 36 37 loop 38 delete from idl_char$ where 39 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6)) 40 and rownum < 5000; 41 exit when sql%rowcount = 0; 42 commit; 43 end loop; 44 end; 45 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL>SQL> Rem Continue even if there are SQL errorsSQL> WHENEVER SQLERROR CONTINUE;SQL>SQL> Rem ===========================================================================SQL> Rem END utlip.sqlSQL> Rem ===========================================================================SQL>SQL> Rem Recompile all DDL triggersSQL> @@utlrdtSQL> Rem Copyright (c) 2006, Oracle. All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem utlrdt.sql - Recompile DDL triggers while still in UPGRADE modeSQL> RemSQL> Rem DESCRIPTIONSQL> Rem This script recompiles all DDL triggers in UPGRADE mode at theSQL> Rem end of one of three operations:SQL> Rem 1. DB upgradeSQL> Rem 2. utlirp to invalidate and recompile all PL/SQLSQL> Rem 3. dbmsupgnv/dbmsupgin to convert PL/SQL to native/interpretedSQL> RemSQL> Rem NOTESSQL> Rem Two DDL triggers referencing the same external object (e.g.,SQL> Rem ORA_SYSEVENT) cannot be recompiled successfully in regular mode.SQL> Rem Here is the sequence of events causing a failure:SQL> Rem 1. DDL is executed
SQL> Rem 2. Trigger 1 needs to be fired, is invalid and gets recompiledSQL> Rem 3. Trigger 1 references ORA_SYSEVENTSQL> Rem 4. ORA_SYSEVENT is invalid and gets recompiled using ALTER COMPILESQL> Rem 5. Before COMMIT, ALTER COMPILE fires DDL trigger 2SQL> Rem 6. Trigger 2 references ORA_SYSEVENT. Because ORA_SYSEVENT isSQL> Rem being recompiled, PLS-201 is raised and trigger 2 compilesSQL> Rem with errors.SQL> Rem 7. Trigger 2 compiled with errors causes all subsequent DDLs to fail.SQL> RemSQL> Rem [5476415] I've observed a self-deadlock brought on by the existence (inSQL> Rem my testing environment) of certain system triggers. While suchSQL> Rem triggers do not exist today, to forestall any problems when we do haveSQL> Rem such triggers, we'll pre-compile the ORA_* synonyms here.SQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem jmuller 10/17/06 - Fix bug 5476415: avoid self-deadlock in utlrpSQL> Rem gviswana 03/09/06 - CreatedSQL> RemSQL>SQL> SET ECHO ONSQL>SQL> declare 2 cursor ora_dict_synonyms is 3 select o.object_id from dba_objects o 4 where o.owner = 'PUBLIC' 5 and o.object_type = 'SYNONYM' 6 and o.object_name like 'ORA_%'; 7 8 cursor ddl_triggers is 9 select o.object_id from dba_triggers t, dba_objects o 10 where t.owner = o.owner and t.trigger_name = o.object_name 11 and o.object_type = 'TRIGGER' 12 and (t.triggering_event like '%ALTER%' or 13 t.triggering_event like '%DDL%'); 14 begin 15 for s in ora_dict_synonyms loop 16 dbms_utility.validate(s.object_id); 17 end loop; 18 19 for t in ddl_triggers loop 20 dbms_utility.validate(t.object_id); 21 end loop; 22 end; 23 /PL/SQL procedure successfully completed.
SQL>SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC> utlirp.sql completed successfully. All PL/SQL objects in theDOC> database have been invalidated.DOC>DOC> Shut down and restart the database in normal mode and run utlrp.sql toDOC> recompile invalid objects.
DOC>#######################################################################DOC>#######################################################################DOC>#SQL> SHUTDOWN IMMEDIATEDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP PFILE='/u01/oramig/MIG/db/tech_st/11.1.0/dbs/init_MIG.ora'ORACLE instance started.
Total System Global Area 2137886720 bytesFixed Size 2155464 bytesVariable Size 486542392 bytesDatabase Buffers 1627389952 bytesRedo Buffers 21798912 bytesDatabase mounted.Database opened.SQL> -- The following step will recompile all PL/SQL modules.SQL> -- It may take serveral hours to complete.SQL> @@ ?/rdbms/admin/utlrp.sqlSQL> RemSQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $SQL> RemSQL> Rem utlrp.sqlSQL> RemSQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem utlrp.sql - Recompile invalid objectsSQL> RemSQL> Rem DESCRIPTIONSQL> Rem This script recompiles invalid objects in the database.SQL> RemSQL> Rem When run as one of the last steps during upgrade or downgrade,SQL> Rem this script will validate all remaining invalid objects. It willSQL> Rem also run a component validation procedure for each component inSQL> Rem the database. See the README notes for your current release andSQL> Rem the Oracle Database Upgrade book for more information aboutSQL> Rem using utlrp.sqlSQL> RemSQL> Rem Although invalid objects are automatically re-validated when used,SQL> Rem it is useful to run this script after an upgrade or downgrade andSQL> Rem after applying a patch. This minimizes latencies caused bySQL> Rem on-demand recompilation. Oracle strongly recommends running thisSQL> Rem script after upgrades, downgrades and patches.SQL> RemSQL> Rem NOTESSQL> 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 theSQL> Rem script. Not following this recommendation may lead to deadlocks.SQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriateSQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuningSQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnosticsSQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catprocSQL> Rem gviswana 06/25/02 - Add documentationSQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serialSQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368SQL> Rem rburns 11/12/01 - validate all components after compilesSQL> Rem rburns 11/06/01 - fix invalid CATPROC callSQL> Rem rburns 09/29/01 - use 9.2.0SQL> Rem rburns 09/20/01 - add check for CATPROC validSQL> Rem rburns 07/06/01 - get version from instance viewSQL> Rem rburns 05/09/01 - fix for use with 8.1.xSQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33SQL> Rem skabraha 09/25/00 - validate is now a keywordSQL> Rem kosinski 06/14/00 - Persistent parametersSQL> Rem skabraha 06/05/00 - validate tables alsoSQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.SQL> Rem rshaikh 09/22/99 - quote name for recompileSQL> Rem ncramesh 08/04/98 - change for sqlplusSQL> Rem usundara 06/03/98 - merge from 8.0.5SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).SQL> Rem Mark Ramacher (mramache) was the originalSQL> Rem author of this script.SQL> RemSQL>SQL> Rem ===========================================================================SQL> Rem BEGIN utlrp.sqlSQL> Rem ===========================================================================SQL>SQL> @@utlprp.sql 0SQL>SQL>SQL> Rem Copyright (c) 2003, 2008, Oracle and/or its affiliates. All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem utlprp.sql - Recompile invalid objects in the databaseSQL> RemSQL> Rem DESCRIPTIONSQL> Rem This script recompiles invalid objects in the database.SQL> RemSQL> Rem This script is typically used to recompile invalid objectsSQL> Rem remaining at the end of a database upgrade or downgrade.SQL> RemSQL> Rem Although invalid objects are automatically recompiled on demand,SQL> Rem running this script ahead of time will reduce or eliminateSQL> Rem latencies due to automatic recompilation.SQL> RemSQL> Rem This script is a wrapper based on the UTL_RECOMP package.SQL> Rem UTL_RECOMP provides a more general recompilation interface,SQL> Rem including options to recompile objects in a single schema. PleaseSQL> Rem see the documentation for package UTL_RECOMP for more details.SQL> RemSQL> Rem INPUTSSQL> Rem The degree of parallelism for recompilation can be controlled bySQL> Rem providing a parameter to this script. If this parameter is 0 or
SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriateSQL> Rem level of parallelism based on Oracle parameters cpu_count andSQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequentialSQL> Rem recompilation is used. Please see the documentation for packageSQL> Rem UTL_RECOMP for more details.SQL> RemSQL> Rem NOTESSQL> Rem * You must be connected AS SYSDBA to run this script.SQL> Rem * There should be no other DDL on the database while running theSQL> Rem script. Not following this recommendation may lead to deadlocks.SQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem cdilling 10/08/08 - Backport cdilling_bug-7243270 from mainSQL> Rem cdilling 01/21/08 - add support for ORA-30552SQL> Rem cdilling 08/27/07 - check disabled indexes onlySQL> Rem cdilling 05/22/07 - add support for ORA-38301SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexesSQL> Rem rburns 03/17/05 - use dbms_registry_sysSQL> Rem gviswana 02/07/05 - Post-compilation diagnosticsSQL> Rem gviswana 09/09/04 - Auto tuning and diagnosabilitySQL> Rem rburns 09/20/04 - fix validate_componentsSQL> Rem gviswana 12/09/03 - Move functional-index re-enable hereSQL> Rem gviswana 06/04/03 - gviswana_bug-2814808SQL> Rem gviswana 05/28/03 - CreatedSQL> RemSQL>SQL> SET VERIFY OFF;SQL>SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2010-07-27 07:34:37
SQL>SQL> DOCDOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC> objects in the database. Recompilation time is proportional to theDOC> number of invalid objects in the database, so this command may takeDOC> a long time to execute on a database with a large number of invalidDOC> objects.DOC>DOC> Use the following queries to track recompilation progress:DOC>DOC> 1. Query returning the number of invalid objects remaining. ThisDOC> number should decrease with time.DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);DOC>DOC> 2. Query returning the number of objects compiled so far. This numberDOC> should increase with time.DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC> This script automatically chooses serial or parallel recompilationDOC> based on the number of CPUs available (parameter cpu_count) multipliedDOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).DOC> On RAC, this number is added across all RAC nodes.
DOC>DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallelDOC> recompilation. Jobs are created without instance affinity so that theyDOC> can migrate across RAC nodes. Use the following queries to verifyDOC> whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC> 1. Query showing jobs created by UTL_RECOMPDOC> SELECT job_name FROM dba_scheduler_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>DOC> 2. Query showing UTL_RECOMP jobs that are runningDOC> SELECT job_name FROM dba_scheduler_running_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>#SQL>SQL> DECLARE 2 threads pls_integer := &&1; 3 BEGIN 4 utl_recomp.recomp_parallel(threads); 5 END; 6 /
The utlrp is still running but the major part is done.