NM.Mathan Oracle Apps DBA: February 2013

Tuesday, 19 February 2013

End date unwanted users

Backup FND_USER table:

create table fnd_user_bkp_ddmmyy as select * from fnd_user;

Then:

DECLARE

CURSOR cur1

SELECT user_name

FROM fnd_user

WHERE

user_name not in ('SYSADMIN', 'GUEST', 'MATHAN.MEYYAZHAGAN')

AND (end_date IS NULL OR TRUNC (end_date) > TRUNC (SYSDATE));

BEGIN

FOR all_user IN cur1

LOOP

fnd_user_pkg.updateuser (x_user_name => all_user.user_name,

x_owner => 'SYSADMIN',

x_end_date => SYSDATE

);

COMMIT;

END LOOP;

END;

Stop Application Script

cd $ADMIN_SCRIPTS_HOME ; sh adstpall.sh `history | grep apps/ | grep -v grep |grep 'sqlplus' | tail -n1 | awk '{print $3}'`

Sunday, 17 February 2013

Backup Using Rman[status through mail]

bash-3.2$ crontab -l
30 00 * * 0-5 /u02/orauat/RMAN/rman.sh
00 00 * * * /home/orauat/scripts/RMAN_COMP/daily_rman_arch_del.sh > /home/orauat/scripts/RMAN_COMP/log/arch_del.log 2>&1
bash-3.2$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

bash-3.2$ pwd
/u02/orauat/RMAN
bash-3.2$ cat rman.sh
#/* REM --------------------------------------------------------------------------
#
# File Name :rman.sh
# Purpose :To take the HOT backup using RMAN
# # --------------------------------------------------------------------------
#

# --------------------------------------------------------------------
. /u03/orauat/db/tech_st/11.1.0/PRODTEST1_KKKMAAIND.env

export v_inst1=PRODTEST1
export v_inst2=PRODTEST2

rm -rf /u02/orauat/PRODTEST_hotbackup/PRODTEST*

rm -rf /u02/orauat/PRODTEST_hotbackup/arch*

# Step 1: Rman Backup Location variable
# --------------------------------------
BACKUP_STATUS=""
date=`date|awk '{print $2 $3}'`
DBA="dbsupport@name.com"
LOG=/u02/orauat/scripts/RMAN_COMP/log

### CROSSCHECK #######

rman target / log=$LOG/Validate_$date.log <<EOF
run {
crosscheck archivelog all;
}
exit
EOF

# Step 2: Archive log current on 1st Instance
# -------------------------------------------
sqlplus rman/password@$v_inst1 << EOF
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF

# Step 3: Archive log current on 2nd Instances
# ---------------------------------------------
sqlplus rman/password@$v_inst2 << EOF
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF

# Step 4: Rman compressed backup incremental Level 0 Backup of database
# -----------------------------------------------------------------------

rman target / log=$LOG/PRODTEST_bkp_$date.log <<EOF
RUN {
allocate channel c1 type DISK;
allocate channel c2 type DISK;
allocate channel c3 type DISK;
allocate channel c4 type DISK;
allocate channel c5 type DISK;
backup as compressed backupset incremental level 0 cumulative tag LEVEL0_BACKUP filesperset 5 format '/u02/orauat/PRODTEST_hotbackup/%d_LVL0_%T_%s' database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}
exit
EOF

#######

cat $LOG/PROD_bkp_$date.log >> $LOG/cron_rman.log

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

# Step 5: Archive log current on 1st and 2nd Instances
# -------------------------------------------------------

sqlplus rman/password@$v_inst1 << EOF
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF

sqlplus rman/password@$v_inst2 << EOF
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF

# Archive log backup with controlfile
# -----------------------------------
rman target / log=$LOG/PRODTEST_archive_$date.log <<EOF
RUN {
allocate channel c1 type DISK;
allocate channel c2 type DISK;
allocate channel c3 type DISK;
allocate channel c4 type DISK;
allocate channel c5 type DISK;
backup as compressed backupset format '/u02/orauat/PRODTEST_hotbackup/arch_%e_%d_%D_%M_%Y_%s_%U.rman' archivelog all not backed up 1 times;
copy current controlfile to '/u02/orauat/PRODTEST_hotbackup/%d_control_file_%D_%T_%s';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}
exit
EOF

#######

cat $LOG/PRODTEST_archive_$date.log >> $LOG/cron_rman.log

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

# Step 6: crosscheck and delete old backups
# ------------------------------------------------

RUN {
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete device type disk;
#delete noprompt archivelog all backed up 2 times to device type disk;
#delete archivelog until time 'SYSDATE-1';
#delete noprompt backup of archivelog all completed before 'sysdate-2';
}
exit
EOF
fi

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

rman target / log=$LOG/PRODTEST_backupset_info.log <<EOF
list backup summary;
list backupset;
list backup of controlfile;
exit
EOF

# Step 8: Mail to DBA
#---------------------

mail -s "PRODTEST RMAN Backup Completed" $DBA < $LOG/cron_rman.log

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

#export ext=`date +%y%m%d`
mv /u02/orauat/RMAN/RMAN_COMP/log/cron_rman.log /u02/orauat/RMAN/RMAN_COMP/log/cron_rman_$ext.log

####### DELETE 45 Days older logs #####################
find /u02/orauat/RMAN/RMAN_COMP/log -name '*.log' -mtime +45 -exec rm -f {} \;

