How to Reorganize SYS.AUD$ Table

How to Reorganize SYS.AUD$ Table?

Please follow the metalink note ID: [ID 166301.1]

E-Business Suite Login Gives App-Fnd-01564

E-Business Suite Login Gives App-Fnd-01564: Oracle Error 6550 In Afpoload

Please follow the metalink note ID :[ID 1097297.1]

Master Note for RAC

Master Note for Real Application Clusters (RAC) Oracle Clusterware and Oracle Grid Infrastructure
 

Please follow the metalink  note   [ID 1096952.1]

How to tune sql using DBMS_SQLTUNE

The DBMS_SQLTUNE package provides the interface to tune SQL statements.

You use the CREATE_TUNING_TASK Functions to create a tuning task for tuning a single statement or a group of SQL statements.

The EXECUTE_TUNING_TASK Procedure executes a previously created tuning task.

The REPORT_TUNING_TASK Function displays the results of a tuning task.

You use the SCRIPT_TUNING_TASK Function to create a SQL*PLUS script which can then be executed to implement a set of Advisor recommendation

SQL> variable stmt_task VARCHAR2(64);

SQL> variable sts_task VARCHAR2(64);

SQL> EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 21965, end_snap => 21966, sql_id => 'at18ft6rsb6n4');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task);

How to check deprecated parameters

How to check deprecated  parameters  in any version of oracle:

Solution: From v$parameter view
Heres a sample from version 11.1.0.7

SQL> select NAME from v$parameter where ISDEPRECATED='TRUE';

NAME
--------------------------------------------------------------------------------
lock_name_space
instance_groups
resource_manager_cpu_allocation
buffer_pool_keep
buffer_pool_recycle
max_commit_propagation_delay
log_archive_start
standby_archive_dest
log_archive_local_first
parallel_server
parallel_server_instances

NAME
--------------------------------------------------------------------------------
fast_start_io_target
serial_reuse
max_enabled_roles
remote_os_authent
global_context_pool_size
cursor_space_for_time
plsql_v2_compatibility
plsql_debug
background_dump_dest
user_dump_dest
commit_write

NAME
--------------------------------------------------------------------------------
sql_trace
parallel_automatic_tuning
sql_version
drs_start

26 rows selected.

Tablespace Utilisation Query

 The below query gives a list of the tablespaces, total size, free size in MB, used size in MB and  tablespace  percentage full. This is a very useful query whenever I need  to make any changes to the tablespaces add or decrease the size of existing datafiles.

SQL> select a.tablespace_name, assigned_space "ASSIGNED (MB)", (nvl(free_space,0) + INCRM) "FREE (MB)", (assigned_space - nvl(free_space,0) - INCRM) "USED (MB)",
  ((assigned_space - nvl(free_space,0) - INCRM) / assigned_space)*100 "% Full"
from
(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM
from dba_data_files group by tablespace_name) A,
(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space
from dba_free_space a group by tablespace_name) B
where b.tablespace_name(+)=a.tablespace_name
union all select a.tablespace_name, assigned_space "ASSIGNED (MB)", (nvl(free_space,0) + INCRM) "FREE (MB)", (assigned_space - nvl(free_space,0) - INCRM) "USED (MB)",
((assigned_space - nvl(free_space,0) - INCRM ) / assigned_space)*100 "% Full"
from
(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,
sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM from dba_temp_files group by tablespace_name) A,
(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space
from dba_free_space group by tablespace_name) B
where b.tablespace_name(+)=a.tablespace_name;

Adcfgclone.pl appsTier error: RC-50014: Fatal: Execution of AutoConfig

While performing 11i cloning  the following  error has  occured.

Adcfgclone.pl appsTier error: RC-50014: Fatal: Execution of AutoConfig

Solution:

Please refer to metalink note id [ID 1142333.1]

How to know timing of SQL statements

How to know how long does the sql statement take to return the result.

Solution:
set timing on.

sql> run  the sql statement

Adding Services to CRS stack in RAC

I was trying to add RAC database  services to the CRS stack after the RAC database has been built. I was getting the  following errors:

au11qapeo0tels2:RACDB2:/opt/app/crs/oracle/product/11.1.0/crs/bin>srvctl add service -d RACDB -s RACBD2 -r RACDB2 -a RACDB2 -P none
PRKP-1087 : RACDB2 is specified as both preferred and available instance lists for service RACDB2

Assumptions for adding the services
--------------------------------------------
RAC database: RACDB
Instances : RACDB1, RACDB2

Service_name : RACDB1 and RACDB2

Service RACDB1 will be running on only instance : RACDB1

Service RACDB2 will be running on only instance : RACDB2

Solution:

