Friday, 23 May 2014


Check FK,PK and CK in Oracle for the specified table


/* foreign keys */

SELECT a.table_name,
       a.column_name,
       a.constraint_name,
       c.owner
  FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C
 WHERE     A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
       AND a.table_name = 'xxxxx'
       AND C.CONSTRAINT_TYPE = 'R';

/* primary keys */

SELECT a.table_name,
       a.column_name,
       a.constraint_name,
       c.owner
  FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C
 WHERE     A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
       AND a.table_name = 'xxxxx'
       AND C.CONSTRAINT_TYPE = 'P';


/* check constraint */

SELECT a.table_name,
       a.column_name,
       a.constraint_name,
       c.owner
  FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C
 WHERE     A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
       AND a.table_name = 'xxxxx'
       AND C.CONSTRAINT_TYPE = 'C';

List of Invalid Objects in Oracle


set heading off
set feedback off
set echo off
set linesize 999

SELECT  OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM    DBA_OBJECTS
WHERE   STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

set heading on
set feedback on
set echo on

INCREASING REDO LOG SIZE


Step 1 : Check the Status of Redo Logfile
SQL>  select group#,sequence#,bytes,archived,status from v$log;

GROUP#    STATUS
------------    ------------
             1     INACTIVE
             2     ACTIVE
             3     CURRENT

Here we see that the Group# 3 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:
Step  2 :  Forcing a Checkpoint  :

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

Step 3 : checking the  status of the log

GROUP#    STATUS
------------    ------------
             1     INACTIVE
             2     INACTIVE
             3     CURRENT


Since the status of group 1,2  are inactive .so we will drop the group 1 and group 2 redo log file.
Step 4 : Check the path for the all the redo logs

 SQL>  SELECT * FROM V$LOGFILE;

Step 5 : Drop Redo Log File  group 1

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Check it has dropped
SQL>  select group#,sequence#,bytes,archived,status from v$log;

Step   : Create new redo log file for group 1

If we don't delete the old redo logfile by OS command when creating the log file with same name then face the below error . Therefore to solve it delete the file by using OS command

SQL>  alter database add logfile group 1 '/redo/PEMSAPMS/redo01.log' size 200m;


Step 7 : Drop Redo Log File  group 2
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;


Step   : Create new redo log file for group 2


sql> alter database add logfile group 2 '/redo/PEMSAPMS/redo02.log' size 200m;



Step 9: Make the redo logs created active

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;

GROUP#    STATUS
------------    ------------
             1     ACTIVE
             2     INACTIVE
             3     CURRENT


Step 10 : Drop Redo Log File  group 3

SQL> alter system checkpoint global;

System altered.

GROUP#    STATUS
------------    ------------
             1     INACTIVE
             2     CURRENT
             3     INACTIVE


Step 11 : Drop Redo Log File  group 3


 SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Step  12  : Create new redo log file for group 3

 SQL > alter database add logfile group 3 '/redo/PEMSAPMS/redo03.log' size 200m;

Step 13 Make the redo logs created active

alter system switch logfile ;




Change REDO log location


1. sql>select * from v$logfile;

Note the location of the redo logs and copy it safely

2. sql > shutdown immediate

3. mv the redo logs to the new location

4. startup mount

5. change the location using below alter script

SQLPLUS >

ALTER DATABASE RENAME FILE '/backup/redologs/PEMSAPMS/redo01.log', '/backup/redologs/PEMSAPMS/redo02.log' ,'/backup/redologs/PEMSAPMS/redo03.log' TO '/redo/PEMSAPMS/redo01.log',

'/redo/PEMSAPMS/redo02.log','/redo/PEMSAPMS/redo03.log';

6. Alter database open;

Solved when apply tag and transport tag increases


If apply tag and transport tag increases then create standby redo logs in standby db

DGMGRL> show database verbose "PEMSAPMSstdb";

Database - PEMSAPMSstdb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   16 hours 48 minutes 27 seconds
  Apply Lag:       16 hours 48 minutes 27 seconds
  Real Time Query: ON
  Instance(s):
    PEMSAPMSstdb


cancel the recovery first before adding

SQL> alter database recover managed standby database cancel ;

SQL> alter database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_3_9qqbp03n_.log' size 209715200;                            
                                                                               
SQL> alter database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_2_9qqbot0k_.log' size 209715200;                            
                                                                               
SQL> alter database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_1_9qqbomkn_.log' size 209715200;

DATAGURAD SETUP IN ORACLE11G

DATAGURAD SETUP IN ORACLE11G

In Primary

1)       Change the spfile in primary database and restart

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTPMS,TESTPMSstdb)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="TESTPMSstdb"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name="TESTPMS_STBY" net_timeout=30','valid_for=(all_logfiles,primary_role)'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;


ALTER SYSTEM SET FAL_SERVER='TESTPMSSTdb';
ALTER SYSTEM SET FAL_CLIENT='TESTPMS';




2)       Genrate pfile in Primary DB
   create pfile='/app/oracle/admin/TESTPMS/pfile/initTESTPMS.ora' from spfile;

3)        Run backup script in primary

4)        copied the control files, backup, onlinelogs and archive logs, pfile

scp -r -i /root/TESTcloud.pem -P 22 /backup/flash_recovery_area/ root@10.0.1.39:/backup/flash_recovery_area/TESTPMS/

scp -r -i /root/TESTcloud.pem -P 22 /backup/fast_recovery_area/TESTPMS/control02.ctl root@10.0.1.39:/backup/fast_recovery_area/TESTPMS/control02.ctl

scp -r -i /root/TESTcloud.pem -P 22 /data/controlfile/TESTPMS/control01.ctl root@10.0.1.39:/data/controlfile/TESTPMS/control01.ctl

