RAW volume verification

select 'dbv file='


name

' blocksize='

block_size

' end='

(bytes/block_size)

' logfile='

substr(name, instr(name, '/', -1, 1) +1)

'.'

file#

'.log' from v$datafile /

HOw to find out list of Hidden parameters in oracle.


SET LINESIZE 145


SET PAGESIZE 9999

SET VERIFY off

COLUMN ksppinm FORMAT A42 HEAD 'Parameter Name'

COLUMN ksppstvl FORMAT A39 HEAD 'Value'

COLUMN ksppdesc FORMAT A60 HEAD 'Description' TRUNC

SELECT

ksppinm

, ksppstvl

, ksppdesc

FROM

x$ksppi x

, x$ksppcv y

WHERE

x.indx = y.indx

AND TRANSLATE(ksppinm,'_','#') like '#%'

/

How to find out how many parallel slaves available in database. 



 select STATUS , count(*) from v$PX_PROCESS group by status ;

STATUS      COUNT(*)
--------- ----------
IN USE            87
AVAILABLE          1

SQL> 

SYSAUX tablespace usage details.

v$sysaux_occupants view provides sysaux tablespace usage and schema details.

select  SCHEMA_NAME ,OCCUPANT_NAME ,SPACE_USAGE_KBYTES from v$sysaux_occupants

ORA-15063: ASM discovered an insufficient number of disks

While mounting diskgroups in asm DBA face issue where is there disks are not mounted properly in all nodes. System admin team need to rediscover disks to fix issue.

srvctl start diskgroup -g DG_TESTPARAM_DT01 -n test-poc-213
srvctl start diskgroup -g DG_TESTPARAM_DT02 -n test-poc-213
srvctl start diskgroup -g DG_TESTPARAM_DT03 -n test-poc-213
srvctl start diskgroup -g DG_TESTPARAM_RC -n test-poc-213



SQL> alter diskgroup DG_TESTPARAM_DT01 mount;
alter diskgroup DG_TESTPARAM_DT01 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DG_TESTPARAM_DT01" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"DG_TESTPARAM_DT01"


/opt/oracle > srvctl start diskgroup -g DG_TESTPARAM_DT03 -n test-poc-213PRCR-1013 : Failed to start resource ora.DG_TESTPARAM_DT03.dg
PRCR-1064 : Failed to start resource ora.DG_TESTPARAM_DT03.dg on node test-poc-213
CRS-5017: The resource action "ora.DG_TESTPARAM_DT03.dg start" encountered the following error:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DG_TESTPARAM_DT03" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DG_TESTPARAM_DT03"

Solution
=========
/usr/sbin > ./oracleasm listdisks
/usr/sbin > ./oracleasm scandisks


SA team need to mount all disks properly in all nodes.

Auto-tuning: Shutting down background process GTX3

In RAC Database DBA may found Auto-tuning: Shutting down background process GTXn in alert log file, which is non corruptive.

GTXn

Global Transaction Process
Provides transparent support for XA global transactions in an Oracle RAC environment

These processes help maintain the global information about XA global transactions throughout the cluster. Also, the processes help perform two-phase commit for global transactions anywhere in the cluster so that an Oracle RAC database behaves as a single system to the externally coordinated distributed transactions.The GLOBAL_TXN_PROCESSES initialization parameter specifies the number of GTXn processes, where n is 0-9 or a-j. The database automatically tunes the number of these processes based on the workload of XA global transactions. You can disable these processes by setting the parameter to 0. If you try to run XA global transactions with these process disabled, an error is returned.

PRVF-4567 : Failed to check existence of VIP node application on nodes

In case of RAC database installation in 11g (11.2.0.2) vip service in case not created , during post installation steps using clufy if vip nodes not created

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost

## RAC cluster nodes
168.40.171.120 test-poc-100.cisco.com test-poc-100
168.40.171.127 test-poc-101.cisco.com test-poc-101
168.40.171.165 test-poc-102.cisco.com test-poc-102

## VIPS
168.40.171.124 test-poc-100-vip.cisco.com test-poc-100-vip
168.40.171.154 test-poc-101-vip.cisco.com test-poc-101-vip
168.40.171.166 test-poc-102-vip.cisco.com test-poc-102-vip

## RAC IPS
10.101.176.116 test-poc-100-rac.cisco.com test-poc-100-rac
10.101.176.117 test-poc-101-rac.cisco.com test-poc-101-rac
10.101.176.118 test-poc-102-rac.cisco.com test-poc-102-rac
##SCAN IPS
#168.40.171.171 testpoc-scan.cisco.com testpoc-scan
#168.40.171.172 testpoc-scan.cisco.com testpoc-scan



ERROR found
===============

Checking existence of VIP node application (required)
Node Name Required Running? Comment
------------ ------------------------ ------------------------ ----------
test-poc-102 yes no does not exist
test-poc-101 yes no does not exist
test-poc-100 yes yes passed
PRVF-4567 : Failed to check existence of VIP node application on nodes "test-poc-102,test-poc-101"


Steps to create vip nodes

srvctl add vip -n node7 -A 192.168.16.17/255.255.255.0 -k 2 à just a sample command. Pls replace strings appropriately


