Drop database links from other schemas using sys

Create or replace procedure DROP_LINK(schemaName varchar2, dbLink varchar2 ) is
            plsql   varchar2(1000);
            cur     number;
            uid     number;
            rc      number;
    begin
            select
                    u.user_id into uid
           from    dba_users u
           where   u.username = schemaName;
             plsql := 'drop database link "'||dbLink||'"';
             cur := SYS.DBMS_SYS_SQL.open_cursor;
             SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => plsql,
                   language_flag => DBMS_SQL.native,
                   userID => uid
          );
             rc := SYS.DBMS_SYS_SQL.execute(cur);

             SYS.DBMS_SYS_SQL.close_cursor(cur);
   end;
   /
 
 exec Drop_LINK( 'WGPOMSUSR', 'DBNAME' );

 select * from dba_db_links;

 drop procedure Drop_DbLink;

Solaris password commands

Force user to change password on next login

passwd -f oracle


Turn off password aging

passwd -x -1 oracle

This displays password expiry time

passwd -s oracle

DataGuard Health Check

STANDBY

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

or

SELECT sequence# || '|' ||
thread# || '|' ||
TO_CHAR(completion_time, 'DD-MON-RRRR HH24:MI:SS')
FROM v$archived_log
WHERE sequence#= (SELECT MAX(sequence#) FROM v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database))
AND thread#=1
AND resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
AND dest_id=1
UNION ALL
SELECT sequence# || '|' ||
thread# || '|' ||
TO_CHAR(completion_time, 'DD-MON-RRRR HH24:MI:SS')
FROM v$archived_log
WHERE sequence#= (SELECT MAX(sequence#) FROM v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database))
AND resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
AND thread#=2
AND dest_id=1;

Solaris script to find cpu cores

#!/bin/bash

/usr/bin/kstat -m cpu_info | egrep "chip_id|core_id|module: cpu_info" > /var/tmp/cpu_info.log

nproc=`(grep chip_id /var/tmp/cpu_info.log | awk '{ print $2 }' | sort -u | wc -l | tr -d ' ')`
ncore=`(grep core_id /var/tmp/cpu_info.log | awk '{ print $2 }' | sort -u | wc -l | tr -d ' ')`
vproc=`(grep 'module: cpu_info' /var/tmp/cpu_info.log | awk '{ print $4 }' | sort -u | wc -l | tr -d ' ')`

nstrandspercore=$(($vproc/$ncore))
ncoresperproc=$(($ncore/$nproc))

speedinmhz=`(/usr/bin/kstat -m cpu_info | grep clock_MHz | awk '{ print $2 }' | sort -u)`
speedinghz=`echo "scale=2; $speedinmhz/1000" | bc`

echo "Total number of physical processors: $nproc"
echo "Number of virtual processors: $vproc"
echo "Total number of cores: $ncore"
echo "Number of cores per physical processor: $ncoresperproc"
echo "Number of hardware threads (strands or vCPUs) per core: $nstrandspercore"
echo "Processor speed: $speedinmhz MHz ($speedinghz GHz)"

# now derive the vcpu-to-core mapping based on above information #

echo -e "\n** Socket-Core-vCPU mapping **"
let linenum=2

for ((i = 1; i <= ${nproc}; ++i ))
do
        chipid=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $2 }'`
        echo -e "\nPhysical Processor $i (chip id: $chipid):"

        for ((j = 1; j <= ${ncoresperproc}; ++j ))
        do
                let linenum=($linenum + 1)
                coreid=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $2 }'`
                echo -e "\tCore $j (core id: $coreid):"

                let linenum=($linenum - 2)
                vcpustart=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $4 }'`

                let linenum=(3 * $nstrandspercore + $linenum - 3)
                vcpuend=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $4 }'`

                echo -e "\t\tvCPU ids: $vcpustart - $vcpuend"
                let linenum=($linenum + 4)
        done
done

rm /var/tmp/cpu_info.log

Sample output

oracle:/export/home/oracle>./proc.sh

Total number of physical processors: 1

Number of virtual processors: 64

Total number of cores: 8

Number of cores per physical processor: 8

Number of hardware threads (strands or vCPUs) per core: 8

Processor speed: 2848 MHz (2.84 GHz)

** Socket-Core-vCPU mapping **

Physical Processor 1 (chip id: 0):

        Core 1 (core id: 1026):

                vCPU ids: 0 - 7

        Core 2 (core id: 1030):

                vCPU ids: 8 - 15

        Core 3 (core id: 1034):

                vCPU ids: 16 - 23

        Core 4 (core id: 1038):

                vCPU ids: 24 - 31

        Core 5 (core id: 1042):

                vCPU ids: 32 - 39

        Core 6 (core id: 1046):

                vCPU ids: 40 - 47

        Core 7 (core id: 1050):

                vCPU ids: 48 - 55

        Core 8 (core id: 1054):

                vCPU ids: 56 - 63

