문서 ID: 133920.1 유형: BULLETIN
마지막 갱신 날짜: 03-APR-2008 상태: PUBLISHED
Checked for relevance on 02-OCT-2006
PURPOSE
-------
This document is created for use as a guideline and checklist when
manually upgrading oracle.
SCOPE & APPLICATION
-------------------
Database adminstrators
UPGRADE CHECKLIST
-----------------
1. ----------------------------------------------------------------------------
Perform a complete online backup!!!! (or a full cold backup if you prefer)
-- root account crontab info: /usr/tivoli/tsm/backup/onbackup.sh
-- =======================================================================
-- TOTAL STATUS: SUCCESS
-- START DATE & TIME: 2009-03-12 15:10
-- END DATE & TIME: 2009-03-13 14:09
-- TOTAL SIZE: 1045 GB
-- TSM SERVER: HJSMS_TSM
-- TSM CLIENT: HDDDB
-- =======================================================================
2. ----------------------------------------------------------------------------
Verify all necessary OS patches are installed.
Example for Solaris:
$ showrev -p
--Example for AIX:
--# instfix -iak
3. ----------------------------------------------------------------------------
Verify the kernel parameters according to the installation guide of the
new version
Example for Solaris:
$ cat /etc/system
4. ----------------------------------------------------------------------------
Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
Echo $ORACLE_HOME
5. ----------------------------------------------------------------------------
What version is running? What option is installed?
Select * from v$version;
Select * from v$option;
6. ----------------------------------------------------------------------------
I the procedural option installed(pl/sql)?
Start svrmgrl
7. ----------------------------------------------------------------------------
Verify characterset of the database:
$ Sqlplus SYS/
Select name, substrb(value$,1,40) value from props$;
8. ----------------------------------------------------------------------------
Check for corruption in the dictionary, use:
Set verify off
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.
This script (analyze.sql) should not return any errors.
9. ----------------------------------------------------------------------------
List all objects that are not VALID. After migration all objects will be
invalid, this list returns a list of fatal objects.
Select substr(owner,1,12) owner, substr(object_name,1,30) object,
Substr(object_type,1,30) type,status from dba_objects where status <>'VALID';
To create a script to compile all invalid objects run the following, this
creates a script Obj.sql.
set verify off
set space 0
Set heading off
Set feedback off
Set pages 1000
spool obj.sql
select 'set termout on' from dual;
select 'set echo on' from dual;
select 'alter trigger '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='TRIGGER';
select 'alter package '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='PACKAGE';
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status <> 'VALID'
and object_type='PACKAGE BODY';
select 'alter procedure '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='PROCEDURE';
select 'alter function '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='FUNCTION';
select 'alter view '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='VIEW';
/
spool off
Run the script and than rerun the query to get invalid objects.
spool invalid_pre.lst
Select substr(owner,1,12) owner,
Substr(object_name,1,30) object,
Substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off
This creates a file with a list of invalid objects.
10. ---------------------------------------------------------------------------
List the grants,
If the upgrade fails and at the dictionary was already rebuild, grants are lost.
If you want to go back it is advisable to have a list of grants. Use the
following script:
#!/bin/sh
#
# struct.sh
#--:
#--: generates DDL of database
#--:
ORAENV_ASK=NO; export ORAENV_ASK
ORACLE_SID=$3; export ORACLE_SID
. oraenv
ORAENV_ASK=YES; export ORAENV_ASK
Exp userid=$1/$2 file=/tmp/struct compress=no full=y rows=n
Imp userid=$1/$2 file=/tmp/struct full=y show=y 2> /tmp/contents.lst
Rm /tmp/struct.dmp
Awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"REVOKE / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\n" );
}' < /tmp/contents.lst > /tmp/struct1
rm /tmp/contents.lst
sed /^$/d < /tmp/struct1 > /tmp/struct2
rm /tmp/struct1
fold -s -w75 /tmp/struct2 > $3.sql
rm /tmp/struct2
The script takes 3 arguments username, password and SID. The script SID.sql
Is generated. If only grants are needed, change the line:
' fold -s -w75 /tmp/struct2 > $3.sql '
by
' grep ^GRANT /tmp/struct2 | fold -s -w75 > $3.sql '
11. ---------------------------------------------------------------------------
Ensure that all Snapshot refreshes are succesfully completed.
And replication is stopped.
$ Sqlplus SYS/
Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
12. ---------------------------------------------------------------------------
Stop the listener for the database
$ lsnrctl
Lsnrctl> stop
13. ---------------------------------------------------------------------------
Ensure no files need media recovery:
$ sqlplus SYS/
Select * from v$recover_file;
This should return no rows
14. ---------------------------------------------------------------------------
Ensure no files are in backup mode:
Select * from v$backup where status!='NOT ACTIVE';
This should return no rows.
15. ---------------------------------------------------------------------------
Resolve any outstanding unresolved distributed transaction:
Select * from dba_2pc_pending;
If this returns rows you should do the following:
Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('
Commit;
16. ---------------------------------------------------------------------------
If you are upgrading from an 8.0 release check no users or roles are called
either MIGRATE or OUTLN.
Select * from dba_users where username in ('MIGRATE','OUTLN');
Select * from dba_roles where role in ('MIGRATE','OUTLN');
If so these users/roles will need to be dropped prior to migration.
17. ---------------------------------------------------------------------------
Disable all batch and cron jobs.
18. ---------------------------------------------------------------------------
Prepare the system rollback segment:
Alter rollback segment system storage (maxextents 121 next 1M);
19. ---------------------------------------------------------------------------
Ensure plenty of free space in the SYSTEM tablespace. A minimum of 50 Mb
free space.
Select max(bytes) from dba_free_space where tablespace_name='SYSTEM';
20. ---------------------------------------------------------------------------
Ensure the users sys and system have 'system' as their default tablespace.
Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');
21. ---------------------------------------------------------------------------
To modify use:
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;
22. ---------------------------------------------------------------------------
Ensure aud$ table is in System tablespace when auditing is enabled.
Select tablespace_name from dba_tables where table_name='AUD$';
If the aud$ table is in a non-SYSTEM Tablespace then auditing needs
to be disabled before migration. This is because auditing will try to
acquire a non-system rollback segment. However these are all taken
offline during migration.
Auditing during migration can only be achieved with the aud$ in
the System tablespace, as use will be made of the System rollback
segment.
23. ---------------------------------------------------------------------------
Note down where all control files are located.
Select * from v$controlfile;
24. ---------------------------------------------------------------------------
Note down all sysdba users.
Select * from v$pwfile_users;
If a passwordfile is used copy it to the new location. On unix the default
is $ORACLE_HOME/dbs/orapw
%ORACLE_HOME%\database\orapw
25. ---------------------------------------------------------------------------
Shutdown the database
$ svrmgrl
SVRMGR> Shutdown immediate
26. ---------------------------------------------------------------------------
Change the init.ora file:
- Make a backup of the init.ora file.
- Ensure there is a value for DB_BLOCK_SIZE
- Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- Comment out the AQ_TM_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- If archiving is enabled set LOG_ARCHIVE_START=TRUE
- Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST
are set to an explicit directory
- Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade.
Note: Only applies for upgrades/migrations to 8i/9i. See Note 149948.1 for
further information.
- Set the parameter OPTIMIZER_MODE to RULE during the upgrade. This is a
workaround for Bug 1362374.
- Comment out obsoleted parameters(list in appendix A).
- Comment out SNAPSHOT_REFRESH_? parameters
- Ensure the COMPATIBLE parameter points to the current
Version. This to ensure a more easy downgrade when something goes wrong.
We can alter this to point to the new release when everything is tested.
27. ---------------------------------------------------------------------------
Check for adequate freespace on archive log destination file systems.
28. ---------------------------------------------------------------------------
Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG
29. ---------------------------------------------------------------------------
If needed copy the listener.ora and the tnsnames.ora to the new location
(when no TNS_ADMIN env. Parameter is used)
cp $ORACLE_HOME/network/admin
30. ---------------------------------------------------------------------------
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.
C:\ORADIM80 ?DELETE ?SID ORCL
And create the ORACLE 8I service:
C:\ORADIM ?NEW ?SID ORCL ?INTPWD
-STARTMODE AUTO ?PFILE ORACLE_HOME\DATABASE\init.ora
31. ---------------------------------------------------------------------------
If needed copy the init.ora file to the new oracle_home or
Create a link to the init.ora.
cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora
OR
Ln ?s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora
Also check 'ifile' parameters in the init.ora, to be set to the correct file.
if an IFILE is used, verify the above mentioned parameter for the init.ora
and copy this to the correct location. Change the IFILE entry in the init.ora
file when this file changes from location.
32. ---------------------------------------------------------------------------
Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:
33. ---------------------------------------------------------------------------
Update the enviroment variables like ORACLE_HOME and PATH
$ . oraenv
34. ---------------------------------------------------------------------------
Make sure the following enviroment variables point to the new
Release directories:
- ORACLE_HOME
- PATH
- ORA_NLS33
- ORACLE_BASE
- LD_LIBRARY_PATH
- ORACLE_PATH
For HP-UX systems verify the SHLIB_PATH parameter points to the new release
directories.
$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS33
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH
HP-UX:
$ env | grep SHLIB_PATH
35. ---------------------------------------------------------------------------
Run the upgrade script:
$ cd /$ORACLE_HOME/rdbms/admin
Svrmgrl
SVRMGR> Connect internal
SVRMGR> Startup restrict
SVRMGR> Spool catoutu.log
Run the appropriate script for your version.
From To Only Script to Run
==== === ==================
8.0.3 8.0.4 or higher @u0800030.sql
8.0.4 8.0.5 or higher @u0800040.sql
8.0.5 8.0.6 or higher @u0800050.sql
8.0.6 8.1.5 or higher @u0800060.sql
8.1.3 8.1.5 or higher @u0801030.sql
8.1.4 8.1.5 or higher @u0801040.sql
8.1.5 8.1.6 or higher @u0801050.sql
8.1.6 8.1.7 @u0801060.sql
SVRMGR> spool off
Each of these scripts are a direct upgrade path from the version you are
on to 8.1.x. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.
Possible problem:
You have just installed the binaries for Oracle 8.1.6. You alreadyhave
Oracle 8.1.5 installed and a database created with 8.1.5. You want to
manually upgrade the 8.1.5 database to 8.1.6. You did not install the
Migration Utility, because you are going to upgrade manually. When you
actually run the the following script:
u0801050.sql
You receive an error 'cannot find i0801050.sql'.
Solution 1:
The i0801050.sql is not installed unless you install the Migration
Utility, eventhough it is required when upgrading the database
Manually. So, you must go back and install the Migration Utility.
Solution 2:
Using your favorite text editor, create the i0801050.sql script in
$ORACLE_HOME/rdbms/admin and add the following instruction:
alter table argument$ add pls_type varchar2(30);
-- 30 = M_IDEN
Now rerun your upgrade procedure and it should complete without errors.
The file i0801050.sql is called by the script u0801050.sql. This file is
only installed with the Migration Utility.
36. ---------------------------------------------------------------------------
Shutdown the database and startup in restricted mode:
SVRMGR> Shutdown (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SVRMGR> Startup restrict
37. ---------------------------------------------------------------------------
Run catrep script if replication is used:
$ cd $ORACLE_HOME/rdbms/admin
$ svrmgrl
SVRMGR> connect internal
SVRMGR> Startup restrict
SVRMGR> spool catoutrp.log
SVRMGR> @catrep
38. ---------------------------------------------------------------------------
Run post-catrep advanced replication upgrade script, if needed:
Run the appropriate script for your version.
From Only Script to Run
==== ==================
8.0.3 @r0800030.sql
8.0.4 @r0800040.sql
8.0.5 @r0800050.sql
8.0.6 @r0800050.sql (Same as 8.0.5)
This script do not exist for upgrades from an earlier 8.1.x version.
As it is not necessary to run this script when upgrading from an
earlier 8.1.x version.
39. ---------------------------------------------------------------------------
Run script to recompile invalid pl/sql modules:
SVRMGR> @utlrp
40. ---------------------------------------------------------------------------
Edit init.ora file:
- put back the old value for the job_queue_processes parameter
- put back the old value for the aq_tm_processes parameter
- remove the parameter _system_trig_enabled from the init.ora file. This
parameter was explicitly set to false during the upgrade.
- modify the log_archive_dest parameter specify only the path, but make sure it
ends with a '/'. (remove the format)
e.g. log_archive_dest=/path/arch into log_archive_dest=/path/
- Modify the marameter log_archive_format and add the format previously
removed from the log_archive_dest.
E.g log_archive_format=arch%t_SID_%s.log
41. ---------------------------------------------------------------------------
Shutdown the database and startup the database normal.
$ svrmgrl
SVRMGR> Connect internal
SVRMGR> Shutdown
SVRMGR> Startup
42. ---------------------------------------------------------------------------
Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.
--Before: (ORACLE_HOME = /oracle2/app/oracle/product/816)
--After: (ORACLE_HOME = /oracle2/app/oracle/product/817)
43. ---------------------------------------------------------------------------
Start the listener
$ lsnrctl
LSNRCTL> start
44. ---------------------------------------------------------------------------
Enable cron and batch jobs
45. ---------------------------------------------------------------------------
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y
--Before: HDDDB:/oracle2/app/oracle/product/816:N
--After: HDDDB:/oracle2/app/oracle/product/817:N
46. ---------------------------------------------------------------------------
When everything is well tested, update the compatible parameter in the
init.ora file and restart to the new release number.
Compatible=8.1.x where x is the release number
--/oracle2/app/oracle/admin/HDDDB/pfile/initHDDDB.ora
--Before: compatible = "8.1.0"
--After: compatible = "8.1.7"
---------------------------------------------------------------------------
Appendix A: Obsolete parameter
8.1.5 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
8.1.6 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
optimizer_search_limit (not obsolete in 8.1.5)
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
8.1.7 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
optimizer_search_limit
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
댓글 없음:
댓글 쓰기