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;

10g standby database using dataguard

Primary and standby :

tnsnames.ora on both servers

testDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1521))
    (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/10.2.0/dbs/

scp  orapwtest oracle@10.0.1.2:/opt/app/oracle/product/10.2.0/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
mkdir -p /opt/app/oracle/admin/test/bdump
mkdir -p /opt/app/oracle/admin/test/cdump
mkdir -p /opt/app/oracle/admin/test/udump




Primary:

alter database create standby controlfile as'/export/home/oracle/test_stby.ctl' reuse;

scp  /export/home/oracle/test_stby.ctl oracle@10.0.1.2:/export/home/oracle/


Run incremental/ full backup

Once completed. Make sure the backup location is accessible by standby database.


Standby:

startup nomount


rman target=sys/password@test auxiliary=/

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK 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;


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;

10g oracle database refresh or clone to same name

test_OLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

test_NEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


On Source:

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5


create pfile from spfile;


scp  orapwtest oracle@10.120.1.2:/opt/app/oracle/product/10.2.0/dbs/

scp  inittest.ora oracle@10.120.1.2:/opt/app/oracle/product/10.2.0/dbs/

Rman > CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;


Take a fullbackup.


On TARGET

Edit pfile

*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.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'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump
mkdir -p /opt/app/oracle/admin/test/bdump
mkdir -p /opt/app/oracle/admin/test/cdump
mkdir -p /opt/app/oracle/admin/test/udump


Make sure the backup from source db is accessible in the same location to standby

If not create directories and copy backup and archive logs to the target

scp -r /oracle/oradata1/backup/test oracle@10.120.1.2:/oracle/oradata1/backup/

Copy the archive logs from source to target

scp -r /oracle/oradata1/archive/test oracle@10.120.1.2:/oracle/oradata1/archive/


Target:

startup nomount

rman target sys/Password@test_old auxiliary /

DUPLICATE TARGET DATABASE TO test;

11g oracle database refresh or clone to different name

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

Source and Target :

test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

testx =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.2)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testx)
    )
  )


Source:

create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

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

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




Target:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+REDO'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.control_files='+DATA/testx/controlfile/control01.ctl','+REDO/testx/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/testx/adump'
*.diagnostic_dest='/opt/app/oracle'

create directories as per pfile

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



 Target:

 startup nomount


 Source :

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


 duplicate target database to testx from active database;

11g oracle database refresh or clone to same name

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

Source and Target :

test_old =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
test_new =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.120.1.2)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


Source:

create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

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

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




Target:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+REDO'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.diagnostic_dest='/opt/app/oracle'

create directories as per pfile

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



 Target:

 startup nomount


 Source :
 
 rman target sys/password@test_old auxiliary sys/password@test_new


 duplicate target database to test from active database;

Check block corruption in oracle database

