Oracle Hang Analysis

sqlplus -prelim /nolog 

 oradebug setmypid
 oradebug unlimit
 oradebug hanganalyze 3
 oradebug dump ashdumpseconds 30
 oradebug systemstate 266
 oradebug tracefile_name

oradebug hanganalyze 12 
oradebug dump systemstate 10 
oradebug tracefile_name

Port Checking on AIX

netstat -Aan | grep LISTEN | grep 5000
rmsock f1000e000016a3b8 tcpcb
ps -ef|grep 5439696

Fast Start Failover

Setup the Data Guardbroker  configuration
dgmgrl
DGMGRL> connect sys/oracle
create configuration drs as    primary database is rmanp    connect identifier is rmanp;
add database rmans as   connect identifier is rmans   maintained as physical;
enable configuration;

DGMGRL> show configuration;

Configuration - drs

  Protection Mode: MaxAvailability
  Databases:
    rmanp - Primary database
    rmans - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS


Setup Fast failover:

Enable flashback on both databases

alter system set db_recovery_file_dest_size=2G;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata';

set fast failover target

DGMGRL> EDIT DATABASE rmanp SET PROPERTY FastStartFailoverTarget = rmans;
DGMGRL> EDIT DATABASE rmans SET PROPERTY FastStartFailoverTarget = rmanp;

set log mode and protection mode
DGMGRL> EDIT DATABASE 'rmans' SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE 'rmanp' SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 10;

FastStartFailoverThreshold Used to specify the number of seconds to delay failover after the detection of a primary database failure. This parameter defaults to 30 seconds.
FastStartFailoverPmyShutdown In its default setting of true, this parameter causes the primary database to shut down when FastStartFailoverThreshold has been reached for a particular database. This parameter setting is ignored in the case of a user-configurable condition failover.
FastStartFailoverLagLimit Allows for definition of the number of seconds the standby database is able to fall behind. When this threshold is exceeded, automatic failover will not be allowed.
FastStartFailoverAutoReinstate When set to its default of true, this parameter enables the automatic reinstatement of a failed primary database as a standby. Automatic reinstatement is not possible for user-configurable failover conditions regardless of this parameter setting.

DGMGRL> edit database 'cosp' set property  ' FastStartFailoverLagLimit '='60';
DGMGRL> ENABLE FAST_START FAILOVER CONDITION 'Inaccessible Logfile';

enable fash failover
DGMGRL> enable fast_start failover

DGMGRL>  show fast_start failover

Fast-Start Failover: ENABLED

  Threshold:        10 seconds
  Target:           rmans
  Observer:         Yusuf-PC
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile           YES
    Stuck Archiver                 YES
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

DGMGRL> show configuration;

Configuration - drs

  Protection Mode: MaxAvailability
  Databases:
    rmanp - Primary database
    rmans - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Oracle Database Preinstallation check

We can use RDA tool to Health Check/Validation for "Oracle Database Preinstallation "

