Find largest table in oracle

The below query gives you the top 10 largest tables in oracle database.

Script

SELECT * FROM
(select 
 SEGMENT_NAME, 
 SEGMENT_TYPE, 
 BYTES/1024/1024/1024 GB, 
 TABLESPACE_NAME 
from 
 dba_segments
order by 3 desc ) WHERE
ROWNUM <= 10

Multinode adop patching:

 In a multi-node setup on EBS 12.2, we can apply patches individually on each node as well.

If ssh is enabled from primary to secondary node then it is not necessary.

But I will show in this post how we can do it manually. This is an interview question and may be required sometimes.

Scenario 1: Shared Application Tier File System with SSH enabled

primary node run below

adop phase=<phase_name>

Scenario 2: Shared Application Tier File System without SSH enabled

primary node: 

adop phase=<phase_name> allnodes=no action=db

secondary node:

adop phase=<phase_name> allnodes=no action=nodb

Note: In a multi-node environment, the cleanup phase is to be executed on the primary node only as it is the DB phase. All other phases have to be executed on primary and all secondary nodes.

Scenario 3: Non-Shared Application Tier File System with SSH enabled

primary node run below

adop phase=<phase_name>

Scenario 4: Non-Shared Application Tier File System without SSH enabled

primary node: 

adop phase=<phase_name> allnodes=no action=db

secondary node:

adop phase=<phase_name> allnodes=no action=nodb

DBA SCRIPTS

 https://dbaclass.com/monitor-your-db/

Restore INST_TOP if it is deleted or corrupted accidently?

 

Restore INST_TOP if it is deleted or corrupted accidently?

Below steps to restore the INST_TOP if it is deleted or corrupted without using adcfgclone.

Step 1: 

If we have CONTEXT_FILE backup then we can easily restore 

(follow  step-2). If CONTEXT_FILE is not there

 then need to follow below steps to restore CONTEXT_FILE.

  perl /clone/bin/adclonectx.pl retrieve 

Step 2:

Once we restored CONTEXT_FILE then run the auto-config using below script. 

It will recreate INST_TOP without using adcfgcolne.

Run AutoConfig using:

 "perl $AD_TOP/bin/adconfig.pl contextfile=<CONTEXT_FILE>" command 

and it will create the $INST_TOP

 

Updating Apps Password from Backend in Oracle Apps R12.2

In Oracle Apps R12.2 whenever we are changing apps password we need to update the new value in EBSDatasource in Weblogic.

We can either update the same by going into weblogic console and updating it or we can update it through server using below method.


Steps:

1. Start AdminServer 

$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start 

Note: Do not start any other application tier services.
update apps pass:

FNDCPASS apps/apps 0 Y system/Manager SYSTEM APPLSYS apps

2. Change the APPS password in WLS Data Source by running the the following script:

perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl

When prompted, select 'updateDSPassword' to update the new APPS password in the WLS Datasource.

Patch failed with Relinking

Recenty i got an issue with patch..   Patch failed with Relinking..the following are the details

Patch Failed With Relink Error..

Relink of module “RAXTRX” failed.
See error messages above (also recorded in log file) for possible
reasons for the failure.  Also, please check that the Unix userid

Recent Change : cloned from Prdouction

Error MSg :- adrelink error

make -f /apps11i/apapps11i/11510/admin/APPS11i/out/link_ar_20452.mk
/apps11i/apapps11i/11510/ar/11.5.0/bin/RAXTRX

Starting  link of ar executable ‘RAXTRX’ on Wed March 25 02:12:13 IST 2011
gcc -s -L/apps11i/product/806/lib -L/apps11i/product/806/lib/stubs -ldl -o
/apps11i/apapps11i/11510/ar/11.5.0/bin/RAXTRX
/apps11i/apapps11i/11510/ar/11.5.0/lib/raamai.o \
/apps11i/apapps11i/11510/ar/11.5.0/lib/libar.a
/apps11i/apapps11i/11510/fnd/11.5.0/lib/libfnd.a -lsql
/apps11i/product/806/lib/nautab.o /apps11i/product/806/lib/naeet.o
/apps11i/product/806/lib/naect.o /apps11i/product/806/lib/naedhs.o `cat
/apps11i/product/806/lib/naldflgs` -lnetv2 -lnttcp -lnetwork -lncr  -lclient
-lvsn -lcommon -lgeneric -lmm -lnlsrtl3  -lcore4 -lnlsrtl3 -lcore4 -lnlsrtl3
-lnetv2 -lnttcp -lnetwork -lncr  -lclient -lvsn -lcommon -lgeneric  -lepc
-lnlsrtl3  -lcore4 -lnlsrtl3 -lcore4 -lnlsrtl3 -lclient -lvsn -lcommon
-lgeneric -lnlsrtl3  -lcore4 -lnlsrtl3 -lcore4 -lnlsrtl3   `cat
/apps11i/product/806/lib/sysliblist` -ldl -lpthread -lm
/apps11i/product/806/rdbms/lib/defopt.o
/apps11i/product/806/rdbms/lib/ssbbded.o
/usr/bin/ld: cannot find /PROD/806/lib/stubs/libc.so.6
collect2: ld returned 1 exit status
make: *** [/apps11i/apapps11i/11510/ar/11.5.0/bin/RAXTRX] Error 1
Done with link of ar executable ‘RAXTRX’ on Wed March 25 02:12:14 IST 2011