Errors in file /opt/oradiag/diag/rdbms/phsub/phsub2/trace/phsub2_pz99_23564.trc  (incident=496822):
ORA-01578: ORACLE data block corrupted (file # 1, block # 112636)
ORA-01110: data file 1: '/oracle/oradata1/phsub/system01.dbf'
Incident details in: /opt/oradiag/diag/rdbms/phsub/phsub2/incident/incdir_496822/phsub2_pz99_23564_i496822.trc




SELECT DISTINCT owner, segment_name
FROM   gv$database_block_corruption dbc
       JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;


SELECT segment_type, segment_name
  FROM dba_extents
WHERE file_id = 1
   AND 112636 BETWEEN block_id AND block_id + blocks -1;
 
 


Validate datafile 249 block 226263 ;

VALIDATE CHECK LOGICAL DATAFILE 249;

VALIDATE DATAFILE 249;

Password reset procedure oracle

Deleting a password from history table in oracle and resetting the password

CREATE OR REPLACE PROCEDURE password_reset( p_user_name IN VARCHAR2,
                                       p_password  IN VARCHAR2) is
  ln_user_id   NUMBER;
  lc_alter     VARCHAR2(100);
BEGIN
   ln_user_id := NULL;
   lc_alter   := NULL;
   SELECT user_id
     INTO ln_user_id
  FROM dba_users
 WHERE username = p_user_name;
   dbms_output.put_line('User id is :'||ln_user_id);
 
   DELETE FROM  user_history$
    WHERE user#=ln_user_id;
   dbms_output.put_line(SQL%ROWCOUNT||' rows deleted for the User id :'||ln_user_id);
   COMMIT;
 
   lc_alter := 'alter user '||p_user_name||' identified by '||p_password||' account unlock';
   EXECUTE IMMEDIATE lc_alter;
 
EXCEPTION
WHEN OTHERS
THEN
   dbms_output.put_line('Exception Message is :'||SQLERRM);
END;


grant execute on sys.password_reset to user;



Execute it using the following

DECLARE
  P_USER_NAME VARCHAR2(32767);
  P_PASSWORD VARCHAR2(32767);

BEGIN
  P_USER_NAME := 'xxx';
  P_PASSWORD := 'Passwrd#1';

  SYS.PASSWORD_RESET ( P_USER_NAME, P_PASSWORD );
  COMMIT;
END;

pre req's for 11g oracle database install

For linux

yum install xorg-x11-apps

yum install xorg-x11-apps.x86_64
xhost +


yum install wget
For linux 7

Oracle Linux 7
cd /etc/yum.repos.d
wget http://yum.oracle.com/public-yum-ol7.repo

yum install oracle-rdbms-server-11gR2-preinstall


after install try to install below rpm's

If fails saying gpgcheck failed

add below line and rerun the commands

yum install --nogpgcheck gcc -y





yum install binutils -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y
yum install zlib-devel -y
yum install elfutils-libelf-devel -y

yum install ksh -y


Once everything is installed.. proceed with installation


while installing oracle database

During the link phase you will encounter an error invoking the "ins_emagent.mk" file. To fix this, edit the "$ORACLE_HOME/sysman/lib/ins_emagent.mk", doing a search and replace for the line shown below.

FROM:
$(MK_EMAGENT_NMECTL)
TO  :
$(MK_EMAGENT_NMECTL) -lnnz11


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

For Solaris

pkg install pkg:/x11/diagnostic/x11-info-clients
pkg install oracle-rdbms-server-12-1-preinstall


ndd -set /dev/udp udp_smallest_anon_port 9000
ndd -set /dev/udp udp_largest_anon_port 65500
ndd -set /dev/tcp tcp_smallest_anon_port 9000
ndd -set /dev/tcp tcp_largest_anon_port 65500

Scheduler job oracle db

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'schema.JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2017/06/07 15:00:00.000000 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=10'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'shema.proc_name'
      ,comments        => 'ITSM xxxx'
    );
    END;
    /

Using logrorate in linux for oracle listener and alert logs

Do the following as root user

cd /etc/logrotate.d

vi oracle_listener

/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan3/trace/listener_scan3.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan2/trace/listener_scan2.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan1/trace/listener_scan1.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/oracle/diag/tnslsnr/nasipexadb01/listener/trace/listener.log {
weekly
copytruncate
rotate 4
compress
}

Refer logrorate manpage for more info on parameters.

The above does copy the log file to a diff name and zip it, then truncates the original files.

Rotation will be done weekly and 4 copies will be retained.

If you want to force rotation immediate run the below command

logrotate -f oracle_listener

Note: You may change permissions on the file to 777 just in case if you dont want to ask sys admin every time you want to edit the file

Hanganalyze and Systemstate collection during RAC hang

During hanging situation in database, you may do :

Collection commands for Hanganalyze and Systemstate: RAC

sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
SQL> oradebug tracefile_name

Then upload alert.log and in case hanging reoccur. 

[off topic] Thinkorswim scripts

MTD
plot mreturn =round(100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[21])/(close(period = AggregationPeriod.DAY)[21]),2);
;

WTD
plot wreturn =round(100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[5])/(close(period = AggregationPeriod.DAY)[5]),2);
;

1 year
plot yearreturn =100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[251])/(close(period = AggregationPeriod.DAY)[251]);
;

YTD

def startOfYear = GetYear() <> GetYear()[1];
rec startingClose = if startOfYear then close[1] else startingClose[1];
plot percentChange = 100 * (close / startingClose - 1);
percentChange.AssignValueColor(if percentChange > 1.0 then Color.BLACK else if percentChange < -1.0 then Color.BLACK else Color.CURRENT); AssignBackgroundColor(if percentChange > 1.0 then Color.GREEN else if percentChange < -1.0 then Color.RED else Color.CURRENT);


Study filter for scan


SV_1year_perf() is greater than or equal to 30 and SV_YTD_perf() is greater than or equal to 18

Find process using swap in linux

$top 
then press OpEnter
This will list process by swap usage. Hit C to show the full process name 

Or use this script to find. Got this script from below link. Credits to original writer

http://northernmost.org/blog/find-out-what-is-using-your-swap/

#!/bin/bash 
# Get current swap usage for all running processes
# Erik Ljungstrom 27/05/2011
# Modified by Mikko Rantalainen 2012-08-09
# Pipe the output to "sort -nk3" to get sorted output
# Modified by Marc Methot 2014-09-18
# removed the need for sudo