[oracle@cloud1 rda]$ perl rda.pl -T hcve
Processing HCVE tests ...
Available Pre-Installation Rule Sets:
   1.  Oracle Database 10g R1 (10.1.0) Preinstall (Linux)
   2.  Oracle Database 10g R2 (10.2.0) Preinstall (Linux)
   3.  Oracle Database 11g R1 (11.1) Preinstall (Linux)
   4.  Oracle Database 11g R2 (11.2.0) Preinstall (Linux)
   5.  Oracle Database 12c R1 (12.1.0) Preinstallation (Linux)
   6.  Oracle Identity and Access Management PreInstall Check: Oracle Identity
       and Access Management 11g Release 2 (11.1.2) Linux
   7.  Oracle JDeveloper PreInstall Check: Oracle JDeveloper 11g Release 2
       (11.1.2.4) Linux
   8.  Oracle JDeveloper PreInstall Check: Oracle JDeveloper 12c (12.1.3)
       Linux
   9.  OAS PreInstall Check: Application Server 10g R2 (10.1.2) Linux
  10.  OAS PreInstall Check: Application Server 10g R3 (10.1.3) Linux
  11.  OFM PreInstall Check: Oracle Fusion Middleware 11g R1 (11.1.1) Linux
  12.  OFM PreInstall Check: Oracle Fusion Middleware 12c (12.1.3) Linux
  13.  Oracle Forms and Reports PreInstall Check: Oracle Forms and Reports 11g
       Release 2 (11.1.2) Linux
  14.  Portal PreInstall Check: Oracle Portal Generic
  15.  IDM PreInstall Check: Identity Management 10g (10.1.4) Linux
  16.  BIEE PreInstall Check: Business Intelligence Enterprise Edition 11g
       (11.1.1) Generic
  17.  EPM PreInstall Check: Enterprise Performance Management Server (11.1.2)
       Generic
  18.  Oracle Enterprise Manager Cloud Control PreInstall Check: Oracle
       Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4) Linux
  19.  Oracle E-Business Suite Release 11i (11.5.10) Preinstall (Linux x86 and
       x86_64)
  20.  Oracle E-Business Suite Release 12 (12.1.1) Preinstall (Linux x86 and
       x86_64)
  21.  Oracle E-Business Suite Release 12 (12.2.0) Preinstall (Linux x86_64)

Available Post-Installation Rule Sets:
  22.  RAC 10G DB and OS Best Practices (Linux)
  23.  Data Guard Postinstall (Generic)
  24.  WLS PostInstall Check: WebLogic Server 11g (10.3.x) Generic
  25.  WLS PostInstall Check: WebLogic Server 12c (12.x) Generic
  26.  Portal PostInstall Check: Oracle Portal Generic
  27.  OC4J PostInstall Check: Oracle Containers for J2EE 10g (10.1.x) Generic
  28.  SOA PostInstall Check: Service-Oriented Architecture 11g and Later
       Generic
  29.  OSB PostInstall Check: Service Bus 11g and Later Generic
  30.  Oracle Forms 11g Post Installation (Generic)
  31.  Network Charging and Control Database Post Installation (Generic)

Enter the HCVE rule set number or 0 to cancel the test
Press Return to accept the default (0)
> 5

Performing HCVE checks ...
Enter value for < Planned ORACLE_HOME location >
Press Return to accept the default (/u01/app/oracle/product/12.1.0)
>

Enter value for < JDK Home >
> /u01/app/oracle/product/12.1.0/jdk


Test "Oracle Database 12c R1 (12.1.0) Preinstallation (Linux)" executed at 09-Jun-2015 18:21:56

