Oracle Database Upgrade von 11.2.04 auf 12.1.02 (SAP technisch)

Oracle Upgrade von 11.2.04 auf 12.1.02
###########
Zuvor unbedingt ein DB Fullbackup erstellen (offline und online_cons)
###########
"oracle" user anlegen und DB umswitchen

Auf dem Oracle DB Server: 

groupadd oinstall
useradd -g oinstall -G dba,oper -c "Oracle Software Owner" -m -s "/bin/sh" oracle 
passwd oracle
su - oracle -c "id; pwd"
su - oracle
echo $PATH 
	sollte vorhanden sein ":/usr/local/bin:"
su - al2adm -c "id; pwd"

als "root"
mkdir -p /home/oraal2
chown oraal2:dba /home/oraal2
usermod -d /home/oraal2 oraal2
usermod -g dba -G oper,oinstall oraal2
cd /oracle/AL2
cp -frv .profile .login .bashrc .cshrc .sap* .dbenv* .j2eeenv* /home/oraal2
cp -frv startdb stopdb /home/oraal2
chown -R oraal2:dba /home/oraal2

su - oraal2
env | grep -i DB_SID
	DB_SID=AL2
env | grep -i ORACLE_
	ORACLE_SID=AL2
	ORACLE_HOME=/oracle/AL2/112_64
	ORACLE_BASE=/oracle
env | grep -i NLS
	NLS_LANG=AMERICAN_AMERICA.UTF8
env | grep -i SAPDA
	SAPDATA_HOME=/oracle/AL2
cat /etc/oratab

su - oracle 
. oraenv
	ORACLE_SID = [oracle] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/11204
env | grep ORACLE
	ORACLE_SID=AL2
	ORACLE_BASE=/oracle
	ORACLE_HOME=/oracle/AL2/112_64

su - al2adm
env | grep ORACLE
	ORACLE_SID=AL2
	ORACLE_HOME=/oracle/AL2/112_64
	ORACLE_BASE=/oracle

su - oraal2
env | grep ORACLE
	ORACLE_SID=AL2
	ORACLE_HOME=/oracle/AL2/112_64
	ORACLE_BASE=/oracle

Eigentümer der Oracle Verzeichnis umsetzten:
Download von orasid2oracle.sh 	
1915317 - Migration des Software-Eigentümers zu 'oracle'

su - oraal2
chown oraal2:sapsys /home/oraal2/orasid2oracle.sh
chmod 777 /home/oraal2/orasid2oracle.sh
env | grep SID
	DB_SID=AL2
	ORACLE_SID=AL2
sudo SAPDATA_HOME=$SAPDATA_HOME ORACLE_BASE=$ORACLE_BASE DB_SID=$DB_SID ./orasid2oracle.sh -v
sudo SAPDATA_HOME=$SAPDATA_HOME ORACLE_BASE=$ORACLE_BASE DB_SID=$DB_SID ./orasid2oracle.sh -v -exec

Software-Eigentümer des Oracle Home von oraal2 auf oracle ändern.
Download von "clone_oh-1.7-005.zipâ
1983457 - Klonen von Oracle Homes unter Unix und Linux

SAP Instanz stoppen
su - al2adm
stopsp all

DB und Listener stoppen
su - oraal2
sqlplus	/ as sysdba
	shutdown immediate;
	exit;
lsnrctl stop
ps -ef | grep -i ora

chown -R oraal2:sapsys /home/oraal2/pkg_clone_oh
su - oraal2
cd /home/oraal2/pkg_clone_oh
chmod -R 777 *
./clone_oh.sh -version
	######################################################################
	### clone_oh.sh
	### Oracle Home Cloning
	### Copyright (c) Oracle Corporation 2015. All Rights Reserved.
	######################################################################
	clone_oh.sh - Version
	Version      2.0
	Patch level  002
	Release date 2015-11-04
	SAP note     1983457
	Oracle Home Cloning Package Version 2.0 Patchlevel 002
