Oracle

Thursday, September 27, 2012

Datapump PARTITION_OPTION parameter

PARTITION_OPTIONS= NONE | DEPARTITION | MERGE

· NONE: The partitions are created exactly as they were on the system the export was taken from.

· DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-) partition name.

· MERGE: Combines all partitions into a single table.

Here I want to Import schema1 partitioned tables to schema2 non-partitioned tables, to do so

1) Take data pump complete Export of schema1

2) Import into schema2 as below

NOTE : Here “schema2” is not there but creates automatically during the Datapump import process

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

$ impdp dumpfile=xxx.dmp directory=EXP remap_schema=schema1:schema2 PARTITION_OPTIONS=merge

Import: Release 11.2.0.2.0 - Production on Thu Sep 27 20:38:11 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_05" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_05": /******** AS SYSDBA dumpfile=xxx.dmp directory=EXP remap_schema= schema1:schema2 PARTITION_OPTIONS=merge

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCHEMA2"."TABLE1":"SYS_P23561" 14.92 KB 34 rows

-------- OUTPUT TRIMMED ------------------

Job "SYS"."SYS_IMPORT_FULL_05" completed with 12 error(s) at 20:39:53

A new schema2 will be created and all the partitioned tables are created as Single tables (non-partitioned)

Tuesday, September 18, 2012

STEPS TO CREATE ORACLE DATABASE MANUALLY ON LINUX

Step 1:
Prepare the database creation script. Following is my script "testdb.sql"

CREATE DATABASE TESTDB

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/TESTDB/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/TESTDB/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/TESTDB/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/TESTDB/system01.dbf' SIZE 200M,

'/opt/oracle/TESTDB/users01.dbf' SIZE 100M

sysaux datafile '/opt/oracle/TESTDB/sysaux01.dbf' SIZE 200M

undo tablespace undotbs1

datafile '/opt/oracle/TESTDB/undotbs01.dbf' SIZE 100M

CHARACTER SET WE8MSWIN1252

;

NOTE : You can get this similar script , when you perform ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’, we can edit this script.

Step 2:

Create all the necessary directories.

Oracle:/opt/oracle $ mkdir TESTDB

Similarly create all necessary directories

Step 3:

Prepare the init file(pfile) [initTESTDB.ora]

TESTDB.__db_cache_size=436207616

TESTDB.__java_pool_size=4194304

TESTDB.__large_pool_size=4194304

TESTDB.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment

TESTDB.__pga_aggregate_target=432013312

TESTDB.__sga_target=641728512

TESTDB.__shared_io_pool_size=0

TESTDB.__shared_pool_size=184549376

TESTDB.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/TESTDB/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/opt/oracle/TESTDB/control01.ctl','/opt/oracle/fra/TESTDB/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='TESTDB'

*.db_recovery_file_dest='/opt/oracle/fra'

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest='/opt/oracle/'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'

#*.local_listener='LISTENER_TESTDB'

*.memory_target=1073741824

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS'

Step 4:

Now perform the following steps:

$ export ORACLE_SID=TESTDB

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008

Connected to an idle instance.

SQL >

SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initTESTDB.ora

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1263176 bytes

Variable Size 167774648 bytes

Database Buffers 427819008 bytes

Redo Buffers 7122944 bytes

SQL> @testdb.sql

Database created.

Step 5:

So your database is created. Now just run the catalog.sql and catproc.sql scripts.

You will find the in $ cd $ORACLE_HOME/rdbms/admin

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

This will create all dictionary views, now verify

SQL> select name from v$database;

NAME

---------

TESTDB

Okay, now your database is ready to use.

Steps to change database name only

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.7 (Tikanga)

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

Now, I want to change only database name from DUMMYDB to DEMODB for my standalone dev database

Follow the same steps from my last post Steps to Rename database name and DBID

NOTE: This is tested on my Standalone Development database, for RAC please see [Doc ID 464922.1]

STEP 1 ) Shut down the database and open database in mount

SQL> select name from v$database;

NAME

---------

DUMMYDB

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

DUMMYDB OPEN

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 683674632 bytes

Database Buffers 377487360 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL>

Step 2) Invoke the DBNEWID utility (nid) specifying the new database name in DBNAME and set=YES

$ nid TARGET=/ dbname=DEMODB SETNAME=YES

DBNEWID: Release 11.2.0.2.0 - Production on Tue Sep 18 14:00:18 2012

Connected to database DUMMYDB (DBID=471263051)

Connected to server version 11.2.0

Control Files in database:

/opt/oracle/oradata/TESTDB/control01.ctl

/opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl

Change database name of database DUMMYDB to DEMODB? (Y/[N]) => Y

Proceeding with operation

Changing database name from DUMMYDB to DEMODB

Control File /opt/oracle/oradata/TESTDB/control01.ctl - modified

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - modified

Datafile /opt/oracle/oradata/TESTDB/system01.db - wrote new name

Datafile /opt/oracle/oradata/TESTDB/users01.db - wrote new name

Datafile /opt/oracle/oradata/TESTDB/sysaux01.db - wrote new name

Datafile /opt/oracle/oradata/TESTDB/undotbs01.db - wrote new name

Control File /opt/oracle/oradata/TESTDB/control01.ctl - wrote new name

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - wrote new name

Instance shut down

Database name changed to DEMODB.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

Step 3) Make changes to pfile and password file and opened the database with resetlogs option

$ cd $ORACLE_HOME/dbs

Edit pfile ----> initDEMODB.ora

Vi initDEMODB.ora

Change ‘dbname’

NOTE: database need not to be open with RESTLOGS option

$ export ORACLE_SID=DEMODB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 14:03:10 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 616565768 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

Database mounted.

Database opened.

SQL> select name from v$database;

NAME

---------

DEMODB

Now, your database name has been changed :-)

See: How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Doc ID 863800.1)

Monday, September 17, 2012

Steps to Change database name and DBID

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.7 (Tikanga)

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

Here I’m renaming TESTDB database name to DEMODB database including DBID

STEP 1 ) Shut down the database and open database in mount

SQL> select name from v$database;

NAME

---------

TESTDB

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

TESTDB OPEN

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 683674632 bytes

Database Buffers 377487360 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL>

Step 2) Invoke the DBNEWID utility (nid) specifying the new database name in DBNAME

$ nid target=/ dbname=DEMODB

DBNEWID: Release 11.2.0.2.0 - Production on Mon Sep 17 14:45:30 2012

Connected to database TESTDB (DBID=2577169847)

Connected to server version 11.2.0

Control Files in database:

/opt/oracle/oradata/TESTDB/control01.ctl

/opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl

Change database ID and database name TESTDB to DEMODB? (Y/[N]) => y

Proceeding with operation

Changing database ID from 2577169847 to 3790527754

Changing database name from TESTDB to DEMODB

Control File /opt/oracle/oradata/TESTDB/control01.ctl - modified

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - modified

Datafile /opt/oracle/oradata/TESTDB/system01.db - dbid changed, wrote new name

Datafile /opt/oracle/oradata/TESTDB/sysaux01.db - dbid changed, wrote new name

Datafile /opt/oracle/oradata/TESTDB/users01.db - dbid changed, wrote new name

Datafile /opt/oracle/oradata/TESTDB/undotbs01.db - dbid changed, wrote new name

Control File /opt/oracle/oradata/TESTDB/control01.ctl - dbid changed, wrote new name

Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to DEMODB.

Modify parameter file and generate a new password file before restarting.

Database ID for database DEMODB changed to 3790527754.

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.

Step 3) Make changes to pfile and password file and opened the database with resetlogs option

$ cd $ORACLE_HOME/dbs

Edit pfile ----> initDEMODB.ora

Vi initDEMODB.ora

Change ‘dbname’

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 17 19:28:23 2012

Connected to an idle instance.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2233336 bytes

Variable Size 616565768 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME

---------

DEMODB

Also see:

Changing Database Id (DBID)

Steps tochange database name only

How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Doc ID 863800.1)

Thursday, August 16, 2012

ORA-12162: TNS:net service name is incorrectly specified

oracle@oradev801:/opt/oracle INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 16 16:57:18 2012

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

Strange!!

I thought it is a problem with the TNS entry, but after doing some research I came to know that it was a problem with ORACLE_SID, May be ORACLE_SID is not properly set or it was not exported.

oracle@oradev801:/opt/oracle INT$ export ORACLE_SID=XXXXXX

oracle@oradev801:/opt/oracle INT$ echo $ORACLE_SID

XXXXXX

oracle@oradev801:/opt/oracle INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 16 16:57:18 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

XXXXXX

On WINDOWS: set ORACLE_SID=XXXXXX

Wednesday, August 8, 2012

ORA-12906, DROPPING DEFAULT TEMPORARY TABLESPACE

First find the Default temporary tablespace in your database

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL > select property_value from database_properties Where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEMP

Check the size of TEMP tablespace:

SQL > SELECT tablespace_name, file_name, bytes/1024/1024/1024 GB FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

TABLESPACE_NAME FILE_NAME GB

-------------------------- -------------------------------- ------------

TEMP /opt/oracle/oradata/temp01.dbf 6

For Oracle 8 and above, the following query will return all users and their SIDs which are doing a sort:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

To see USED and FREE space of it:

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from v$temp_space_header;

GBUSED GBFREE

------------ ---------

1.25 4.75

Now, try to drop the Default temp tablespace

SQL> DROP TABLESPACE temp;

DROP TABLESPACE temp

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

In order to drop and recreate the default temp tablepsace first thing is to create another temporary tablespace and make that tablespace as default tablespace for the database and then drop the old default tablespace(TEMP)

SQL> CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/opt/oracle/oradata/temp_new01.dbf' size 5G reuse AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

Database altered.

Now drop the TEMP tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Monday, August 6, 2012

Find the delay of standby from primary

Here is a quick way to find the current timestamp from the current SCN.

http://www.chandu208.blogspot.com/2012/03/finding-current-scn-of-database.html

On primary:

SQL> select to_char(CURRENT_SCN) from v$database;

TO_CHAR(CURRENT_SCN)

————————————

12345678909

SQL> select scn_to_timestamp(48133107062) from dual;

SCN_TO_TIMESTAMP(12345678909)

—————————————————

19-JUN-11 08.40.27.000000000 AM

SQL>

And do the same on standby database, then find the difference in time.

If you set the delay parameter then find it in the parameter file

Thanks !!

Thursday, August 2, 2012

import only one table ROWS

TASK : I want to import 1 schema table rows from a full or full schema dump

SCHEMA: Chandra

Table : Demo

Dumpfile : dummy.dmp

Directory = TEST

$ impdp dumpfile=dummy.dmp directory=TEST content=data_only tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 14:59:52 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39166: Object SYS.DEMO was not found.

REASON : Here we are running this import as sysdba user so oracle will find and import the sys.table rows , since it is not found thrown an error above

$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log schemas=chandra

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:00:46 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-00010: multiple job modes requested, schema and tables

REASON : I tried to import table rows by using both SCHEMAS at the same time

$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

Username: Chandra

Password : *********

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_02": /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=EXPORT_DIR content=data_only tables=demo logfile=impdp_0802.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "CHANDRA"."DEMO" 692.9 KB 15700 rows

Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58

Here it searched for Chandra.demo table and imported rows

(or)

impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=chandra.DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_02": /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=TEST content=data_only tables=chandra.demo logfile=impdp_0802.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "CHANDRA"."DEMO" 692.9 KB 15700 rows

Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58

NOTE : schema “CHANDRA” from which you are importing should have ‘imp_full_database’ role granted, also READ and WRITE permissions on TEST Directory

Thank you !!! :-)

Thursday, July 26, 2012

Exporting(expdp) & Importing(impdp) specified objects Only

EXPORT/IMPORT only schema Tables :

Expdp system/****** dumpfile=XXXXX.dmp directory=EXPORT_DIR schemas=DEMO include=TABLE

generating a DDL for above Tables :

impdp system/***** dumpfile=xxxxx.dmp directory=EXPORT_DIR schemas=DEMO sqlfile=table_DDL.sql

If we need only for 1 table DDL from entire Tables then,

impdp system/***** dumpfile=xxxxx.dmp directory=EXPORT_DIR schemas=DEMO sqlfile=tables_1.sql include=TABLE:\"IN $\'CUSTOMER\'$\"

The above command creates a file tables_1.sql which contains only DEMO.CUSTOMER table DDL

NOTE: SQLFILE parameter works only for import(impdp) and should have dumpfile available

Export and Import Only CONSTRAINTS:

Expdp system/wfargo#123 dumpfile=xxxxx.dmp directory=EXPORT_DIR schemas= DEMO include=CONSTRAINT

Here in the above I’m exporting only DEMO schema constraints

If we want this DEMO schema DDL in a file then run the command below:

impdp system/****** dumpfile=XXXXX.dmp directory=EXPORT_DIR schemas=DEMO SQLFILE=constraint_DDL.sql

Then a file constraint_DDL.sql will be created contains all DEMO schema constraints

You can find Creating a DDL for an INDEX here :

http://chandu208.blogspot.com/2012/02/datapump-sqlfile-parameter-during.html

Monday, July 16, 2012

Oracle TKPROF

The TKPROF (Transient Kernel Profiler) is an Oracle database utility which converts Oracle trace files into a more human readable form.

Recently a developer wants me to send his trace file output for a particular trace file in a flatfile

Then I performed the below steps:

SQL> show parameter diag;

NAME TYPE VALUE

---------------------------------- ----------- ------------------------------

diagnostic_dest string /opt/oracle/app

Then go to diag location and go to the trace location and perform the following

$ tkprof DEV71_ora_24961_INFO_PKG.trc dba.txt sys=no

Here in the above I’m putting the trace file output in a dba.txt file

Tkprof parameters:

TRACEfile : The name of the TRACE file containing the statistics by SQL_TRACE.

Output_file: The name of the file where TKPROF writes its output.

SORT= parameters

The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.

PRINT=number

The number of statements to include in the output. included, TKPROF will list all statements in the output.

EXPLAIN=username/password@sid

Run the EXPLAIN PLAN on the user's SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.

INSERT= filename

This option creates a script to create a table and store the TRACE file statistics for each SQL statement Traced.

RECORD= filename

This option will produce a file of all the user's SQL statements.

SYS= YES/NO

This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.

Sort – It will be useful if we want to see the top SQL which are consumed the most resources. Resource like CPU usage, disk usage etc.

The following are the data elements available for sorting:

. prscnt – The number of times the SQL was parsed.

· prscpu – The CPU time spent parsing.

· prsela – The elapsed time spent parsing the SQL.

· prsdsk – The number of physical reads required for the parse.

· prsmis – The number of consistent block reads required for the parse.

· prscu – The number of current block reads required for the parse.

· execnt – The number of times the SQL statement was executed.

· execpu – The CPU time spent executing the SQL.

· exeela – The elapsed time spent executing the SQL.

· exedsk – The number of physical reads during execution.

· exeqry – The number of consistent block reads during execution.

· execu – The number of current block reads during execution.

· exerow – The number of rows processed during execution.

· exemis – The number of library cache misses during execution.

· fchcnt – The number of fetches performed.

· fchcpu – The CPU time spent fetching rows.

· fchela – The elapsed time spent fetching rows.

· fchdsk – The number of physical disk reads during the fetch.

· fchqry – The number of consistent block reads during the fetch.

· fchcu – The number of current block reads during the fetch.

· fchrow – The number of rows fetched for the query.

NOTE:

The TKPROF utility puts a TRACED output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying "explain=username/password" (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query

More on Tkprof : http://www.oracleutilities.com/OSUtil/tkprof.html

ORA-01127, ORA-01163 during database creation

In my earlier post i mentioned how to create/add another database in an existing server (http://www.chandu208.blogspot.com/2012/07/add-another-database-on-existing-server.html). Again today i'm creating new instance on an existing server and hit an error

ERROR: ORA-01127: database name 'RCDEV' exceeds size limit of 8 characters

CAUSE: The database name mentioned above exceeds the limit set

SOLUTION: change the database name to 8 or less characters

Then changed the name to 8 characters

Then error resolved

Now, running the control file script to create controlfiles for new database

SQL > @con.ctl

CREATE CONTROLFILE REUSE DATABASE "RCDEV" NORESETLOGS ARCHIVELOG

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01163: SIZE clause indicates 12800 (blocks), but should match header 640

ORA-01110: data file 4: '/OPT/ORACLE/ORADATA/RCDEV/USERS01.DBF'

Vi con.ctl

CREATE CONTROLFILE SET DATABASE "RCDEV" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/oradata/ RCDEV/redo01.log' SIZE 1024M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/oradata/ RCDEV/redo02.log' SIZE 1024M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/oradata/ RCDEV/redo03.log' SIZE 1024M BLOCKSIZE 512,

GROUP 4 '/opt/oracle/app/fast_recovery_area/ RCDEV/onlinelog/o1_mf_4_7zscnkom_.log' SIZE 100M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/oradata/ RCDEV/system01.dbf',

'/opt/oracle/oradata/ RCDEV/sysaux01.dbf',

'/opt/oracle/oradata/ RCDEV/undotbs01.dbf',

'/opt/oracle/oradata/ RCDEV/users01.dbf', ß Comma leads to an error

CHARACTER SET WE8MSWIN1252

;

Then deleted that ‘comma’ and reran the con.ctl script then controlfiles have been created.

Friday, July 13, 2012

Catproc is invalid after database creation

Recently I created new database instance on my Linux oracle 11gr2 existing server(see here: http://chandu208.blogspot.com/2012/07/add-another-database-on-existing-server.html ),

after creating it I ran below oracle scripts

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

And bounced the database

Then I query for invalid objects:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 13 14:21:34 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID VERSION STATUS

------------------------------ ------------------------------ -----------

OWB 11.2.0.2.0 VALID

APEX 3.2.1.00.12 VALID

EM 11.2.0.2.0 VALID

AMD 11.2.0.2.0 VALID

SDO 11.2.0.2.0 VALID

ORDIM 11.2.0.2.0 VALID

XDB 11.2.0.2.0 VALID

CONTEXT 11.2.0.2.0 VALID

EXF 11.2.0.2.0 VALID

RUL 11.2.0.2.0 VALID

OWM 11.2.0.2.0 VALID

COMP_ID VERSION STATUS

-------------------------- ------------------------------ -----------

CATALOG 11.2.0.2.0 VALID

CATPROC 11.2.0.2.0 INVALID

JAVAVM 11.2.0.2.0 VALID

XML 11.2.0.2.0 VALID

CATJAVA 11.2.0.2.0 VALID

APS 11.2.0.2.0 VALID

XOQ 11.2.0.2.0 VALID

18 rows selected.

In the above we see my CATPROC is invalid, then ran utlrp.sql script to validate objects

$ORACLE_HOME/rdbms/admin/utlrp.sql

But still same then i did some research and found the solution

SQL> exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID VERSION STATUS

------------------------------ ------------------------------ -----------

OWB 11.2.0.2.0 VALID

APEX 3.2.1.00.12 VALID

EM 11.2.0.2.0 VALID

AMD 11.2.0.2.0 VALID

SDO 11.2.0.2.0 VALID

ORDIM 11.2.0.2.0 VALID

XDB 11.2.0.2.0 VALID

CONTEXT 11.2.0.2.0 VALID

EXF 11.2.0.2.0 VALID

RUL 11.2.0.2.0 VALID

OWM 11.2.0.2.0 VALID

COMP_ID VERSION STATUS

------------------------------ ------------------------------ -----------

CATALOG 11.2.0.2.0 VALID

CATPROC 11.2.0.2.0 VALID

JAVAVM 11.2.0.2.0 VALID

XML 11.2.0.2.0 VALID

CATJAVA 11.2.0.2.0 VALID

APS 11.2.0.2.0 VALID

XOQ 11.2.0.2.0 VALID

18 rows selected.

About this blog

The contents in my blog are uploaded purely according to my convenience & based on my results and should not be considered professional advice, official documentation, or a substitute for vendor‑supported guidance, viewers may or may not get same results ............Any actions you take based on the content of this blog are strictly at your own risk. I am not responsible for any losses, damages, outages, or issues arising from the use or misuse of the information provided here. All product names, trademarks, and registered trademarks are the property of their respective owners. I thank you everyone for visiting my blog