Garuda Oracle SQL postgresql AWS blog

Friday, 19 January 2018

Restore last refresh time in sql server

++++++++++++++++++++
Select Destination_database_name,
restore_date,
database_name as Source_database,
Physical_device_name as Backup_file_used_to_restore,
bs.user_name,
bs.machine_name
from msdb.dbo.restorehistory rh
inner join msdb.dbo.backupset bs
on rh.backup_set_id=bs.backup_set_id
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC

USE msdb ;
SELECT
DISTINCT
DBRestored = destination_database_name ,
RestoreDate = restore_date ,
SourceDB = b.database_name ,
BackupDate = backup_start_date
FROM RestoreHistory h
JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
GROUP BY destination_database_name ,
restore_date ,
b.database_name ,
backup_start_date
ORDER BY RestoreDate DESC
GO

_________________________________________________________________________________

WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

How to change the open mode of all pluggable databases

When you need to modify the open mode of all your PDBs at the same time (look at this post if you want to change the open mode of only a specific pluggable database) you can use ALTER PLUGGABLE DATABASE command and the ALL option.

As usual you have to ensure that the current container is the root.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

The following statement for example changes the open mode of all your pluggable databases at the same time.

SQL>ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

When you need to change the open mode of all pluggable database except for listed ones you can include also the EXCEPT option as in the following example:

SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB001 OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          READ WRITE    4

To open all my pluggable databases I can simply execute the following command. It doesn't take care that PDB002 is already open in READ WRITE mode: any error is returned because the pluggable databases are in different open mode.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

However, if any of your pluggable databases are in READ ONLY mode, then the statement returns the error "ORA-65019: pluggable database PDB001 already open" as you can see:

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE PDB001 OPEN READ ONLY;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database PDB001 already open

Even if the statement fails for pluggable database PDB001 it was able to open PDB002 as requested:

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          READ WRITE    4

To avoid the error ORA-65019 you can include the FORCE option in your command:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

To shutdown all your pluggable databases except one you can use the following command:

SQL> alter pluggable database all except PDB002 close immediate;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE

To list some of your pluggable databases you can use the comma as in the following example where I need to open all my pluggable databases (PDB001 and PDB002) except those listed (just PDB001 and PDB002!!). Nothing happens of course.

SQL> alter pluggable database all except PDB002,PDB001 open;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE

Wednesday, 10 January 2018

DATAGAURD SWITCHOVER IN ORACLE:-

Preparing for a Role Transition:-
=>Verify that there are no redo transport errors or redo gaps at the standby database
=>by querying the V$ARCHIVE_DEST_STATUS view on the primary database.
=>Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

******SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; ******

Step 1 : Verify whether it is possible to perform a switchover
On the current primary database, query the "switchover_status" column of the V$DATABASE fixed view
on the primary database to verify it is possible to perform switchover.

SQL> select name,open_mode,log_mode,protection_mode,switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY

NOTE:-
=>The TO STANDBY value in the "switchover_status" column indicates that it is possible to switch the primary database to the standby role.
=>If the TO STANDBY value is not displayed, then verify the configuration is functioning correctly
. (for example, verify all "log_archive_dest_n" parameter values are specified correctly).
=>If the value in the switchover_status column is SESSIONS ACTIVE or FAILED DESTINATION then click here .

Step 2 : Check that there is no active users connected to the databases.
SQL> select distinct osuser,username from v$session;

Step 3 : Switch the current online sdbto log file on primary database and verify that it has been appleid
SQL>alter system switch logfile ;

Step 4 : Connect with primary database and initiate the switchover
[www.sakthidbtech.com@stby ]sqlplus sys/xxxx@sakthi as sysdba
SQL> alter database commit to switchover to physical standby;
Database altered.

Now, the primary database is converted into standby database.
The controlfile is backed up to the current SQL session trace file before the switchover.
This makes it possible to reconstruct a current control file,if necessary.
If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

In order to perform a switchover, run below command on the primary database.