./clone_assistant.sh
	Enter name of the Oracle Software Owner:
		[oraal2]: oraal2
	( 3) Action 'config'    : Changing the configuration of an existing Oracle home
	Enter action code or action id.
		[clone]: 3
	Enter Oracle database release:
		[11.2]: 11.2
	Enter location for target Oracle home (ORACLE_HOME):
		[/oracle/AL2/1120XCLONE]: /oracle/AL2/11204								#Darf nicht der Symlink sein.
	Enter name for target Oracle home (ORACLE_HOME_NAME):
	If you do not specify an Oracle home name, it will be generated by OUI.
		[]: 
	Enter location for target Oracle base (ORACLE_BASE):
	Reference: SAP Note 1521371
		[/oracle/AL2]: /oracle/AL2
	Enter name of OS group (OSDBA):
		[dba]: dba
	Enter name of OS group (OSOPER):
		[oper]: oper
	Do you want to change the software owner for this Oracle home?
		[no]: yes
	Enter name of Oracle Software Owner:
		[oracle]: oracle
	Enter name of primary group of Oracle Software Owner:
		[oinstall]: oinstall
	Enter name of super user:
		[root]: root
	Enter location for oraInst.loc:
		[/etc/oraInst.loc]: /etc/oraInst.loc
	Enter location for scripts (script directory):
		[/home/oraal2/pkg_clone_oh/clone_scripts]: /home/oraal2/pkg_clone_oh/clone_scripts
	Do you want to run clone_oh.sh in verbose mode?
		[no]: yes
	Do you want to generate the scripts?
		[yes]: yes
	Do you want to see a list of the generated scripts?
		[yes]: yes

cd clone_scripts/		
./config01_show_env.sh
./config02_detach_oh.sh
exit
als root
cd /home/oraal2/pkg_clone_oh/clone_scripts/
./config03_set_swowner.sh
su - oracle 
. oraenv
	ORACLE_SID = [oracle] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/11204
cd /home/oraal2/pkg_clone_oh/clone_scripts
./config04_clone_oh.sh
	The following configuration scripts need to be executed as the "root" user.
	/oracle/AL2/11204/root.sh
	To execute the configuration scripts:
		1. Open a terminal window
		2. Log in as "root"
		3. Run the scripts
exit
als root
/oracle/AL2/11204/root.sh
cd /home/oraal2/pkg_clone_oh/clone_scripts
./config05_run_rootsh.sh
cat /oracle/AL2/11204/install/root_oracle-sap_2016-01-18_14-20-54.log
su - oraal2 
cd /home/oraal2/pkg_clone_oh/clone_scripts
./config06_cleanup.sh 