Test Results
~~~~~~~~~~~~

ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00100 OS Certified?        PASSED  Adequate
A01020 User in /etc/passwd? PASSED  userOK
A01040 Group in /etc/group? PASSED  GroupOK
A01050 Enter ORACLE_HOME    RECORD  /u01/app/oracle/product/12.1.0
A01060 ORACLE_HOME Valid?   PASSED  OHexists
A01070 O_H Permissions OK?  PASSED  CorrectPerms
A01210 Enter JDK Home       RECORD  /u01/app/oracle/product/12.1.0/jdk
A01220 JDK Version          PASSED  Adequate
A01410 oraInventory Permiss PASSED  oraInventoryOK
A01420 Other OUI Up?        PASSED  NoOtherOUI
A01430 Got Software Tools?  PASSED  ld_nm_ar_make_found
A01440 Other O_Hs in PATH?  FAILED  OratabEntryInPath
A02010 Umask Set to 022?    PASSED  UmaskOK
A02030 Limits Processes     PASSED  Adequate
A02040 Limits Stacksize     PASSED  Adequate
A02050 Limits Descriptors   PASSED  Adequate
A02100 LDLIBRARYPATH Unset? FAILED  IsSet
A02170 JAVA_HOME Unset?     PASSED  UnSet
A03100 RAM (in MB)          PASSED  2952
A02210 Kernel Parameters OK PASSED  KernelOK
A02300 Tainted Kernel?      PASSED  NotVerifiable
A03010 Temp Adequate?       PASSED  TempSpaceOK
A03020 Disk Space OK?       FAILED  NoSpace
A03050 Swap (in MB)         RECORD  4255
A03100 RAM (in MB)          PASSED  2952
A03150 SwapToRam OK?        PASSED  SwapToRamOK
A03500 Network              PASSED  Connected
A03510 IP Address           RECORD  10.0.3.15
A03530 Domain Name          RECORD  domain.com
A03540 /etc/hosts Format    PASSED  Adequate IPv4 entry
A03550 DNS Lookup           FAILED  nslookup host.domain
A03600 ip_local_port_range  PASSED  RangeOK
A04301 OL5 Server RPMs OK?  SKIPPED NotOL5
A04302 OL5 32-bit Client RP SKIPPED NotOL5
A04303 OL6 Server RPMs OK?  FAILED  [unixODBC(x86_64)] not installed [uni...
A04304 OL6 32-bit Client RP FAILED  [compat-libstdc++-33(i686)] not insta...
A04305 OL7 Server RPMs OK?  SKIPPED NotOL7
A04311 RHEL5 Server RPMs OK SKIPPED NotRedHat
A04312 RHEL5 32-bit Client  SKIPPED NotRedHat
A04313 RHEL6 Server RPMs OK SKIPPED NotRedHat
A04314 RHEL6 32-bit Client  SKIPPED NotRedHat
A04315 RHEL7 Server RPMs OK SKIPPED NotRedHat
A04321 SLES11 Server RPMs O SKIPPED NotSuSE
A04322 SLES11 32-bit Client SKIPPED NotSuSE
Result file: output/collect/DB_HCVE_A_DB12R1_lin_res.htm

Datagurad Scripts

Check Last Applied Log:

select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';

Check Applied Rate:

select to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time,
item, round(sofar/1024,2) "MB/Sec"
from v$recovery_progress
where (item='Active Apply Rate' or item='Average Apply Rate');

How to set up DNS for scan ip's

[root@vm32 ~]# nslookup abc-scan
;; connection timed out; no servers could be reached



1. check dnsmasq installed or not
[root@vm32 ~]# rpm -qa|grep -i dnsmasq
dnsmasq-2.48-4.el6.x86_64

2. create new file /etc/racdns and add below entry for  dnsmasq

[root@vm32 ~]# cat /etc/racdns
192.168.0.37   abc-scan.domain.com        abc-scan
192.168.0.38   abc-scan.domain.com        abc-scan
192.168.0.39   abc-scan.domain.com        abc-scan


cat /etc/dnsmasq.conf | grep addn-hosts
addn-hosts=/etc/racdns


3. Start dnsmasq

service dnsmasq start
chkconfig dnsmasq on


4.

# Generated by NetworkManager
search domain.com


# No nameservers found; try putting DNS servers into your
# ifcfg files in /etc/sysconfig/network-scripts like so:
#
# DNS1=xxx.xxx.xxx.xxx
# DNS2=xxx.xxx.xxx.xxx
# DOMAIN=lab.foo.com bar.foo.com
nameserver 127.0.0.1
nameserver 192.168.1.1


5.chattr +i /etc/resolv.conf

Verify:

[root@vm32 ~]# nslookup abc-scan
Server:         127.0.0.1
Address:        127.0.0.1#53

Name:   abc-scan.domain.com
Address: 192.168.0.39
Name:   abc-scan.domain.com
Address: 192.168.0.37
Name:   abc-scan.domain.com
Address: 192.168.0.38
Name:   abc-scan.domain.com
Address: 192.168.0.39
Name:   abc-scan.domain.com
Address: 192.168.0.38
Name:   abc-scan.domain.com
Address: 192.168.0.37

How to recover corrupted OCR & Voting Disk

As root user
crsctl stop crs -f => all nodes
crsctl start crs -excl -nocrs => from one node
format OCR & Voting disk
dd if=/dev/zero of=/dev/raw/raw1 count=10004 bs=1024

As grid user

sqlplus "/as sysasm"

create diskgroup ocr_vote external redundancy disk '/dev/raw/raw1' attribute 'COMPATIBLE.ASM' = '11.2';

root user
Restore OCR from the backup

$GRID_HOME/bin/ocrconfig -restore /u01/app/11.2.0.4/cdata/abc-scan/backup00.ocr

Recreate the voting Disk

crsctl replace votedisk +ocr_vote


root user

crsctl stop crs -f
crsctl start crs

Manually Configure Grid Infrastructure on standalone server

Error:

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service

Solution:

root@vm34:/u01/app/grid/crs/install =>perl roothas.pl
Using configuration parameter file: ./crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'system'..
Operation successful.
CRS-4664: Node vm34 successfully pinned.
Adding Clusterware entries to inittab
vm34 2015/05/25 01:30:30 /u01/app/grid/cdata/vm34/backup_20150525_013030.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server


[oracle@vm34]:../bin>crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'vm34'
CRS-2672: Attempting to start 'ora.diskmon' on 'vm34'
CRS-2676: Start of 'ora.diskmon' on 'vm34' succeeded
CRS-2676: Start of 'ora.cssd' on 'vm34' succeeded

Now we can start the asm instance

sqlplus "/as sysasm"

startup

DDL : initial_setup: :ORA-01031: insufficient privileges while running ddl_setup.sql

Error:

DDL : Creating DDL sequence: :ORA-01031: insufficient privileges
DDL : initial_setup: :ORA-01031: insufficient privileges
DDL : STARTING DDL REPLICATION SETUP
DDL : Instantiating new DDL replication package
DDL : Initial setup starting
DDL : Creating DDL sequence: :ORA-01031: insufficient privileges
DDL : initial_setup: :ORA-01031: insufficient privileges
DDL : STARTING DDL REPLICATION SETUP
DDL : Instantiating new DDL replication package
DDL : Initial setup starting
DDL : Creating DDL sequence: :ORA-01031: insufficient privileges
DDL : initial_setup: :ORA-01031: insufficient privileges
DDL : STARTING DDL REPLICATION SETUP
DDL : Instantiating new DDL replication package
DDL : Initial setup starting
DDL : Creating DDL sequence: :ORA-01031: insufficient privileges
DDL : initial_setup: :ORA-01031: insufficient privileges
DDL : STARTING DDL REPLICATION SETUP
DDL : Instantiating new DDL replication package

Solution:

1. Create new table space for Golden Gate schema.

2.Golden Gate schema should have below privilleges

grant connect, resource to ggs_owner;

grant select any dictionary, select any table to ggs_owner;

grant create table to ggs_owner;

grant flashback any table to ggs_owner;

grant execute on dbms_flashback to ggs_owner;

grant execute on utl_file to ggs_owner;

grant create any table to ggs_owner;

grant insert any table to ggs_owner;

grant update any table to ggs_owner;

grant delete any table to ggs_owner;

grant drop any table to ggs_owner;

grant create any table to ggs_owner;

grant create any view to ggs_owner;

grant create any procedure to ggs_owner;

grant create any sequence to ggs_owner;

grant create any index to ggs_owner;

grant create any trigger to ggs_owner;

grant create any view to ggs_owner;

get filesystem details from V$datafile

select distinct file_name from (
select distinct substr(name,1,instr(name,'/',1,3)-1)
file_name from v$datafile
union all
select distinct substr(name,1,instr(name,'/',1,3)-1)
file_name from v$controlfile
union all
select distinct substr(member,1,instr(member,'/',1,3)-1)
file_name from v$logfile
union all
select distinct substr(name,1,instr(name,'/',1,3)-1)
file_name from v$tempfile
);