SUM=0
OVERALL=0
for DIR in `find /proc/ -maxdepth 1 -type d -regex "^/proc/[0-9]+"`
do
    PID=`echo $DIR | cut -d / -f 3`
    PROGNAME=`ps -p $PID -o comm --no-headers`
    for SWAP in `grep VmSwap $DIR/status 2>/dev/null | awk '{ print $2 }'`
    do
        let SUM=$SUM+$SWAP
    done
    if (( $SUM > 0 )); then
        echo "PID=$PID swapped $SUM KB ($PROGNAME)"
    fi
    let OVERALL=$OVERALL+$SUM
    SUM=0
done
echo "Overall swap used: $OVERALL KB"

View hidden parameters in oracle database 11g

select name,value from v$parameter where substr(name,1,1)='_';

Database resource manager oracle 11g

Using create_simple_plan procedure

This below query will automatically create resource manager plan, resource manager groups if they dont exist and assign directives to each group. First group will get level2 75% usage and second group will get 25% usage under contention.

Resource_manager_plan= EDW_PLAN
DEFAULT_CONSUMER_GROUP = 75%
GG_GROUP = 25%

begin
dbms_resource_manager.create_simple_plan(simple_plan=>'EDW_PLAN',
CONSUMER_GROUP1=>'DEFAULT_CONSUMER_GROUP',GROUP1_PERCENT=>75,
CONSUMER_GROUP2=>'GG_GROUP',GROUP2_PERCENT=>25);
END;
/

After this assign the users to their respective groups

BEGIN
    dbms_resource_manager_privs.grant_switch_consumer_group(
        grantee_name => 'SRAVAN',
        consumer_group => 'GG_GROUP',
        grant_option => TRUE
    );
END;
BEGIN
    dbms_resource_manager.set_initial_consumer_group(
        user => 'SRAVAN',
        consumer_group => 'GG_GROUP'
    );
END;


After this activate the resource manager plan

alter system set resource_manager_plan="EDW_PLAN";

You can verify and monitor DBRM using below queries

SELECT name, is_top_plan FROM v$rsrc_plan;

SELECT name, active_sessions, queue_length,consumed_cpu_time, cpu_waits, cpu_wait_time FROM v$rsrc_consumer_group;

SELECT se.sid sess_id, co.name consumer_group, se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time FROM v$rsrc_session_info se, v$rsrc_consumer_group co WHERE se.current_consumer_group_id = co.id;

SELECT sequence# seq, name plan_name,to_char(start_time, 'DD-MON-YY HH24:MM') start_time,to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name FROM v$rsrc_plan_history;

select sequence# seq, name, cpu_wait_time, cpu_waits,consumed_cpu_time from V$RSRC_CONS_GROUP_HISTORY;



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

Normal procedure of creating resource manager plan

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'EDW_PLAN'
     ,mgmt_mth                     => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => 'Allocate 75% to default consumer group and 25% to golden gate user under contention
'
     ,sub_plan                      => FALSE);
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,mgmt_p2                      => 75
     ,mgmt_p3                      => 100
     ,switch_for_call              => FALSE
     ,comment                      => 'OTHER_GROUPS Level 3'  );
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'SYS_GROUP'
     ,switch_estimate              => FALSE
     ,mgmt_p1                      => 100
     ,switch_for_call              => FALSE
     ,comment                      => 'SYS Level 1'  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'GG_GROUP'
     ,comment                      => 'Level 2 Group 2');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'GG_GROUP'
     ,switch_estimate              => FALSE
     ,mgmt_p2                      => 25
     ,switch_for_call              => FALSE
     ,comment                      => 'Level 2 Group 2'  );
  sys.dbms_resource_manager.submit_pending_area();
end;



Drop all the plans, plan directives, groups

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.delete_plan_cascade ('EDW_PLAN');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

Note:

You can use toad or enterprise manager to to create, alter any resource plans in easier way if you feel the above code is complicated.

Please also make sure you modify default_maintenance_plan because this will be activated during automatic maintenance windows and if you did not include your allocations it will stick to default ones.

You can limit parallelism, CPU% and other parameters using DBRM


Switching user group

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'SXXXX',
    consumer_group => 'BATCH_GROUP',
    grant_option   => FALSE);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
BEGIN
  SYS.DBMS_RESOURCE_MANAGER.set_initial_consumer_group(
    user           => 'SXXXX',
    consumer_group => 'BATCH_GROUP');
END;
/

find inserts and updates count in oracle database

