Garuda Oracle SQL postgresql AWS blog: November 2018

Tuesday 27 November 2018

ORA-12514: TNS:listener does not currently know of service requested in connect

ORA-12514: TNS:listener does not currently know of service requested in connect

check the service name and sid name.

if there is sid _name running fine , then add a entry about the service_name as global _name

example

listener.ora

(SID_DESC =
(GLOBAL_DBNAME = DOM.h1.al.mi.com)
(ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = DOM)
)
(SID_DESC =
(GLOBAL_DBNAME = DOM)
(ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
(SID_NAME = DOM)

Let me explain :

listener status :

Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh

a) When you connected using SID : magesh , connection succeed as the instance name in the listener matched :

Note :
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh
----------------------------------------------------------

b) When you connected using SERVICE_NAME = magesh , connection succeed as the service name in the listener status is not matching : :

Note :
Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com

Solution:

Change the SERVICE_NAME=magesh.hq.dal.omi.com

Wednesday 14 November 2018

Table Reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:

-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990

--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304

Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16

ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12

Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';

If db is up:
=================

select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';

INDEXES NEEDS TO BE REBUILDED:
=================================

--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;

Step 3:Make sure to make it noparallel and logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';

Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.

Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off

Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.

SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
APPS ORDER_TAB_NU17 UNUSABLE
ORD_SCHEMA ORDER_TAB_F2 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_NU3 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N10 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N11 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N12 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_U1 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N6 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_U2 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_NU1 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N9 UNUSABLE

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA XHL_ORDER_TAB_NU4 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N1 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_NU2 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N7 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N8 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N2 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N3 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N4 UNUSABLE
ORD_SCHEMA OE_ORDER_HEADERS_N5 UNUSABLE
APPS XHL_ORDER_TAB_NU5 UNUSABLE
ORD_SCHEMA XHL_ORDER_TAB_R1 UNUSABLE

22 rows selected.

To check unusable indexes for table:
==========================================

SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>

Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';

Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out

sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/

Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352

Hope it helps.Enjoy DBA tasks...

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN (<TABLENAMES>);

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<INDEXNAMES>);

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';

2. Export table statistic

Exporting the tables stats is very important; else, tables will start gathering the stats from beginning for the whole table and sometimes that will affects the performance of the queries respect to these tables. Once the reorg has been completed we need to import the stats back to the table, so that the tables will use the old stats while querying the table data

(a) Create Stats Table to store the statistics

Exporting the stats to one of the table say ‘MY_STATS_TABLE’ as temporary table. Once the stats has been imported you can drop the table

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>'<OWNERNAME>’,STATTAB =>'<TABLENAME>’, TBLSPACE =>'<TABLESPACENAME>’);
Example:

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>’SCOTT’,STATTAB =>’MY_STATS_TABLE’, TBLSPACE =>’USER’);

Example:

EXEC DBMS_STATS.EXPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);

3. Perform Table & associated index reorg

(a) Table Reorg:

For Non-LOB Tables:

ALTER TABLE <USERNAME>.<TABLE NAME> MOVE;
Example:
ALTER TABLE SCOTT.EMP MOVE;
For Non-LOB Tables:

ALTER TABLE <USERNAME>.<TABLE_NAME> MOVE LOB (COLUMN_NAME) STORE AS (TABLESPACE NEWTABLESPACE);
Example:

ALTER TABLE SCOTT.EMP MOVE LOB (PHOTO) STORE AS TABLESPACE DATA;

b) Rebuild the associated indexes:

If the table has associated indexes which we got it from the step-1 queries, we need to perform the rebuild as they were become INVALID once after the reorg of the parent table.
For Normal Indexes:

ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD ONLINE;
Example:

ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE;
For Bitmap indexes:

ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD;
Example:

ALTER INDEX SCOTT.EMP_BIT_IDX REBUILD;

4. Import table stats

EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘<USERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);

Example:
EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);

5. Repeat step 1

6. Please collect the invalid object count

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';

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

7. Run utlrp.sql (If necessary)

SQL> @?/rdbms/admin/utlrp.sql

8. Again Collect the Invalid Object information

9. Execute the below query and compare the values before and after the the reorg, you will be surprised

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);

------------------ TABLE FRAGMENTATION--------------------

(I) ------------------- TABLE FRAGMENTATION--------------------

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

===TABLE SIZE (WITH FRAGMENTATION)===============

SQL> select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';

Example:

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb

========ACTUAL DATA IN TABLE======================

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables
where table_name = 'BIG1';

Example:
TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb

Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

#################################################################################################################################################################

2. JOBS RUNNING STATUS SCRIPTS

SQL>SELECT owner, job_name, enabled FROM dba_scheduler_jobs

SQL>select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name='ABC';

Tuesday 13 November 2018

Privileges and roles

Privilege command

-- System privileges granted to an user ( scott) 
SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';  
-- Roles granted to an user ( scott) 
SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';  
-- Object privileges granted to an user ( SCOTT) 
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';  
-- Column specific privileges granted 
SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';

/*for tables*/
select 'grant select on '||owner||'.'||table_name||' to <user name>' from dba_tables where owner=<schema>;
/*for procedures*/
select 'grant execute on '||owner||'.'||object_name||' to <user name>' from dba_objects where owner=<schema>
and object_type='PROCEDURE';
where OBJECT_TYPE='PROCEDURE'

Select 'GRANT SELECT ON GBO_DB.'||Table_Name||'TO GBO_READONLY' From All_Tables Where Owner='GBO_DB';

select 'GRANT SELECT ON GBO_DB.'||view_name||' TO GBO_READONLY' From dba_views where Owner='GBO_DB';

BEGIN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || GBO_DB.table_name || ' TO readon';
END LOOP;
END;

/
==================================================================================

SQL> CREATE USER books IDENTIFIED BY books
2 ;

User created.

SQL> GRANT CONNECT TO books;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE, DBA TO books;

select * from dba_sys_privs

grant debug connect session to SCDM;

GRANT DEBUG ANY PROCEDURE TO SCDM;

password expire

SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='SIMON';

What are the privileges that role contain
================================================================================

select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';

SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DBMON';

select privilege from dba_sys_privs where grantee='DWUSER';

select * from dba_role_privs where grantee='DWUSER';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
DBMON APP_BASE_ROLE YES YES
DBMON CONNECT NO YES
DBMON SQLT_USER_ROLE NO YES
DBMON DBA NO YES

select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';

SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DWUSER';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('SCOTT')
UNION
SELECT grantee,granted_role
FROM dba_role_privs
UNION
SELECT grantee,privilege
FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;

=================================================================================