ORACLE_BASE beim al2adm auf "/oracle/AL2" setzen
su - al2adm
cd /home/al2adm
vi .dbenv.csh
vi .dbenv_oracle-sap.csh
vi .dbenv_oracle-sap.sh
vi .dbenv.sh
grep ORACLE_BASE .dbenv*
	oracle-sap:al2adm 19> grep ORACLE_BASE .dbenv*
	.dbenv.csh:## ORACLE_BASE set now server version dependent
	.dbenv.csh:#setenv ORACLE_BASE     /oracle
	.dbenv.csh:    setenv ORACLE_BASE     /oracle/${DB_SID}
	.dbenv.csh:    setenv ORACLE_BASE     /oracle/AL2
	.dbenv.csh:    setenv ORACLE_BASE     /oracle/AL2
	.dbenv_oracle-sap.csh:## ORACLE_BASE set now server version dependent
	.dbenv_oracle-sap.csh:#setenv ORACLE_BASE     /oracle
	.dbenv_oracle-sap.csh:    setenv ORACLE_BASE     /oracle/${DB_SID}
	.dbenv_oracle-sap.csh:    setenv ORACLE_BASE     /oracle/AL2
	.dbenv_oracle-sap.csh:    setenv ORACLE_BASE     /oracle/AL2
	.dbenv_oracle-sap.sh:# ORACLE_BASE now depends on oracle server version
	.dbenv_oracle-sap.sh:#ORACLE_BASE=/oracle; export ORACLE_BASE
	.dbenv_oracle-sap.sh:    ORACLE_BASE=/oracle/${DB_SID}; export ORACLE_BASE
	.dbenv_oracle-sap.sh:    ORACLE_BASE=/oracle/AL2; export ORACLE_BASE
	.dbenv_oracle-sap.sh:    ORACLE_BASE=/oracle/AL2; export ORACLE_BASE
	.dbenv.sh:# ORACLE_BASE now depends on oracle server version
	.dbenv.sh:#ORACLE_BASE=/oracle; export ORACLE_BASE
	.dbenv.sh:    ORACLE_BASE=/oracle/${DB_SID}; export ORACLE_BASE
	.dbenv.sh:    ORACLE_BASE=/oracle/AL2; export ORACLE_BASE
	.dbenv.sh:    ORACLE_BASE=/oracle/AL2; export ORACLE_BASE

Listener und Oracle DB als neuen Owner starten
Check ob wirklich nötig.
su - oracle
. oraenv
	ORACLE_SID = [Al2] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/11204
lsnrctl start
sqlplus / as sysdba
startup;
select status from v$instance;
	STATUS
	------------
	OPEN

auf SAP Appl. Server
su - al2adm
R3trans -d
startsap all
########################

Auf dem SAP Appl. Server
Oracle Client Update auf SAP Appl. Srv.

mkdir -p /oracle/client/12x
chown al2adm:sapsys 12x
cd /oracle/client/12x

Oracle Client im Verz. entpacken
SAPCAR -xvf OCL12164.SAR
ln -s instantclient_12102 instantclient
ls -al
cd /oracle/client
mv 11x_64 11x_64_save
ln -s /oracle/client/12x /oracle/client/11x_64
ls -al

######
#Notwendig für ab Oracle 12.
#SSFS konfiguieren
DEFAULT.PFL
rsec/ssfs_datapath = $(DIR_GLOBAL)$(DIR_SEP)security$(DIR_SEP)rsecssfs$(DIR_SEP)data
rsec/ssfs_keypath = $(DIR_GLOBAL)$(DIR_SEP)security$(DIR_SEP)rsecssfs$(DIR_SEP)key
rsdb/ssfs_connect = 1

echo setenv RSEC_SSFS_DATAPATH /usr/sap/AL2/SYS/global/security/rsecssfs/data >> .sapenv.csh
echo setenv RSEC_SSFS_KEYPATH /usr/sap/AL2/SYS/global/security/rsecssfs/key >> .sapenv.csh
echo setenv RSEC_SSFS_DATAPATH /usr/sap/AL2/SYS/global/security/rsecssfs/data >> .sapenv_"$HOST".csh
echo setenv RSEC_SSFS_KEYPATH /usr/sap/AL2/SYS/global/security/rsecssfs/key >> .sapenv_"$HOST".csh
rsecssfx put DB_CONNECT/DEFAULT_DB_USER SAPSR3 -plain		ggf. Schema beachten (SAPSR3) "select username, account_status from dba_users; "
rsecssfx put DB_CONNECT/DEFAULT_DB_PASSWORD Test1234
rsecssfx list
	|---------------------------------------------------------------------------------|
	| Record Key                     | Status             | Timestamp of last Update  |
	|---------------------------------------------------------------------------------|
	| DB_CONNECT/DEFAULT_DB_PASSWORD | Encrypted          | 2016-01-19  11:49:06  UTC |
	| DB_CONNECT/DEFAULT_DB_USER     | Plaintext          | 2016-01-19  11:48:45  UTC |
	|---------------------------------------------------------------------------------|
	Summary
	-------
	Active Records    : 2 (Encrypted : 1, Plain : 1, Wrong Key : 0, Error : 0)
	Datafile Location : /usr/sap/AL2/SYS/global/security/rsecssfs/data/SSFS_AL2.DAT (when existing)
	Keyfile Location  : /usr/sap/AL2/SYS/global/security/rsecssfs/key/SSFS_AL2.KEY (when existing)
