How to Instal SQLTXPLAIN (SQLT)

SQLTXPLAIN (SQLT)->  Tool that helps to diagnose SQL statements performing poorly.

Download SQLTXPLAIN (SQLT)

10.2, 11.1, 11.2 and 12.1 download
9.2 and 10.1 download

Installation Steps:
********************

After downloading the SQLTXPLAN from the above link..please follow the below steps to install.

SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly.  It is not like AWR or Statspack which provide a system-wide view of performance.  SQLT is very focused and works on one SQL statement at a time.  SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.  In this blog, I will give an overview of SQLT and how to get started with it. 


Installing SQLT SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.
Installation steps:

1. Uninstall a prior version (optional).
This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).



# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql

2. Execute installation script sqlt/install/sqcreate.sql connected as SYS.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql

 

 

 

>> Download SQLT tool from here: Doc ID 215187.1
>> Install SQLT as per SQLT Usage Instructions: Doc ID 1614107.1
>> Identify your SQL ID as per doc How to Determine the SQL_ID for a SQL Statement ( Doc ID 1627387.1 ) <<<<<<<<<<<< identify your query as per document
>> Execute sqlt XTRXEC method for your SQL_ID


Quick example of usage:
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtrxec.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxtrxec.sql 2524255098 sqltxplain_password 
Please refer for more:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=373648185377378&id=1627387.1&_afrWindowMode=0&_adf.ctrl-state=ng46hiduj_641

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=373651036195714&id=1614107.1&_afrWindowMode=0&_adf.ctrl-state=ng46hiduj_682

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=373653549527768&id=215187.1&_afrWindowMode=0&_adf.ctrl-state=ng46hiduj_731

http://www.dbspecialists.com/use-sqlt-to-diagnose-difficult-sql-performance-issues/

 

How To change DBNAME and DBID in Oracle Database?

DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID

Now I want to change the DBID and DB Name of my database.
Please note: If you want to change only the DBID then don't give the parameter dbname in nid command  --> it will only change the dbid for you.




SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
4110800738 ORCL10

I will change my Name of the database to --> PROD

Please follow the below steps to change the name and dbid of the database...

1. Identify the DBID and name of the database
 SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
4110800738 ORCL10

2. Shutdown the database in normal mode

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3. Start the database to mount phase

Please Note: We can issue the command (nid ) during the mount phase of the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             671089104 bytes
Database Buffers          394264576 bytes
Redo Buffers                4603904 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production With the Partitioning, OLAP, Data Mining and Real Application Testing options.



4. In the Terminal Window, execute the nid command

 oracle@dekhatai009 ~
$ which nid
/cygdrive/c/app/dr/product/11.2.0/dbhome_6/bin/nid

 You can use either one of the below commands

oracle@dekhatai009 ~
$ nid target=/

or

oracle@dekhatai009 ~
$ nid target=sys/manager dbname=PROD      <<changing name from ORCL10 to PROD>>

DBNEWID: Release 11.2.0.1.0 - Production on Tue Feb 2 15:21:05 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database ORCL10 (DBID=4110800738)

Connected to server version 11.2.0

Control Files in database:
    C:\APP\IBM\ORADATA\ORCL10\CONTROL01.CTL
    C:\APP\IBM\FLASH_RECOVERY_AREA\ORCL10\CONTROL02.CTL

Change database ID and database name ORCL10 to PROD? (Y/[N]) => Y   <<click Y>>

Proceeding with operation
Changing database ID from 4110800738 to 314608993
Changing database name from ORCL10 to PROD
    Control File C:\APP\DR\ORADATA\ORCL10\CONTROL01.CTL - modified
    Control File C:\APP\DR\FLASH_RECOVERY_AREA\ORCL10\CONTROL02.CTL - modified
    Datafile C:\APP\DR\ORADATA\ORCL10\SYSTEM01.DB - dbid changed, wrote new name
    Datafile C:\APP\DR\ORADATA\ORCL10\SYSAUX01.DB - dbid changed, wrote new name
    Datafile C:\APP\DR\ORADATA\ORCL10\UNDOTBS01.DB - dbid changed, wrote new name
    Datafile C:\APP\DR\ORADATA\ORCL10\USERS01.DB - dbid changed, wrote new name
    Datafile C:\APP\DR\ORADATA\ORCL10\EXAMPLE01.DB - dbid changed, wrote new name
    Datafile C:\APP\DR\ORADATA\ORCL10\TEMP01.DB - dbid changed, wrote new name
    Control File C:\APP\DR\ORADATA\ORCL10\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\APP\DR\FLASH_RECOVERY_AREA\ORCL10\CONTROL02.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to PROD.
Modify parameter file and generate a new password file before restarting.
Database ID for database PROD changed to 314608993.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully. 
after that startup database in nomount mode an change db_name parameter after that you can start database with resetlogs option;

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             671089104 bytes
Database Buffers          394264576 bytes
Redo Buffers                4603904 bytes

---- Database name should be change in parameter file as well----

SQL> alter system set db_name=PROD scope=spfile;

System altered.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             671089104 bytes
Database Buffers          394264576 bytes
Redo Buffers                4603904 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Now check the DBID and database name


SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
 314608993 PROD

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Success !!


 Please refer

https://dbacatalog.wordpress.com/2013/11/14/how-to-change-dbname-and-dbid-in-oracle-database/
https://naveenkumarsr.wordpress.com/2011/03/25/change-oracle-dbid/

Select Count(*) Script in Oracle

Today one of the requirement came, where I have to found out the the record counts of more than 500 tables. Can you believe how much time it will take to encounter select count(*) from ... for each table !! Horrible. So I found the below solution for it and  worked for me fine.

For Example under DEBSHIS schema,I want to find out all the table count details

The above will create a script of all the tables under DEBASHIS and again run that script to collect the count of table.But problem is that for 500 tables,how could you know which count is for which table because in a single function we can added a individual column which will leads to
ORA-00937: not a single-group group function. But yes for 10 or 15 tables  I can suggest you to use the below method.

spool countscript.sql

select 'select count(*) from '||table_name||';' from dba_tables where owner = 'DEBASHIS';

spool off

Then run the script from  SQLPLUS

@countscript.sql

But for >100 tables,please use the below method to get your results.

Connect to your schema or as sysdba and use the below query to get the count of the all the tables in a schema.... Cheers !!

select table_name,
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'DEBASHIS' order by table_name asc; 

For more information please refer..

http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm 

How to Manually fill the gap of the archive redo log files in Production and Standby database

Usually, a Data Guard archive gap sequence is created as the result of a network outage and the connection between the primary and the standby site is lost. The archive gap sequence can also occur during the creation of a standby database. 

Three methods of automatic archive gap detection became available.  The following is a description of their features:
- The archiver process of the primary database polls the standby databases every 60 seconds. This is referred to as ?heartbeat.? During heartbeat, if a standby database reports an archive gap, the archiver of the primary database sends the archived redo log files required to fill the gap. Once the files have been transferred, the site is marked as up to date.

- Archived redo logs are transferred from the primary database to the standby database. On the primary database side, the LGWR or ARCH is responsible for sending the archived redo logs. On the standby site, the RFS process takes the archived redo log file and writes to the host machine. An archived redo log file is uniquely identified by its sequence number and thread number.

- When the RFS process receives an archived redo log file, it compares the sequence number with the sequence number of the previously received archived redo log file. If the sequence number of the current archived redo log file is greater than the sequence number of the last received archived redo log file plus one, a request is sent to the archiver process of the primary database that is providing a list of missing archived redo log files. The archiver of the primary database then retransmits these archived redo log files, thereby resolving the gap sequence.

- For a physical standby database, Oracle introduced a request-response system for gap resolution through the FAL background process. The FAL method of gap resolution is initiated by the Managed Recovery Process (MRP). If the MRP detects a gap in archived redo logs during the application of log files, it requests the archiver process of the primary database to resend the archived redo log files which have caused the gap sequence.

- There are two important parameters required for gap resolution through the FAL background process: fal_server and fal_client. These two parameters need to be set in the initialization parameter file of the physical standby database. fal_server is the service name of the primary database and fal_client is the service name of the standby database.

- The fal_client should be one of the remote archival destinations of the primary database pointing to this standby database. When MRP detects a gap sequence, it requests the archiver process of the primary database, identified by the fal_server parameter, and passes the sequence number of log files causing the archive gap. In addition, it passes the service name defined by the fal_client parameter to the archiver of the primary database. The archiver process of the primary database then retransmits the archived log files requested by the MRP of the standby database.


Manual Procedure...

1. start with detecting gaps in the redo logs by querying the v$archive_gap

SQL> select * from v$archive_gap;

thread#   low_sequence#   high_sequence#
-------- ----------------  ------------------
         1                     30                      34 

   the output indicates our standby database is currently missing log files from sequence 30 to
   34.

2. issue following statement on primary database to locate the archived redo log files. assuming  
    the local archive destination on primary is LOG_ARCHIVE_DEST_1

SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 30 and 34; 

name
----------------------------------

/u02/oraarchive/DB01/arch_t1_s30.dbf  

/u02/oraarchive/DB01/arch_t1_s31.dbf  

/u02/oraarchive/DB01/arch_t1_s32.dbf  

/u02/oraarchive/DB01/arch_t1_s33.dbf  

/u02/oraarchive/DB01/arch_t1_s34.dbf 

3. stop the automatic recovery (MRP) of the data guard

SQL> alter database recover managed standby database cancel;

4. transfer manually the archived log files shown on the step 2 to standby database
5. register that archived log files on standby database

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s30.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s31.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s32.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s33.dbf'; 

SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s34.dbf'; 

6. put the standby database into automatic recovery managed mode

 SQL> alter database recover managed standby database disconnect from session;

7. verify that the gap gets resolved on standby database

SQL> select sequence#, applied from v$archived_log order by sequence#; 

now you figure it out, that your archived log files (ARCLs) has been synchronize with your primary database. as a DBA you must take a notice at log using this SQL statement

SQL> select message from v$dataguard_status where severity like 'Warning';

also on your alert.log


For more please refer the below links...

http://asnsblues.blogspot.in/2013/12/manually-resolving-gaps-oracle-data.html 

http://www.dba-oracle.com/b_data_guard_archived_gap_sequence.htm