1-2-3 PSU

Shutdown all the database instances in the 11.2.0.4.0 home
as oracle
srvctl stop home -o /ora01/oracle/product/11.2.0.4/db -s dbstatefile

Rollback patch 18769590
as oracle
$ORACLE_HOME/OPatch/opatch rollback -id 18769590

Rollback the psu
as root
opatch auto -rollback -ocmrf ocm.rsp

Rollback just the db Home
as root
opatch auto -oh /ora01/oracle/product/11.2.0.4/db -rollback -ocmrf ocm.rsp

Apply the PSU with the -norestart option
as root
opatch auto -ocmrf ocm.rsp -norestart

Start all the databasesas oracle
as root
crsctl start resource -w "TYPE = ora.database.type"

Start has and ASM
as root
crsctl start has

Check for ORACLE Patches applied

as grid and 11g:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed  | grep -i 'PSU'

 

for database:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU'

 

for CRS:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'TRACKING BUG' | grep -i 'PSU'

 

For GI:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'GI PSU'

 

query the registry$history database object

select substr(action_time,1,30) action_time,

substr(id,1,10) id,

substr(action,1,10) action,

substr(version,1,8) version,

substr(BUNDLE_SERIES,1,6) bundle,

substr(comments,1,20) comments

from registry$history;

How to login to user when you don't know the password in Oracle

   steps :-

1.    select dbms_metadata.get_ddl('USER','SCOTT') from dual;
2.    change password
3.    connect 
4.    do the work
5.    change it back
6.    revert back to original password from step 1

How to reset password in Oracle after the current password has expired

declare
 cursor pass is 
SELECT    
     'ALTER '||
     SUBSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),
     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),200),'DEFAULT')-1) ,
     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),200),'USER')) USER_PASS 
FROM  DBA_USERS 
WHERE USERNAME IN 
     (SELECT USERNAME  
      FROM DBA_USERS 
      WHERE (USERNAME  in
            (select username  from dba_users where account_status like '%EXPIRED%' and
              (
               username like 'SYSTEM' or 
               username like 'TEST%' ))));

BEGIN
  for rec in pass loop
   begin
    execute immediate rec.USER_PASS;
    exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM);
   end;
  end loop;
END;
/

tnsnames.ora setup

system@DEMO>
system@DEMO> show parameter name

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                           DEMO
db_unique_name                       string                           DEMO
global_names                         boolean                          FALSE
instance_name                        string                           DEMO1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                           DEMO
system@DEMO>
system@DEMO> show parameter domain

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_domain                            string


system@DEMO>  select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
DEMO


sun001@DEMO1:/ora01/oracle/admin/network $ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
ADR_BASE = /oramisc01/oracle
DIAG_ADR_ENABLED=true
SQLNET.SEND_TIMEOUT=10

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

Db_name=DEMO

Service name : DEMO and DEMO_REPORT

step 1

# In database

alter system set service_name= DEMO,  DEMO_REPORT scope=both sid='*';

step 2
check sqlnet.ora and comment parameter names.default_domain 

step 3
lsnrctl reload
lsnrctl status 


step 4

# in tnsnames

DEMO_REPORT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )


DEMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )

# the end