** Myself
Objective: Manual Upgrade Oracle Database from 9.2.0.4 to 11.2.0.3Preparation of 9i for Upgrade 11g
Up oracle9i timezone patch for upgrade to oracle 11g
| Command | Detail |
| cd /sapbackup/UPECC6/ORABUNDLE/5878965 /oracle/SOD/920*/OPatch/opatch apply | Oracle Interim Patch Installer version 1.0.0.0.64 Copyright (c) 2011 Oracle Corporation. All Rights Reserved.. Oracle recommends you to use the latest OPatch version and read the OPatch documentation available in the OPatch/docs directory for usage. For information about the latest OPatch and other support-related issues, refer to document ID 293369.1 available on My Oracle Support (https://myoraclesupport.oracle.com) Oracle Home : /oracle/SOD/920_64 Oracle Home Inventory : /oracle/SOD/920_64/inventory Central Inventory : /oracle/oraInventory from : /var/opt/oracle/oraInst.loc OUI location : /oracle/SOD/920_64/oui OUI shared library : /oracle/SOD/920_64/oui/lib/hpunix/liboraInstaller.sl Java location : /oracle/SOD/920_64/jdk/jre/bin/java Log file location : /oracle/SOD/920_64/.patch_storage/<patch ID>/*.log Creating log file "/oracle/SOD/920_64/.patch_storage/5878965/Apply_5878965_10-11-2013_15-26-32.log" Invoking fuser to check for active processes. Invoking fuser on "/oracle/SOD/920_64/bin/oracle" Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: lek Invalid email address. Email address/User Name: coco@coco.co.th Provide your My Oracle Support password to receive security updates via your My Oracle Support account. Password (optional): Unable to establish a network connection to Oracle. If your systems require a proxy server for outbound Internet connections, enter the proxy server details in this format: [<proxy-user>@]<proxy-host>[:<proxy-port>] If you want to remain uninformed of critical security issues in your configuration, enter NONE Proxy specificatio OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = /oracle/SOD/920_64) Is this system ready for updating? Please respond Y|N > Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = /oracle/SOD/920_64) Is this system ready for updating? Please respond Y|N > y Applying patch 5878965... Patching copy files... Inventory is good and does not have any dangling patches. Updating inventory... Verifying patch... Backing up comps.xml ... OPatch succeeded.gfux031:orasod 1 |
Step Upgrade Oracle 11g
Extract software to $ORACLE_STAGE/11203/database
Installing Software Oracle 11.2.0.3
$ORACLE_STAGE/11203/database/SAP/RUNINSTALLER
Next
No
Skip software updates
Install software only
Single instance
Add Thai
Enterprise edition
Inventory=/oracle/oraInventory
Yes
Default option :dba and oper group
Ignore because no issue
Summary
Logon root user to execute /oracle/SOD/112_64/root.sh
Finish
Upgrade Oracle 11.2.0.3 (26 step)
1 copy script to 9i
gfux031:orasod 17> pwd
/sapbackup/112_64/rdbms/admin
gfux031:orasod 18> cp -p utlu112i.sql utltzuv2.sql /oracle/SOD/920_64
2 Run utlrp script to fix any invalid object before the upgrade
@?/rdbms/admin/utlrp.sql
3 Save a script for Deprecated CONNECT Role
set linesize 170
set pages 100
spool /oracle/SOD/920_64/connect_role.sql
SELECT 'grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to '||grantee||';'
FROM dba_role_privs WHERE granted_role = 'CONNECT' AND grantee
NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR',
'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST',
'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR',
'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA',
'XDB', 'ODM');
spool off
4 check DB link
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
5 Upgrade the TIMEZONE version: # refer pre ora9
6 Run utlu112i.sql
cd /oracle/SOD/920_64
$sqlplus "/ as sysdba"
spool utlu112i.log
@utlu112i.sql
spool off
7 Gather SYS schema statistics
"EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
"
8 Check the Dictionary
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
@?/rdbms/admin/utlvalid.sql
@analyze.sql
9 Ensure the users sys and system have 'system' as their default tablespace.
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
10 Ensure that the aud$ is in the system tablespace when auditing is enabled
SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
11 Saving database files location
col name for a70
col file_name for a70
col member for a70
set feedback off
spool db_files.txt
SELECT name FROM v$controlfile;
SELECT file_name FROM dba_data_files;
SELECT group#, member FROM v$logfile;
spool off
12 externally authenticated SSL users
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
13 Set the DB in NoArchivelog mode
sqlplus "/as sysdba"
shutdown immediate;
alter database noarchivelog;
archive log stop;
shutdown immediate;
14 Modify the 9i Pfile
Comment out obsoleted parameters like:
transaction_auditing
hash_join_enabled
DDL_WAIT_FOR_LOCKS
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
=Replace all deprecated parameters like:
BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
STANDBY_ARCHIVE_DEST
log_archive_start
max_enabled_roles
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
=set the COMPATIBLE parameter to 10.1.0
compatible=10.1.0
=set the parameter CLUSTER_DATABASE=FALSE
=Add the parameter DIAGNOSTIC_DEST to hold database logs:
diagnostic_dest=/u01/oracle/ora11g/11.2.0.1/diagnostics/ORCL
When done copy the pfile to 11g ORACLE_HOME/dbs
15 Create the Diagnostic directory for the DB
mkdir /oracle/SOD/saptrace/audit
16 Set 11g Env variables
ORACLE_BASE
ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
TNS_ADMIN
CLASSPATH
PATH
dbenv_unibwdv.sh 920_64 >> 112_64
.dbenv.sh 920_64 >> 112_64
.cshrc
.dbenv_unibwdv.csh
.dbenv.csh
Update ORACLE_HOME in this profile files.
Old : /oracle/SOD/920_64
New : /oracle/SOD/112_64
17 Update oratab & oraInventory
/etc/oratab
SOD:/oracle/SOD/112_64:N
/var/opt/oracle/oraInst.loc
inventory_loc=/oracle/oraInventory
inst_group=
18 Upgrading the Database to 11gR2
sqlplus "/ as sysdba"
startup UPGRADE
CREATE TABLESPACE SYSAUX
DATAFILE '/oracle/SOD/sapdata1/system_1/sysaux01.dbf' SIZE 1200M reuse
autoextend on next 100m maxsize unlimited
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;
Start the upgrade script:
------------------------
$cd $ORACLE_HOME/rdbms/admin
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
Run the Post-Upgrade Status Tool:
--------------------------------
SQL> @?/rdbms/admin/utlu112s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script:
--------------------------------------------------------------------------------
Download that script from Metalink Note [ID 556610.1] and run it.
During the test I found some duplicate objects between SYS and SYSTEM so I followed the Metalink Note and dropped duplicate objects owned by SYSTEM user:
Warning:
Don't run the following commands unless you make sure that following objects are duplicate ones in your DB.
SQL> drop TABLE system.AQ$_SCHEDULES;
SQL> drop INDEX system.AQ$_SCHEDULES_PRIMARy;
SQL> drop PACKAGE system.DBMS_REPCAT_AUTH;
SQL> drop PACKAGE system.BODYDBMS_REPCAT_AUTH;
SQL> drop PACKAGE system.DBMS_SHARED_POOL;
SQL> drop PACKAGE system.BODYDBMS_SHARED_POOL;
SQL> drop TABLE system.UTL_RECOMP_INVALID ;
SQL> drop TABLE system.UTL_RECOMP_SORTED;
SQL> drop TABLE system.UTL_RECOMP_COMPILED;
SQL> drop TABLE system.UTL_RECOMP_BACKUP_JOBS;
SQL> drop TABLE system.UTL_RECOMP_LOG;
SQL> drop PACKAGE system.UTL_RECOMP;
SQL> drop PACKAGE system.BODYUTL_RECOMP;
SQL> drop TABLE system.CHAINED_ROWS;
19 Grant the missing privileges related to "connect" which been deprecated in 11g: Using Script been created in Step3-
grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to SAPSOD;
grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to OPS$ORASOD;
grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to OPS$SODADM;
20 ###############
Post Upgrade Steps: for oracle
###############
Check listener.ora for any modifications needed to listen on the upgraded DB.
21 Create the SPFILE & Password file
create spfile from pfile;
#restart
22 Compatibility version
alter system set compatible='11.2.0.3' scope=spfile
23 Load Product user profile information
This step is needed to tackle the error "Error accessing PRODUCT_USER_PROFILE" when you login to SQLPLUS.
Running script pupbld.sql as SYSTEM user:
SQL> conn system/xxx
SQL> @/oracle/SOD/112_64/sqlplus/admin/pupbld.sql
24 Rebuild unusable indexes
select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online parallel 4;'from dba_indexes where status ='UNUSABLE';
25 Gather Statistics on the database
This step is very crucial for DB performance.
1-Gather DICTIONARY stats:
- -----------------------
SQL> Execute DBMS_STATS.GATHER_DICTIONARY_STATS ();
#last step 2-Gather database statistics:
- --------------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE,degree => 4);
3-Gather FIXED OBJECTS stats: <Do this step after the upgrade and during production workload time*>
- --------------------------
SQL> Execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (no_invalidate => FALSE );
Note:"gather fixed objects" is not included in gather_dictionary_stats, also not included in the the daily gather statistics job. The database does not gather these statistics automatically.
26 Fix invalid objects:
-------
SQL> @?/rdbms/admin/utlrp
Upgrade Oracle 11.2.0.3 Bundle patch
/oracle/SOD/112_64/MOPa*/mopatch.sh -v -s /sapbackup/UPECC6/ORABUNDLE/SAP11203P_1309-20009979.zip
###############
Post Upgrade Steps: for SAP
###############
@/oracle/SOD/112_64/SAP/ora_upgrade/post_upgrade/post_upgrade_tasks.sql
@/oracle/SOD/112_64/SAP/ora_upgrade/post_upgrade/set_remote_os_authent.sql
@/oracle/SOD/112_64/SAP/ora_upgrade/post_upgrade/set_compatible.sql
@/oracle/SOD/112_64/SAP/ora_upgrade/post_upgrade/sapuprof_profile
##Need move controlfile after done
*.control_files='/oracle/SOD/origlogA/cntrl/cntrlSOD.dbf','/oracle/SOD/origlogB/cntrl/cntrlSOD.dbf','/oracle/SOD/sapdata1/cntrl/cntrlSOD.dbf'
POST STEPS
su – orasod
cd /oracle/SOD
dbenv_gfux031.sh 920_64 >> 112_64
.dbenv.sh 920_64 >> 112_64
.cshrc
.dbenv_unibwdv.csh
.dbenv.csh
Update ORACLE_HOME in this profile files.
Old : /oracle/SOD/920_64
New : /oracle/SOD/112_64
brconnect -u / -c -f stats -t system_stats – noworkload option
brconnect -u / -c -f stats -t oradict_stats
Check the Oracle database parameters against SAP Note 1431798 and adjust them if necessary.
You can find an automated script in SAP Note 1171650 to help you check whether your SAP system complies with the database parameter recommendations at any given point in time.
You need to create this link so that BR*Tools (which is linked to the Oracle 10.2 client) can use the newer Oracle 11.2 clients.
cd $ORACLE_HOME/lib
ln -s libnnz11.so libnnz10.so
mv <old_oracle_home>/bin <old_oracle_home>/bin-old
update environments of sodadm (sidadm user)
cd /home/sodadm
update profile files. check ORACLE_HOME value.
Updating the Oracle Instant Client
You need to ensure that the SAP system runs with at least Instant Client release 10.2.0.4 Version 5 (V5)
or Oracle 11.2.0.2.
N O T E
This step also applies to SAP systems running the 640_REL kernel with the Oracle 9.2.0.8 database.
The Instant Client 10.2.0.4 or 11.2.0.2 is used immediately after the kernel switch to 640_EX2 or
higher (including SAP 7.XX kernels).
Procedure
Update the Oracle Database Instant Client for UNIX as described in SAP Note 1431794.
Note :Add the following entry to the tnsnames.ora file that is used by the <sapsid>adm user:
LISTENER_<DBSSID>.WORLD = (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST
= <hostname>)(PORT = <listener_port>))
The upgrade is completely and successfuly finished.
Change oracle password time life ,lock ,grace ,attempt
select resource_name,limit from dba_profiles
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
#To unlimited.
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;
select resource_name,limit from dba_profiles
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
alter system reset COMMIT_WRITE scope = spfile sid = '*';
alter system reset CURSOR_SPACE_FOR_TIME scope = spfile sid = '*';
alter system reset DB_FILE_MULTIBLOCK_READ_COUNT scope = spfile sid = '*';
alter system reset HASH_JOIN_ENABLED scope = spfile sid = '*';
alter system reset LOG_ARCHIVE_START scope = spfile sid = '*';
alter system reset OPTIMIZER_INDEX_COST_ADJ scope = spfile sid = '*';
alter system reset OPTIMIZER_MODE scope = spfile sid = '*';
alter system reset STATISTICS_LEVEL scope = spfile sid = '*';
alter system reset TIMED_STATISTICS scope = spfile sid = '*';
alter system reset TRACE_ENABLED scope = spfile sid = '*';
#cd /sapmnt/<SID>/exe
#sqlplus /nolog @sapconn_role
#sqlplus /nolog @sapdba_role.sql SOD
check oracle client
cd /oracle/clinet/11x*/
./genezi -v
Client Shared Library 64-bit - 11.2.0.2.0
System name: HP-UX
Release: B.11.31
Version: U
Machine: 9000/800
Operating in Instant Client mode.
Small timezone file = timezone_14.dat
Large timezone file = timezlrg_14.dat
brconnect -c -u / -f stats -t all -f collect -p 4
if connection error
Check: Connection
Logon oracle : orasid
select * from OPS$SODADM.sapuser;
Solve:
Reset password OPS user to ‘sap’
delete from OPS$SODADM.sapuser;
insert into OPS$SODADM.sapuser values('SAPSOD','
commit;
Reset password OPS user to ‘sap’ in encrypt
Logon csdadm
select granted_role from dba_role_privs where grantee = 'OPS$SODADM';
grant DBA to OPS$SODADM;
grant DBA to OPS$ORASOD;
brconnect -u / -f chpass -o SAPSOD -p
R3load -testconnect
CREATE TABLE OPS$ORASOD.sapuser AS (SELECT * FROM OPS$SODADM.sapuser);
select * from OPS$SODADM.sapuser;
select * from OPS$ORASOD.sapuser;
update kernel to 640_ext2 (oracle 11g support)
R3trans error
cp -r /sourcesap/ker640_ext2 /sapmnt/SOD/exe
update sapdba tool to 700 (oracle 11g support)
cp -r /sourcesap/ker700_dba /sapmnt/SOD/exe
update oracle client 10 for <sid>adm use to 700 & 640_ext (oracle 10g support)
cp -r /sourcesap/client10_11/* /oracle/client
sapcar -xvf /oracle/client/10x*
add user orasod to sapsys group
sapsys::200:sodadm,orasod
change group write /sapmnt/SOD/exe for orasod
chmod 775 /sapmnt/SOD/exe
# backint interface will be copy initSOD.sap to exe folder permission
shutdown immediate
startup mount
alter database noarchivelog
1. Find out the flash recovery area location.
show parameter db_recovery_file_dest;
show parameter db_recovery_file_dest_SIZE;
2. Disable the Flash Recovery Area
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
adrci
SHOW CONTROL
DIA-48448: This command does not support multiple ADR homes
adrci> show home
ADR Homes:
diag/tnslsnr/gfux032/listener
diag/rdbms/sod/SOD
adrci> set HOME diag/rdbms/sod/SOD
adrci> SHOW CONTROL
ADR Home = /oracle/SOD/saptrace/diag/rdbms/sod/SOD:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- ------
2077535307 720 8760 2013-10-24 18:16:24.971952 +07:00 2013-11-07 18:24:13.190414 +07:00 1 2 80 1 2013-10-24 18:16:24.971952 +07:00
1 rows fetched
for prod
SET CONTROL (SHORTP_POLICY = 72 ) SET CONTROL (SHORTP_POLICY = 360 )
SET CONTROL (LONGP_POLICY = 4380 )
adrci> SHOW CONTROL
0 comments:
Post a Comment