Oracle Applications DBA

Friday, 12 June 2015

How to check tablespace with sysdate - Oracle database

select substr(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-
(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT
(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT
(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
sysdate
from dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 1;

How To Rename A Diskgroup On ASM 11gR2

SOLUTION:

1) First of all, the desired diskgroup needs to be dismounted (from each node if this is a RAC configuration):

# asmcmd umount DATA

2) Verify that the desired diskgroup was dismounted:

# asmcmd lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB
Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 32756 31828 244 15792 0 N DATA_OCR/
MOUNTED EXTERN N 512 4096 1048576 16378 10134 0 10134 0 N LOBDATA/

3) Then execute the rename statement:

# renamedg phase=both dgname=DATA newdgname=DG_PROD_DATA verbose=true
Parsing parameters..
Parameters in effect:
Old DG name : DATA
New DG name : DG_PROD_DATA
Phases :
Phase 1
Phase 2
Discovery str : (null)
Clean : TRUE
Raw only : TRUE
renamedg operation: phase=both dgname=DATA newdgname=DG_PROD_DATA verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA1 with disk
number:0 and timestamp (32925601 972709888)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA2 with disk
number:1 and timestamp (32925601 972709888)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA3 with disk
number:2 and timestamp (32925601 972709888)
Checking for hearbeat...
Rediscovering
the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA1 with disk
number:0 and timestamp (32925601 972709888)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA2 with disk
number:1 and timestamp (32925601 972709888)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA3 with disk
number:2 and timestamp (32925601 972709888)
Checking if the diskgroup is mounted
Checking disk number:0
Checking disk number:1
Checking disk number:2
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:DATA1
Modifying the header
Looking for ORCL:DATA2
Modifying the header
Looking for ORCL:DATA3
Modifying the header
Completed phase 2
Terminating kgfd context 0x2af21517e0a0

4) Finally, mount the renamed diskgroup and validate that it was mounted (on each node if this is a RAC configuration):

# asmcmd mount DG_PROD_DATA

# asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB
Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 32756 31828 244 15792 0 N DATA_OCR/
MOUNTED EXTERN N 512 4096 1048576 24567 14267 0 14267 0 N DG_PROD_DATA/
MOUNTED EXTERN N 512 4096 1048576 16378 10134 0 10134 0 N LOBDATA/

<renamedg tool cannot rename the associated ASM disk(s) name(s). >>>
IMPORTANT : With renamedg the diskgroup name changes. As the ASM File names ( like datafiles / controlfiles etc
), include the diskgroups name, you need to take care ( update pfile or rename datafiles ) from the RDBMS
instance, after renamedg.

How to change time in Solaris Operating System

root@testapp1:~# date 051514412015

Friday, May 15, 2015 02:41:00 PM CAT

How to Retrieve deleted rows in table Oracle Database

You can use FLASHBACK TABLE to retrieve your rows. But before that you must enable row movement by following query

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

And there after firing the below query for the particular timestamp when your rows were existing at that time.

FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('17-10-2011 12:19:00','DD-MM-YYYY HH24:MI:SS');

Example: select * from apps.PER_CONTACT_RELATIONSHIPS as of timestamp to_timestamp('31-05-2015 04:00:00','DD-MM-YYYY HH24:MI:SS');

FLASHBACK is used to retrive the dropped tables.

Use the following to retrieve the deleted and committed rows

select * from table_name as of timestamp to_timestamp(sysdate-(360/1440))

The above sample SQL will fetch the status of data in the table 6 hours earlier.

To check recently changed Profile Options SQL Query Oracle EBS R12

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
order by last_update_date desc, short_name, level_set;

WARNING: oradism not set up correctly. Dynamic ISM can not be locked. Please setup oradism, or unset sga_max_size