SQL>alter database commit to switchover to physical standby with session shutdown ;
NOTE:-
=>The above statement first terminates all active sessions by closing the primary database.
=>Then any non-archived sdbto log files are transmitted and applied to standby database.
=>Apart from that an end-of-sdbto marker is added to the header of the last log file that was archived.
=>A backup of current control file is created and the current control file is converted into a standby control file.

Step 5 : Shut down and restart the primary instance(sdbt).
SQL>shutdown immediate;

SQL> startup mount ;

Step 6 : Verify the switchover status in the v$database view.
After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status" column of the v$database fixed view on the target standby database.

On old Primary database(sakthi)
SQL> select name,open_mode,db_unique_name from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
------ ----------- -------------------- ----------------------
Ravis MOUNTED Ravis TO PRIMARY

Step 7:On old standby database (sdbt)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
------ ------------ ----------------- ---------------------
Ravis MOUNTED sdbt TO PRIMARY

Step 8 : Switch the target physical standby database role to the primary role
We can switch a physical standby database from the standby role to the primary role
when the standby database instance is either mounted in sdbto Apply mode or open for read-only access.
It must be in one of these modes so that the primary database switchover request can be coordinated.
After the standby database is in an appropriate mode, issue the following sql statement on the physical
standby database that we want to change to the primary role:

SQL>alter database commit to switchover to primary ;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 213911796 bytes
Database Buffers 41943040 bytes
sdbto Buffers 6410240 bytes
Database mounted.
Database opened.

Step 9 : Check the new primary database(sdbt) and switch logfile :

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE

Note : it's a good idea to perform a log switch on the primary .

SQL> alter system switch logfile;
System altered.

Step 10 : Open new standby database(sakthi) in read-write

SQL> alter database open;
Database altered

SQL> select name,open_mode ,db_unique_name,switchover_status from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME SWITCHOVER_STATUS
------ ------------- ------------------ -----------------------
Ravis READ ONLY Ravis RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ---------------------------------
Ravis READ ONLY WITH APPLY

Patching In Oracle Version of 12.1.0.1.3

Patching in oracle 12.1.0.1.3

export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH

oracle@dbtech:/oradata/software/23054246> echo $ORACLE_HOME
/oracle/product/12.1.0.2/db_1
oracle@dbtech:/oradata/software/23054246> echo $PATH
/oracle/product/12.1.0.2/db_1/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib/mit/bin:/usr/lib/mit/sbin:/oracle/product/12.1.0/db_1/bin
oracle@dbtech:/oradata/software/23054246>

oracle@dbtech:/oradata/software/23054246> /oracle/product/12.1.0.2/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch apply

PREREQ session

Oracle Home : /oracle/product/12.1.0.2/db_1
Central Inventory : /oracle/oraInventory
from : /oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : /oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2016-08-03_12-44-17PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
oracle@dbtech:/oradata/software/23054246>

create or replace directory ORACLE_BASE as '/oracle';
create or replace directory ORACLE_HOME as '/oracle/product/12.1.0.2/db_1';

Sql Commads

Find the objects that are currently being accessed.

select distinct sp. object_name from gv$sql_plan sp,gv$session gs
where sp.sql_id =gs.sql_id
and gs.status='ACTIVE'
and sp.object_name is not null
and gs.username not in ('SYS','SYSTEM')
and sp.object_name not like '%:%'

Find the SQL and its duration that is currently running

select distinct a.sid,a.serial#,a.logon_time,a.username,a.sql_id,a.sql_exec_start,sysdate,a.status,
substr(to_char(NUMTODSINTERVAL(a.sql_exec_start-sysdate, 'day')),5,15) duration ,a.event,a.program,a.module,b.sql_text
from gv$session a, gv$sql b
where a.status='ACTIVE'
and a.username not in ('SYS','SYSTEM')
and b.sql_id=a.sql_id
and b.parsing_schema_name=a.username
and a.program not like 'orac%'
and b.parsing_schema_id=a.user#
order by a.sql_exec_start;

Find the Top SQLs order by executing duration for a particular Period.