SELECT sum( stat.executions_delta ) insert_executions
  FROM dba_hist_sqlstat stat
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN TO_DATE('April 25, 2017, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.') AND TO_DATE('April 25, 2017, 11:50 A.M.','Month dd, YYYY, HH:MI A.M.')
   AND txt.command_type = 2;

SELECT sum( stat.executions_delta ) update_executions
  FROM dba_hist_sqlstat stat
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN TO_DATE('April 25, 2017, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.') AND TO_DATE('April 25, 2017, 11:50 A.M.','Month dd, YYYY, HH:MI A.M.')
   AND txt.command_type = 6;

Using SAR in Linux

ls -al /var/log/sa | grep "Apr 22"




Load average at particular time and date

ls -al /var/log/sa | grep "Apr 22"

sar -q -f /var/log/sa/sa22 -s 10:00:01


Swap space used at particular time and date
sar -S -f /var/log/sa/sa22 -s 10:00:01

Memory usage
sar -r -f /var/log/sa/sa22 -s 10:00:01

IO usage
sar -b -f /var/log/sa/sa22 -s 10:00:01

Check swap pages in and out
sar -W -f /var/log/sa/sa22

Changing agent timezone in oem 12c

/opt/app/oracle/agentHome/core/12.1.0.2.0/bin>echo $TZ
US/Pacific
/opt/app/oracle/agentHome/core/12.1.0.2.0/bin>./emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping agent ..... stopped.

/opt/app/oracle/agentHome/core/12.1.0.2.0/bin>./emctl resetTZ agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Updating /opt/app/oracle/agentHome/agent_inst/sysman/config/emd.properties...
Successfully updated /opt/app/oracle/agentHome/agent_inst/sysman/config/emd.properties.
Login as the em repository user and run the  script:
exec mgmt_target.set_agent_tzrgn('xxx:3872','US/Pacific')
and commit the changes
This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('xxx:3872','US/Pacific')
SQL> commit
/opt/app/oracle/agentHome/core/12.1.0.2.0/bin>./emctl start agent

ssh password less connectivity exadata

[celladmin]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/celladmin/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/celladmin/.ssh/id_rsa.
Your public key has been saved in /home/celladmin/.ssh/id_rsa.pub.
The key fingerprint is:
55:xx:yy:zz:44:21:d0:29:dc:d0:cf:06:ac:7b:14:c4 celladmin@cel01.xxx.local
The key's randomart image is:
+--[ RSA 2048]----+
|   .oXo.o.  o.   |
|    o Eo   o     |
|     o =. o      |
|    . ..++       |
|     o .S +      |
|    . .  o       |
|     .           |
|                 |
|                 |
+-----------------+

Once the key is generated verify in the folder to see the new id* files..

Now create a new file with entries of all the hostnames which you want to have ssh password less connectivity with

vi cell_group

cel01
cel02
cel03
cel04

After that run the below.. give the password of the user when prompted of the other cells

dcli -g ./cell_group -k
celladmin@cel01's password:
celladmin@cel02's password:
celladmin@cel04's password:
celladmin@cel03's password:
cel01: ssh key added
cel02: ssh key added
cel03: ssh key added
cel04: ssh key added


Repeat the same process in all the storage cells...

NTP change on  Exadata db nodes

# grep ^server /etc/ntp.conf

server 10.120.0.10 prefer iburst burst minpoll 4 maxpoll 4
server 10.120.2.1 iburst burst minpoll 4 maxpoll 4
server 10.120.2.2 iburst burst minpoll 4 maxpoll 4


Modify the servers in /etc/ntp.conf

Now we can restart the ntp server.

# /etc/init.d/ntpd restart
Shutting down ntpd:                        [ OK ]
ntpd: Synchronizing with timer server:     [ OK ]
Starting ntpd:                             [ OK ]




To force update time


# ntpd -gq
-g requests an update irrespective of the time offset, and -q requests the daemon to quit after updating the date from the ntp server.




************************************************************************************************************************************************
NTP change in storage cells

server 10.120.0.10 prefer iburst burst minpoll 4 maxpoll 4
server 10.120.2.1 iburst burst minpoll 4 maxpoll 4
server 10.120.2.2 iburst burst minpoll 4 maxpoll 4



Log in as the root user.

####Check status

cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

####Make grid disks inactive

cellcli -e alter griddisk all inactive

####Verify if offline

cellcli -e list griddisk

cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

####Shut down services

cellcli -e alter cell shutdown services all

service ocrvottargetd stop

####Change NTP settings as above

/usr/local/bin/ipconf


####Verify NTP settings

