Upgrade 19c with autoupgrade.jar

 select name from v$datafile;

set lines 499

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

select sum(bytes)/1024/1024/1024 size_in_gb from dba_data_files;

select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_temp_files;

select name from v$tempfile;

select name,open_mode from v$database;

alter database datafile'/dbdata01-mussaib/tbs_default01.dbf'autoextend on maxsize unlimited;

------------------------------------------------------------------------

select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_temp_files;

select name from v$tempfile;

alter database tempfile'/dbtemp01-mussaib/temp_01.dbf'autoextend on maxsize unlimited;

select sum(bytes)/1024/1024/1024 size_in_gb from dba_data_files;

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

----------------------------------------

set pages 999

set lines 400

col FILE_NAME format a75

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB

from dba_temp_files d,

 v$tempfile v

where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

alter database tempfile'/dbtemp01-mussaib/temp_01.dbf'autoextend on maxsize unlimited;

----------------------------------------------------------------- 

select sum(bytes)/1024/1024/1024 size_in_gb  from dba_data_files;

select * from v$version;

archive log list;

 ****************************Upgrade commands- use during upgrade activity only***********************

 Step 4:Copy paste in word before upgrade evidence.

 

Pre-check for Upgrade :Autoupgrade Analyze (Dont set 18c env)

1. Pre-upgrade evidence collection.

spool /dbbkup01-mussaib/mussaib_12c_rman_backup/precheck.log

ps -ef | grep tns

lsnrctl status

set feedback on;

set echo on;

set linesize 500 pages 500;

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

set pagesize 2000

set lines 2000

set long 99999

col username form a20

col machine form a37

col PROGRAM form a30

col MODULE form a20

col osuser form a20

col TERMINAL form a20

select username,machine,PROGRAM,MODULE, osuser,TERMINAL, status

from v$session where username not in ('SYS')

group by username,machine,PROGRAM,MODULE, osuser,TERMINAL,status

order by 1;

select comp_id,status ,version from dba_registry;

SELECT version FROM v$timezone_file;

sho parameter compa

archive log list

select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_temp_files;

select sum(bytes)/1024/1024/1024 size_in_gb  from dba_data_files;

select sum(bytes)/1024/1024/1024 size_in_gb  from dba_segments;

SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

--invalid object

SELECT DISTINCT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE  STATUS='INVALID';

--component status

select comp_id,status ,version from dba_registry;

--timezone

SELECT version FROM v$timezone_file;

--DB link

select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# =

 42 AND bitand(s.mflags, 8) = 8;

 

--Disable dbms_schduler jobs

set pagesize 2000

set lines 2000

set long 99999

set lines 400

col OWNER for a30

col JOB_NAME for a30

select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

--mview script

select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# =

 42 AND bitand(s.mflags, 8) = 8;

 

spool off

*********************************************************************************************************************

*********************************************************************************************************************

 Step 5:

 

2. Take users backup.

spool /dbbkup01-mussaib/mussaib_12c_rman_backup/users_backup.sql

select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()

