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