echo setenv rsdb_ssfs_connect 1 >> .sapenv.csh
echo setenv rsdb_ssfs_connect 1 >> .sapenv_"$HOST".csh
echo export rsdb_ssfs_connect=1 >> .sapenv.sh
echo export rsdb_ssfs_connect=1 >> .sapenv_"$HOST".sh

Auf dem Oracle DB Server
?? Test ob wirlich notwendig
sqlplus / as sysdba
	alter system reset remote_os_authent;
	shutdown immediate;
	startup;

sqlplus system/Test1234
	drop table ops$al2adm.sapuser;
	exit;

sqlplus / as sysdba
	create user al2$adm identified by Test1234;
	grant sapdba to al2$adm;
	select username, account_status from dba_users; 
	exit;
/sapmnt/AL2/exe/brconnect -u / -c -f chpass -o al2$adm -p Test1234 -s brtools 

#################	
Scripte anpassen Auf dem SAP Appl. Srv.
cd /home/al2adm
grep instantclient .*
	.dbenv.csh:  set ADD=/oracle/client/11x_64/instantclient
	.dbenv.sh:  ADD=/oracle/client/11x_64/instantclient
	.dbenv_saplnx.csh:  set ADD=/oracle/client/11x_64/instantclient
	.dbenv_saplnx.sh:  ADD=/oracle/client/11x_64/instantclient
cp -frv .dbenv.sh .dbenv.sh.20160119
cp -frv .dbenv.csh .dbenv.csh.20160119
cp -frv .dbenv_saplnx.csh .dbenv_saplnx.csh.20160119
cp -frv .dbenv_saplnx.sh .dbenv_saplnx.sh.20160119
vi .dbenv.csh -> ÃŮdern "set ADD=/oracle/client/11x_64/instantclient" nach "set ADD=/oracle/client/12x/instantclient"
vi .dbenv.sh -> ÃŮdern "set ADD=/oracle/client/11x_64/instantclient" nach "set ADD=/oracle/client/12x/instantclient"
vi .dbenv_saplnx.csh -> ÃŮdern "set ADD=/oracle/client/11x_64/instantclient" nach "set ADD=/oracle/client/12x/instantclient"
vi .dbenv_saplnx.sh -> ÃŮdern "set ADD=/oracle/client/11x_64/instantclient" nach "set ADD=/oracle/client/12x/instantclient"
su - al2adm
env |grep LIB
	LD_LIBRARY_PATH=/usr/sap/AL2/SYS/exe/run:/usr/sap/AL2/SYS/exe/uc/linuxx86_64:/oracle/client/12x/instantclient
cd /sapmnt/AL2/exe
ldd dboraslib.so 
cd /oracle/client/12x/instantclient_12102
./genezi -v
R3trans -d
cat trans.log
	Auf "SSFS" achten und "Oracle client version: 12.1.0.2.0, V2, default build, (dbsl 721 180915, UNICODE[2])"
###############

Auf dem DB Server
Update BRTools auf 7.40

prüfen ob folgende Verz. vorhanden sind. Wenn nicht anlegen
mkdir /oracle/AL2/11204/saptrace
mkdir /oracle/AL2/11204/sapreorg
mkdir /oracle/AL2/11204/sapbackup
mkdir /oracle/AL2/11204/saparch
mkdir /oracle/AL2/11204/sapcheck