with qduration as ( select ash.sql_id,ash.user_id,ash.sql_exec_start,min(ash.sample_time) qmin ,max(ash.sample_time) qmax ,
EXTRACT (hour FROM (max(ash.sample_time)-min(ash.sql_exec_start))) HH,
EXTRACT (MINUTE FROM (max(ash.sample_time)-min(ash.sql_exec_start))) MI ,
EXTRACT (SECOND FROM (max(ash.sample_time)-min(ash.sql_exec_start))) SS
from dba_hist_active_sess_history ash
where ash.sample_time BETWEEN to_date('07-DEC-2017 10:00:00','DD-MON-YYYY HH24:MI:SS')
and to_date('07-DEC-2017 13:23:00','DD-MON-YYYY HH24:MI:SS')
and sql_id is not null
and sql_exec_start is not null
--and ash.user_id=71
group by ash.sql_exec_start,ash.sql_id,ash.user_id)
select qd.sql_id,qd.user_id,qd.sql_exec_start,qd.qmax,qd.qmin,qd.HH,qd.mi,qd.ss,dbms_lob.substr(dh.sql_text,4000,1) from qduration qd,dba_hist_sqltext dh
where qd.sql_id=dh.sql_id
order by 6 desc , 7 desc,8 desc;

SQL to find the tables are analyzed.

select owner,table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename and owner=&owner;

From delveloper perspective, use

select table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename ;

Find temp usage

select s.username,s.osuser,u.tablespace,s.sql_id,substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
sum(round(((u.blocks*p.value)/1024/1024),2)) size_mb
from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by s.username,s.osuser,u.tablespace,s.sql_id,
substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1))

oracle interview questions

1. Tell me about ur responsibilities in your project ?

2. How amny database instance do u maintain ?

3. Backup information in ur environment ?

4. Is it necessaryto restore the entire database when system datafile is corrupt r removed ?

5. How will you restore & recover system datafile ? Do we need to give resetlogs after recovering the datafile , is yes explain if no y ?

6. What is a cpu patch & psu patch ?

7. how will u apply psu patch ?

8. Abbrevation of psu & cpu ?

9. Types of clonning ?

10. Explain rman clonning steps ?

11. What is rac ?

12. How amny nodes do u have ?

13. How to apply patch in RAC database

14. 10g to 11g migration steps ?

15. What do u know abt performance tunning

16. What all u have done in tunning

1. How will configure ASM and data guard in RAC environment
2. What is SCAN listener
3. How will tune single node in RAC.
4. How will apply Patch in RAC.
5. What is HOT and Cold Cloning. Give steps
6. Upgrade DB in ASM with RAC environment
7. How will find Performance drop in DB
8. If we need Log file backup for Cold Cloning..? Yes means why..? No means why..?
9. If DB was crashed in RAC without backup of OCR and Voting Disk. Hw will you recover the DB.
10. Explain plan and TKPROF explain

1. Tell about your profile?

2. Day to day activities?

3. How to check flash_recovery enabled or not?

4. Dbv uses?

5. If two node using rac and archive are going to these two nodes,if we had 3rd node as non rac active standby ?what I have to do ,so that archive to go to 3rd node?

6. Row chaining and row migration? How to avoid row chaining?

7. Dataguard modes? And what parameter is used to set for that modes?

8. Oracle background process?

9. When checkpoint occur?

10. 10g and 11g difference?

11. What is the use of diag in 11g?

12. Trace location in 11g and 10g?

13. Opatch ? when evenly they release patches?

14. Patching steps?

15. What you will do when error occur during patching?

16. What ticketing tool using?

17. What is catbundle?

18. What kind of refresh and all done? What tool used for that?

19. How to create tablespace with blocksize of 16k?

20. Dirty buffer? How the dirty buffer will get clear/

21. Which process write the data in buffer memory?

22. Instance recovery?

23. How you will connect database to client?

24. Obsolete backup? How you will use the obsolete backup as valid?

25. What happen when we set init trans parameter as 0?

26. Rman cloning steps?

27. Auxilary use?

Tell about urself and day to day activities ?