Relink of module “RAXTRX” failed.
See error messages above (also recorded in log file) for possible
reasons for the failure.  Also, please check that the Unix userid

Cause:-libc.so is pointing to Production ..

Resolution/Fix:- Need to change libc.so reference to apps11i

Steps to resolve this Problem
============================

1) login as apuser(applmgr)
2) change directory to $ORACLE_HOME/lib/stubs
3) cat libc.so

chk wther its pointing to the COrrect instance

4) if not Then chanage to Present instance and restart the patch…

In my case :

$ cd $ORACLE_HOME/lib/stubs

$  ls -lrt libc.so
-rw-r–r–  1 apapps11i dba    77 Mar 25 02:42 libc.so

$ cat libc.so
GROUP ( /PROD/806/lib/stubs/libc.so.6   /usr/lib/libc_nonshared.a )

here Our libc.so pointed to PROD instance..
changing entry from /PROD/806/lib/stubs/libc.so.6 to /apps11i/806/lib/stubs/libc.so.6
And restarted the patch… Its applied successfully

Reference Note:-
=================

1275418.1
244256.1

 19c PDB service remove/add:

By default, Container Database (CDB) provides only one service for every Pluggable Database (PDB) for connections, but we can create our own services for various entries of applications.

There're two ways to add a service to a PDB:

1. DBMS_SERVICE Create Service to PDB

It's an administration package for managing services of a database.

2. Srvctl Add Service to PDB

For RAC databases or Oracle Restart, we can use it to manage services.

To add a service to a normal, non-CDB database, you may go for that post.

DBMS_SERVICE.CREATE_SERVICE to PDB

For single-instance, we can only use DBMS_SERVICE to add services to a PDB.

Check Listener

Let's see current status of listener.

[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "aa736f65d66215cce053992aa8c08959" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

As you can see, the default service ORCLPDB of the PDB now registers with listener.

Switch Container

Let's see what PDB we have.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

Let's switch container to the PDB.

SQL> alter session set container=ORCLPDB;

Session altered.

Make sure that we are in the right container.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB

Let's see current services of the container before adding a service to it.

SQL> column con_id format 99
SQL> column pdb format a15
SQL> column network_name format a30
SQL> select con_id, pdb, network_name from cdb_services where pdb is not null and con_id > 2 order by pdb;

CON_ID PDB             NETWORK_NAME
------ --------------- ------------------------------
     3 ORCLPDB         ORCLPDB 

Where dynamic view CDB_SERVICES is derived from DBA_SERVICES.

Add a Service to PDB

Create a service

SQL> exec dbms_service.create_service('ERP', 'ERP');

PL/SQL procedure successfully completed.

The service is added to current PDB, no matter a single-instance or a RAC database.

There're more variations on adding a failover service to the cluster by DBMS_SERVICE.

Start the service

SQL> exec dbms_service.start_service('ERP');

PL/SQL procedure successfully completed.

For RAC database, you should start the services on all instances in the cluster:

SQL> exec dbms_service.start_service('ERP', DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.

DBMS_SERVICE.START_SERVICE Automatically in PDB

To automatically start the service after PDB is open, you just need to save current state for the PDB.

SQL> alter pluggable database orclpdb save state;

Pluggable database altered.

For RAC database, you should save the state of PDB on all instances in the cluster:

SQL> alter pluggable database orclpdb save state instances=all;

Pluggable database altered.

There're more variations on saving state of a PDB or all PDB.

Check services of the container

Let's see current services of the container after adding a service to it.

SQL> select con_id, pdb, network_name from cdb_services where pdb is not null and con_id > 2 order by pdb;

CON_ID PDB             NETWORK_NAME
------ --------------- ------------------------------
     3 ORCLPDB         ERP
     3 ORCLPDB         ORCLPDB

Let's check online services of current PDB.

SQL> column name format a30;
SQL> column network_name format a30;
SQL> select name, network_name from v$active_services;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
orclpdb                        orclpdb
ERP                            ERP

For RAC databases, you may see the information like this:

SQL> select inst_id, name, network_name from gv$active_services;

   INST_ID NAME                           NETWORK_NAME
---------- ------------------------------ ------------------------------
         1 orclpdb                        orclpdb
         1 ERP                            ERP
         2 orclpdb                        orclpdb
         2 ERP                            ERP

Check Listener Again

Let's see current status of listener.

[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ERP" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "aa736f65d66215cce053992aa8c08959" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

We have a new service which registers with the new listener.

Connection Test

We use SQL*Plus to test the connection from an external client.

C:\Users\edchen>sqlplus hr/hr@192.168.0.11:1521/erp
...
SQL> select count(*) from employees;

  COUNT(*)
----------
       107

We have connected to the PDB.

Remove Service

If you want to remove the service from the PDB, you have to stop it first.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> exec dbms_service.stop_service('ERP', DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.

Then delete it.

SQL> exec dbms_service.delete_service('ERP');

PL/SQL procedure successfully completed.