scp -r -i /root/TESTcloud.pem -P 22 /backup/flash_recovery_area/TESTPMS/autobackup/ root@10.0.1.39:/backup/flash_recovery_area/TESTPMS/autobackup/

scp -i /root/TESTcloud.pem -P 22 /app/oracle/admin/TESTPMS/pfile/initTESTPMS.ora root@10.0.1.39:/app/oracle/admin/TESTPMS/pfile/initTESTPMS.ora



5)        Enable DG broker

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

6)       Remote login password file.
 orapwd file=orapwTESTPMSstdb password=STANDARD IGNORECASE=Y

7)        Configure listener

#####################
#tnsnames.ora########
#####################

# tnsnames.ora Network Configuration File: /app/oracle/11.3.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TESTPMSprdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = devprimary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTPMS.psc.com)
    )
  )

TESTPMSstdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = devstandby)(PORT = 1521))
    (CONNECT_DATA = (UR=A)
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTPMSstdb.psc.com)
    )
  )


#####################
#listener.ora########
#####################

# listener.ora Network Configuration File: /app/oracle/11.3.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

ADR_BASE_LISTENER_TESTPMS = /app/oracle

LISTENER_TESTPMS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = devprimary)(PORT = 1521))
     )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTPMS)
      (ORACLE_HOME = /app/oracle/11.3.0)
      (SID_NAME = TESTPMS)
    )
  )

8)        Create DG config

CREATE CONFIGURATION 'TESTR' AS PRIMARY DATABASE IS 'TESTPMS' CONNECT IDENTIFIER IS TESTPMSprdb

ADD DATABASE 'TESTPMSstdb' AS CONNECT IDENTIFIER IS TESTPMSstdb MAINTAINED AS PHYSICAL;

enable configuration;

9)       Connect to RMAN  ( after step 7 in Standby)

 rman target sys/STANDARD@TESTPMSprdb auxiliary sys/STANDARD@TESTPMSstdb

10)    Duplicate DB connect to auxiliary db from primary   ( after step 7 in Standby)

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;


In Standby

1)        Modify the bash_profile ( ORACLE_HOSTNAME,ORACLE_UNQNAME,ORACLE_SID). Changed the ORACLE_SID FROM TESTpms to TESTpmsstdb in bash_profile

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=devstandby; export ORACLE_HOSTNAME
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_UNQNAME=TESTPMSstdb; export ORACLE_UNQNAME
ORACLE_HOME=$ORACLE_BASE/11.3.0; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
export ORACLE_SID=TESTPMSstdb
PATH=$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$ORACLE_HOME/OPatch; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

2)        Created a template form the old DB in standby DB



3)       Create new database from the template


4)       Changed the below in the pfile copied from primary ( after step 4 in primary)

*.db_unique_name='TESTPMSstdb'
*.fal_client='TESTPMSSTDB'
*.fal_server='TESTPMS'
*.log_archive_dest_2='SERVICE=TESTPMSprdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTPMS'

5)       Rename the file from initTESTPMS.ora to initTESTPMSstdb.ora

6)       Create spfile from the modified pfile from the above steps

   create spfile from pfile='/app/oracle/admin/TESTPMS/pfile/initTESTPMSstdb.ora';


7)        Configure listener

#####################
#tnsnames.ora########
#####################

# tnsnames.ora Network Configuration File: /app/oracle/11.3.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TESTPMSprdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = devprimary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTPMS.psc.com)
    )
  )

TESTPMSstdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = devstandby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTPMSstdb)
    )
  )


#####################
#listener.ora########
#####################

# listener.ora Network Configuration File: /app/oracle/11.3.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

ADR_BASE_LISTENER_TESTPMS = /app/oracle

LISTENER_TESTPMSSTDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = devstandby)(PORT = 1521))
     )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTPMS)
      (ORACLE_HOME = /app/oracle/11.3.0)
      (SID_NAME = TESTPMSstdb)
    )
  )

8)       Duplicate DB from standby ( This is optional)

rman target sys/STANDARD
startup mount
restore database

9)       Enable DG broker
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

10)   Remote login password file.

orapwd file=orapwTESTPMSstdb password=STANDARD IGNORECASE=Y


11)   Once duplicate DB recovery completed after the step in 10 (Primary)

shutdown immediate
startup
alter database recover managed standby database disconnect from session;


13.  Any error from the above step, then execute the below commands
     alter database recover managed standby database CANCEL;
     alter database recover managed standby database disconnect from session;


14. Check DB mode
select OPEN_MODE, DATABASE_ROLE from v$database;


OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY


15. Add standby redo log files
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;;

alter database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_3_9qqbp03n_.log' size 209715200;                            
                                                                               
alter database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_2_9qqbot0k_.log' size 209715200;                            
                                                                               
alter database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_1_9qqbomkn_.log' size 209715200;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

16. If redo online log location is in the flash_recovery location then change the location of the redo online logs to match the primary
17. Install script to clean up the archive log
#!/bin/bash
##. ~/.bash_profile
export ORATAB=/etc/oratab
export PATH=/usr/local/bin:/usr/sbin:$PATH
export SQLDIR=/home/oracle/scripts/
export DATE=`date +%y%m%d`

ps -ef|grep -v grep|grep pmon|awk '{printf(substr($8,10)"\n")}' | while read SID
do

      ORACLE_SID=$SID
      export ORACLE_SID=$SID
      if [[ "$ORACLE_SID" = "+ASM"* ]]; then
      echo "ASM instance"
      else
      export ORAENV_ASK=NO; . oraenv
      echo Delete archivelog for  ${ORACLE_SID} database.
      ${ORACLE_HOME}/bin/rman target / <<EOF
delete noprompt archivelog until time 'SYSDATE-2';
EOF
      fi
done
exit