Listing Privilege and Role Information
To list the grants made for objects, a user can query the following data dictionary views:

ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD
ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD
DBA_ROLES
USER_ROLE_PRIVS, DBA_ROLE_PRIVS
USER_SYS_PRIVS, DBA_SYS_PRIVS
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
SESSION_PRIVS, SESSION_ROLES
See Also: See the Oracle8 Reference for a detailed description of these data dictionary views.

Listing Privilege and Role Information: Examples
For the following examples, assume the following statements are issued:

CREATE ROLE security_admin IDENTIFIED BY honcho;

GRANT create profile, alter profile, drop profile,
create role, drop any role, grant any role, audit any,
audit system, create user, become user, alter user, drop user
TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON sys.aud$ TO security_admin;

GRANT security_admin, create session TO swilliams;

GRANT security_admin TO system_administrator;

GRANT create session TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Listing All System Privilege Grants
The following query indicates all system privilege grants made to roles and users:

SELECT * FROM sys.dba_sys_privs;

GRANTEE PRIVILEGE ADM
-------------- --------------------------------- ---
SECURITY_ADMIN ALTER PROFILE YES
SECURITY_ADMIN ALTER USER YES
SECURITY_ADMIN AUDIT ANY YES
SECURITY_ADMIN AUDIT SYSTEM YES
SECURITY_ADMIN BECOME USER YES
SECURITY_ADMIN CREATE PROFILE YES
SECURITY_ADMIN CREATE ROLE YES
SECURITY_ADMIN CREATE USER YES
SECURITY_ADMIN DROP ANY ROLE YES
SECURITY_ADMIN DROP PROFILE YES
SECURITY_ADMIN DROP USER YES
SECURITY_ADMIN GRANT ANY ROLE YES
SWILLIAMS CREATE SESSION NO
JWARD CREATE SESSION NO

Listing All Role Grants
The following query returns all the roles granted to users and other roles:

SELECT * FROM sys.dba_role_privs;

GRANTEE GRANTED_ROLE ADM
------------------ ------------------------------------ ---
SWILLIAMS SECURITY_ADMIN NO

Listing Object Privileges Granted to a User
The following query returns all object privileges (not including column specific privileges) granted to the specified user:

SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'JWARD';

TABLE_NAME PRIVILEGE GRANTABLE
----------- ------------ ----------
EMP SELECT NO
EMP DELETE NO

To list all the column specific privileges that have been granted, use the following query:

SELECT grantee, table_name, column_name, privilege FROM sys.dba_col_privs where grantee='GOURC';

GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
JWARD EMP JOB INSERT

Listing the Current Privilege Domain of Your Session
The following query lists all roles currently enabled for the issuer:

SELECT * FROM session_roles;

If SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:

ROLE
------------------------------
SECURITY_ADMIN

The following query lists all system privileges currently available in the issuer's security domain, both from explicit privilege grants and from enabled roles:

SELECT * FROM session_privs;

If SWILLIAMS has the SECURITY_ADMIN role enabled and issues this query, Oracle returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

If the SECURITY_ADMIN role is disabled for SWILLIAMS, the first query would have returned no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

Listing Roles of the Database
The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM sys.dba_roles;

ROLE PASSWORD
---------------- --------
CONNECT NO
RESOURCE NO
DBA NO
SECURITY_ADMIN YES

Listing Information About the Privilege Domains of Roles
The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.

For example, the following query lists all the roles granted to the SYSTEM_ADMIN role:

SELECT granted_role, admin_option
FROM role_role_privs
WHERE role = 'SYSTEM_ADMIN';
GRANTED_ROLE ADM
---------------- ----
SECURITY_ADMIN NO

The following query lists all the system privileges granted to the SECURITY_ADMIN role:

SELECT * FROM role_sys_privs WHERE role = 'SECURITY_ADMIN';

ROLE PRIVILEGE ADM
----------------------- ----------------------------- ---
SECURITY_ADMIN ALTER PROFILE YES
SECURITY_ADMIN ALTER USER YES
SECURITY_ADMIN AUDIT ANY YES
SECURITY_ADMIN AUDIT SYSTEM YES
SECURITY_ADMIN BECOME USER YES
SECURITY_ADMIN CREATE PROFILE YES
SECURITY_ADMIN CREATE ROLE YES
SECURITY_ADMIN CREATE USER YES
SECURITY_ADMIN DROP ANY ROLE YES
SECURITY_ADMIN DROP PROFILE YES
SECURITY_ADMIN DROP USER YES
SECURITY_ADMIN GRANT ANY ROLE YES

The following query lists all the object privileges granted to the SECURITY_ADMIN role:

SELECT table_name, privilege FROM role_tab_privs
WHERE role = 'SECURITY_ADMIN';

SELECT table_name, privilege FROM role_tab_privs WHERE role ='QUERY_ALL_ROLE';

TABLE_NAME PRIVILEGE
--------------------------- ----------------
AUD$ DELETE
AUD$ SELECT

SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1;
SQL> CONNECT u1/u1
Connected.
SQL> GRANT SELECT ON scott.t2 TO u2;
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'employees';

GRANTEE OWNER
------------------------------ ------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
U2 SCOTT
SCOTT SELECT NO

-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
2> FROM DBA_TAB_PRIVS
3> WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANTEE OWNER GRANTOR PRIVILEGE
-------------- -------------- ------------- ---------------
ESRVT_ROLE ESRVP ESRVP DELETE

-------------------------------------------------------------------------------------------------------------------------------------------------
----->SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';

The syntax for granting privileges on a table is:

grant privileges on object to user;

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:

========================================================================================
grant select, insert, update, delete on suppliers to smithj;

grant select, insert, update, delete on suppliers to smithj;

grant select on Eagan to gourc;

===========================================================================================

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to smithj;

If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:

grant select on suppliers to public;

Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from user;

For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:

revoke delete on suppliers from anderson;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke all on suppliers from public;

Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege Description
Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to user;

For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:

grant execute on Find_Value to smithj;

If you wanted to grant all users the ability to execute this function, you would execute the following:

grant execute on Find_Value to public;

Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from user;

If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:

revoke execute on Find_Value from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke execute on Find_Value from public;

SELECT grantee, table_name, column_name, privilege
FROM sys.dba_col_privs;

GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT

SQL> select privilege from dba_sys_privs where GRANTEE='ERMUSR';

PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
UNLIMITED TABLESPACE

9 rows selected.

grant sysdba to ermusr;

SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='QUERY_ALL_ROLE';

select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';

SELECT * FROM sys.dba_role_privs where grantee='MASTERSAF'

select * from dba_role_privs where granted_role='APPDBA';

