Steps to rename the listener log file in Oracle

Steps to rename the listener log file in Oracle

Following are the steps to rename the listener log file in Oracle:

1. Open the command prompt or terminal, Set the Oracle home and PATH:

-- Windows
set oracle_home=E:\oracle\18.0.0\dbhome_1
set path=%oracle_home%\bin;%oracle_home%\opatch;%path%

--Linux
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/18.0.0
export PATH=$ORACLE_HOME/bin:$PATH

2. Open the listener command prompt with command “lsnrctl”.

C:\windows\system32>lsnrctl
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 26-NOV-2020 20:40:20
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL>

3. Stop the listener log with command “set log_status off”

LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WINORACLE.ORCL.com)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

4. Manually rename the listener log file by Operating system commands

Go to location of listener.log file:
mv listener.log listener_old.log

Note: If you donot know the location of listener log file then use command “status”

LSNRCTL> status
-- In status you will find the following line but it will point to xml.
Listener Log File C:\Oracle\dbhomeXE\diag\tnslsnr\WINORACLE\listener\alert\log.xml

--You will change the path of last folder instead of "alert" to "trace".
C:\Oracle\dbhomeXE\diag\tnslsnr\WINORACLE\listener\trace\listener.log

5. Start the listener log with command “set log_status on”

LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=WINORACLE.ORCL.com)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully

ADRCI tool useful commands in Oracle

For login to ADRCI utility

1. Just get the session of the server.

2. Set the oracle_home and oracle_sid parameter for database.

3. Execute the adrci utility command.

#adrci
oracle@localhost/u01/app/oracle# adrci
ADRCI: Release 11.2.0.2.0 - Production on Tue Jul 23 15:25:00 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>

4. Check the present homes on the server with following commands.

#show homes
adrci> show homes
ADR Homes:
diag/rdbms/darvel/11
diag/tnslsnr/darvel/listener_11

5. You need to set the home which you want to check log file. In Oracle 11g, LISTENER and RDBMS home is at different location.

adrci> set homepath diag/tnslsnr/darvel/11

6. Check the alert log file error on the home location.

---For check the last lines of alert log
adrci> show alert –tail
--For checking last 200 line of alert log
adrci> show alert -tail 200
--For check the error message present in the alert log file
adrci> show alert -p "message_text like '%ORA-%' "
--For check the last 30 days error present in alert log file
adrci> show alert -p "message_text like '%ORA-%' and originating_timestamp > systimestamp-30"

7. For Purge diagnostic data that is over 1 day old (1440 minutes).