Usage: srvctl add vip -n -k -A //[if1[if2...]] [-v]!
-n Node name
-A //[if1[if2...]] VIP address spec for node applications
-k VIP network number (default number is 1)


srvctl add vip -n test-poc-101 -A 168.40.171.154/255.255.255.0 -k 1
srvctl add vip -n test-poc-102 -A 168.40.171.166/255.255.255.0 -k 1

srvctl start vip -n test-poc-101
srvctl start vip -n test-poc-102

DB-link creation or Db-link copy from 9i to 10g and 10g to 11g.

In 9i version sys.link$ password column stored with password. During migration activity from 9i to 10g/11g DBA can recreate db-link using below script.
Target Database db-link owner password change to manager99

Take backup of in target database and source database password backup.

set linesize 200
set pagesize 30000
spool password_backup.sql
select 'ALTER USER 'USERNAME' IDENTIFIED BY VALUES '''PASSWORD''' PROFILE 'PROFILE';' from dba_users ORDER BY USERNAME;
spool off


Below script will help only when database is migrated from 9i to 10g or 11g and db-links are not copied or missing.

In Source Database.


Extract db-link script by executing in source and o/p of below script execute in target.

Set linesize 200
Set pagesize 30000
Spool db_link_creation.sql
SELECT 'connect 'u.name'/''manager99' ,
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
L.NAMEchr(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#
order by l.OWNER#
/
Spool off

Example

connect Testdba/manger99
create database link test123.WORLD connect to test identified by values 'test234' using 'TEMP05';

Db-link copy from 10g to 11g database.
Since oracle password column is null. In this senario DBA still copy db_links using encrypted password. In 10g/11g sys.link$ is stored with encrypted password at location PASSWORDX.

Take backup of in target database and source database password backup.

set linesize 200
set pagesize 30000
spool password_backup.sql
select 'ALTER USER 'USERNAME' IDENTIFIED BY VALUES '''PASSWORD''' PROFILE 'PROFILE';' from dba_users ORDER BY USERNAME;
spool off


chage passwords of all schemas at target database to manager99.

set linesize 200
set pagesize 30000
spool password_change.sql
select 'ALTER USER 'USERNAME' IDENTIFIED BY VALUES manager99'';' from dba_users ORDER BY USERNAME;
spool off


SQL> desc sys.link$
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
CTIME NOT NULL DATE
HOST VARCHAR2(2000)
USERID VARCHAR2(30)
PASSWORD VARCHAR2(30)
FLAG NUMBER
AUTHUSR VARCHAR2(30)
AUTHPWD VARCHAR2(30)
PASSWORDX RAW(128)
AUTHPWDX RAW(128)



Set linesize 200
Set pagesize 30000
Spool db_link_creation.sql
SELECT 'connect 'u.name'/''manager99' ,
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
L.NAMEchr(10)
'connect to ' L.USERID ' identified by values '''
L.PASSWORDX''' using ''' L.host ''''
chr(10)';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER#
order by l.OWNER#
/
spool off



Example

connect testdba/manager99
create database link test123.world connect to test1 identified by values '05A2E8411F97EC0539E47A76AE2EC191609DDA29EBBA5CDC0E' using 'TEST' ;

DBV utility usage for RAW/ASM/NORMAL Datafiles.

DBV oracle utility used to find out any block corruption at datafile level.

In case any corruption errors found out in alert log file , DBA need to use dbv utlity to find out corruption level issues. Dba can run DBV utlity while database is up and running.

In case of Database located in cooked file system


Below script will provide dbv o/p file for all datafiles located

sqlplus /nolog
connect / as sysdba
set feedback off
set head off
set echo off
set linesize 200
set pagesize 2500
spool /tmp/dbvchk.sh
select 'dbv file=' name ' blocksize=' block_size ' logfile='
substr(name, instr(name, '/', -1, 1) +1)
'.' file# '.log' from v$datafile
/
spool off

Example

dbv file=/u01/oradata/test011.dbf blocksize=2048 logfile=test01_dbv.log feedback=100

RAW Devices
should use the END parameter to avoid running off the end of the Oracle file space.
eg: "dbv FILE=/dev/rdsk/r1.dbf END="


sqlplus /nolog
connect / as sysdba
set feedback off
set head off
set echo off
set linesize 200
set pagesize 2500
spool /tmp/dbvchk.sh
select 'dbv file=' name ' blocksize=' block_size ' end=' (bytes/block_size) ' logfile=' substr(name, instr(name, '/', -1, 1) +1) '.' file# '.log' from v$datafile
/
spool off


Example

dbv file=/dev/mappers/rv4000p1 blocksize=2048 END=5120 log=rv4000p1.log



ASM files

If ASM storage being used, we need to specify USERID to get authenticated on ASM Instance.You can use following query if you are running in ASM storage.

sqlplus /nolog
connect / as sysdba
set feedback off
set head off
set echo off
set linesize 200
set pagesize 2500
select 'dbv file=' name ' blocksize=' block_size ' USERID=sys/&SYS_PASSWORD logfile='
substr(name, instr(name, '/', -1, 1) +1) '.' file# '.log' from v$datafile
/
spool off


Example

dbv file=+DG_TS1TEST_DT03/oradata/undo_ts3_50.dbf blocksize=8192 USERID=sys/xxxxxx logfile=undo_ts3_50.dbf.7416.log