Patching steps for psu ?
Upgradation for 10 to 11g steps?
Is it possible for reuse for logfiles ?
If 1000 archives missing in standby database what u will do ? explain the steps?
Ur backup strategies in ur environment ?
Rman cloning steps in oracle ?explain step by step procedure
How to refresh ur database in oracle ?
What is meant by mrp process ?
How to stop mrp process ?
Why we have to run catlog and catproc ?
Which ticketing tool utility u have to use in ur environment ?
DIfference between hot cloning and cold cloning?
Difference between consistent backup,inconsistent backup and logical backup ?
Asm advantages ?why we have to use asm instance ?
How many servers u have to maintained ?
How many db’s u have to handle ?
Whats ur role in ur project ?
Why we have to run catbundle ?
What are all the background process in asm ?
In awr report what r all u have to seen in tat report?
Any five wait events u have to tell about cause and solutions ?
Explain between level 1 and level 0 backup?
In ur environment which incremental backup ur preferable ? cumulative or differential ?
If ur patch is apply wrong ly,how can u recover for original state ?
What is rollback patch ?

1. tell briefly about yourself
2. what is day to day activites
3. will you write scripts in shell
4. what all necessary scripts required for a db
5. what all assistance tools we have in oracle
6. what are all levels of auditing
7. how to encrypt db files
8. what all information we can see in v$logminer_contents
9. how to check index fragmentation
10. diff b/w v$instance and gv$instance
11. diff b/w oracle home and oracle base
12. Rac wait events
13. what is ocr
14. what is GRD
15. migration steps
16. upgradation steps
17. Will create tablespace have a entry in RLBC
18. how to get metadata of a table
19. how you will change db name
20. In which language oui installer in designed
21. what is change management
22. full statement to create a user
23. what all statements wont have entry in RLBC
24. can v coalese LMT
25. any one 12c feature

Infosys Questions(25.08.2015)

1. Tell me about yourself

2. Day to day activities?

3. Do you have work experience in RAC and ASM

4. How will you check database growth?

5. What is meant by patches?

6. Have u done upgradation? Tell the steps?

7. Steps for schema refresh?

8. Backup strategies?

9. I have 1,2,2 gb sizes in 3 different disks. How will take backup in expdp?

10. Tell the command for rman active cloning?

26. How you will remove ASM from RAC

Step for upgrade from 10g to 11g

2. If i have 10 materilized view, all 9 are sync properly and one view is not sync. How you give solution.

3. Why 11g DB start with Undo TBS.

4. 11g Arc

5. How will u recover if DG failure in ASM.

6. Difference between One way and two way Mirroring

7. Steps to convert Non ASM to ASM and Why we take backip before convert and change name for Control file

8. Data Guard Modes and Explain

9. If archive was not sync properly means how you give solution

10. If u have one standby and some archive was miss at standby, if you have perform switchover. Tell the steps and hw u recover those missing archive

11. Why u kept FAL_ClIENT and FAL_SERVER parameters

12. How will you apply missing archive from one standby to another standby

13. RMAN Cloning

14. If I have Level 0 backup and i took level 1 differential for Monday, Tuesday. Now am going to take level 1 Cumulative at wednesday. Which backup is base backup for Cumulative backup. (Vice Versa)

15. Migration from Windows to Linux

16. Why we give REMAP SCHEMA and REMAP TABLESPACE parameter in imp/exp

17. Different between import and Data Pump.

18. Different between HOT and COLD backup.

19. Why you give ALTER DATABASE OPEN RESETLOG, If you provide that commend what happen the prev archives.

20. Explain HOT CLONING and COLD CLONING.

21. Why u change REUSE to SET in control file.

22. If the query is not respose properly. Hw will give solution on that.

23. What you do initial level of Tuning

24. Hw will force index to the table

25. If an object was move from one tbs to another tbs. Hw will you recover Index and Partition.

26. Startup and Shoutdown stages and Explain.

27. Hw will you change segment parameters.

28. Explain deadlock

29. What is ORA1555 error

30. IF RAC have two nodes one node was failure which process will take care of switching. Hw will check RAC health.

Upgrade from 12.1.0.1 to 12.1.0.2

Oracle upgrade steps

Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)