SQL> desc dba_role_privs
Name Null? Type
------------ -------- ------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS DBA YES YES
SYSTEM DBA YES YES

WHAT ARE THE PRIVILEGES ASSAIGN TO ROLE

SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='APPDBA';

=================================================================================

SELECT * FROM sys.dba_sys_privs where grantee='DW_DML_ROLE';

SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='BI_SEC';

revoke role from user

=========================

REVOKE <role_name> FROM <user_name>;

Roles

SELECT granted_role, admin_option FROM role_role_privs WHERE role = 'HS_ADMIN_SELECT_ROLE';

SQL>FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || your.table_names || ' TO <<user>>' ;
END LOOP ;

Roles
++++

select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE like '%DBA%' and ADMIN_OPTION='YES';

select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED
_ROLE like '%DBA%';

select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE='DBA';

select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTEE
like '%ORACLE%';

select GRANTEE,PRIVILEGE,ADMIN_OPTION from dba_sys_privs where grantee like
'%ORACLE%';

to check what are the privilege assign to role
-------------------------------------------------------------

SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='DW_DML_ROLE';

Karthik, Team,
The below incident was not worked upon correctly. The request was to:
Create a new role APPSDBARESTRICT in UNNPSHRD. It should have the following privileges:
CONNET
CREATE SESSION
SELECT ANY DICTIONARY
ADVISOR
EXECUTE privilege on DBMS_WORKLOAD_REPOSITORY
Create a new user SARATHJ1 in database UNNPSHRD. Assign APPSDBARESTRICT role to SARATHJ1.

Of this, the role APPSDBARESTRICT was created , but the role has not been assigned to SARATHJ1 but I see that it has been assigned to SYSTEM!!!. See below queries. Going forward, please do not close the Incident unless the requester agrees.

SQL> select name, sysdate from v$database;

NAME SYSDATE
--------- ---------
UNNPSHRD 28-NOV-12

SQL> set lines 300
SQL> set pages 1000
SQL> select * from dba_sys_privs where grantee='SARATHJ1';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SARATHJ1 UNLIMITED TABLESPACE NO

SQL> select * from dba_ROLE_privs where grantee='SARATHJ1';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SARATHJ1 CONNECT NO YES
SARATHJ1 RESOURCE NO YES

SQL> select * from dba_roles where role='APPSDBARESTRICT';

ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
APPSDBARESTRICT NO NONE

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='APPSDBARESTRICT';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM APPSDBARESTRICT YES YES

SQL> select * from dba_sys_privs where grantee='APPSDBARESTRICT';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
APPSDBARESTRICT ADVISOR NO
APPSDBARESTRICT CREATE SESSION NO
APPSDBARESTRICT SELECT ANY DICTIONARY NO

Sunday 11 November 2018

TNS-12541: TNS:no listener

TNS-12541: TNS:no listener

The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.
TNS-12545: Connect failed because target host or object does not exist

The host in specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping '. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

What are the symptoms?

The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LIST_ANDY'
Why is this happening?

Oracle only checks for listeners running on the default port (1521). It would have to spend all day trying every possible port number otherwise. You'll need to give it some help to find your listener.

How do I fix it?

Simply add an entry to the servers tnsnames.ora pointing at the listener. For example my listener 'LIST_ANDY' is running on port 3801, so I would add the following entry to my tnsnames:
LIST_ANDY = (ADDRESS = (PROTOCOL = TCP)(HOST = fermat)(PORT = 3801))
Test it with tnsping:
tnsping
Then try starting your instance again - it should work now.

ORA-12505: TNS:listener does not currently know of SID given in connect

Either the SID is misspelled in the tnsnames, or the listener isn't listening for it. Check the tnsnames.ora first. If it looks ok, do a 'lsnrctl status' on your server, to see what databases the listener is listening for.

ORA-12520: TNS:listener could not find available handler for requested type of server

ORA-12520: TNS:listener could not find available handler for requested type of server

If you are seeing this error, first check the listener services status. Notice the 'state:blocked' on my listener below:
oracle@bloo$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.2.0 - Production

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...

Service "scr9" has 1 instance(s).
Instance "scr9", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:blocked
LOCAL SERVER
The problem turned out to be caused by the fact that my listener was not named LISTENER. The database was still registering with it somehow, presumably because it was using the default port, but when attempting to open a connection to the service I saw the following error:
/home/oracle/tns_admin$ sqlplus system@scr9

SQL*Plus: Release 10.2.0.2.0 - Production

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:
ERROR:
ORA-12520: TNS:listener could not find available handler for
requested type of server
To resolved the issue I configured the instances local_listener parameter to point at my listener:
alter system set local_listener=
'(ADDRESS=(PROTOCOL=TCP)(HOST=bloo)(PORT=1521))' scope=spfile;
If you have an entry in your tnsnames.ora that points to the listener, you can simply use the listener name instead:
alter system set local_listener='' scope=spfile;

Step by Step Oracle Database 12c Release 1 Upgrade from 11.2.0.3

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

Minimum version of the database that can be directly upgraded to Oracle 12c Release 1 (12.1)

Source Database Target Database
10.2.0.5 12.1.x
11.1.0.7 12.1.x
11.2.0.2 or higher 12.1.x

Requirements and recommendations for Source database

• Either take a cold or hot backup of the source database (advisable to have cold backup).
a) Perform Cold Backup
(or)
b) Take a backup using RMAN
Connect to RMAN:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>';
}

Below is a SQL*Plus script that will list all objects that have been created in both the SYS and SYSTEM schema:

SQL> column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';

If the list of objects is large then you may want to use the following SQL*Plus script to automatically generate an SQL script that contains the appropriate DROP commands:

set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
spool off
exit

ORA-1418 (specified index does not exist):
This occurs because the table that the index was created on has already been dropped which also drops the index. When the script tries to drop the index it is no longer there and thus the ORA-1418 error. You can safely ignore this error.

Install 12C software on different location

Check for the integrity of the source database.
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

Download and save the script as dbupgdiag.sql. The script needs no additional configuration

SQL> alter session set nls_language='American';

SQL> @dbupgdiag.sql

Enter location for Spooled output:

Enter value for 1: /tmp

SQL> @utrlrp.sql

#############Pre-Upgrade Steps################
How to Download and Run Oracle's Database Pre-Upgrade Utility [ID 884522.1]

Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1)

Run the new Pre-Upgrade Information Tool. For example, if you copied preupgrd.sql to the /admin directory of the source Oracle Home:

SQL> @/u01/oinstall/product/12.1.1/rdbms/admin/preupgrd.sql

SQL> @/u01/oinstall/product/12.1.1/rdbms/admin/utluppkg.sql

SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

SQL> show processes parameter processes;

SQL> @/u01/oinstall/product/12.1.1/rdbms/admin/emremove.sql

SQL> create pfile from spfile;

SQL> @/u01/oinstall/product/11.2.0/olap/admin/catnoamd.sql

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

SQL> @/u01/oinstall/product/11.2.0/cfgtoollogs/MUTH/preupgrade/preupgrade_fixups.sql

##############Deprecated CONNECT Role#############
After upgrading to Oracle Database 12c Release 1 (12.1) from INDIRECT database upgrade release like Oracle Database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SQL> SELECT 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', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT';

#################Dependencies on Network Utility Packages #############
Execute the following query

SQL> 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');

###########Database Links with Passwords from Earlier Releases#############

SQL> 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#;

###############Optimizer Statistics ###################
Oracle recommends collecting statistics the night before starting the upgrade to decrease the amount of downtime

To decrease the amount of downtime incurred when collecting statistics, Oracle recommends to collect statistics prior to performing the actual database upgrade.
As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Verify That Materialized View Refreshes Have Completed Before Upgrading
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Run the following query to determine if there are any materialized view refreshes still in progress:

SQL> 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;

Ensure That No Files Need Media Recovery Before Upgrading
Make ensure that there are no files requiring media recovery by executing the following query

SQL> SELECT * FROM v$recover_file;

no rows selected

Ensure That No Files Are in Backup Mode Before Upgrading
Execute the following query to verify that No Files in backup mode when upgrading Oracle Database

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Resolve Outstanding Distributed Transactions Before Upgrading
To resolve outstanding distributed transactions:
1. Issue the following statement:

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

If the query in the previous step returns any rows, then issue the following
statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

###########Purge the Database Recycle Bin Before Upgrading :#############
To empty the database recycle bin, issue the following command:

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

NOTE : The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors and to minimize the upgrade time.

##########Synchronize the Standby Database with the Primary Database When Upgrading ############
To check if a standby database exists and to synchronize it:

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

Disable all batch and cron jobs
For jobs initiated by Oracle then packages DBMS_JOB, DBMS_SCHEDULER can be used.
For cron jobs (external jobs controlled at the OS level) then this is a task for your Unix administrator.
See also:
Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER
Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB
Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification

############Verify SYS and SYSTEM Default tablespace ############

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:
SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

#############Verify the existance of the AUD$ tables################
Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.

SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name='AUD$';

############Check whether database has any externally authenticated SSL users###########
Execute the following query

SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';

Location of datafiles, redo logs and control files
Note the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;

###########Remove Enterprise Manager Database Control repository :#################
Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express .
Therefore no repository is needed anymore .
Remove Enterprise Manager Database Control repository MANUALLY using the following command
Note : You will get the emremove.sql script in the Oracle 12c home .
Copy the emremove.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORALCE_HOME/rdbms/admin and then execute on the source database prior to upgrade.

SQL> @/u01/oinstall/product/12.1.1/rdbms/admin/emremove.sql

################Run olspreupgrade.sql :#################
If OLS(Lable Security) and/or DV ( Database Vault) was already in the database prior to the upgrade then execute the following steps on Source database prior to upgrade
Note : You will get the olspreupgrade.sql script in the Oracle 12c home .
Copy the olspreupgrde.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORALCE_HOME/rdbms/admin and then execute on the source database prior to upgrade.

SQL> @/u01/oinstall/product/12.1.1/rdbms/admin/olspreupgrade.sql

##########Copy Pfile from 11.2.0.3 oracle home to 12c Oracle home###############

[oinstall@arjun dbs]$ cd /u01/oinstall/product/12.1.1/dbs
[oinstall@arjun dbs]$ cp ../../11.2.0/dbs/initMUTH.ora .
[oinstall@arjun dbs]$lsnrctl stop MUTH

##########Copy Tnsnames and Listner names from 11.2.0.3 Oracle Home###############

[oinstall@arjun admin]$ cp -r ../../../11.2.0/network/admin/tnsnames.ora .
[oinstall@arjun admin]$ cp -r ../../../11.2.0/network/admin/listener.ora .
[oinstall@arjun admin]$ emctl stop dbconsole

######## Shut Down your database ##########

[oinstall@arjun admin]$ sqlplus "/as sysdba"

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

[oinstall@arjun admin]$export ORACLE_HOME=/u01/oinstall/product/12.1.1
[oinstall@arjun admin]$export ORACLE_SID=MUTH
[oinstall@arjun admin]$export PATH=$ORACLE_HOME/bin:$PATH

######### Listener Configuration #########

[oinstall@arjun admin]$vi /u01/oinstall/product/12.1.1/network/admin/listener.ora # Change Oracle Home location

[oinstall@arjun admin]$ lsnrctl start MUTH

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 16-JUL-2013 09:28:43

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Starting /u01/oinstall/product/12.1.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /u01/oinstall/product/12.1.1/network/admin/listener.ora
Log messages written to /u01/oinstall/diag/tnslsnr/arjun/muth/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arjun.bangalore.bedford.local)(PORT=1550)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1550)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arjun.bangalore.bedford.local)(PORT=1550)))
STATUS of the LISTENER
------------------------
Alias MUTH
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 16-JUL-2013 09:28:43
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oinstall/product/12.1.1/network/admin/listener.ora
Listener Log File /u01/oinstall/diag/tnslsnr/arjun/muth/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arjun.bangalore.bedford.local)(PORT=1550)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1550)))
Services Summary...
Service "MUTH" has 1 instance(s).
Instance "MUTH", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oinstall@arjun admin]$ orabase
/u01/oinstall

########Upgrading Database to 12cR1##########

[oinstall@arjun admin]$ sqlplus "/as syss dba"

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 09:31:38 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size 2289064 bytes
Variable Size 570425944 bytes
Database Buffers 1090519040 bytes
Redo Buffers 6987776 bytes
Database mounted.
Database opened.
SQL> exit

[oinstall@arjun admin]$ cd $ORACLE_HOME/rdbms/admin

[oinstall@arjun admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Analyzing file catupgrd.sql
Log files in /u01/oinstall/product/12.1.1/diagnostics
14 scripts found in file catupgrd.sql
Next path: catalog.sql
32 scripts found in file catalog.sql
Next path: catproc.sql
37 scripts found in file catproc.sql
Next path: catptabs.sql
61 scripts found in file catptabs.sql
Next path: catpdbms.sql
205 scripts found in file catpdbms.sql
Next path: catpdeps.sql
77 scripts found in file catpdeps.sql
Next path: catpprvt.sql
260 scripts found in file catpprvt.sql
Next path: catpexec.sql
26 scripts found in file catpexec.sql
Next path: cmpupgrd.sql
16 scripts found in file cmpupgrd.sql

[Phase 0] type is 1 with 1 Files
catupstr.sql

[Phase 1] type is 1 with 3 Files
cdstrt.sql cdfixed.sql cdcore.sql

[Phase 2] type is 1 with 1 Files
ora_restart.sql

[Phase 3] type is 2 with 18 Files
cdplsql.sql cdsqlddl.sql cdmanage.sql cdtxnspc.sql
cdenv.sql cdrac.sql cdsec.sql cdobj.sql
cdjava.sql cdpart.sql cdrep.sql cdaw.sql
cdsummgt.sql cdtools.sql cdexttab.sql cddm.sql
catldr.sql cdclst.sql

[Phase 4] type is 1 with 1 Files
ora_restart.sql

[Phase 5] type is 1 with 5 Files
cdoptim.sql catsum.sql catexp.sql cddst.sql
cdend.sql

[Phase 6] type is 1 with 1 Files
catpstrt.sql

[Phase 7] type is 1 with 3 Files
catptyps.sql catpgrants.sql catgwm.sql

[Phase 8] type is 1 with 1 Files
ora_restart.sql

[Phase 9] type is 2 with 60 Files
catnodpt.sql catbac.sql prvtrctv.plb catactx.sql
prvtuttv.plb catsvrmg.sql prvtlsis.plb prvtlsss.plb
cattrans.sql catrule.sql catsnap.sql catpitr.sql
catdip.sql catrls.sql catar.sql catfga.sql
catamgt.sql catidxu.sql cattsm.sql catchnf.sql
catodm.sql catkppls.sql catsscr.sql catqueue.sql
cathae.sql catadvtb.sql catrm.sql catsch.sql
catol.sql catdpb.sql catcrc.sql dbmscr.sql
dbmsutil.sql catdbfus.sql catalrt.sql catatsk.sql
catmntr.sql catsqlt.sql catawrtv.sql catsmbvw.sql
catwrrtb.sql catsumat.sql catrep.sql catlmnr.sql
catdef.sql catadrvw.sql catrepv.sql catpexe.sql
cattlog.sql catcapi.sql catpspi.sql catts.sql
catnacl.sql catredact.sql catproftab.sql catpstdy.sql
catrupg.sql catratmask.sql catqitab.sql catappcont.sql

[Phase 10] type is 1 with 1 Files
ora_restart.sql

[Phase 11] type is 1 with 1 Files
catpspec.sql

[Phase 12] type is 1 with 1 Files
ora_restart.sql

[Phase 13] type is 2 with 199 Files
utlinad.sql utlsmtp.sql utlurl.sql utlenc.sql
utlgdk.sql utlcstk.sql utlcomp.sql utli18n.sql
utllms.sql dbmsplsw.sql utlnla.sql dbmspdb.sql
dbmstrns.sql dbmsrwid.sql dbmspclx.sql dbmserlg.sql
dbmsspu.sql dbmsapin.sql dbmssyer.sql dbmspipe.sql
dbmsalrt.sql dbmsdesc.sql dbmspexp.sql dbmsjob.sql
dbmsstat.sql dbmsstts.sql dbmsddl.sql dbmsedu.sql
dbmspp.sql prvthddl.plb prvthjob.plb prvthsye.plb
prvtzhlp.plb dbmsidxu.sql prvthidx.plb dbmspsp.sql
dbmstran.sql dbmsxa.sql dbmstxfm.sql dbmsread.sql
prvtreut.plb dbmspb.sql dbmspbt.sql dbmsplts.sql
dbmspitr.sql utlrefld.sql utlcoll.plb dbmstrst.sql
dbmsrlsa.sql dbmsrpr.sql dbmsobtk.sql dbmshtdb.sql
dbmslm.sql dbmslmd.sql prvtlmes.plb utlcxml.sql
dbmsfga.sql dbmsamgt.sql dbmstypu.sql dbmsres.sql
dbmstxin.sql dbmsdrs.sql dbmsdg.sql dbmssum.sql
dbmshord.sql dbmsxfr.sql dbmsmap.sql dbmsfi.sql
dbmsdbv.sql dbmstcv.sql dbmscoll.sql dbmscdcu.sql
dbmscdcp.sql dbmscdcs.sql dbmspbp.sql dbmshpro.sql
dbmssrv.sql dbmschnf.sql dbmsxpln.sql utlmatch.sql
dbmsdbvn.sql dbmspool.sql dbmsrcad.sql prvthcrc.plb
prvtkpps.plb dbmsaq.plb dbmsaqad.sql dbmsaq8x.plb
dbmsaqem.plb prvtaqxi.plb dbmsslrt.sql dbmsmntr.sql
dbmshm.sql catsqltk.sql dbmsir.sql prvtsss.plb
dbmsocm.sql dbmslobu.sql dbmsmp.sql dbmsaddm.sql
prvttxfs.plb dbmsrmin.plb dbmsrmad.sql dbmsrmpr.sql
dbmsrmpe.plb dbmsrmge.plb dbmsrmpa.plb prvtrmie.plb
prvthjob.plb prvthesh.plb dbmsol.sql prvtdputh.plb
dbmsmeta.sql dbmsmetb.sql dbmsmetd.sql dbmsmet2.sql
dbmsdp.sql prvthpp.plb prvthpd.plb prvthpdi.plb
prvthpvi.plb prvtdtde.plb prvtsum.plb prvtjdbs.plb
dbmsslxp.sql prvssmgu.plb dbmsawr.sql prvsemxi.plb
prvsemx_admin.plb prvsemx_dbhome.plb prvsemx_memory.plb prvsemx_perf.plb
dbmsperf.sql prvsrept.plb prvsrepr.plb prvshdm.plb
prvsrtaddm.plb prvs_awr_data_cp.plb prvscpaddm.plb prvsadv.plb
prvsawr.plb prvsawri.plb prvsawrs.plb prvsash.plb
prvsawrv.plb dbmssqlu.sql prvssqlf.plb dbmswrr.sql
dbmsfus.sql prvsfus.plb dbmsuadv.sql dbmsrepl.sql
dbmsspm.sql prvsspmi.plb prvssmb.plb prvssmbi.plb
dbmsstr.sql dbmssqlt.sql dbmsspa.sql prvsautorepi.plb
dbmsautorep.sql dbmsratmask.sql dbmsdiag.sql dbmsobj.sql
dbmskzxp.sql dbmscu.sql dbmsdst.sql dbmscomp.sql
dbmsilm.sql dbmspexe.sql prvthpexei.plb dbmscapi.sql
dbmsfuse.sql dbmsfspi.sql dbmspspi.sql dbmsdnfs.sql
dbmsadr.sql dbmsadra.sql prvsadri.plb xsrs.sql
xssc.sql xsacl.sql xsds.sql xsns.sql
xsdiag.sql xssess.sql dbmsredacta.sql dbmssqll.sql
dbmsgwm.sql dbmsappcont.sql dbmsspd.sql prvsspdi.plb
dbmsfs.sql dbmssqlm.sql catprofp.sql prvtsys.plb
dbmspart.sql dbmsrupg.sql dbmstsdp.sql

[Phase 14] type is 1 with 1 Files
ora_restart.sql

[Phase 15] type is 1 with 3 Files
dbmsmeti.sql dbmsmetu.sql dbmsqopi.sql

[Phase 16] type is 1 with 1 Files
ora_restart.sql

[Phase 17] type is 2 with 33 Files
catmettypes.sql prvthdbu.plb catost.sql dbmshae.sql
catxpend.sql prvtotpt.plb prvthlut.plb prvthlin.plb
prvthsdp.plb dbmsrman.sql dbmsbkrs.sql dbmstrig.sql
dbmsrand.sql dbmsjdwp.sql catxs.sql dbmssnap.sql
prvtxrmv.plb depsaq.sql prvthlrt.plb catadv.sql
dbmscred.sql catcredv.sql cataqsch.sql catrssch.sql
catplug.sql prvtsql.plb prvtssql.plb prvtlmd.plb
prvtlmcs.plb prvtlmrs.plb dbmslms.sql prvthpu.plb
prvthpv.plb

[Phase 18] type is 1 with 1 Files
ora_restart.sql

[Phase 19] type is 1 with 3 Files
prvtkupc.plb prvtaqiu.plb catlsby.sql

[Phase 20] type is 1 with 1 Files
ora_restart.sql

[Phase 21] type is 2 with 23 Files
catmetviews.sql prvthpw.plb prvthpm.plb prvthpfi.plb
prvthpf.plb dbmsodm.sql prvtitrg.plb prvtsms.plb
depssvrm.sql deptxn.sql catstr.sql prvthsts.plb
prvthfgr.plb prvthfie.plb prvthcmp.plb catpexev.sql
depscapi.sql depspspi.sql catwrrvw.sql dbmsjdcu.sql
dbmsjdmp.sql prvthpc.plb prvt_awr_data.plb

[Phase 22] type is 1 with 1 Files
ora_restart.sql

[Phase 23] type is 2 with 11 Files
catmetgrant1.sql catldap.sql prvtocm.sql prvtrepl.sql
catpstr.sql prvthpci.plb catilm.sql catemxv.sql
catnaclv.sql dbmsnacl.sql dbmswlm.sql

[Phase 24] type is 1 with 1 Files
ora_restart.sql

[Phase 25] type is 1 with 1 Files
catcdbviews.sql

[Phase 26] type is 1 with 1 Files
ora_restart.sql

[Phase 27] type is 2 with 0 Files

[Phase 28] type is 1 with 1 Files
ora_load_without_comp.sql

[Phase 29] type is 2 with 130 Files
prvtfile.plb prvtrawb.plb prvttcp.plb prvtinad.plb
prvtsmtp.plb prvthttp.plb prvturl.plb prvtenc.plb
prvtgdk.plb prvtlob.plb prvtlobu.plb prvtcstk.plb
prvtcomp.plb prvti18n.plb prvtlms2.plb prvtnla.plb
prvttrns.plb prvtsess.plb prvtrwid.plb prvtpclx.plb
prvterlg.plb prvtapin.plb prvtsyer.plb prvtlock.plb
prvtpipe.plb prvtalrt.plb prvtdesc.plb prvtpexp.plb
prvtzexp.plb prvtstts.plb prvtddl.plb prvtpp.plb
prvtscrp.plb prvtkppb.plb prvtutil.plb prvtpsp.plb
prvttran.plb prvtxa.plb prvtany.plb prvtread.plb
prvtpb.plb prvtpbt.plb prvtxpsw.plb prvtcoll.plb
prvttrst.plb prvtrlsa.plb prvtodci.plb prvtrpr.plb
prvtobtk.plb prvthtdb.plb prvtxmlt.plb prvturi.plb
prvtxml.plb prvtcxml.plb prvtemxi.plb prvtemx_admin.plb
prvtemx_dbhome.plb prvtemx_memory.plb prvtemx_perf.plb prvtperf.plb
prvtrep.plb prvtrept.plb prvtrepr.plb prvtfga.plb
prvtamgt.plb prvttypu.plb prvtjdwp.plb prvtjdmp.plb
prvtres.plb prvtcr.plb prvttxin.plb prvtdrs.plb
prvtdg.plb prvtfi.plb prvtmap.plb prvthpui.plb
prvtdbv.plb prvttcv.plb prvtpbp.plb prvthpro.plb
prvtbdbu.plb prvtsrv.plb prvtpool.plb prvtkzxs.plb
prvtkzxp.plb prvtcrc.plb prvtrc.plb prvtaq.plb
prvtaqdi.plb prvtaqxe.plb prvtaqis.plb prvtaqim.plb
prvtaqad.plb prvtaq8x.plb prvtaqin.plb prvtaqal.plb
prvtaqjm.plb prvtaqmi.plb prvtaqme.plb prvtaqem.plb
prvtaqip.plb prvtaqds.plb prvtsqdi.plb prvtsqds.plb
prvtsqis.plb prvthm.plb prvtwlm.plb prvtsqtk.plb
prvtkjhn.plb prvtir.plb prvtssb.plb prvttxfm.plb
prvtrmin.plb prvtrmad.plb prvtrmpr.plb prvtrmpe.plb
prvtrmge.plb prvtrmpa.plb prvtjob.plb prvtbsch.plb
prvtesch.plb prvtcred.plb prvtol.plb prvtlm.plb
prvtlmcb.plb prvtlmrb.plb prvtlms.plb prvtlmeb.plb
prvtbpu.plb prvtwrr.plb

[Phase 30] type is 1 with 1 Files
ora_load_with_comp.sql

[Phase 31] type is 1 with 1 Files
ora_restart.sql

[Phase 32] type is 1 with 1 Files
ora_load_without_comp.sql

[Phase 33] type is 2 with 122 Files
prvtbpui.plb prvtdput.plb prvtmeta.plb prvtmeti.plb
prvtmetu.plb prvtmetb.plb prvtmetd.plb prvtmet2.plb
prvtdp.plb prvtbpc.plb prvtbpci.plb prvtbpw.plb
prvtbpm.plb prvtbpfi.plb prvtbpf.plb prvtbpp.plb
prvtbpd.plb prvtbpdi.plb prvtbpv.plb prvtbpvi.plb
prvtdpcr.plb prvtplts.plb prvtpitr.plb prvtreie.plb
prvtrwee.plb prvtidxu.plb prvtrcmp.plb prvtchnf.plb
prvtedu.plb prvtlsby.plb prvtlsib.plb prvtlssb.plb
prvtsmv.plb prvtsma.plb prvtbxfr.plb prvtbord.plb
prvtjdbb.plb prvtslrt.plb prvtslxp.plb prvtatsk.plb
prvtmntr.plb prvtsmgu.plb prvtdadv.plb prvtadv.plb
prvtawr.plb prvtawrs.plb prvtawri.plb prvtash.plb
prvtawrv.plb prvtsqlf.plb prvtsqli.plb prvtsqlt.plb
prvtautorepi.plb prvtautorep.plb prvtfus.plb prvtmp.plb
prvthdm.plb prvtaddm.plb prvtrtaddm.plb prvt_awr_data_cp.plb
prvtcpaddm.plb prvtuadv.plb prvtsqlu.plb prvtspai.plb
prvtspa.plb prvtratmask.plb prvtspmi.plb prvtspm.plb
prvtsmbi.plb prvtsmb.plb prvtfus.plb catfusrg.sql
prvtwrk.plb prvtsmaa.plb prvtxpln.plb prvtstat.plb
prvtstai.plb prvtsqld.plb prvtspcu.plb prvtodm.plb
prvtkcl.plb prvtdst.plb prvtcmpr.plb prvtilm.plb
prvtpexei.plb prvtpexe.plb prvtcapi.plb prvtfuse.plb
prvtfspi.plb prvtpspi.plb prvtdnfs.plb prvtfs.plb
prvtadri.plb prvtadr.plb prvtadra.plb prvtadmi.plb
prvtutils.plb prvtxsrs.plb prvtsc.plb prvtacl.plb
prvtds.plb prvtns.plb prvtdiag.plb prvtkzrxu.plb
prvtnacl.plb prvtredacta.plb prvtpdb.plb prvttlog.plb
prvtsqll.plb prvtappcont.plb prvtspd.plb prvtspdi.plb
prvtpprof.plb prvtsqlm.plb prvtpart.plb prvtrupg.plb
prvtrupgis.plb prvtrupgib.plb prvtpstdy.plb prvttsdp.plb
prvtqopi.plb prvtlog.plb

[Phase 34] type is 1 with 1 Files
ora_load_with_comp.sql

[Phase 35] type is 1 with 1 Files
ora_restart.sql

[Phase 36] type is 1 with 4 Files
catmetinsert.sql catpcnfg.sql utluppkg.sql catdph.sql

[Phase 37] type is 1 with 1 Files
ora_restart.sql

[Phase 38] type is 2 with 13 Files
catmetgrant2.sql execemx.sql execcr.sql caths.sql
catemini.sql execaq.sql execsvrm.sql exechae.sql
execsec.sql execbsln.sql dbmspump.sql olappl.sql
execrep.sql

[Phase 39] type is 1 with 1 Files
ora_restart.sql

[Phase 40] type is 2 with 10 Files
execstr.sql execsvr.sql execstat.sql catsnmp.sql
wpiutil.sql owainst.sql catilmini.sql execocm.sql
exectsdp.sql execqopi.sql

[Phase 41] type is 1 with 1 Files
ora_restart.sql

[Phase 42] type is 1 with 1 Files
catpend.sql

[Phase 43] type is 1 with 1 Files
ora_restart.sql

[Phase 44] type is 1 with 1 Files
catupprc.sql

[Phase 45] type is 1 with 1 Files
cmpupstr.sql

[Phase 46] type is 1 with 1 Files
ora_restart.sql

[Phase 47] type is 1 with 2 Files
cmpupjav.sql cmpupnjv.sql

[Phase 48] type is 1 with 1 Files
ora_restart.sql

[Phase 49] type is 1 with 2 Files
cmpupxdb.sql cmpupnxb.sql

[Phase 50] type is 1 with 1 Files
ora_restart.sql

[Phase 51] type is 1 with 2 Files
cmpupord.sql cmpupmsc.sql

[Phase 52] type is 1 with 1 Files
ora_restart.sql

[Phase 53] type is 1 with 1 Files
cmpupend.sql

[Phase 54] type is 1 with 1 Files
catupend.sql

[Phase 55] type is 1 with 1 Files
catuppst.sql

[Phase 56] type is 1 with 1 Files
catshutdown.sql

Using 6 processes.
Serial Phase #: 0 Files: 1
Time: 118s
Serial Phase #: 1 Files: 3 Time: 29s
Restart Phase #: 2 Files: 1 Time: 1s
Parallel Phase #: 3 Files: 18 Time: 6s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 15s
Serial Phase #: 6 Files: 1 Time: 14s
Serial Phase #: 7 Files: 3 Time: 6s
Restart Phase #: 8 Files: 1 Time: 1s
Parallel Phase #: 9 Files: 60 Time: 24s
Restart Phase #:10 Files: 1 Time: 1s
Serial Phase #:11 Files: 1 Time: 14s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 199 Time: 52s
Restart Phase #:14 Files: 1 Time: 1s
Serial Phase #:15 Files: 3 Time: 1s
Restart Phase #:16 Files: 1 Time: 0s
Parallel Phase #:17 Files: 33 Time: 31s
Restart Phase #:18 Files: 1 Time: 1s
Serial Phase #:19 Files: 3 Time: 5s
Restart Phase #:20 Files: 1 Time: 0s
Parallel Phase #:21 Files: 23 Time: 52s
Restart Phase #:22 Files: 1 Time: 1s
Parallel Phase #:23 Files: 11 Time: 22s
Restart Phase #:24 Files: 1 Time: 1s
Serial Phase #:25 Files: 1 Time: 24s
Restart Phase #:26 Files: 1 Time: 0s
Parallel Phase #:27 Files: 0 Time: 1s
Serial Phase #:28 Files: 1 Time: 0s
Parallel Phase #:29 Files: 130 Time: 11s
Serial Phase #:30 Files: 1 Time: 0s
Restart Phase #:31 Files: 1 Time: 0s
Serial Phase #:32 Files: 1 Time: 0s
Parallel Phase #:33 Files: 122 Time: 12s
Serial Phase #:34 Files: 1 Time: 0s
Restart Phase #:35 Files: 1 Time: 1s
Serial Phase #:36 Files: 4 Time: 48s
Restart Phase #:37 Files: 1 Time: 0s
Parallel Phase #:38 Files: 13 Time: 29s
Restart Phase #:39 Files: 1 Time: 1s
Parallel Phase #:40 Files: 10 Time: 8s
Restart Phase #:41 Files: 1 Time: 1s
Serial Phase #:42 Files: 1 Time: 5s
Restart Phase #:43 Files: 1 Time: 0s
Serial Phase #:44 Files: 1 Time: 7s
Serial Phase #:45 Files: 1 Time: 0s
Restart Phase #:46 Files: 1 Time: 0s
Serial Phase #:47 Files: 2 Time: 340s
Restart Phase #:48 Files: 1 Time: 0s
Serial Phase #:49 Files: 2 Time: 278s
Restart Phase #:50 Files: 1 Time: 0s
Serial Phase #:51 Files: 2 Time: 1167s
Restart Phase #:52 Files: 1 Time: 1s
Serial Phase #:53 Files: 1 Time: 1s
Serial Phase #:54 Files: 1

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

1. Evaluate the errors found in the upgrade logs (*.log) and determine the proper action.
2. Execute the post upgrade script as described in Chapter 3 of the Database Upgrade Guide.

Time: 110s
Grand Total Time: 2441s
[oinstall@arjun admin]$
[oinstall@arjun admin]$ ls -lrt *.log
[00mls: *.log: No such file or directory
[oinstall@arjun admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 10:22:15 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size 2289064 bytes
Variable Size 570425944 bytes
Database Buffers 1090519040 bytes
Redo Buffers 6987776 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool 07-16-2013 10:24:24
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. UPGRADED 12.1.0.1.0 00:09:02
JServer JAVA Virtual Machine
. VALID 12.1.0.1.0 00:04:08
Oracle Workspace Manager
. VALID 12.1.0.1.0 00:00:45
OLAP Analytic Workspace
. VALID 12.1.0.1.0 00:00:54
Oracle OLAP API
. ORA-00942: table or view does not exist
. ORA-00942: table or view does not exist
. ORA-00942: table or view does not exist
. ORA-00942: table or view does not exist
. ORA-00942: table or view does not exist
. VALID 12.1.0.1.0 00:00:12
Oracle XDK
. VALID 12.1.0.1.0 00:00:36
Oracle Text
. VALID 12.1.0.1.0 00:01:02
Oracle XML Database
. VALID 12.1.0.1.0 00:02:38
Oracle Database Java Packages
. VALID 12.1.0.1.0 00:00:10
Oracle Multimedia
. VALID 12.1.0.1.0 00:01:40
Spatial
. VALID 12.1.0.1.0 00:04:08
Oracle Application Express
. VALID 4.2.0.00.27 00:13:10
Final Actions
. 00:01:14
Total Upgrade Time: 00:40:03

PL/SQL procedure successfully completed.

SQL>

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql

Session altered.

Session altered.

Session altered.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2013-07-16 10:25:24

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2013-07-16 10:25:24

PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2013-07-16 10:25:24

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

This script will migrate the Baseline data on a pre-11g database
to the 11g database.

... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

0 rows created.

Commit complete.

Table created.

2 rows created.

1 row updated.

2 rows updated.

0 rows updated.

Table dropped.

Commit complete.

0 rows updated.

Commit complete.

0 rows updated.

Commit complete.

0 rows updated.

Commit complete.

0 rows created.

Commit complete.

0 rows created.

Commit complete.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

catuppst: Gathering fixed objects stats now...
catuppst: Gathering fixed objects stats done.

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2013-07-16 10:26:41

PL/SQL procedure successfully completed.

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Generating apply and rollback scripts...
Check the following file for errors:
/u01/oinstall/cfgtoollogs/catbundle/catbundle_PSU_MUTH_GENERATE_2013Jul16_10_26_44.log
Apply script: /u01/oinstall/product/12.1.1/rdbms/admin/catbundle_PSU_MUTH_APPLY.sql
Rollback script: /u01/oinstall/product/12.1.1/rdbms/admin/catbundle_PSU_MUTH_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...

SQL> alter session set "_ORACLE_SCRIPT" = true;

Session altered.

SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/oinstall/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '12.1.0.1',
9 0,
10 'PSU',
11 'Patchset 12.1.0.0.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/oinstall/cfgtoollogs/catbundle/catbundle_PSU_MUTH_APPLY_2013Jul16_10_26_45.log

Session altered.

Session altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oinstall@arjun admin]$ vi /u01/oinstall/cfgtoollogs/catbundle/catbundle_PSU_MUTH_APPLY_2013Jul16_10_26_45.log

############Compile Invalid Objects#########
[oinstall@arjun admin]$sqlplus "/as sysdba"

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @/usr/tmp/dbupgdiag.sql

###########Post Upgrade Steps################

Make sure the following environment variables point to the Oracle 12c Release 1 (12.1) directories
- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH and SHLIB_PATH
Ensure that your oratab file and any client scripts that set the value of ORACLE_HOME point to the new Oracle home that is created for the new Oracle Database 12c release,

############Initialization parameter file##########
Edit init.ora
If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
Migrate your initialization parameter file to a server parameter file.
Create a server parameter file with an initialization parameter file.
SQL> create spfile from pfile;

###############Password File###################

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to either exclusive or shared, create a password file with ORAPWD.

#############COMPATIBLE Initialization Parameter#############

The COMPATIBLE initialization parameter controls the compatibility level of your database.
When you are certain that you no longer need the ability to downgrade your database to its original release
Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).
If you are using a server parameter file, then complete the following steps:
a. Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter.
For example, to set the COMPATIBLE initialization parameter to 12.0.0, enter the following statement:
SQL> ALTER SYSTEM SET COMPATIBLE = '12.0.0' SCOPE=SPFILE;
b. Shut down and restart the instance.
If you are using an initialization parameter file, then complete the following steps:
a. Shut down the instance if it is running:
SQL> SHUTDOWN IMMEDIATE
b. Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter.
For example, to set the COMPATIBLE initialization parameter to for Oracle Database release 12.1, enter the following in the initialization parameter file:
COMPATIBLE = 12.1.0
c. Start the instance using STARTUP.

Change passwords for Oracle-Supplied Accounts.

SQL> SELECT username, account_status FROM dba_users ORDER BY username;

To lock and expire passwords, issue the following SQL statement:

SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

Upgrade the Recovery Catalog After Upgrading Oracle Database

You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command

Upgrade the Time Zone File Version After Upgrading Oracle Database

Identify Invalid Objects With the utluiobj Script

SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL>

Garuda Oracle SQL postgresql AWS blog