grep ^server /etc/ntp.conf

####Startup services

service ocrvottargetd start

cellcli -e alter cell startup services all

####Make grid disks online

cellcli -e alter griddisk all active

####Verify grid disks are online

cellcli -e list griddisk

cellcli -e list griddisk attributes name, asmmodestatus


After this if time is still wrong force update it

service ntpd stop
ntpd -gq
service ntpd start


Once everything is synced and online repeast same steps on other cells..



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

[root@xxx ~]# cellcli -e alter cell shutdown services all

Stopping the RS, CELLSRV, and MS services...
The SHUTDOWN of services was successful.
[root@xxx ~]# service ocrvottargetd stop
ocrvottargetd: unrecognized service
[root@xxx ~]# /usr/local/bin/ipconf
[Info]: ipconf command line: /opt/oracle.cellos/ipconf.pl -nocodes
Logging started to /var/log/cellos/ipconf.log
Interface ib0   is                      Linked.    hca: mlx4_0
Interface ib1   is                      Linked.    hca: mlx4_0
Interface eth0  is                      Linked.    driver/mac: ixgbe/00:10:e0:b4:18:ac
Interface eth1  is .................... Unlinked.  driver/mac: ixgbe/00:10:e0:b4:18:ad
Interface eth2  is .................... Unlinked.  driver/mac: ixgbe/00:10:e0:b4:18:ae
Interface eth3  is .................... Unlinked.  driver/mac: ixgbe/00:10:e0:b4:18:af

Network interfaces
Name  State
ib0   Linked
ib1   Linked
eth0  Linked
eth1  Unlinked
eth2  Unlinked
eth3  Unlinked
[Warning]: Some network interface(s) are disconnected. Check cables and swicthes and retry
Do you want to retry (y/n) [y]: n

The current nameserver(s): 10.120.11.6 10.120.11.7
Do you want to change it (y/n) [n]:
The current timezone: America/Los_Angeles
Do you want to change it (y/n) [n]:
The current NTP server(s): 10.120.0.10 216.239.32.15
Do you want to change it (y/n) [n]: y
Fully qualified hostname or ip address for NTP server. Press enter if none: 10.120.0.10
Continue adding more ntp servers (y/n) [n]: y
Fully qualified hostname or ip address for NTP server. Press enter if none: 10.120.2.1
Continue adding more ntp servers (y/n) [n]: y
Fully qualified hostname or ip address for NTP server. Press enter if none: 10.120.2.2
Continue adding more ntp servers (y/n) [n]:

Network interfaces
Name  State    Status  IP address    Netmask       Gateway     Net type   Hostname
ib0   Linked   UP      192.168.10.26 255.255.252.0             Private    xxx-priv1.hke.local
ib1   Linked   UP      192.168.10.27 255.255.252.0             Private    xxx-priv2.hke.local
eth0  Linked   UP      10.120.16.203 255.255.255.0 10.120.16.1 Management xxx.hke.local
eth1  Unlinked UNCONF
eth2  Unlinked UNCONF
eth3  Unlinked UNCONF
Select interface name to configure or press Enter to continue:

Select canonical hostname from the list below
1: xxx-priv1.hke.local
2: xxx-priv2.hke.local
3: xxx.hke.local
Canonical fully qualified domain name [3]:

Select default gateway interface from the list below
1: eth0
Default gateway interface [1]:

Canonical hostname: xxx.hke.local
Nameservers: 10.120.11.6 10.120.11.7
Timezone: America/Los_Angeles
NTP servers: 10.120.0.10 10.120.2.1 10.120.2.2
Default gateway device: eth0
Network interfaces
Name  State    Status  IP address    Netmask       Gateway     Net type   Hostname
ib0   Linked   UP      192.168.10.26 255.255.252.0             Private    xxx-priv1.hke.local
ib1   Linked   UP      192.168.10.27 255.255.252.0             Private    xxx-priv2.hke.local
eth0  Linked   UP      10.120.16.203 255.255.255.0 10.120.16.1 Management xxx.hke.local
eth1  Unlinked UNCONF
eth2  Unlinked UNCONF
eth3  Unlinked UNCONF
Is this correct (y/n) [y]:

Do you want to configure basic ILOM settings (y/n) [y]:
Loading basic configuration settings from ILOM ...
ILOM Fully qualified hostname [xxx-ilom.hke.local]:
Inet protocol (IPv4,IPv6) [IPv4]:
ILOM IP address [10.120.16.11]:
ILOM Netmask [255.255.255.0]:
ILOM Gateway or none [10.120.16.1]:
ILOM Nameserver (multiple IPs separated by a comma) or none [10.120.11.6]:
ILOM Use NTP Servers (enabled/disabled) [enabled]:
ILOM First NTP server. Fully qualified hostname or ip address or none [10.120.0.10]:
ILOM Second NTP server. Fully qualified hostname or ip address or none [216.239.32.15]: 10.120.2.1
ILOM Vlan id or zero for non-tagged VLAN (0-4079) [0]:

Basic ILOM configuration settings:
Hostname             : xxx-ilom.hke.local
IP Address           : 10.120.16.11
Netmask              : 255.255.255.0
Gateway              : 10.120.16.1
DNS servers          : 10.120.11.6
Use NTP servers      : enabled
First NTP server     : 10.120.0.10
Second NTP server    : 10.120.2.1
Timezone (read-only) : America/Los_Angeles
VLAN id              : 0
Is this correct (y/n) [y]:

[Info]: Run /opt/oracle.cellos/validations/init.d/saveconfig
[Info]: Stopping cellwall service ...
cellwall: Flushing firewall rules:                         [  OK  ]
[Info]: cellwall service stopped
[Info]: Restart ntpd service
Shutting down ntpd:                                        [  OK  ]
Starting ntpd:                                             [  OK  ]
[Info]: Starting cellwall service ...
cellwall: Checking network interfaces:                     [  OK  ]
cellwall: Checking ILOM interface:                         [  OK  ]
cellwall: Flushing firewall rules:                         [  OK  ]
cellwall: Applying firewall rules:                         [  OK  ]
[Info]: cellwall service started
[Info]: Save /etc/sysctl.conf in /etc/sysctl.conf.backupbyExadata
[Info]: Adjust settings for IB interfaces in /etc/sysctl.conf
Re-login using new IP address 10.120.16.203 if you were disconnected after following commands
ip addr show eth0
sleep 4

[Warning]: You modified NTP server.
         Ensure you also update the Infiniband Switch NTP server
         if the same NTP server was also used by the Infiniband switch.







*****************************************************************************************************
NTP change in Infiniband switches


Login to infiniband using ilom-admin user default password is welcome1


check existing ntp configuration

show -d properties /SP/clients/ntp/server/1
show -d properties /SP/clients/ntp/server/2


check ntp status

show -d properties /SP/clock

To check current time

show /SP/clock datetime


Change ntp server address

set /SP/clients/ntp/server/1 address=10.120.0.10

set /SP/clients/ntp/server/2 address=10.120.2.1


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

On solaris

ntpq -p

this lists ntp servers

cd /etc/inet

vi ntp.conf

edit the servers to new servers

Example:

server 10.120.2.1 prefer
server 10.120.2.2

After this save the file.

Now disable and enable ntpd

svcadm disable ntp

svcadm enable ntp

verify time servers

ntpq -p

date

Metric collection error on storage cell Exadata

Run below on all db nodes from agent Home

emctl control agent runCollection xxxcel01.hxx.local:oracle_exadata Response
emctl control agent runCollection xxxcel02.hxx.local:oracle_exadata Response
emctl control agent runCollection xxxcel03.hxx.local:oracle_exadata Response
emctl control agent runCollection xxxcel04.hxx.local:oracle_exadata Response

Failed to execute_exadata_response.pl



Set up SSH connectivity by following Enterprise Manager Exadata Management Getting Started Guide, Chapter 8 Troubleshooting, Section 8.1 Establish SSH Connectivity.



Basically go to /home/oracle/.ssh on db node where agent home exists

see if id_dsa and id_dsa.pub exists

if not

ssh-keygen -t dsa -f id_dsa


The above commands will create those pub files

cat id_dsa.pub

copy the text for that particular node

for example

ssh-dss AAAAB3NzaC1kc3MAAACBAKoOgGt4Ewz9rbgDz6/ChbLJ+yMEmezgLvr/bbOp+UztDtpZiIP5NTKLNyn1kFdZpQCxxssdewdsdsjFyX+Qg6vWaMqnnqtvIgXMMHnv7omiMmg735PsBQYGP8xXRn8eUMv/2+gdInRiA9kysyZVJtMma7IFisZV4oTVSr8qwww0kafBAAAAFQCfDGhPXVVtZ/UEHZdhzgd9g5iJKQAAAIAZ0ZKbgXAzk4w1I5KsUEvhOiDYPh6idFp+J6Fe2lLYHmTCwkAPejSXDh3JWNEXuVU2zq58GaAJ+ta8Kb+ous819sZgRsgHNrcZ6n3aq+XrVgm3wpjdAp5R6dQ5CyDnuOIeNUFYKcQLSHlJ+LDV/UBoi0n6/UXx4+5RdBAe4y/ZWQAsssIB+pQocF+nsQjschcZThyJNNrOqM1Sj24+G/rAe/91jn/crgE3pRanvkWW/YkPE9Kpbqw5U84gH76Hv8jGwHwu55Q6iCsPdwC7iSxA0W7mU9YWuD+YIyBZ38qIarMbno56sGY4yfFRQNVgq/G0yL2h/K4qz3b/u6ihc5EZKAriV3w== oracle@xxx.hxx.local

