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