su - al2adm
cd /sapmnt/AL2/exe
chown al2adm:sapsys br*
SAPCAR -xvf DBATL740O11_20-80000788.SAR
exit (als root)
cd /sapmnt/AL2/exe
./saproot.sh AL2
./oraroot.sh AL2 oracle
ls -l br*
	-rwsrwsr-- 1 oracle oinstall 10131558 Dec 30 13:09 brarchive
	-rwsrwsr-- 1 oracle oinstall 10220383 Dec 30 13:09 brbackup
	-rwsrwsr-- 1 oracle oinstall 12273581 Dec 30 13:09 brconnect
	-rwsrwsr-- 1 oracle oinstall 10694987 Dec 30 13:09 brrecover
	-rwsrwsr-- 1 oracle oinstall  6272430 Dec 30 13:05 brrestore
	-rwsrwsr-- 1 oracle oinstall 12747194 Dec 30 13:09 brspace
	-rwxr-xr-x 1 al2adm sapsys    6883481 Dec 30 13:05 brtools

su - oracle
. oraenv
	ORACLE_SID = [oracle] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/11204
cp -frv /oracle/AL2/112_64/dbs/initAL2.sap /oracle/AL2/sapprof/
cd /oracle/AL2/sapprof/
touch initAL2.ora
ls -al
	-rw-r--r-- 1 oracle oinstall     0 Jan 19 14:52 initAL2.ora
	-rw-r-xr-x 1 oracle oinstall 28108 Jan 19 14:49 initAL2.sap
chmod 664 *
sqlplus / as sysdba
	select USERNAME, ACCOUNT_STATUS from dba_users;
	create user ops$oracle identified externally;
	grant sapdba to ops$oracle;
	select USERNAME, ACCOUNT_STATUS from dba_users;
		OPS$ORACLE                     OPEN
	exit;
exit;
su - al2adm (darauf achten das <sid>adm in den selben Gruppen ist wie oracle und auf die UIDs in den beiden Server achten)
su - oracle
. oraenv
	ORACLE_SID = [oracle] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/11204
/sapmnt/AL2/exe/brconnect  -u / -f check
	BR0801I BRCONNECT 7.40 (20)
	Last successful BRCONNECT check run: caaaaaaa.chk 0000-00-00 00:00:00
	BRCONNECT completed successfully with warnings

############
Installation Oracle Software 12.1
Inst. Patch Set 12.1.0.2

Auf dem DB Server
su - oracle
. oraenv
	ORACLE_SID = [oracle] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/11204
cd /etc/
ls -al ora*
	-rw-r--r-- 1 root   root      50 Jan 12 07:55 oraInst.loc
	-rw-rw-r-- 1 oracle oinstall 741 Jan 12 07:56 oratab
cat oraInst.loc
	inventory_loc=/oracle/oraInventory
	inst_group=dba
cat oratab
cd /oracle
cd /etc/profile.d/
ls -al ora* und ggf. wegkopieren
export DB_SID=AL2
export DISPLAY=172.17.190.1:0.0
xclock
cd /oracle
cp -frv oraInventory oraInventory.20160119		ggf. als root
cd ora_12/entpackt/database/SAP
./RUNINSTALLER -version
	. RUNINSTALLER 12.1.0.2.0 (058)
	. Oracle Release   12.1.0.2.0
	. Release date     2015-03-02
	. Patch level      058
	. Release status   rel
	. SAP note         1915301
./RUNINSTALLER -check
	Problem:
		Reference data is not available for release "12.1" on the operating system distribution "Linux3.12.28-4-default"
	Lösung:
		???
		
./RUNINSTALLER
	Wizard folgen
	Email	nichts
	Install database software only
	Single instance database installation
	English
	Enteprise Editions
	Location:
		Oracle base: /oracle/AL2
		Sofware location: /oracle/AL2/12102
		ca. 8 GB Frei (Disk space: required 6.4 GB available 8.22 GB)
	System Groups:
		Database Administrator: dba
		Database Operator: oper
		Database Backup: oper
		Data Guard: dba
		Encryp Key: dba

	Als root "/oracle/AL2/12102/root.sh"
		Enter the full pathname of the local bin directory: [/usr/local/bin]: /usr/local/bin
		The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
			[n]: y
		The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
			[n]: y
		The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
			[n]: y
		The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
			[n]: y
	Fertig