||''';'  old_password from dba_users;

spool off

*********************************************************************************************************************

*********************************************************************************************************************

3.Check the Listener details & Stop .

ps -fea|grep LISTENER

lsnrctl status LISTENER_mussaib

***********************************************************************************

9.Create pfile & control manual backup.

echo $ORACLE_HOME-----It should point to 18c HOME.

sqlplus / as sysdba

create pfile='/dbbkup01-mussaib/mussaib_12c_rman_backup/pfile_before.ora' from spfile;

alter database backup controlfile to trace as '/dbbkup01-mussaib/mussaib_12c_rman_backup/before_control.trc';

-------------- READ ONLY ORACLE_HOME

If in the installation we didnt change ORACLE_HOME to read only we can do it in this moment

export ORA_INVENTORY=/dboracle-mussaib/oraInventory19.0.0.0

export ORACLE_UNQNAME=

export ORACLE_BASE=/dboracle-mussaib

export ORACLE_HOME=/dboracle-mussaib/19.0.0.0

export ORACLE_SID=mussaib

export TNS_ADMIN=/dboracle-mussaib/19.0.0.0/network/admin

export LD_LIBRARY_PATH=/dboracle-mussaib/19.0.0.0/lib

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/dboracle-mussaib/19.0.0.0/bin:/dboracle-mussaib/19.0.0.0/OPatch:/bin

export TMP=/dbbkup01-mussaib/software_19c/temp

export TMPDIR=/dbbkup01-mussaib/software_19c/temp

which roohctl

prhtorac$ roohctl -enable -force;

-----------------------------------------------------------------

lsnrctl status LISTENER_mussaib

lsnrctl stop LISTENER_mussaib

4.Stop & mount the DB .

Shut immediate;

startup mount;

exit

Step 8 :

8.RMAN Backup

rman <<'EOF'

CONNECT TARGET /

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dbbkup01-mussaib/mussaib_12c_rman_backup/control%F.rman';

run

{

ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

ALLOCATE CHANNEL c2 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

ALLOCATE CHANNEL c3 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

ALLOCATE CHANNEL c4 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

ALLOCATE CHANNEL c5 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

ALLOCATE CHANNEL c6 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

ALLOCATE CHANNEL c7 TYPE DISK FORMAT '/dbbkup01-mussaib/mussaib_12c_rman_backup/mussaib%t_%s_%p';

BACKUP AS COMPRESSED BACKUPSET TAG 'BCK_${ORACLE_SID}_DB_${FECHA}' DATABASE;

RELEASE CHANNEL c1;

RELEASE CHANNEL c2;

RELEASE CHANNEL c3;

RELEASE CHANNEL c4;

RELEASE CHANNEL c5;

RELEASE CHANNEL c6;

RELEASE CHANNEL c7;

}

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

EXIT;

EOF

sql>alter database open;

**collect the evidence of mussaibcessful backup in paste in word pad.

***********************************************************************************

Step 10 :

@?/rdbms/admin/utlrp.sql

purge dba_recyclebin;

***********************************************************************************

17.Analyze the autoupgrade.

**If analyze is already done as a part of installation (Replace log folder then run analyze command)

    cd /respaldo/upgrade/

    mv logs logsbck

cd /respaldo/upgrade/

check the config file & run analyze command

/dboracle-mussaib/12.2.0.1/jdk/jre/bin/java -jar autoupgrade.jar -config mussaib.cfg -mode analyze

/dboracle-mussaib/12.1.0.2/jdk/jre/bin/java -jar autoupgrade.jar -config mussaib.cfg -mode deploy

nohup /dboracle-mussaib/12.2.0.1/jdk/jre/bin/java -jar autoupgrade.jar -config mussaib.cfg -mode deploy -noconsole &

/dboracle-mussaib/12.2.0.2/jdk/jre/bin/java -jar autoupgrade_20220712.jar -config mussaib.cfg -mode analyze

nohup/dboracle-mussaib/12.2.0.2/jdk/jre/bin/java -jar autoupgrade_20220712.jar -config mussaib.cfg -mode analyze

/dboracle-mussaib/12.2.0.2/jdk/jre/bin/java -jar autoupgrade_20220712.jar -config mussaib.cfg -mode analyze

**Check the logs.

***********************************************************************************

Step 14 :

nohup java -jar /dbbkup01-mussaib/upgrade/autoupgrade_20220712.jar -config /dbbkup01-mussaib/upgrade/mussaib.cfg -mode analyze -noconsole &

nohup java -jar /dbbkup01-mussaib/upgrade/autoupgrade_20220712.jar -config /dbbkup01-mussaib/upgrade/mussaib.cfg -mode deploy -noconsole &

18.Deploye autoupgrade.  

cd /respaldo/upgrade/

/dboracle-mussaib/12.1.0.2/jdk/jre/bin/java -jar autoupgrade.jar -config mussaib.cfg -mode deploy----Upgrade deploy command has to be given

 

lsj  --> to check status 

tasks

Check the upgrade logs and ensure upgrade was mussaibcessful. 

*********************************************************************************************************************

*********************************************************************************************************************

Step 15:

Add 19c entries in .bash_profile

[prhtorac@mussaib ~]$ls -la

[prhtorac@mussaib ~]$ cp .bash_profile .bash_profile_bkp----EDIT BASH PROFILE WITH LASTEST 19C network admin location.

[prhtorac@mussaib ~]$ . .bash_profile

/dboracle-mussaib/homes/OraDB19Home1/network/admin

Change sqlnet.ora & Listener.ora

check & Add sqlnet.ora parameters.

    go to new oracle [ 19c] home 

    and check below if not there, add the below manually 

    

vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Listener.ora------make changes as per 19c

Stop & start the Listener after makeing changes in listener.ora sqlnet.ora & .bash_profile

lsnrctl start LISTENER_mussaib

***********************************************************************************

20. Set compatible with alter command & start,stop the db.

    

sqlplus / as sysdba

create pfile='/dbbkup01-mussaib/mussaib_12c_rman_backup/pfile_after.ora' from spfile;

alter database backup controlfile to trace as '/dbbkup01-mussaib/mussaib_12c_rman_backup/after_control.trc';

alter system set compatible='19.0.0.0' scope=spfile;

*********************************************************************************************************************

Step 17:

22.Post Upgrade steps:

Check invalids & check any alertq invalids are there.

23. If alertQ objects are invalid follow below steps.

SET LINES 200

COL OWNER FOR A20

COL OBJECT_NAME FOR A30

SELECT OWNER, OBJECT_NAME, STATUS

FROM DBA_OBJECTS WHERE STATUS NOT IN ('VALID');

Recreate the the SYS.ALERT_QUE using the following steps:

1. Drop the alert_QT

sqlplus / as sysdba

alter system enable restricted session;

exec dbms_aqadm.drop_queue_table('ALERT_QT',true);

2. Drop tables wri$_alert_history and WRI$_ALERT_OUTSTANDING

drop table WRI$_ALERT_OUTSTANDING;

drop table WRI$_ALERT_HISTORY;

3. Recreate the alert_que and associated objects:

@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql

@$ORACLE_HOME/rdbms/admin/catalrt.sql

@$ORACLE_HOME/rdbms/admin/catmwin.sql

@$ORACLE_HOME/rdbms/admin/exechae.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

alter system disable restricted session;

@?/rdbms/admin/utlrp.sql

SET LINES 200

COL OWNER FOR A20

COL OBJECT_NAME FOR A30

SELECT OWNER, OBJECT_NAME, STATUS

FROM DBA_OBJECTS WHERE STATUS NOT IN ('VALID');

4.Stop & start DB

shu immediate;

startup;

Check INVALID Objects and crosscheck with earlier results------->cross check before & after INVALIDS.

SET LINES 200

COL OWNER FOR A20

COL OBJECT_NAME FOR A30

SELECT OWNER, OBJECT_NAME, STATUS

FROM DBA_OBJECTS WHERE STATUS NOT IN ('VALID');

*********************************************************************************************************************

*********************************************************************************************************************

Step 20 :

Evidence collecting post upgrade:

SET LINESIZE 500

SET PAGESIZE 1000

SET SERVEROUT ON

SET LONG 2000000

COLUMN action_time FORMAT A12

COLUMN action FORMAT A10

COLUMN comments FORMAT A30

COLUMN description FORMAT A60

COLUMN namespace FORMAT A20

COLUMN status FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,action,status,

description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

select comp_id,status ,version from dba_registry;

SELECT version FROM v$timezone_file;

show parameter comp;

set lines 999

col name for A35

COL VALUE FOR A60

select name ,type,value from v$parameter where name in ('sga_max_size','sga_target','db_cache_size','shared_pool_size','shared_pool_reserved_size','streams_pool_size','java_pool_size','large_pool_size','log_buffer','processes','job_queue_processes');

exit;

lsnrctl status LISTENER_mussaib