adrci> PURGE -age 1440 -type ALERT
--For purging incident data older than 1 day
adrci> purge -age 1440 -type incident
adrci> PURGE [[-i id1 | start_id end_id] | [-age mins [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]

8. Show all the incident happening in database.

adrci> show incident
adrci> show incident -p "incident_id = 40165 or incident_id = 145"
adrci> show incident -mode detail
adrci> show incident -mode detail -p "incident_id=33"
adrci> show incident -p "CREATE_TIME > '2011-09-18 21:35:25.012579 +00:00'"
adrci> show incident -p "problem_key='ORA 600 [ksmnfy2]'"
adrci> show incident -p "problem_key='ORA 700 [kfnReleaseASM1]'" -mode basic -last -all

9. Show the problem happening in database.

adrci> show problem
adrci> show problem -all
adrci> show problem -p "problem_id=44"
adrci> show problem -p "problem_key='ORA 600 [krfw_switch_4]'"

1. Go to command windows and used adrci command:

C:\Windows\System32\config\systemprofile>adrci
ADRCI: Release 12.1.0.2.0 - Production on Wed Mar 29 05:04:40 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
ADR base = "E:\oracle"
adrci>

2. List the oracle home present by show home command:

adrci> show home
ADR Homes:home
diag\rdbms\ic\ic
diag\tnslsnr\IC\listener

3. Select the home with set homepath command:

adrci> set homepath diag\rdbms\ic\ic

4. For list all the problem going, please run show problem command:

adrci> show problem

ADR Home = E:\oracle\diag\rdbms\ic\ic:
************************************************************
PROBLEM_ID PROBLEM_KEY  LAST_INCIDENT LASTINC_TIME
---------- ------------ ------------- ---------------------------------
3          ORA 600[723] 246517        2017-03-10 21:14:18.539000 -05:00
2          ORA 445      243935        2017-03-11 06:06:54.406000 -05:00
1          ORA 4030     283042        2017-03-23 02:25:05.963000 -04:00


5. Show all incident and specific incident with following commands:

--Show all incident on at home:
ardci> show incident
--If you want for specific problem then enter define problem key:
adrci> show incident -p "problem_key='ORA 4030'"

ADR Home = E:\oracle\diag\rdbms\ic\ic:
*********************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
----------- ----------- -----------------------------
235513      ORA 4030    2017-03-02 22:00:05.502000 -05:00
235401      ORA 4030    2017-03-02 22:00:05.861000 -05:00
235402      ORA 4030    2017-03-02 22:00:11.002000 -05:00

6. Create package for specific incident id for upload on oracle support.
Note: specify the path and incident number for creating package.

adrci> ips pack incident 235402 in E:\patch_backup
Generated package 2 in file E:\patch_backup\ORA4030_20170329050754_COM_1.zip, mode complete

Script for monitoring with ADRCI utility in Oracle

Following is the script used for monitoring with help of ADRCI utility. It will generate or report error and incident coming in Listener.log and alert.log files.

Script:

#################################################

###### ALERT LOG CHECKING VIA ADRCI #############
#################################################
# To run for last 24 hours – ./check_alert.sh D
# To run for last hour – ./check_alert.sh
# Edit variables below for moving between servers
# Changes To the Script
#

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u01/crs/oracle/product/11.2.0/grid/
export PATH=$PATH:$ORACLE_HOME/bin
DAILY_LOG=/home/oracle/bin/alert_log_check_daily.txt
HOURLY_LOG=/home/oracle/bin/alert_log_check_hourly.txt
MAIL_SUBJ="PRD:WARNING HOST: “
MAIL_RECIPIENT="your_dba_group@your_company.com"

HOST_NAME=`hostname -a`

if [ “$1″ = “D" ]
then

############################################
###############DAILY CHECKS ################
############DBMS AND ASM CHECK##############
############################################

adrci_homes=( $(adrci exec="show homes" | grep -e rdbms -e asm))

echo ‘####################################################’ > $DAILY_LOG
echo ‘####### ALERT LOG OUTPUT FOR LAST 24 HOURS #########’ >> $DAILY_LOG
echo ‘####################################################’ >> $DAILY_LOG
echo " >> $DAILY_LOG
echo " >> $DAILY_LOG
echo " >> $DAILY_LOG

for adrci_home in ${adrci_homes[@]}
do
echo $adrci_home’ Alert Log’ >> $DAILY_LOG
adrci exec="set home ${adrci_home};

show alert -p \\\"message_text like ‘%ORA-%’ and originating_timestamp > systimestamp-1\\\"" -term >> $DAILY_LOG
done

############################################
############## DAILY CHECKS ################
############# LISTENER CHECK###############
############################################

adrci_lsnr_homes=( $(adrci exec="show homes" | grep -e tnslsnr))

echo " >> $DAILY_LOG
echo " >> $DAILY_LOG
echo " >> $DAILY_LOG
echo ‘####################################################’ >> $DAILY_LOG
echo ‘###### LISTENER LOG OUTPUT FOR LAST 24 Hours #######’ >> $DAILY_LOG
echo ‘####################################################’ >> $DAILY_LOG
echo " >> $DAILY_LOG
echo " >> $DAILY_LOG
echo " >> $DAILY_LOG

for adrci_lsnr_home in ${adrci_lsnr_homes[@]}
do
echo $adrci_lsnr_home’ Listener Log’ >> $DAILY_LOG
adrci exec="set home ${adrci_lsnr_home}; show alert -p \\\"message_text like ‘%TNS-%’ and originating_timestamp > systimestamp-1\\\"" -term >> $DAILY_LOG
done

num_errors=`grep -c -e ‘TNS’ -e ‘ORA’ $DAILY_LOG`
if [ $num_errors != 0 ]
then
MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Daily Alert Summary"
mailx -s “$MAIL_SUBJ" $MAIL_RECIPIENT $HOURLY_LOG
echo " >> $HOURLY_LOG
echo " >> $HOURLY_LOG
echo ‘####################################################’ >> $HOURLY_LOG
echo ‘######### ALERT LOG OUTPUT FOR LAST HOUR ###########’ >> $HOURLY_LOG
echo ‘####################################################’ >> $HOURLY_LOG
echo " >> $HOURLY_LOG
echo " >> $HOURLY_LOG
echo " >> $HOURLY_LOG

for adrci_home in ${adrci_homes[@]}
do
echo $adrci_home’ Alert Log’ >> $HOURLY_LOG
adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like ‘%ORA-%’ and originating_timestamp > systimestamp-1/24\\\"" -term >> $HOURLY_LOG
done

num_errors=`grep -c -e ‘TNS’ -e ‘ORA’ $HOURLY_LOG`
if [ $num_errors != 0 ]
then
MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Hourly Alert Summary"
mailx -s “$MAIL_SUBJ" $MAIL_RECIPIENT < $HOURLY_LOG
fi

fi

Oracle TSM TDPO

設定TDPO

1.設定tdpo.opt
———————
cd /usr/tivoli/tsm/client/oracle/bin64/
copy tdpo.opt.smp to tdpo.opt
修改內容如下:
DSMI_ORC_CONFIG    /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
DSMI_LOG           /tmp
TDPO_NODE          <nodename> TSM設定的node name

2.建立dsm.sys
———————
copy /usr/tivoli/tsm/client/api/bin64/dsm.sys.smp to
     /usr/tivoli/tsm/client/oracle/bin64/dsm.sys
修改內容如下:
SErvername  tdpo
CHANGINGRETRIES 0
NODENAME <nodename> TSM設定的node name
PASSWORDACCESS prompt
COMMMethod         TCPip
TCPPort            1500
TCPServeraddress   <address> TSM Server的address

3.建立dsm.opt
———————
cd /usr/tivoli/tsm/client/oracle/bin64/
產生dsm.opt,內容如下:
SERVERNAME tdpo

4.在TSM上面註冊TDPO NOde(option)
————————————————
在TSM上面註冊Oracle Server
TSM:TSM_SERVER> reg node <nodename> orabakpw maxnummp=2 passexp=0

使用RMAN catalog的archiving and expiration設定來控制backup retention
TSM:TSM_SERVER> update node <nodename> backupdelete=yes

5.設定TSM Copy Group Options(option)
————————————————
因為RMAN每次備份產生的檔案都是唯一的,使得TSM上面備份的檔案永遠不會過期,
所以要設定copy group attribute : verdeleted = 0,讓TDPO可以從TSM backup storage pool
移除RMAN設定成inactive or expired的檔案
TSM:TSM_SERVER> update copygroup standard standard standard verdeleted=0

6.產生TSM Server使用的密碼檔
——————————————
為了不使每次做RMAN backup都必須輸入密碼,我們可以建立密碼檔
先看環境設定:
root@hostname> tdpoconf showenvironment
或是
root@hostname> tdpoconf showenvironment -tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt

IBM Tivoli Storage Manager for Databases:
Data Protection for Oracle
Version 5, Release 3, Level 3.0
(C) Copyright IBM Corporation 1997, 2006. All rights reserved.

ANS1035S (RC406)  Options file ‘*’ could not be found.

如果發生上面的錯誤,請依照下面指示除錯:
copy /usr/tivoli/tsm/client/ba/bin/dsm.opt and dsm.sys to
        /usr/tivoli/tsm/client/api/bin64

設定密碼:
root@hostname> tdpoconf password
或是
root@hostname> tdpoconf password -tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt

7.建立symbolic links in the Oracle library directory
———————————————————
oracle@hostname> ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a

8.測試TDPO連線
————————–
設定TDPO_OPTFILE環境變數指向tdpo.opt
oracle@hostname> export TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt
oracle@hostname> sbttest test
The sbt function pointers are loaded from libobk.a(shr.o) library.
Return code -1 from sbtinit, bsercoer = 7011, bsercerrno = 106
sbtopen: System error – eg. malloc, fork errors

確保 /usr/tivoli/tsm/client/oracle/bin64/tdpoerror.log 或是 /tmp/tdpoerror.log,
ORACLE用戶要有寫入權限

依照上文的設定,我們改變/tmp/tdpoerror.log 的權限:
chmod 777 /tmp/tdpoerror.log

oracle@hostname> sbttest test
The sbt function pointers are loaded from libobk.a(shr.o) library.
— sbtinit succeeded
Return code -1 from sbtinit, bsercoer = 0, bsercerrno = 0
Message 0 not found;  product=RDBMS; facility=SBT

How to Monitor OEM URL using CURL

How to Monitor OEM URL using CURL

Posted on 

If in case your new OEM13c doesn’t have Always ON Feature & Management server goes down so in that you can monitor the monitoring URL by using simple unix command to called “curl”. It ping the URL whether it alive or not.

#Title:chkoem_url.ksh
#Author: Sandeep R Narani
#Date:02/23/2016
echo "This scripts monitors the OEM URL for 15mins"
URL=https://oracleslbhostname/em/faces/logon/core-uifwk-console-login
APP_NAME=OEM13c
DATE=$(date)
status_code=$(/bin/timeout 5 curl -vk $URL -o /tmp/healthcheck.log -w "\n%{http_code}\n")
MAIL_TO=youremail@gmail.com
if [ $status_code == 200 ]
then 
##echo "13c Oracle Management Server is Working Fine"
echo "13c Oracle Management Server is UP" | /bin/mailx -v -s "${APP_NAME} is UP on ${DATE} with ${status_code} status" ${MAIL_TO}
else
echo "13c Oracle Management Server is DOWN" | /bin/mailx -v -s "${APP_NAME} is down on ${DATE} with ${status_code} status" ${MAIL_TO}
 # Add the mail command after echo statement with a pipe, which is 'mail -s "${APP_NAME} is down on ${DATE} with ${status_code} status" ${MAIL_TO}'
fi

you can schedule using crontab or Autosys scheduler

*/15   *   *   *   * /u01/app/oracle/chkoem_url.ksh

定期刪除table過期資料

1. 建立執行清除資料的 Table 候選清單
    create table “zdba_del_tab"
2. 建立 Procedure 只處理 delete 動作
    create procedure “zdba_delete_commit"
3. 建立 Procedure 呼叫 “zdba_delete_commit" 處理 Table清單 “zdba_del_tab" 中的 Table
    create procedure “zdba_del_tab_p""zdba_delete_commit" 做刪除動作
4. 建立 Job 定期自動執行

1.
CREATE TABLE CMO.ZDBA_DEL_TAB
(
  OWNER         VARCHAR2(30 BYTE),
  SEGMENT_NAME  VARCHAR2(81 BYTE),
  KEYCOL        VARCHAR2(30 BYTE),
  CONDITION     VARCHAR2(10 BYTE),
  KEYCOLVAL     VARCHAR2(100 BYTE),
  ENABLED       VARCHAR2(1 BYTE),
  STIME         DATE,
  ETIME         DATE,
  CONDITION2    VARCHAR2(500 BYTE),
  SHRINK        VARCHAR2(1 BYTE),
  STATUS        VARCHAR2(20 BYTE)
)
TABLESPACE <tablespace_name>;

COMMENT ON TABLE ZDBA_DEL_TAB IS
‘Purpose: Table List for Delete Expired Table Data  
Created by: DBA  
Keep days: Always  
Purge key: NA  
Desc:  
  enabled=A :簡單型, 每日1:00執 delete  by crontab
  enabled=B :簡單型, 每日5:00執 delete  by DB job
  enabled=C :複雜型, 每日1:00執 delete  by crontab in
  enabled=X :不執行 delete’;

COMMENT ON COLUMN ZDBA_DEL_TAB.SHRINK IS ‘是否做shrink動作’;

CREATE UNIQUE INDEX ZDBA_DEL_TAB_U01 ON ZDBA_DEL_TAB
(OWNER, SEGMENT_NAME)
LOGGING
TABLESPACE <tablespace_name>;

ALTER TABLE ZDBA_DEL_TAB ADD (
  CONSTRAINT ZDBA_DEL_TAB_U01
 UNIQUE (OWNER, SEGMENT_NAME)
    USING INDEX
    TABLESPACE <tablespace_name>);

2.
CREATE OR REPLACE PROCEDURE zdba_delete_commit (
   p_statement           IN   VARCHAR2,
   p_commit_batch_size   IN   NUMBER DEFAULT 10000
)
IS
/* ———————————-
Purpose: Delete Table in Batch Mode
Created by: DBA
Date:
structure:
  1. define sql statement
  2. open cursor
  3. execute cursor
  4. close cursor
updated:
———————————- */
   cid                 INTEGER;
   changed_statement   VARCHAR2 (2000);
   finished            BOOLEAN;
   nofrows             INTEGER;
   lrowid              ROWID;
   rowcnt              INTEGER;
   errpsn              INTEGER;
   sqlfcd              INTEGER;
   errc                INTEGER;
   errm                VARCHAR2 (2000);
BEGIN
— If the actual statement contains a WHERE clause, then append a
— rownum < n clause after that using AND, else use WHERE rownum < n clause
   IF (UPPER (p_statement) LIKE ‘% WHERE %’)
   THEN
      changed_statement :=
         p_statement || ‘ AND rownum < ‘ || TO_CHAR (p_commit_batch_size + 1);
   ELSE
      changed_statement :=
         p_statement || ‘ WHERE rownum < ‘
         || TO_CHAR (p_commit_batch_size + 1);
   END IF;

   BEGIN
      cid := DBMS_SQL.open_cursor;              — Open a cursor for the task
      DBMS_SQL.parse (cid, changed_statement, DBMS_SQL.native);
                                                         — parse the cursor.
      rowcnt := DBMS_SQL.last_row_count;
   — store for some future reporting
   EXCEPTION
      WHEN OTHERS
      THEN
         errpsn := DBMS_SQL.last_error_position;
                               — gives the error position in the changed sql
                               — delete statement if anything happens
         sqlfcd := DBMS_SQL.last_sql_function_code;
                                     — function code can be found in the OCI
                                     — manual
         lrowid := DBMS_SQL.last_row_id;
         — store all these values for error reporting. However
         — all these are really useful in a stand-alone proc
         — execution for DBMS_OUTPUT to be successful, not
         — possible when called from a form or front-end tool.
         errc := SQLCODE;
         errm := SQLERRM;
         DBMS_OUTPUT.put_line (   ‘Error:’
                               || TO_CHAR (errc)
                               || ‘ Posn:’
                               || TO_CHAR (errpsn)
                               || ‘SQL fCode ‘
                               || TO_CHAR (sqlfcd)
                               || ‘ rowid’
                               || ROWIDTOCHAR (lrowid)
                              );
         raise_application_error (-20000, errm);
                             — it’ll ensure the display of at least the error
                             — message if something happens.
   END;

   finished := FALSE;

   WHILE NOT (finished)
   LOOP      — keep on executing the cursor till there is no more to process.
      BEGIN
         nofrows := DBMS_SQL.EXECUTE (cid);
         rowcnt := DBMS_SQL.last_row_count;
      EXCEPTION
         WHEN OTHERS
         THEN
            errpsn := DBMS_SQL.last_error_position;
            sqlfcd := DBMS_SQL.last_sql_function_code;
            lrowid := DBMS_SQL.last_row_id;
            errc := SQLCODE;
            errm := SQLERRM;
            DBMS_OUTPUT.put_line (   ‘Error:’
                                  || TO_CHAR (errc)
                                  || ‘Posn:’
                                  || TO_CHAR (errpsn)
                                  || ‘SQL fCode ‘
                                  || TO_CHAR (sqlfcd)
                                  || ‘ rowid’
                                  || ROWIDTOCHAR (lrowid)
                                 );
            raise_application_error (-20000, errm);
      END;

      IF nofrows = 0
      THEN
         finished := TRUE;
      ELSE
         finished := FALSE;
      END IF;

      COMMIT;
   END LOOP;

   BEGIN
      DBMS_SQL.close_cursor (cid);     — close the cursor for a clean finish
   EXCEPTION
      WHEN OTHERS
      THEN
         errpsn := DBMS_SQL.last_error_position;
         sqlfcd := DBMS_SQL.last_sql_function_code;
         lrowid := DBMS_SQL.last_row_id;
         errc := SQLCODE;
         errm := SQLERRM;
         DBMS_OUTPUT.put_line (   ‘Error:’
                               || TO_CHAR (errc)
                               || ‘ Posn:’
                               || TO_CHAR (errpsn)
                               || ‘SQL fCode ‘
                               || TO_CHAR (sqlfcd)
                               || ‘ rowid’
                               || ROWIDTOCHAR (lrowid)
                              );
         raise_application_error (-20000, errm);
   END;
END;
/

3.
CREATE OR REPLACE PROCEDURE zdba_del_tab_p (p_enabled IN VARCHAR2)
IS
/*
Purpose: Delete Expired Data
Created by: DBA
Desc: 排crontab 每日 1:00執行
Update:
*/
   CURSOR c1
   IS
      SELECT owner, segment_name, keycol, condition, keycolval, condition2
        FROM zdba_del_tab
       WHERE enabled = p_enabled;

   r1           c1%ROWTYPE;
   str          VARCHAR2 (500);
   v_sqlcode    VARCHAR2 (200);
   v_sqlerrm    VARCHAR2 (200);
   v_stime      DATE;
   v_etime      DATE;
   v_difftime   NUMBER;
   v_sender     VARCHAR2 (200) := ‘XX System’;
   v_maillist   VARCHAR2 (200)
                         := ‘dba@domain,dba_backup@domain’;
BEGIN
   OPEN c1;

   IF p_enabled = ‘A’ or p_enabled = ‘B’
   THEN
      SELECT SYSDATE
        INTO v_stime
        FROM DUAL;

      LOOP
         FETCH c1
          INTO r1;

         EXIT WHEN c1%NOTFOUND;

         BEGIN
            UPDATE zdba_del_tab
               SET stime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
            str :=
                  ‘delete from ‘
               || r1.owner
               || ‘.’
               || r1.segment_name
               || ‘ where ‘
               || r1.keycol
               || ‘ ‘
               || r1.condition
               || ‘ ‘
               || r1.keycolval;
            zdba_delete_commit (str);

            UPDATE zdba_del_tab
               SET etime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               raise_application_error (-20001,
                                           ‘A loop error was encountered ‘
                                        || SQLCODE
                                        || ‘ -ERROR- ‘
                                        || SQLERRM
                                       );
         END;
      END LOOP;

      SELECT SYSDATE
        INTO v_etime
        FROM DUAL;

      v_difftime := trunc((v_etime – v_stime) * 24 * 60,2);
   ELSIF p_enabled = ‘C’
   THEN
      SELECT SYSDATE
        INTO v_stime
        FROM DUAL;

      LOOP
         FETCH c1
          INTO r1;

         EXIT WHEN c1%NOTFOUND;

         BEGIN
            UPDATE zdba_del_tab
               SET stime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
            str :=
                  ‘delete from ‘
               || r1.owner
               || ‘.’
               || r1.segment_name
               || ‘ where ‘
               || r1.condition2;
            zdba_delete_commit (str);

            UPDATE zdba_del_tab
               SET etime = SYSDATE
             WHERE owner = r1.owner AND segment_name = r1.segment_name;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               raise_application_error (-20001,
                                           ‘A loop error was encountered ‘
                                        || SQLCODE
                                        || ‘ -ERROR- ‘
                                        || SQLERRM
                                       );
         END;
      END LOOP;

      SELECT SYSDATE
        INTO v_etime
        FROM DUAL;

      v_difftime := trunc((v_etime – v_stime) * 24 * 60,2);
   ELSE
      NULL;
   END IF;

   CLOSE c1;

   UTL_MAIL.send (sender          => v_sender,
                  recipients      => v_maillist,
                  cc              => NULL,
                  subject         => ‘OK…!! Delete Expired Data Completely. Type=’||p_enabled||’ (apps.zdba_del_tab_p)’,
                  MESSAGE         =>    ‘Total consume ‘
                                     || v_difftime
                                     || ‘ min, from ‘
                                     || TO_CHAR (v_stime,
                                                 ‘yyyy-mm-dd hh24:mi:ss’
                                                )
                                     || ‘ to ‘
                                     || TO_CHAR (v_etime,
                                                 ‘yyyy-mm-dd hh24:mi:ss’
                                                ),
                  mime_type       => ‘text/plain; charset=UTF-8’
                 );
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001,
                                  ‘A main error was encountered ‘
                               || SQLCODE
                               || ‘ -ERROR- ‘
                               || SQLERRM
                              );
      v_sqlcode := SQLCODE;
      v_sqlerrm := SQLERRM;
      UTL_MAIL.send (sender          => v_sender,
                     recipients      => v_maillist,
                     cc              => NULL,
                     subject         => ‘ERROR…!! Delete Expired Data Failed. Type=’||p_enabled||’ (apps.zdba_del_tab_p)’,
                     MESSAGE         =>    ‘SQLCODE: ‘
                                        || v_sqlcode
                                        || ‘ ,SQLERRM: ‘
                                        || v_sqlerrm,
                     mime_type       => ‘text/plain; charset=UTF-8’
                    );
END;
/

RMAN Backup and Recovery (Oracle 10g on AIX) TSM

RMAN Backup and Recovery (Oracle 10g on AIX)

Install Oracle 10g software for RMAN catalog DB

Environment:
OS Level = AIX 5.3
Oracle DB Version = 10.2.0.3
Oracle Catalog DB SID = CATDB
Oracle RAC DB SID = PROD
Oracle RAC DB SID1 = PROD1
Oracle RAC DB SID2 = PROD2
TDPO node name = dbsrv_oracle


1. Install 10.2.0.1 software DBlogin as rootcd /source/Disc1/cdrom/database/rootpre./rootpre.shlogin as oraclevi .profileexport TMP=/oracle/tmpexport TMPDIR=/oracle/tmpexport ORACLE_HOME=/oracle/10.2.0/dbexport PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport ORACLE_SID=catdbcd /source/Disc1/cdrom/databaseexport DISPLAY=catdbsrv:1.0mkdir /oracle/tmp./runInstallerinstall software only
2. Apply 10.2.0.2 patchsetcd /source/patch/Disk1./runInstaller
3. Create catalog databasedbca (SID=catdb)
4. Create Listenernetca
5. Create alias for PROD dbnetca
Configure Catalog Database

1. Creating tablespace to store backup information
SQL> create tablespace rmancat datafile
‘/rman/catdb/rmancat01.dbf’ size 4g;
2. Creating the Recovery Catalog Owner
SQL> CREATE USER rman IDENTIFIED BY cat
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rmancat
QUOTA UNLIMITED ON rmancat;
SQL> grant CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER to rman;
3. Creating the Recovery Catalog
oracle> rman catalog rman/cat
RMAN> CREATE CATALOG;
4. Verify the user
sqlplus rman/cat
SQL> SELECT TABLE_NAME FROM USER_TABLES;

Configure Production Database

1. Login to db server using oracle account
2. Create tnsnames alias for catdb
netca
3. Link TSM Data Protection for Oracle
ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a
4. Define Data Protection for Oracle options
login root
vi /usr/tivoli/tsm/client/oracle/bin64/tdpo.optdsmi_orc_config     /usr/tivoli/tsm/client/oracle/bin64/dsm.optdsmi_log     /usr/tivoli/tsm/client/oracle/bin64tdpo_node     dbsrv_oracletdpo_pswdpath     /usr/tivoli/tsm/client/oracle/bin64
vi /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
SErvername tdpo

vi /usr/tivoli/tsm/client/ba/bin/dsm.sysSErvername     tdpoCHANGINGRETRIES     0NODENAME     dbsrv_oraclePASSWORDACCESS     promptCOMMMethod     TCPipTCPPort     1600TCPServeraddress     xx.xx.xx.xx
cd /usr/tivoli/tsm/client/api/bin64
ln -s /usr/tivoli/tsm/client/ba/bin/dsm.sys  dsm.sys
login root
dsmadmc
REG NODE   dbsrv_oracle   oracleprod maxnummp=2   passexp=0
cd /usr/tivoli/tsm/client/oracle/bin64
tdpoconf password (enter <password> three times)
tdpoconf   showenvironment   (confirm the configuration)
rm /usr/tivoli/tsm/client/oracle/bin64/tdpoerror.log

Managing Target Database in the Recovery Catalog

1. Registering a Database in the Recovery Catalog
login oracle
rman TARGET / CATALOG rman/cat@catdb
RMAN> REGISTER DATABASE;
RMAN> REPORT SCHEMA;
2. Configure Default RMAN Setting
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE sbt 
CONNECT ‘SYS/<pwd>@PROD1’ 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt 
CONNECT ‘SYS/<pwd>@PROD2’ 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> crosscheck archivelog all;

PROD Database Backup Scripts

1. Level 0 Backup
rman target / catalog rman/cat@catdb
RMAN> backup incremental level 0
filesperset 50 
format “PROD_df_level0_%t_%s_%p" database
plus archivelog format “PROD_arch_%t_%s_%h"
delete all input;
RMAN> delete noprompt obosolete;
(The script is create under $ORACLE_HOME/scripts/rman_bck_level0.sh)
2. Level 1 Backup
rman target / catalog rman/cat@catdb
RMAN> backup incremental level 1 cumulative
filesperset 50
format “PROD_df_level1_%t_%s_%p" database
plus archivelog format “PROD_arch_%t_%s_%h”
delete all input;
(The script is create under $ORACLE_HOME/scripts/rman_bck_level1.sh)
3. Archive Log Files Backup
rman target / catalog rman/cat@catdb
RMAN> backup
filesperset 10
format “PROD_arch_%t_%s_%h" archivelog all delete all input;

Catalog Database Backup Scripts(Use DataPump)

1. Create Directory Definition for DataPump
sqlplus “/ as sysdba”
SQL> create directory dpump_dir as ‘/rman/bck_catdb’;
SQL> grant READ, WRITE ON DIRECTORY dpump_dir TO rman;
2. Use DataPump Export to backup the catalog
expdp rman/cat DUMPFILE=dpump_dir:exp_rman.dmp \
logfile=dpump_dir:exp_rman.log

Restore & Recovery Scripts

1. Loss all database files, but control files, and online redo log files 
are not loss
rman target / catalog rman/cat@catdb
RMAN> run {
startup mount;
restore database;
recover database;
alter database open;
}
2. Loss all database files, control files, and online redo log files
For this situation, you need to perform incomplete recovery. Please try to 
find the latest archived log file information in the alert file, and check the 
timing information
rman target / catalog rman/cat@catdb
RMAN> run {
startup nomount;
ALLOCATE CHANNEL T1 DEVICE TYPE sbt 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’; 
ALLOCATE CHANNEL T2 DEVICE TYPE sbt 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
restore controlfile from autobackup;
alter database mount;
set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;
restore database;
recover database;
alter database open resetlogs;
}

If the above recovery failed due to no latest archivelog file to be applied, then perform the following statements to open the database.
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> run { alter database open resetlogs; }
Remove all 2PC pending transactions
Generate the scripts first if there are many 2PC pending transactions, then edit the scripts 
to add ‘commit;’ statement for every DBMS_TRANSACTION statement
sqlplus “/ as sysdba”
set linesize 120
set pages 24
set head off
spool remove_2pc_pending.sql
select ‘exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (“‘||
LOCAL_TRAN_ID||"‘);’ from DBA_2PC_PENDING;
spool off
Remove all expired archivelogs
$ cd /prodarch
remove restored archivelogs
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;
3. All the machines crash, need to find a new machine to startup the database
Install Oracle 10g Software on the new machine
Setup TSM and Data Protection for Oracle
Please refer the “Configure Production Database” Section
Create initial parameter initPROD1.ora with the following setting
background_dump_dest =/oracle/10.2.0/db/admin/PROD1/bdump
core_dump_dest =/oracle/10.2.0/db/admin/PROD1/cdump
user_dump_dest =/oracle/10.2.0/db/admin/PROD1/udump
db_name ="PROD"
instance_name =PROD1
control_files =(‘/prodora/control01.ctl’,’/proddata1/control02.ctl’,’/prodindx1/control03.ctl’)
#Set the below to the same as the production target
undo_management =AUTO
undo_tablespace =APPS_UNDOTBS1
db_block_size = 8192
log_archive_format=%t_%s_%r.arc
log_archive_dest_1=’location=/prodarch/’
log_archive_format=%t_%s_%r.arc
sga_target=8G
db_cache_size=4G
shared_pool_size=2G
pga_aggregate_target=16G
compatible = 10.2.0.
# To clone RAC db to non-RAC db need to add this parameter
_no_recovery_through_resetlogs=TRUE
Perform the Recovery
rman target / catalog rman/cat@catdb
RMAN> run {
startup nomount;
ALLOCATE CHANNEL T1 DEVICE TYPE sbt 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
ALLOCATE CHANNEL T2 DEVICE TYPE sbt 
PARMS=’ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
restore controlfile from autobackup;
alter database mount;
set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;
restore database;
recover database;
alter database open resetlogs;
}
If the above recovery failed due to no latest archivelog file to be applied, then perform the 
following statements to open the database.
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> run { alter database open resetlogs; }
Remove all 2PC pending transactions
Generate the scripts first if there are many 2PC pending transactions, then edit the scripts 
to add ‘commit;’ statement for every DBMS_TRANSACTION statement
sqlplus “/ as sysdba”
set linesize 120
set pages 24
set head off
spool remove_2pc_pending.sql
select ‘exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (“‘||
LOCAL_TRAN_ID||"‘);’ from DBA_2PC_PENDING;
spool off
Remove all expired archivelogs
$ cd /prodarch
remove restored archivelogs
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

Duplicate Database

1. create an alias entry in tnsnames.ora file for PROD and catdb(optional)
2. confirm the connection to PROD and catdb(optional) working
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = db1srv_vip.domain)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD1)
)
)
CATDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = catdbsrv.domain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = catdb)
)
)
sqlplus system/<pwd>@prod
sqlplus rman/cat@catdb
3. determine the files location and space to be restored
this information is used to set ‘db_file_name_convert’ parameter in 
init<SID>.ora file
4. create the init<SID>.ora parameter file and administration directories 
for the duplicate database, the parameter file should have the following 
setting:
background_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/bdump
core_dump_dest =/newprod/10.2.0/db/adminNEWPROD_otsrv/cdump
user_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/udump
db_name ="NEWPROD"
instance_name =NEWPROD
control_files =(‘/newprod/data/control01.ctl’,’/newprod/data /control02.ctl’,’/newprod/data /control03.ctl’)
db_file_name_convert =(“/proddata1/","/newprod/data/", “/prodindx1/","/newprod/data/", “/prodindx1/","/newprod/data/", “/prodora/","/newprod/data/")
log_file_name_convert =(“/prodora/PROD/", “/newprod/data/",”/proddata1/”,”/newprod/data/”)
#Set the below to the same as the production target
undo_management =AUTO
undo_tablespace =APPS_UNDOTBS1
db_block_size = 8192
log_archive_format=%t_%s_%r.arc
log_archive_dest_1=’location=/arch/’
compatible = 10.2.0.
# To clone RAC db to non-RAC db need to add this parameter
_no_recovery_through_resetlogs=TRUE
5. Startup the duplicate database instance
Sqlplus “/ as sysdba”
SQL> startup nomount;
6. Prepare RMAN duplicate script
run {
allocate auxiliary channel t1 type sbt
parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";
allocate auxiliary channel t2 type sbt
parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";
set until time “to_date(‘<the_time_within_RMAN_backup>’,’YYYY/MM/DD 
HH24:MI:SS’)";
duplicate target database to NEWPROD;
}
7. run RMAN to duplicate the database
rman target sys/<pwd>@prod nocatalog auxiliary /
RMAN> @duplicate.script

Migrating Database from ASM to non-ASM

Migrating Databases from ASM to non-ASM

1. Start your database with ASM.
2. Create pfile from spfile.
3. Edit pfile/spfile to reflect controlfile name in file system location.
4. Startup nomount the DB.
SQL> Startup nomount
5. Use RMAN to copy the control file from ASM to NON-ASM.RMAN> RESTORE CONTROLFILE FROM '';
6. Mount the DB.
SQL> alter database mount;
7. Use RMAN to copy the database from ASM to NON-ASM.RMAN> BACKUP AS COPY DATABASE format '/u01/oradata/nonasmdb/datafile/%U';
8. From RMAN.RMAN> SWITCH DATABASE TO COPY;
9. Recreate the tempfile and redo logs as before. See step 7 and 8 above.