I Have ran using the following commands and it worked: srvctl add service -d RACDB -s RACDB2 -r RACDB2 -P none srvctl start service -d RACDB -s RACDB1 -i RACDB1 srvctl start service -d RACDB -s RACDB2 -i RACDB2 HA Resource Target State ----------- ------ ----- ebr_vip1 ONLINE ONLINE on node2 ora.RACDB.RACDB1.RACDB1.srv ONLINE ONLINE on node1 ora.RACDB.RACDB1.cs ONLINE ONLINE on node1 ora.RACDB.RACDB1.inst ONLINE ONLINE on node1 ora.RACDB.RACDB2.RACDB2.srv ONLINE ONLINE on node2 ora.RACDB.RACDB2.cs ONLINE ONLINE on node2 ora.RACDB.RACDB2.inst ONLINE ONLINE on node2 ora.RACDB.db ONLINE ONLINE on node1 ora.node1.LISTENER_node1.lsnr ONLINE ONLINE on node1 ora.node1.gsd ONLINE ONLINE on node1 ora.node1.ons ONLINE ONLINE on node1 ora.node1.vip ONLINE ONLINE on node1 ora.node2.LISTENER_node2.lsnr ONLINE ONLINE on node2 ora.node2.gsd ONLINE ONLINE on node2 ora.node2.ons ONLINE ONLINE on node2 ora.node2.vip ONLINE ONLINE on node2 node1:RACDB1:/opt/app/crs/oracle/product/11.1.0/crs/bin>srvctl status service -d RACDB Service RACDB1 is running on instance(s) RACDB1 Service RACDB2 is running on instance(s) RACDB2

ERROR IN INVOKING TARGET 'RELINK' OF MAKEFILE, LD: LIBRARY -LDCE: NOT FOUND

Problem Description: error when applying patchset 10205 on top of 10201. error invoking target relink of makefile .
ERROR IN INVOKING TARGET 'RELINK' OF MAKEFILE, LD: LIBRARY -LDCE: NOT FOUND

ld: fatal: library -lclntsh: not found

applied Patch 4589082: patch for 10201 binaries

10201 install was successful tried applying patchset 10205 failed . followed the below metalink note

Install 10gR2 on Solaris 64 bits fails with: 'library -ldce: not found' [ID 333348.1].


found the below error from this file /opt/app/oracle/product/10.2.0/db_1/lib/clntsh.fileslist

relink found an unidentified object in the library file
Undefined first referenced
symbol in file
nnfdboot /opt/app/oracle/product/10.2.0/db_1/lib32/libn10.a(nnfgt.o)
ld: fatal: Symbol referencing errors. No output written to /opt/app/oracle/product/10.2.0/db_1/lib32/libclntsh.so.10.1

Cause:
This is an issue with Oracle 10.2.0.5 and DCE installed on the given computer. It would also work if you first install Oracle all the way to 10.2.0.5 before installing DCE.


Solution:
 Here is a work around as recommended by Oracle Development
1. This leaves DCE running and we are going to reinstall all the back to Oracle 10.2.0.1
2. Unzip the file that has the 10.2.0.1 install files
3. Please install Patch 4589082 onto the "staging area" of the Oracle 10.2.0.1 install. Please thoroughly follow the directions to a "tee"
4. Install Oracle 10.2.0.1 process
5. After a successful install, within the Oracle 10.2.0.1 ORACLE_HOME directory, please remove these files:
lib/libncds10.a
network/install/ldflags.cds
lib/libndce10.a
network/install/ldflags.dce
6. Please install the Oracle 10.2.0.5 Patchset

ORA-04063: view "SYS.DBA_REGISTRY" has errors

One day I was building 11.1.0.7 standalone database. I messed up the catalog and catproc views and both of the packages became Invalid and I was unable to query DBA_REGISTRY. raised an SR with Oracle and the solution is as follows:

select * from dba_registry;

ORA-04063: view "SYS.DBA_REGISTRY" has errors

Causes:
The probable causes for dba_registry components became invalid could be any of the following.

• Applied a patch and after the patch application because of some dependent object status change registry can become invalid

• Installed a new component and the new component installation got failed then registry components could become invalid

• catalog.sql or catproc.sql was not successfully ran after database creation. Any of them would have failed somewhere or any of the dependent object got invalid afterward

Normally this will be resolved by re- running catalog.sql and then catproc.sql as sysdba as per below metalink note
Note: 457861.1 CATPROC - Packages and Types Invalid in Dba_registry

This issue can happen if you run catproc.sql first and then catalog.sql. It should be run as following. This note is applicable to 11g database as well.

Solution:
$sqlplus "/as sysdba"
SQL> startup upgrade
-- Use 'startup migrate' if database version is lower than 9.2.
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql