Tuesday, December 19, 2017

Flushing the Shared Pool on AWS RDS

--
-- Flushing the Shared Pool on AWS RDS
--
-- On AWS RDS you do not have access to the server 
--or to a proper SYSDBA account.  
-- As a result you are defendant on lots of AWS RDS packages.
-- Or in some instances, you need to find new ways of 
--achieving the same thing
--
 
exec rdsadmin.rdsadmin_util.flush_shared_pool
/

Fix Invalid Objects on AWS RDS

--
-- Fix Invalid Objects on AWS RDS where you do not 
-- have access to utlrp.sql.
--
 
SET LINES 180
SET ECHO OFF
SET HEA OFF
SET FEEDBACK OFF
 
SPOOL /tmp/fixinvalid.sql
select distinct('exec SYS.UTL_RECOMP.RECOMP_SERIAL('''||owner||''');') 
from dba_objects WHERE status = 'INVALID'
/
SPOOL OFF
 
SET FEEDBACK ON
SET ECHO ON
@/tmp/fixinvalid.sql

Granting Permissions to SYS Objects in AWS RDS

Granting Select to SYS Objects

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

--
-- Granting Permissions to SYS Objects in AWS RDS
--
-- On AWS RDS you do not have access to the server or
-- to a proper SYSDBA account.  
-- As a result you are defendant on lots of AWS RDS packages.
-- Or in some instances, you need to find new ways of 
--achieving the same thing
--
 
begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'USER1',
        p_privilege => 'SELECT');
end;
/


Granting Select to SYS Objects with the Grant Option

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

--
-- Granting Permissions to SYS Objects in AWS RDS
--
-- On AWS RDS you do not have access to the server or
 to a proper SYSDBA account.  
-- As a result you are defendant on lots of AWS RDS packages.
-- Or in some instances, you need to find new ways of 
achieving the same thing
--
 
begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'USER1',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

Killing a Session on AWS RDS

--
-- Killing a Session on AWS RDS
--
-- On AWS RDS you do not have access to the server or 
--to a proper SYSDBA account.  
-- As a result you are defendant on lots of AWS RDS packages.
-- Or in some instances, you need to find new ways of 
--achieving the same thing
--
 
begin
    rdsadmin.rdsadmin_util.kill(
        sid    => &sid, 
        serial => &serial_number,
        method => 'IMMEDIATE');
end;
/

Flashback Database Commands

--
-- Flashback to restore Point.
--
 
FLASHBACK DATABASE TO RESTORE POINT &restore_point_name
/


--
-- Flashback to an SCN.
--
 
FLASHBACK DATABASE TO SCN &scn_number
/

List Flashback Database Restore Points

--
-- List Flashback Database Restore Points
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
 
SELECT 
      database_incarnation# as Incar,
      scn,
      name,
      time,
      storage_size,
      guarantee_flashback_database
FROM 
      v$restore_point
ORDER BY 4
/

List the Flashback Logging Size

--
-- List the Flashback Logging Size.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
SELECT 
     estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" 
FROM 
     v$flashback_database_log
/

List Earliest Flashback Database Time and SCN

--
-- List Earliest Flashback Database Time and SCN.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN oldest_flash_scn FOR 999,999,999
 
ALTER SESSION 
   SET nls_date_format='DD MON YYYY hh24:mi:ss'
/
 
SELECT 
   oldest_flashback_scn,
   oldest_flashback_time
FROM  
   v$flashback_database_log
/

Flashback Log Details

--
-- List Flashback Log Details.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN "Log NO" FOR 9,999
COLUMN "Thread No" FOR 99
COLUMN "Seq No" FOR 99
COLUMN name FOR A50
COLUMN "Size(GB)" FOR 999,999
COLUMN "First Chg No" FOR 999,999,999,999,999,999
 
ALTER SESSION
     SET nls_date_format='DD MON YYYY hh24:mi:ss'
/
 
SELECT
     log# as "Log No", 
     thread# as "Thread No",
     sequence# as "Seq No",
     name,
     bytes/1024/1024/1024 as "Size(GB)",
     first_change# as "First Chg No",
     first_time
FROM  
   v$flashback_database_logfile
ORDER BY first_time
/

Monday, December 4, 2017


To BottomTo Bottom

In this Document
Purpose
Details
Case1: Discrepancy found in the list of applied patches on different nodes:
Case2: All nodes have same patches but different patch level:
References



APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

PURPOSE

This note lists causes/solutions for the following issue:
Oracle 12c Grid Infrastructure fails to start after patching:
CRS-6706: Oracle Clusterware Release patch level ('xxxxxxxx') does not match
Software patch level ('xxxxxxxxx'). Oracle Clusterware cannot be started
This can be encountered - for example - in cases where the post patch application instruction 
to run 'rootcrs.pl -postpatch' is not followed.

DETAILS

 

Case1: Discrepancy found in the list of applied patches on different nodes:


Node 1:
<GI_HOME>/bin/kfod op=patches
---------------
List of Patches
===============
15994107
16495244   ====>> this one doesn't exist on the other node
17027533
17303297
<GI_HOME>/bin/kfod op=patchlvl
-------------------
Current Patch level
===================
1581137133

Node 2:
<GI_HOME>/bin/kfod op=patches
---------------
List of Patches
===============
15994107
17027533
17077442         ====>> this one doesn't exist on the other node
17303297
<GI_HOME>/bin/kfod op=patchlvl
-------------------
Current Patch level
===================
3400658801

crsd.trc
======
2015-06-23 15:06:00.260076 :  OCRMAS:1797244672: th_hub_verify_master_pubdata:
Shutdown CacheLocal. Patch Levels don't match. 
Local Patch Level [3467666221] != Cache Writer Patch Level [4137922036]

2015-06-23 15:06:00.260101 :  OCRAPI:1797244672: procr_ctx_set_invalid: ctx is in state [6].
2015-06-23 15:06:00.260103 :  OCRAPI:1797244672: procr_ctx_set_invalid: ctx set to invalid
2015-06-23 15:06:00.260193 :  OCRAPI:1797244672: procr_ctx_set_invalid: Aborting...

In 12c, GI home must have identical patches for the clusterware to start unless during rolling patching.
After applied the same patches on all nodes, GI started fine.

Case2: All nodes have same patches but different patch level:


Node1
$ /data01/12.1.0/grid/bin/kfod op=patches
---------------
List of Patches
===============
17303297
18365185
19121550
$ /data01/12.1.0/grid/bin/kfod op=patchlvl
-------------------
Current Patch level
===================
2061097728
Node2
$ /data01/12.1.0/grid/bin/kfod op=patches
---------------
List of Patches
===============
17303297
18365185
19121550
$ /data01/12.1.0/grid/bin/kfod op=patchlvl
-------------------
Current Patch level
===================
2061097728                                   ====>> same patch level
However opatch lsinventory shows the different patch level:
Patch level status of Cluster nodes :
Patching Level Nodes
-------------- -----
1216323972 node2                          ====>> different patch level
2061097728 node1

For 12.1.0.1 on-wards:
Execute"<GI_HOME>/crs/install/rootcrs.sh -patch" as root
 user on the problematic node and the patch level should be corrected.
For Oracle Grid Infrastructure for a standalone server

Log in as root

# <GI_HOME>/crs/install/roothas.sh -unlock
# <GI_HOME>/crs/install/roothas.sh -patch