#######
SAP Mo/Opatch & Bundle Patch
	
cd /usr/sap/source/solman_71_oracle/OPATCHP_3-20012296
cp -frv OPatch/ /oracle/AL2/12102/
cd /usr/sap/source/solman_71_oracle/MOPATCHP_3-20012296
cp -frv MOPatch/ /oracle/AL2/12102/
cd ..
cp -frv SAP12102P_1511-20012300.ZIP /oracle/AL2/12102/MOPatch/
cd /oracle/AL2/12102/OPatch/
./opatch version
cd /oracle/AL2/12102/MOPatch/
./mopatch.sh -h
env|grep ora
export IHRDBMS=/oracle/AL2/12102
export OHRDBMS=/oracle/AL2/121
export SBPFUSER=/bin/fuser
export ORACLE_HOME=/oracle/AL2/12102
export LD_LIBRARY_PATH=/oracle/AL2/12102/lib/
export ORACLE_SID=AL2
export ORACLE_BASE=/oracle/AL2
echo $PATH
	/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/oracle/AL2/112_64/bin
export PATH=/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/oracle/AL2/12102/bin
echo $PATH
	/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/oracle/AL2/12102/bin
env |grep ORA
./mopatch.sh -v -s SAP12102P_1511-20012300.ZIP
	Installed 8 of 26 patches successfully:
cat $IHRDBMS/cfgtoollogs/mopatch/mopatch-2016_01_20-11-51-29.log

##############
Oracle Pre Update

reboot
su - oracle
export IHRDBMS=/oracle/AL2/12102
export OHRDBMS=/oracle/AL2/121
export SBPFUSER=/bin/fuser
export ORACLE_HOME=/oracle/AL2/12102
export LD_LIBRARY_PATH=/oracle/AL2/12102/lib/
export ORACLE_SID=AL2
export ORACLE_BASE=/oracle/AL2
. oraenv
	ORACLE_SID = [oracle] ? AL2
	ORACLE_HOME = [/home/oracle] ? /oracle/AL2/12102
cd /oracle/AL2/12102/sap/ora_upgrade/pre_upgrade/
ggf. cp -frv /oracle/AL2/11204/dbs/initAL2.ora /oracle/AL2/12102/dbs
	 chown oracle:oinstall initAL2.ora
ls -al
ggf. lsnrctl start
sqlplus / as sysdba
	startup upgrade; #oder nur startup;
	select status from v$instance;
		ggf. create spfile from pfile;
		ggf. shutdown immediate;
		ggf. startup;
		ggf. show parameter spfile;
	@pre_upgrade_tasks.sql;
		PL/SQL procedure successfully completed.
	@pre_upgrade_checks.sql;      
		
	
cd /oracle/AL2/112_64/dbs/
cp -frv spfileAL2.ora spfileAL2.ora.20160121
cp -frv initAL2.ora initAL2.ora.20160121

sqlplus / as sysdba
	ggf. show parameter spfile; 
	ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE ;          
	ALTER SYSTEM RESET "_fix_control" scope =SPFILE;
	ALTER SYSTEM RESET "_ktb_debug_flags" scope =SPFILE;
	ALTER SYSTEM RESET "_mutex_wait_scheme" scope =SPFILE;
	ALTER SYSTEM RESET "_mutex_wait_time"scope =SPFILE;
	ALTER SYSTEM RESET "_optim_peek_user_binds" scope =SPFILE;
	ALTER SYSTEM RESET "_optimizer_adaptive_cursor_sharing" scope =SPF