1. Check Concurrent statistics gathering is set to FALSE

SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

if not, then

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/

2. Check for invalid objects and run utlrp.sql

3. Copy preupgrd.sql and utluppkg.sql into temporary directory and execute preupgrd.sql script on database and fix the recommendations

4. Check parallel settings

5. Check for Events and hidden parameters and disable before upgrade

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

6. Check Dependency on Network utility packages

SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_
INADDR','DBMS_LDAP')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

7. Gather Dictionary statisitics --> DONE

8. Check the recovery pending status

SELECT * FROM v$recover_file;

9. Purge DBA RECYCLE BIN

PURGE DBA_RECYCLEBIN

10. Shutdown the listener --> DONE
11. Shutdown database --> DONE

12. Backup pfile,spfile, pwd file and update Pfile with following and place it in new Oracle home

#Recommended parameters for 12c
_optimizer_reduce_groupby_key =FALSE
_optimizer_aggr_groupby_elim=FALSE
_optimizer_unnest_scalar_sq=FALSE
_optimizer_coalesce_subqueries=FALSE
_use_single_log_writer=TRUE
#Parameters for parallelism
parallel_adaptive_multi_user=FALSE
parallel_degree_limit=20
parallel_degree_policy='AUTO'
parallel_force_local=TRUE
parallel_max_servers=32
parallel_min_servers=2
parallel_min_time_threshold=60
parallel_servers_target=28
parallel_threads_per_cpu=1
#

13. Update Environment variables for the oracle user to new userid and Oracle home and change the owner name of datafiles and other files

14. startup upgrade

15. Increase TEMP tablespace size to atleast 1.5GB after Upgrade resize to actual size

16. cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 2 -l $ORACLE_HOME/diagnostics catupgrd.sql

17. Startup
18. Run utlu121s.sql --> DONE
19. Run utlrp.sql --> DONE
20. Modify Listener home and start it, following should be updated in sqlnet.ora file

for windows server after upgrade change below in sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.INBOUND_CONNECT_TIMEOUT = 180
SQLNET.EXPIRE_TIME = 30

# allows inbound connections from Oracle Clients version >= 12.1
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12a
# allows outbound connections to Oracle Database Servers version >= 11.x (incl. PSU/CPU October 2012 or later)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 12

# Network Encryption (for inbound connections; acting as Database Server)
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
# Network Integrity (for inbound connections; acting as Database Server)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)

# Network Encryption (for outbound connections; acting as Database Client)
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
# Network Integrity (for outbound connections; acting as Database Client)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA1)

21. Start db from spfile --> DONE
22. Rename the old ORACLE_HOME, pfile, spfile and listener file of Old ORACLE_HOME and Run Cockpit scan

/sysmgmt/opt/cfengine/sbin/cfagent -v -q -K

TIMEZONE UPGRADE

1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

2. SELECT version FROM v$timezone_file;

3. purge dba_recyclebin;

4. EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

5. alter session set "_with_subquery"=materialize;

6. alter session set "_simple_view_merging"=TRUE;

7. EXEC DBMS_DST.BEGIN_PREPARE(18);

8. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

9. TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

10. TRUNCATE TABLE sys.dst$affected_tables;

11. TRUNCATE TABLE sys.dst$error_table;

12. set serveroutput on

13. BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

14. SELECT * FROM sys.dst$affected_tables;

15. SELECT * FROM sys.dst$error_table;

16. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

17. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

18. SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

19. EXEC DBMS_DST.END_PREPARE;

20. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

21. shutdown immediate;
22. startup upgrade
23. set serveroutput on;
24. set timing on;

--> execute steps 1,3,9,10,11, 4~6

25. EXEC DBMS_DST.BEGIN_UPGRADE(18);

26. SELECT * FROM sys.dst$error_table;

27. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

28. Shutdown/startup

29. alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

30. set serveroutput on

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

SELECT * FROM sys.dst$error_table;

31. VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

32. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

33.SELECT version FROM v$timezone_file;

34. select TZ_VERSION from registry$database;

35. If output of above 2 queries differ then
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

36. Commit;