Open the following location as root on the storage cell server

/home/cellmonitor/.ssh

see if authorized_keys file is present.

vi authorized_keys

Paste the above key from all the db servers and save it.

Once its done wait for some time and metric error will be gone on storage cells

Exadata Flash card or Damaged hard drive replacement steps

Exadata Hard disk or Flash card replacement

To turn off storage cell

first we need to take grid disk offline

Login as celladmin or root and check the status of grid disks using

cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

It should show Online and yes

If it shows dropped then it means there is a bad drive

Replace the drive first

Let it rebalance

To check rebalance status

select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION;


Once rebalance complete

run verify the status again

cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

If everything is online and yes

Then take grid disks offline

cellcli -e alter griddisk all inactive

cellcli -e list griddisk


Now you should see offline

Now you can reboot the server


Note: See how long your maintenance takes place

If it is more than 3.6 hours please make sure you change the settings on asm diskgroup so that it will just sync back everything once it comes online

To check the configured time

select dg.name,a.value from v$asm_diskgroup
dg, v$asm_attribute a where dg.group_number=a.group_number and
a.name='disk_repair_time';


To change the time.. Do this to all the diskgroups

ALTER DISKGROUP DATA SET ATTRIBUTE 'DISK_REPAIR_TIME'='8.5H';



After maintenance

Make all grid disks active

cellcli -e alter griddisk all active

check status

cellcli -e list griddisk attributes name, asmmodestatus


You might see some of the disks are syncing.. Give it some time and re check it should be done fairly soon..


There will not be db downtime if you have asm redundancy set to normal or high..

Linux commands

Add user celladmin with su access

add below using visudo

visudo

celladmin  ALL=(ALL)       NOPASSWD: ALL


Now to  test

login as celladmin user

type

sudo -s


To set password not expire

password unexpire

chage -l celladmin

chage -I -1 -m 0 -M 99999 -E -1 celladmin


add user to sudo with no password

oracle ALL=(root)  NOPASSWD:  /bin/su - 

Javavm component invalid oracle 11g

If jvm component is invalid

Remove it

connect / as sysdba
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
@?/rdbms/admin/catnojav.sql

For better cleanup remove all java classes as well

delete from sys.obj$ o where o.type# = 29; -- classes

delete from sys.obj$ o where o.type# = 30; -- resources


Once everything is gone shutdown

shutdown;

Startup;

@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql


you might encounter error saying exfsys already exists.

Drop exfsys user from db and rerun.

If you get password verification failed.

Goto default profile in your database and alter it verify function to null

alter profile default limit password_verify_function null;

Now run

@?/rdbms/admin/catexf.sql

Once user is created reboot database

Now run

@?/rdbms/admin/utlrp.sql


You might get a warning says ordim java components not properly loaded. Other than that everything else should be valid in dba_registry

Now fix ORDIM

@?/ord/im/admin/initimj.sql

exit session

log back in and run utlrp again

@?/rdbms/admin/utlrp.sql


Now you should see everything as valid in select * from dba_registry;

don't forget to change password verify function back to normal

alter profile default limit PASSWORD_VERIFY_FUNCTION verify_function;


Note: While doing this in the middle you may notice xml,java,spatial,ordim components will be invalid or removed. Dont worry everything will be clean after completion.

Reference:

How to Reload the JVM in 11.2.0.x (Doc ID 1112983.1)

connection management call elapsed time Wait event on oracle DB

We have instance name db1 on the exadata server. This db is being monitored by platinum gateway as we have platinum support for exadata. Because we have done a lot of testing as poc and now its time to make it production i decided to drop and rebuild the database.

After i rebuilt it, In oem i started seeing lot of concurrency waits in brown color all i can get info from oem is library cache lock and most of the sessions having this lock are related to sys user.

So i want to find out whats actually causing the issue and generated a awr report and sure enough i found the same library cache lock here.

I ran ADDM report and it found the same concurrency wait event consuming 40% of time and it said might be related to shared pool size and there are no recommendations.

So i dig deep into AWR report and found a interesting wait event on top