bash-3.2$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

bash-3.2$ pwd
/home/orauat/scripts/RMAN_COMP
bash-3.2$ cat daily_rman_arch_del.sh
#!/bin/bash
export ORACLE_HOME=/u03/orauat/db/tech_st/11.1.0
export ORACLE_SID=PRODTEST1
export PATH=$PATH:$ORACLE_HOME/bin

rman target / <<EOF
delete noprompt archivelog all completed before 'sysdate - 2';
exit;
EOF
bash-3.2$

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SHOW ALERTLOG (adrci)

bash-3.2$ adrci

ADRCI: Release 11.1.0.7.0 - Production on Mon Feb 18 10:17:17 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/orahr/db/tech_st/11.1.0/admin/PRODDEV_<Node-Name>"
adrci> SHOW PROBLEM

ADR Home = /u01/orahr/db/tech_st/11.1.0/admin/PRODDEV_<Node-Name>/diag/rdbms/proddev/PRODDEV:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
21 ORA 7445 [PC:0x1027CBB50] 205862 2013-02-14 22:25:05.828269 +05:30
22 ORA 7445 [jonclp8_set_verbose_class__cst__()+0090] 205861 2013-02-14 22:24:43.813769 +05:30
4 ORA 600 [pevmexe.c: BREAK: bug 281522 : Hit opcode BREAK] 205285 2013-02-14 20:15:12.707948 +05:30
19 ORA 7445 [pfrxca()+006c] 206717 2013-02-14 17:44:00.040279 +05:30
10 ORA 603 145962 2013-02-13 12:55:00.258402 +05:30
2 ORA 4031 146285 2013-02-13 11:23:24.457306 +05:30
20 ORA 7445 [_ptrgl()] 145306 2013-02-12 22:21:39.271129 +05:30
17 ORA 600 [kgh_heap_sizes:ds] 146179 2013-02-12 22:05:32.269348 +05:30
18 ORA 7445 [qerjotRelease()+01a0] 146178 2013-02-12 22:05:25.559433 +05:30
13 ORA 7445 [PC:0x1027CBAE8] 144971 2013-02-11 19:14:01.631824 +05:30
7 ORA 7445 [pfrdd0d1dpf()+0028] 144970 2013-02-11 19:13:54.650951 +05:30
16 ORA 7445 [PC:0x3105323438323004] 146434 2013-02-11 19:08:09.714690 +05:30
14 ORA 7445 [kghalo()+072c] 146204 2013-02-11 18:55:23.189923 +05:30
15 ORA 600 [17114] 146203 2013-02-11 18:55:15.626540 +05:30
11 ORA 600 [6017] 146202 2013-02-11 18:55:04.610395 +05:30
12 ORA 7445 [PC:0xE86101708A630000] 145978 2013-02-11 18:21:08.129156 +05:30
3 ORA 7445 [PC:0x0] 146146 2013-02-11 17:11:32.349252 +05:30
9 ORA 600 [jslvswu_1] 72447 2013-02-09 08:44:24.656354 +05:30
8 ORA 600 [kkjex1e_1] 73710 2013-02-08 21:11:35.975467 +05:30
6 ORA 7445 [PC:0x801F002870030000] 74559 2013-02-08 16:08:42.312241 +05:30
5 ORA 7445 [PC:0x102BD3508] 73896 2013-02-08 15:54:29.699976 +05:30
1 ORA 600 [krbbGetUndoScn_badic] 129 2013-02-05 10:32:31.567098 +05:30
22 rows fetched

adrci> SHOW ALERT

Choose the alert log from the following homes to view:

1: diag/rdbms/proddev/PRODDEV
2: diag/tnslsnr/<Node-name>/proddev
Q: to quit

Please select option: Q
adrci> exit
bash-3.2$

Sunday, 10 February 2013

Physical Standby Sync

Check On Primary said

SQL> SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
2 WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
3 ORDER BY 1;

Thread Last Sequence Generated
---------- -----------------------
1 88899
1 88899
2 84984
2 84984

SQL>

Check on Physical Standby said

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
2 FROM
3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
4 5 WHERE
ARCH.THREAD# = APPL.THREAD#
6 7 ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 88899 88899 0
2 84984 84984 0

SQL>
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM GV$ARCHIVE_GAP;

no rows selected

SQL>

Saturday, 2 February 2013

Find Unrestored datafile

RMAN>report unrecoverable database;

Used to find unrestored datafile list.

Child Request Summary

select fcr.request_id,
fcr.USER_CONCURRENT_PROGRAM_NAME,
fcr.ACTUAL_START_DATE,
fcr.ACTUAL_COMPLETION_DATE,
DECODE (fcr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
fcr.status_code
) "Status",
decode(fcr.phase_code,
'C','Completed',
'I','Inactive',
'R','Running',
'A','Active',
fcr.phase_code) "Phase Code",
trunc(mod((fcr.actual_completion_date-fcr.actual_start_date)*24,24))||':'||trunc(mod((fcr.actual_completion_date-fcr.actual_start_date)*24*60,60))||':'||trunc(mod((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60,60)) exec_duration,
ARGUMENT_TEXT from apps.fnd_conc_req_summary_v fcr where
-- fcr.phase_code='C' AND
-- FCR.STATUS_CODE = 'C' AND
fcr.PARENT_REQUEST_ID='&request_id';