Apply psu and one off patches for single instance database with ASM

First make sure you have lastest version of OPatch in both oracle and grid homes.

Opatch Grid home owned by oracle:dba

Opatch db home owned by oracle:dba

Patch folder unzipped by oracle

opatch auto to be run by root

ocm.rsp file to be generated by oracle user in both grid and db home


cd $ORACLE_HOME

cd OPatch/ocm/bin

./emo*


Create a response file using above


download patch and unzip to the staging folder

in my case

/export/home/oracle/oracle_software/25476126



First apply for grid home


export ORACLE_HOME=/opt/app/crs/11.2.0.4


./opatch auto /export/home/oracle/oracle_software/25476126 -oh /opt/app/crs/11.2.0.4 -ocmrf /opt/app/crs/11.2.0.4/OPatch/ocm/bin/ocm.rsp



Now apply for database home

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4/

./opatch auto /export/home/oracle/oracle_software/25476126 -oh /opt/app/oracle/product/11.2.0/11.2.0.4/ -ocmrf /opt/app/oracle/product/11.2.0/11.2.0.4/OPatch/ocm/bin/ocm.rsp


***********************************************************************

Apply one off patches

First apply from grid home

export ORACLE_HOME=/opt/app/crs/11.2.0.4

cd /opt/app/crs/11.2.0.4/OPatch
./opatch apply -oh /opt/app/crs/11.2.0.4 -local /export/home/oracle/oracle_software/10194190



Now apply for database home

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4/

cd /export/home/oracle/oracle_software/10194190

/opt/app/oracle/product/11.2.0/11.2.0.4/OPatch/opatch apply







********************************************************************************

NOTE

if response file creation fails with motif error in solaris

please install below

pkg install pkg://solaris/library/motif

cleaning all asm disks and headers and recreating diskgroup in solaris

Identify the disks for the diskgroup you are trying to format everything from

In my case i am cleanup data diskgroup and the raw devices are
/dev/rdsk/c0d3s0
/dev/rdsk/c0d4s0
..
..
...
..


So i am running the following command to cleanup disks



dd if=/dev/zero of=/dev/rdsk/c0d3s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d4s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d5s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d6s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d7s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d8s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d9s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d10s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d11s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d12s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d13s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d14s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d15s0 bs=1048576 count=50
dd if=/dev/zero of=/dev/rdsk/c0d16s0 bs=1048576 count=50



Recreating diskgroup in solaris

Login as root


# format

select disk

y
p
6
default
default
0
0
1
default
default
0
0

0
default
default
1

10920c/65532c (depending on disk)
label
y
q


After its done

View permissions using

ls -lhL /dev/rdsk/c0d*s0

grant permission to oracle user on the disk

chown oracle:dba /dev/rdsk/c0d3s0
chown oracle:dba /dev/rdsk/c0d4s0

chmod 660 /dev/rdsk/c0d3s0
chmod 660 /dev/rdsk/c0d4s0

Once you do this

you should be able to see diskgroup in asmca and proceed creating diskgroup

To improve rebalance speed

alter diskgroup data rebalance power 4;

11g standby database using dataguard

For 11g databases you need to create a temporary listener with a different port on the target server or else it will show blocked status when database is started at nomount

Primary and standby :

tnsnames.ora on both servers

testDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


Edit or change parameters from db

PRIMARY
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set FAL_SERVER='testDR';
alter system set FAL_CLIENT='test';



--*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
--*.LOG_FILE_NAME_CONVERT='stand','prim'


create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp  inittest.ora oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/

scp  orapwtest oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/


Standby:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='test'
*.FAL_CLIENT='testdr'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump



Standby:

startup nomount

Primary :

rman target sys/password@test auxiliary sys/password@testDR


duplicate target database for standby from active database dorecover;

create spfile from pfile;

shut immediate;

startup;

select open_mode from v$database;

output should be read only

Now START MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;



Verify Dataguard configuration is syncing by using below queries


Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)


************************************************************************************
Steps to activate standby:


STOP MRP
alter database recover managed standby database cancel;


Change Redolog entries in control files before activating standby


on STANDBY
ALTER DATABASE ACTIVATE STANDBY DATABASE;


If you get any errors regards to redolog.. Fix the issue



select * from v$logfile;

select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;


alter database rename file '/orabase/origlogA/test/redo01a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo01b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo02a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo02b.log' to '+REDO';
alter database rename file '/orabase/origlogA/test/redo03a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo03b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo04a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo04b.log' to '+REDO';



Then try to activate standby again

ALTER DATABASE ACTIVATE STANDBY DATABASE;



Use this below to stop archivelog transfer from primary to standby
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;