connection management call elapsed time

Statistic Name Time (s) % of DB Time
connection management call elapsed time 19,275.19 99.37

Looks like most of it is because of this. So i decided some application is constantly trying to connect to the db and might be giving wrong password.

In our database if any user inputs incorrect password for 5 times account gets locked except for application and monitoring profile users. So i thought to start with dbsnmp user.

I just thought of checking dba_audit_trail and found orarom user ( which is a readonly account for platinum support) is contsantly trying to login and getting ORA-01017. I got this info from audit trail.

This is because when i created the new db i used a new password for dbsnmp. So i logged into platinum support gateway and changed the password. Immediately that wait event is gone.

Setting Hugepages on Exadata

Get the script for MOS support

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

Start all DB instances on the server

Now run the script from MOS support. It will give you a recommended value. Now configure hugepages for Linux

You need to be root to do this. If this is the first time you are configuring you might need to reboot the server.

Note: Huge pages value should be greater than or equal to all the SGA's combined on the server.

You can check the hugepages config using

$ grep Huge /proc/meminfo

Now run the following as root and enter the recommended value you got from the script.

# vi /etc/sysctl.conf

vm.nr_hugepages=55425

Save it and quit.

Now run

# sysctl -p

Now verify the changes using

$ grep Huge /proc/meminfo

If you dont see any changes you will need to reboot.

While modifying this also verify hard limit and soft limit in the following file

/etc/security/limits.conf

oracle    soft     memlock 237674430
oracle    hard     memlock 237674430

Now set the following in individual db instances

Note: It is a good idea to set value to TRUE because DB will fail to start if you configure only and enough memory is not configured in Huge pages.

We have USE_LARGE_PAGES parameter on the databases. Setting this parameter will improve memory management on the database.

We have 3 values for the above parameter TRUE, FALSE and ONLY

If you set to TRUE, DB will use huge pages and will be started even if hugepages values is less than SGA of database.
If you set to FALSE, DB will not use huge pages
If you set to ONLY, DB will fail to start if huge pages cannot be used for entire SGA of database. 

ORA-24005 Inappropriate utilities used to perform DDL on AQ table

You might get this error while dropping a schema or tablespace or objects it self.

You need to drop using sys user with following syntax

execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'kmaedq_edqstaging.AQ$_EDQ_TOPIC_TAB_G', force => true);

Just mention the tablename in queue_table including schema name.

 Now you can drop the object

Migrating database using expdp and impdp checklist

Expdp and Impdp database migration checklist

On source

Purge dba_recyclebin;

expdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp full=y parallel=8

Use toad to get the ddl for all the roles and users.

Comment out all unnecessary stuff except grants. We will run this grants script at the end of import on Target.

On Target

Create target DB.

Make sure you have same character set.

Create all the tablespaces as source DB.

Create all the roles as source.

Create all the profiles as source.

Make sure you have same temp tablespace name as source.

Once the export is completed. Copy it to the corresponding location and start import. Its better to use nfs storage so that you can skip copying.

Import only the necessary schemas and exclude all the sys related schemas.

Make sure you have a log file assigned to each schema import.

Easier way to do is create script like this on your server and run in nohup so it will run in background.

vi import.sh

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=skwh1

impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x1 table_exists_action=replace logfile=x1_import.log
impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x2 table_exists_action=replace logfile=x2_import.log
impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x3 table_exists_action=replace logfile=x3_import.log


nohup ./import.sh &

Once everything is imported. Verify the logs.

You might see a lot of errors related to grants. Please ignore as we will run the scripts we captured from source.

Try to troubleshoot the errors.

Now run role grants script and user grants script.

If any materialized views failed to create as part of import. Re run now by choosing table_exists_action=skip so you dont need to import whole table again.

After solving all the issues. Go to $ORACLE_HOME/rdbms/admin and run @utlrp.sql

This should compile all invalid objects.

Changing Oracle Database characterset

Here is how to change the DB characterset

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%';

select * from V$nls_parameters where parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

or

select * from nls_database_parameters;

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

ALTER DATABASE CHARACTER SET AL32UTF8;

SHUTDOWN IMMEDIATE;

STARTUP;

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%';

For RAC

You need to stop the database. 

Start in single instance

alter system set cluster_database= false scope=spfile;

shut immediate;

startup restrict;

and follow above steps and change cluster_database=true once done and start db using srvctl normally

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

some times you might need to change ncharset of database.. follow steps below

Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE
STARTUP

It is always not better choice to use 'ALTER DATABASE' command to change the character set.

Make sure to take a full backup before doing this