========================================================================
APPLIES TO:
Oracle Database Enterprise
Edition Version
10.1.0.2 to 10.2.0.4 [Release 10.1 to 10.2]
Oracle Solaris on x86 (32bit)
Oracle Solaris on SPARC (64bit)
Oracle Solaris on x8664
(64bit)
z*OBSOLETE: Fujitsu PRIMEPOWER Solaris
Oracle Solaris on SPARC (32bit)
z*OBSOLETE: Sun Trusted Solaris (Secure)
Fujitsu PRIMEPOWER Solaris
Sun Solaris x86 (32bit)
Sun Solaris SPARC (64bit)
Sun Trusted Solaris (Secure)
Sun Solaris x8664
(64bit)
Sun Solaris SPARC (32bit)
========================================================================
The database alert log will show:
Tue Sep 23 21:03:55 2008
SUCCESS: diskgroup DATA1 was dismounted
SUCCESS: diskgroup DATA1 was dismounted
Tue Sep 23 21:03:55 2008
Errors in file /u01/app/oracle/product/10.2.0/db_
dev3/admin/DEV3/bdump/dev3_j000_1012.trc:
ORA01115:
IO error reading block from file 11 (block # 722525)
ORA01110:
data file 11: '+DATA1/dev3/datafile/development.8115.666100645'
ORA15078:
ASM diskgroup was forcibly dismounted
...
...<more of the above for file 1 as well, different blocks>
...
Tue Sep 23 21:03:56 2008
Errors in file /u01/app/oracle/product/10.2.0/db_dev3/admin/DEV3/bdump/dev3_l
gwr_14502.trc:
ORA00340:
IO error processing online log 1 of thread 1
ORA00345:
redo log write error block 11022 count 12
ORA00312:
online log 1 thread 1: '+DATA1/dev3/onlinelog/group_1.8102.666102843'
ORA15078:
ASM diskgroup was forcibly dismounted
Tue Sep 23 21:03:56 2008
LGWR: terminating instance due to error 340

WARNING: WARNING:
oradism not set up correctly.
Dynamic ISM can not be locked. Please
setup oradism, or unset sga_max_size.
[diagnostic 0, 8, 101]
...
WARNING: Detected too many memory locking problems.
WARNING: Performance degradation may occur.
========================================================================

CAUSE:
These errors and were caused by an improper setup for 'oradism'.
The file permissions for the executable '$ORACLE_HOME/bin/oradism' were not properly set possibly
due to
'root.sh' was not executed after Oracle Universal Installer (OUI) installed the Oracle Home, however this could not
be validated.
========================================================================
Solution:
After fixing the file permissions of '$ORACLE_HOME/bin/oradism' the database instance the issue has stopped.
The permissions for 'oradism' must be 6550 with the setid for the group:
e.g. (rsrs1
root oinstall 129 12 Feb 16 2008 oradism)
========================================================================
Reference: ASM Forcibly Dismounts Diskgroups or Causes Disks to be Offlined If 'oradism' is
Improperly Set (Doc ID 744869.1)

AutoConfig could not successfully execute the following scripts: afcpctx.sh INSTE8_PRF 137

======================================================================
[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /data/R12base/inst/apps/PREPROD_devdb/admin/install
afcpctx.sh INSTE8_PRF 137
ibywebprf.sh INSTE8_PRF 1
======================================================================

This error message is from adcfgclone logfile. So you're also not able to proceed further ......

adcfgclone.pl is failing on two scripts "afcpctx.sh & ibywebprf.sh". After long troubleshooting find out the culprits.

==> afcpctx.sh this script is failing because of missing library file

- If we run this script manually its calling fnd binary "FNDCPUCF" from $FND_TOP/bin and find out that this binary has some missing libaries

Solution:

bash-3.00$ cd $FND_TOP/bin

bash-3.00$ ldd -d FNDCPUCF
libnnz10.so => /data/R12base/apps/tech_st/10.1.2/lib32/libnnz10.so
libkstat.so.1 => /lib/libkstat.so.1
libnsl.so.1 => /lib/libnsl.so.1
libsocket.so.1 => /lib/libsocket.so.1
libgen.so.1 => /lib/libgen.so.1
libdl.so.1 => /lib/libdl.so.1
libsched.so.1 => /usr/lib/libsched.so.1
libaio.so.1 => /lib/libaio.so.1
librt.so.1 => /lib/librt.so.1
libm.so.1 => /lib/libm.so.1
libclntsh.so.10.1 => /data/R12base/apps/tech_st/10.1.2/lib/libclntsh.so.10.1 - wrong ELF class: ELFCLASS64
libpthread.so.1 => /lib/libpthread.so.1
libc.so.1 => /lib/libc.so.1
libmp.so.2 => /lib/libmp.so.2
libmd.so.1 => /lib/libmd.so.1
libscf.so.1 => /lib/libscf.so.1
libdoor.so.1 => /lib/libdoor.so.1
libuutil.so.1 => /lib/libuutil.so.1
libm.so.2 => /lib/libm.so.2
/platform/SUNW,Sun-Fire-V440/lib/libc_psr.so.1
/platform/SUNW,Sun-Fire-V440/lib/libmd_psr.so.1
bash-3.00$

- To solve this follow the steps

bash-3.00$ echo $ORACLE_HOME
/appl/R12base/apps/tech_st/10.1.2

bash-3.00$ ln -s $ORACLE_HOME/lib/ldflags $ORACLE_HOME/lib32/ldflags

bash-3.00$ ls -ltr $ORACLE_HOME/lib32/ldflags
lrwxrwxrwx 1 appclone dbaclone 45 Jun 10 13:16 /appl/R12base/apps/tech_st/10.1.2/lib32/ldflags -> /appl/R12base/apps/tech_st/10.1.2/lib/ldflags

bash-3.00$ genclntsh

bash-3.00$ relink all

- After performing all above steps again check :

bash-3.00$ cd $FND_TOP
bash-3.00$ ls
3rdparty bin forms html java log media out perl resource sql xml
admin fndenv.env help include lib mds mesg patch reports secure usrxit
bash-3.00$ cd bin
bash-3.00$ ls
bash-3.00$ ldd -d FNDCPUCF
libnnz10.so => /appl/R12base/apps/tech_st/10.1.2/lib32/libnnz10.so
libkstat.so.1 => /lib/libkstat.so.1
libnsl.so.1 => /lib/libnsl.so.1
libsocket.so.1 => /lib/libsocket.so.1
libgen.so.1 => /lib/libgen.so.1
libdl.so.1 => /lib/libdl.so.1
libsched.so.1 => /usr/lib/libsched.so.1
libaio.so.1 => /lib/libaio.so.1
librt.so.1 => /lib/librt.so.1
libm.so.1 => /lib/libm.so.1
libclntsh.so.10.1 => /appl/R12base/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1
libpthread.so.1 => /lib/libpthread.so.1
libc.so.1 => /lib/libc.so.1
libmp.so.2 => /lib/libmp.so.2
libmd.so.1 => /lib/libmd.so.1
libscf.so.1 => /lib/libscf.so.1
libm.so.2 => /lib/libm.so.2
libdoor.so.1 => /lib/libdoor.so.1
libuutil.so.1 => /lib/libuutil.so.1
/platform/SUNW,Sun-Fire-V440/lib/libc_psr.so.1
/platform/SUNW,Sun-Fire-V440/lib/libmd_psr.so.1

========================================================================