Online database info: 2014-12-28

Oracle GoldenGate Command Interpreter

Oracle GoldenGate Command Interpreter
GGSCI -- (Oracle) GoldenGate Software Command Interpreter

How to interpret:-
oracle$cd $GGATE
or
oracle$cd /i01/app/oracle/product/gg

[oracle@prod gg]$ ./ggsci

Commands
ggsci> HELP [command] [object]
ggsci> help

GGSCI Command Summary:

Object: Command:
SUBDIRS CREATE
ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP
EXTTRAIL ADD, ALTER, DELETE, INFO
GGSEVT VIEW
MANAGER INFO, REFRESH, SEND, START, STOP, STATUS
MARKER INFO
PARAMS EDIT, VIEW
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP
REPORT VIEW
RMTTRAIL ADD, ALTER, DELETE, INFO
TRACETABLE ADD, DELETE, INFO
TRANDATA ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO
Find email address free Virus protection software Address Air Pump Interview Questions
Commands without an object:
(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD
(DDL) DUMPDDL
(Miscellaneous) FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL, SHOW, VERSIONS, !

For help on a specific command, type HELP [command] [object]
Example: HELP ADD REPLICAT
Note: You must type the word COMMAND after the ! to display the ! help topic.
i.e.: GGSCI> help ! command

ggsci> help add rmttrail

ggsci> CREATE SUBDIRS -- To create default directories within Oracle GoldenGate home directory

ggsci> INFO ALL [TASKS | ALLPROCESSES] -- To display the status of all Manager, Extract, and Replicat processes
ggsci> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTR 00:01:15 00:00:07
REPLICAT ABENDED REP 00:00:00 00:00:04
ggsci> info all tasks
ggsci> info all allprocesses

ggsci> INFO MANAGER -- To determine whether or not the Manager process is running
ggsci> INFO MGR

ggsci> INFO MARKER [COUNT number_of_items] -- To review recently processed markers from a NonStop system
ggsci> info marker

ggsci> INFO EXTRACT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display Status of Extract, Approximate Extract lag, Checkpoint information, Process run history
ggsci> info extract emp_ext
ggsci> info extract cust_ext, detail
ggsci> info extract ext*, showch
ggsci> info extract *, detail
ggsci> info extract hr, tasks

ggsci> INFO REPLICAT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display status of Replicat, Approximate replication lag, the trail from which Replicat is reading, Replicat run history, including checkpoints in the trail, Information about the Replicat environment.
ggsci> info replicat emp_rep
ggsci> info replicat emp_rep, detail
ggsci> info replicat prd*, detail, allprocesses
ggsci> info replicat *, tasks
ggsci> info replicat fin, showch

ggsci> INFO EXTTRAIL trail_name -- To retrieve configuration information for a local trail
ggsci> info exttrail *
ggsci> info exttrail /i01/app/oracle/product/gg/dirdat/cr

ggsci> INFO RMTTRAIL trail_name -- To retrieve configuration information for a remote trail
ggsci> info rmttrail *
ggsci> info rmttrail /i01/app/oracle/product/gg/dirdat/cv

Performance Reviews Vacuum Pump Traces Created Security Systems Find email address free
ggsci> INFO TRANDATA user_name.table_names [, OLDFORMAT] [, USETRIGGER] -- To determine whether logging/replication enabled or not
ggsci> info trandata hr.*
ggsci> info trandata fin.acct

ggsci> INFO CHECKPOINTTABLE [user_name.table_name] -- To confirm the existence of a checkpoint table and view the date and time that it was created
ggsci> info checkpointtable gg_owner.chkpt_table

ggsci> INFO TRACETABLE [owner.table] -- To verify the existence of the specified trace table
ggsci> info tracetable ora_trace

ggsci> INFO ER group_wildcard_specification -- To get information on multiple Extract and Replicat groups as a unit
ggsci> info ER *ext*

ggsci> SHOW -- To display the Oracle GoldenGate environment
ggsci> SHOW ALL

ggsci> DBLOGIN {SOURCEDB|TARGETDB dsn | USERID user_name[, PASSWORD password] [SYSDBA] |
SOURCEDB|TARGETDB dsn, USERID user_name[, PASSWORD password] [SYSDBA] [SQLID sqlid] -- To establish a database connection
ggsci> dblogin sourcedb testdb
ggsci> dblogin targetdb repldb
ggsci> dblogin userid gg
ggsci> dblogin userid gg, password oracle
ggsci> dblogin userid system@localhost:1521/prod, password 12345678
ggsci> dblogin sourcedb ctdb@host1, userid gg_owner, password ggs sysdba

ggsci> ENCRYPT PASSWORD password [ENCRYPTKEY keyname] -- To encrypt a database login password
ggsci> encrypt password oracle143 encryptkey spiderman

ggsci> LIST TABLES table_name -- To list all tables in the database that match the specification
ggsci> list tables cust*

ggsci> EDIT PARAMS {MGR | ./GLOBALS | group_name | file_name} -- To create or change a parameter file
ggsci> edit params mgr
ggsci> edit params ./GLOBALS
ggsci> edit params myload
ggsci> edit params rep_emp

ggsci> VIEW PARAMS {MGR | group_name | file_name} -- To view the contents of a parameter file
ggsci> view params mgr
ggsci> view params s_extr

ggsci> VIEW GGSEVT -- To view GoldenGate error log (ggserr.log file)

ggsci> VIEW REPORT {group_name[n] | file_name} -- To view the process report that is generated by Extract or Replicat
ggsci> view report ext6
ggsci> view report rep

ggsci> ADD EXTRACT group_name
{, SOURCEISTABLE |, TRANLOG [bsds_name] |, VAM |, EXTFILESOURCE file_name |, EXTTRAILSOURCE trail_name |, VAMTRAILSOURCE VAM_trail_name}
{, BEGIN {NOW | yyyy-mm-dd [hh:mi:[ss[.cccccc]]]} |, EXTSEQNO seqno, EXTRBA relative_byte_address |, LOGNUM log_number, LOGPOS byte_offset |, EOF |, LSN value |, EXTRBA relative_byte_address |, PAGE data_page, ROW row |
} [, THREADS n] [, PASSIVE] [, PARAMS parameter_file] [, REPORT report_file] [, DESC "description"] -- To create an Extract group

Syntax for an alias Extract:
ADD EXTRACT group_name, RMTHOST {host_name | IP_address}, {MGRPORT port} | {PORT port} [, RMTNAME name] [, DESC "description"]
Virus protection software Address Air Pump Interview Questions Performance Reviews
ggsci> add extract s_extr, tranlog, begin now
ggsci> add extract finext, tranlog, begin now, threads 4
ggsci> add extract fin, tranlog, begin now, passive
ggsci> add extract ext_ms, extseqno 111, begin now
ggsci> add extract hr_ext, extrba 567890, begin 2012-02-02 12:00:00
ggsci> add extract initload, sourceistable
ggsci> add extract pump, exttrailsource /i01/app/oracle/product/gg/dirdat/hr
ggsci> add extract fin, vam -- VAM - Vendor Access Module
ggsci> add extract fin, vamtrailsource /i01/app/oracle/product/gg/dirdat/vt
ggsci> add extract finA, rmthost host123, mgrport 7810, rmtname fin

ggsci> ADD REPLICAT group_name
{, SPECIALRUN |, EXTFILE full_path_name |, EXTTRAIL full_path_name}
[, BEGIN {NOW | YYYY-MM-DD HH:MM[:SS[.CCCCCC]]} |, EXTSEQNO seqno, EXTRBA rba] [, CHECKPOINTTABLE owner.table | NODBCHECKPOINT]
[, PARAMS parameter_file] [, REPORT report_file] [, DESC "description"] -- To create a Replicat group
ggsci> add replicat repl, exttrail /i01/app/oracle/product/gg/lt
ggsci> add replicat t_rep, exttrail /i01/app/oracle/product/gg/dirdat/lt, checkpointtable gg_owner.checkpoint
ggsci> add replicat initload, specialrun
ggsci> add replicat sales, exttrail /i01/app/oracle/product/gg/dirdat/lt, nodbcheckpoint

ggsci> ADD EXTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] [, SEQNO n] -- To create a trail for online processing on local system
ggsci> add exttrail /i01/app/oracle/product/gg/lt, extract s_extr
ggsci> add exttrail /i01/app/oracle/product/gg/dirdat/et, extract emp_ext
ggsci> add exttrail /i01/app/oracle/product/gg/dirdat/fi, extract fin, megabytes 30

ggsci> ADD RMTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] [, SEQNO n] -- To create a trail for online processing on remote system
ggsci> add rmttrail /i01/app/oracle/product/gg/dirdat/hr, extract extr
ggsci> add rmttrail /i01/app/oracle/product/gg/dirdat/ms, extract msextr
ggsci> add rmttrail /i01/app/oracle/product/gg/dirdat/my, extract mysql, megabytes 50

ggsci> ADD TRANDATA user_name.table_names [, COLS (column_list)] [, INCLUDELONG | EXCLUDELONG]
[, LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX] [, NOKEY] [, OLDFORMAT] [, USETRIGGER] -- To enable Oracle GoldenGate to acquire the transaction information it needs from the transaction records
ggsci> add trandata hr.*
ggsci> add trandata emp.employees
ggsci> add trandata fin.acct, cols (name, address)
ggsci> add trandata fin.acct, nokey, cols (name, pid)
ggsci> add trandata fin.acct, lobsalwaysnoindex

ggsci> ADD CHECKPOINTTABLE [user_name.table_name] -- To create a checkpoint table in the target database
ggsci> add checkpointtable
ggsci> add checkpointtable gg_owner.checkpoint

ggsci> ADD TRACETABLE [owner.table] -- To create a trace table in the Oracle database
ggsci> add tracetable
ggsci> add tracetable ora_trace

ggsci> REGISTER EXTRACT extract_group_name LOGRETENTION
ggsci> register extract extr logretention

ggsci> START MANAGER -- To start Manager process
ggsci> START MGR
ggsci> START *

ggsci> START EXTRACT extract_name -- To start Extract process
ggsci> start extract extr

ggsci> START REPLICAT group_name [SKIPTRANSACTION | ATCSN csn | AFTERCSN csn] -- To start Replicat process
ggsci> start replicat rep
ggsci> start replicat fin, atcsn 5238306 -- commit sequence number (CSN)
ggsci> start replicat fin, aftercsn 0X000006B1:0002334D:0004

ggsci> START ER group_wildcard_specification -- To start multiple Extract and Replicat groups as a unit
ggsci> start er *rep*

ggsci> STOP MANAGER [!] -- To stop Manager process
ggsci> stop manager
ggsci> stop manager ! -- will not ask for confirmation

ggsci> STOP EXTRACT extract_name -- To stop Extract gracefully
ggsci> stop extract extr

ggsci> STOP REPLICAT replicat_name [!] -- To stop Replicat gracefully
ggsci> stop replicat rep

ggsci> STOP ER group_wildcard_specification -- To stop multiple Extract and Replicat groups as a unit
ggsci> stop er *ext*

ggsci> STATUS MANAGER -- To determine whether or not the Manager process is running

ggsci> STATUS EXTRACT group_name [, TASKS | ALLPROCESSES] -- To determine whether or not Extract is running
ggsci> status extract extr_hr
ggsci> status extract ext*, tasks
ggsci> status extract *ext*, allprocesses

ggsci> STATUS REPLICAT group_name [, TASKS | ALLPROCESSES] -- To determine whether or not Replicat is running
ggsci> status replicat emp_rep
ggsci> status replicat cust_rep, allprocesses

ggsci> STATUS ER group_wildcard_specification -- To check the status of multiple Extract and Replicat groups as a unit
ggsci> status er *EX*

Vacuum Pump Traces Created Security Systems Find email address free
ggsci> STATS EXTRACT group_name [, statistic] [, TABLE table] [, TOTALSONLY table_specification] [, REPORTFETCH | NOREPORTFETCH] [, REPORTRATE HR|MIN|SEC] [, ... ] -- To display statistics for one or more Extract group
ggsci> stats ext_hr
ggsci> stats extract ext
ggsci> stats extract ext2 reportrate sec
ggsci> stats extract fin, total, daily
ggsci> stats extract fin, total, hourly, table acct, reportrate min, reset, reportfetch

ggsci> STATS REPLICAT group_name [, statistic] [, TABLE table] [, TOTALSONLY table_specification] [, REPORTDETAIL | NOREPORTDETAIL] [, REPORTRATE HR|MIN|SEC] [, ... ] -- To display statistics for one or more Replicat groups
ggsci> stats rep_hr
ggsci> stats replicat fin, total, table acct, reportrate hr, reset, noreportdetail

ggsci> STATS ER group_wildcard_specification -- To get statistics on multiple Extract and Replicat groups as a unit
ggsci> stats er ext*

ggsci> REFRESH MANAGER -- not available in Oracle 11g

ggsci> SEND MANAGER [CHILDSTATUS [DEBUG]] [GETPORTINFO [DETAIL]] [GETPURGEOLDEXTRACTS] -- To retrieve the status of the active Manager process or to retrieve dynamic port information as configured in the Manager parameter file
ggsci> send manager childstatus
ggsci> send manager childstatus debug
ggsci> send manager getportinfo
ggsci> send manager getportinfo detail
ggsci> send manager getpurgeoldextracts

ggsci> SEND EXTRACT group_name,
{ CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL} | FORCESTOP | FORCETRANS id [THREAD n] [FORCE] | GETLAG | GETTCPSTATS | LOGEND | REPORT | ROLLOVER | SHOWTRANS [id] [THREAD n] [COUNT n] [DURATION duration_unit] [TABULAR] [FILE file_name [DETAIL]] | SKIPTRANS id [THREAD n] [FORCE] | STATUS | STOP | TLTRACE {DEBUG | OFF | level} [SIZELIMIT size] [DDLINCLUDE | DDL[ONLY]] [FILE] file_name | TRACE[2] {tracefile | OFF} | TRACEINIT | TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS} | TRANLOGOPTIONS TRANSCLEANUPFREQUENCY minutes | VAMMESSAGE "Teradata_command" | VAMMESSAGE {ARSTATS | INCLUDELIST [filter] | EXCLUDELIST [filter]} | VAMMESSAGE OPENTRANS
} -- To communicate with a running Extract process
Teradata_command = {"control:terminate" | "control:suspend" | "control:resume" | "control:copy database.table"
ggsci> send extract exthr status
ggsci> send extract extr, getlag
ggsci> send extract group_name tltrace file file_name ddlinclude
ggsci> send extract fin, rollover
ggsci> send extract fin stop
ggsci> send extract fin, vammessage control:suspend
ggsci> send extract fin, tranlogoptions transcleanupfrequency 15
ggsci> send extract fin, showtrans count 10
ggsci> send extract fin, skiptrans 5.17.27634 thread 2

ggsci> SEND REPLICAT group_name,
{ FORCESTOP | GETLAG | HANDLECOLLISIONS [table_specification] | NOHANDLECOLLISIONS [table_specification] | REPORT [HANDLECOLLISIONS [table_specification]] | STATUS | STOP | TRACE[2] [DDLINCLUDE | DDL[ONLY]] [FILE] file_name | TRACE[2] OFF | TRACEINIT
} -- To communicate with a starting or running Replicat process
ggsci> send replicat fin, handlecollisions
ggsci> send replicat fin, report handlecollisions fin_*
ggsci> send replicat fin, getlag

ggsci> SEND ER group_wildcard_specification -- To send instructions to multiple Extract and Replicat groups as a unit
ggsci> send er *ext

ggsci> ALTER EXTRACT group_name [, ADD_EXTRACT_attribute] [, THREAD number] [, ETROLLOVER] -- To change the attributes of an Extract group, To increment a trail to the next file in the sequence
ggsci> alter extract fin, begin 2012-02-16
ggsci> alter extract fin, etrollover
ggsci> alter extract fin, extseqno 26, extrba 338
ggsci> alter extract accounts, thread 4, begin 2012-03-09
ggsci> alter extract sales, lsn 1234:123:1

ggsci> ALTER REPLICAT group_name ,
[, BEGIN {NOW | YYYY-MM-DD HH:MM[:SS[.CCCCCC]]} |, EXTSEQNO seqno, EXTRBA rba] [, PARAMS parameter_file] [, REPORT report_file] [, DESC "description"] -- To change the attributes of a Replicat group
ggsci> alter replicat fin, begin 2011-09-28 08:08:08
ggsci> alter replicat fin, extseqno 53
ggsci> alter replicat fin, extrba 666

ggsci> ALTER EXTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] -- To change the attributes of a trail (on the local system)
ggsci> alter exttrail /i01/app/oracle/product/gg/dirdat/aa, extract fin, megabytes 30

ggsci> ALTER RMTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] -- To change the attributes of a trail (on a remote system)
ggsci> alter rmttrail /i01/app/oracle/product/gg/dirdat/et, extract fin, megabytes 25

ggsci> CLEANUP EXTRACT group_name [, SAVE count] -- To delete run history for specified Extract group
ggsci> cleanup extract fin
ggsci> cleanup extract e*, save 6

ggsci> CLEANUP REPLICAT group_name [, SAVE count] -- To delete run history for specified Replicat group
ggsci> cleanup replicat fin
ggsci> cleanup replicat *, save 10

ggsci> CLEANUP CHECKPOINTTABLE [user_name.table_name] -- To remove checkpoint records from the checkpoint table
ggsci> cleanup checkpointtable ggs.fin_check

ggsci> DELETE EXTRACT group_name [!] -- To delete an Extract group
ggsci> delete extract emp_ext
ggsci> delete extract emp_ext ! -- will not ask for confirmation

ggsci> DELETE REPLICAT group_name [!] -- To delete a Replicat group
ggsci> delete replicat emp_ext
ggsci> delete replicat emp_ext ! -- will not ask for confirmation

ggsci> DELETE EXTTRAIL trail_name -- To delete the record of checkpoints associated with a trail on a local system
ggsci> delete exttrail /i01/app/oracle/product/gg/dirdat/et

ggsci> DELETE RMTTRAIL trail_name -- To delete the record of checkpoints associated with a trail on a remote system
ggsci> delete rmttrail /i01/app/oracle/product/gg/dirdat/et

ggsci> DELETE TRANDATA user_name.table_names [, OLDFORMAT] [, USETRIGGER] -- To delete logging/replication data
ggsci> delete trandata fin.acct
ggsci> delete trandata fin.cust*
ggsci> delete trandata emp.hr, usetrigger

ggsci> DELETE CHECKPOINTTABLE [user_name.table_name] [!] -- To drop checkpoint table from database
ggsci> delete checkpointtable ggs.fin_check

ggsci> DELETE TRACETABLE [owner.table] -- To delete a trace table
ggsci> delete tracetable ora_trace

ggsci> KILL EXTRACT group_name -- To kill an Extract process running in regular or PASSIVE mode
ggsci> kill extract fin

ggsci> KILL REPLICAT group_name -- To kill a Replicat process
ggsci> kill replicat fin

ggsci> KILL ER group_wildcard_specification -- To forcefully terminate multiple Extract and Replicat groups as a unit
ggsci> kill er *x*

ggsci> LAG EXTRACT group_name -- To determine a true lag time between Extract and the datasource
ggsci> lag extract ext*
ggsci> lag extract *

ggsci> LAG REPLICAT group_name -- To determine a true lag time between Replicat and the trail
ggsci> lag replicat myrepl
ggsci> lag replicat *

ggsci> LAG ER group_wildcard_specification -- To get lag information on multiple Extract and Replicat groups as a unit
ggsci> lag er *ext*

ggsci> DUMPDDL [SHOW] -- To view the data in the Oracle GoldenGate DDL history table
ggsci> dumpddl

ggsci> HISTORY [n] -- To view the most recently issued GGSCI commands since the session started
ggsci> history -- by default, shows last 10 commands
ggsci> history 30

ggsci> SET EDITOR program_name -- To change the default text editor for the current session of GGSCI, default editors are Notepad for Windows and vi for UNIX
ggsci> set editor wordpad

ggsci> VERSIONS -- To display operating system and database version information

ggsci> FC [n | -n | string] -- To display edit a previously issued GGSCI command and then execute it again
ggsci> fc
ggsci> fc 9
ggsci> fc -3
ggsci> fc sta
FC editor commands
i text -- Inserts text
r text -- Replaces text
d -- Deletes a character
replacement_text -- Replaces the displayed command with the text that we enter on a one-for-one basis.

ggsci> SHELL shell_command -- To execute shell commands from within GGSCI interface
ggsci> shell ls -l /i01/app/oracle/product/gg/dirdat
ggsci> shell dir dirprm/*
ggsci> shell rm ./dat*
Virus protection software Address Air Pump Interview Questions Performance Reviews

ggsci> ! [n | -n | string] -- To execute previous GGSCI command
ggsci> !
ggsci> ! 6 -- To run the command 6 listed in the history
ggsci> ! -3
ggsci> ! sta
ggsci> help ! command

ggsci> OBEY ggsci_script -- To process a file that contains a list of GoldenGate commands.
ggsci> shell more /i01/app/oracle/product/gg/startup.txt
START MANAGER
START EXTRACT EXT3
START EXTRACT DPUMP
INFO ALL
ggsci> OBEY /i01/app/oracle/product/gg/startup.txt
ggsci> OBEY ./mycommands.txt

ggsci> EXIT

ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

How to increase PROCESSES initialization parameter:

1.Login as sysdba
sqlplus / as sysdba

2. Check Current Setting of Parameters:-

SQL> show parameter sessions

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 247
shared_server_sessions integer

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150

SQL> show parameter transactions

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
transactions integer 271
transactions_per_rollback_segment integer 5

3.If you are planning to increase "sessions" parameter you should also plan to increase
"processes and "transactions" parameters.

A basic formula for determining these parameter values is as follows:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1

4. These paramters can't be modified in memory. You have to modify the spfile only
(scope=spfile) and restart the instance.
SQL> alter system set processes=400 scope=spfile;

System altered.

SQL> alter system set sessions=445 scope=spfile;

System altered.

SQL> alter system set transactions=490 scope=spfile;

System altered.

Done..

ORA-00000: normal, successful completion

ORA-00000: normal, successful completion

This error may occur in many circumstances you need to check environment variables:
$ORACLE_HOME
$ORACLE_BASE
$ORACLE_SID
also check /etc/hosts file it must contains correct hostname and ip.
example:

[oracle@oel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 1 9:12:17 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected.
SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-00000: normal, successful completion
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-00000: normal, successful completion

After that i checked environment variables:
[oracle@server1 ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@server1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@server1 ~]$ echo $ORACLE_SID
prim

Then i checked /etc/hosts
cat /etc/hosts
#192.168.2.102 server1.soumya.com server1

So i found the issue. The hostname and ip was commented inside /etc/hosts file.

Then i started up the database and everything was right.
SQL> startup
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 331352056 bytes
Database Buffers 75497472 bytes
Redo Buffers 4308992 bytes
Database mounted.
Database opened.

P.S.Generally ORA-00000 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days

Cause: The user's account is about to about to expire within 7days and the password needs
to be changed.
Action: Change the password.

Solutions:-
sqlplus soumya/soumya
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 2 08:52:52 2015
ERROR:
ORA-28002: the password will expire within 7 days
1. Simply change the password to avoid it temporary :-

SQL> password
Changing password for SOUMYA
Old password:
New password:
Retype new password:
Password changed

2. Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently:-

SQL> SELECT PROFILE FROM dba_users WHERE username = 'SOUMYA';

PROFILE
------------------------------
DEFAULT

SQL> SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------

60

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------
UNLIMITED

You may notice that even after setting the password expiry to unlimited you are still getting the “ERROR: ORA-28002: the password will expire” message. I’m guessing this is due to additional processes that Oracle does in the background for checking password age etc. So this forces us to “reset” the password to it’s current value to remove the error.

SQL> alter user SYSTEM identified by "password";(PASSWORD OF SOUMYA)

User altered.

Tuning individual Oracle SQL statements

The acronym SQL stands for Structured Query Language. SQL is an industry standard database query language that was adopted in the mid-1980s. It should not be confused with commercial products such as Microsoft SQL Server or open source products such as MySQL, both of which use the acronym as part of the title of their products.

Do this before you start individual SQL statement tuning

This broad-brush approach can save thousands of hours of tedious SQL tuning because you can hundreds of queries at once. Remember, you MUST do this first, else later changes to the optimizer parameters or statistics may un-tune your SQL.

Remember, you must ALWAYS start with system-level SQL tuning, else later changes might undo your tuned execution plans:

· Optimize the server kernel - You must always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency.

· Adjusting your optimizer statistics - You must always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates.

· Adjust optimizer parameters - Optimizer optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.

· Optimize your instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.

· Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a Godsend for SQL tuning.

11g Note: The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process.

Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (using a regression testing approach) to accurately identify the salient changes to SQL execution plans, based on your environmental changes. Using SPA, we can predict the impact of system changes on a workload, and we can forecast changes in response times for SQL after making any change, like parameter changes, schema changes, hardware changes, OS changes, or Oracle upgrades.

Once the environment, instance, and objects have been tuned, the Oracle administrator can focus on what is probably the single most important aspect of tuning an Oracle database: tuning the individual SQL statements. In this final article in my series on Oracle tuning, I will share some general guidelines for tuning individual SQL statements to improve Oracle performance.

Oracle SQL tuning goals

Oracle SQL tuning is a phenomenally complex subject. Entire books have been written about the nuances of Oracle SQL tuning; however, there are some general guidelines that every Oracle DBA follows in order to improve the performance of their systems. Again, see the book "Oracle Tuning: The Definitive Reference", for complete details.

The goals of SQL tuning focus on improving the execution plan to fetch the rows with the smallest number of database "touches" (LIO buffer gets and PIO physical reads).

· Remove unnecessary large-table full-table scans—Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. The most common tuning remedy for unnecessary full-table scans is adding indexes. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.

· Cache small-table full-table scans—In cases where a full-table scan is the fastest access method, the administrator should ensure that a dedicated data buffer is available for the rows. In Oracle8 and beyond, a small table can be cached by forcing it into the KEEP pool.

· Verify optimal index usage—Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index.

· Materialize your aggregations and summaries for static tables - One features of the Oracle 10gSQLAccess advisor is recommendations for new indexes and suggestions for materialized views. Materialized views pre-join tables and pre-summarize data, a real silver bullet for data mart reporting databases where the data is only updated daily. Again, see the book "Oracle Tuning: The Definitive Reference", for complete details on SQL tuning with materialized views.

These are the goals of SQL tuning in a nutshell. However, they are deceptively simple, and to effectively meet them, we need to have a through understanding of the internals of Oracle SQL. Let's begin with an overview of the Oracle SQL optimizers.

Oracle SQL optimizers

One of the first things the Oracle DBA looks at is the default optimizer mode for the database. The Oracle initialization parameters offer many cost-based optimizer modes as well as the deprecated yet useful rule-based hint:
The cost-based optimizer uses 'statistics' that are collected from the table using the 'analyze table' command. Oracle uses these metrics about the tables in order to intelligently determine the most efficient way of servicing the SQL query. It is important to recognize that in many cases, the cost-based optimizer may not make the proper decision in terms of the speed of the query. The cost-based optimizer is constantly being improved, but there are still many cases in which the rule-based optimizer will result in faster Oracle queries.
Prior to Oracle 10g, Oracle's default optimizer mode was called 'choose.' In the choose optimizer mode, Oracle will execute the rule-based optimizer if there are no statistics present for the table; it will execute the cost-based optimizer if statistics are present. The danger with using the choose optimizer mode is that problems can occur in cases where one Oracle table in a complex query has statistics and the other tables do not.
Starting in Oracle 10g, the default optimizer mode is all_rows, favoring full-table scans over index access. The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans. Index access (first_rows_n) adds additional I/O overhead, but they return rows faster, back to the originating query:

Full-table scans touch all data blocks

Index scans return rows fast by doing additional I/O

Note: Staring in Oracle9i release 2, the Oracle performance tuning guide says that the first_rows optimizer mode has been deprecated and to use first_rows_n instead.
When only some tables contain CBO statistics, Oracle will use the cost-based optimization and estimate statistics for the other tables in the query at runtime. This can cause significant slowdown in the performance of the individual query.
In sum, the Oracle database administrator will always try changing the optimizer mode for queries as the very first step in Oracle tuning. The foremost tenet of Oracle SQL tuning is avoiding the dreaded full-table scan. One of the hallmarks of an inefficient SQL statement is the failure of the SQL statement to use all of the indexes that are present within the Oracle database in order to speed up the query.

Of course, there are times when a full-table scan is appropriate for a query, such as when you are doing aggregate operations such as a sum or an average, and the majority of the rows within the Oracle table must be read to get the query results. The task of the SQL tuning expert is to evaluate each full-table scan and see if the performance can be improved by adding an index.
In most Oracle systems, a SQL statement will be retrieving only a small subset of the rows within the table. The Oracle optimizers are programmed to check for indexes and to use them whenever possible to avoid excessive I/O. However, if the formulation of a query is inefficient, the cost-based optimizer becomes confused about the best access path to the data, and the cost-based optimizer will sometimes choose to do a full-table scan against the table. Again, the general rule is for the Oracle database administrator to interrogate the SQL and always look for full-table scans.
A strategic plan for Oracle SQL tuning

Many people ask where they should start when tuning Oracle SQL. Tuning Oracle SQL is like fishing. You must first fish in the Oracle library cache to extract SQL statements and rank the statements by their amount of activity.

Step 1: Identify high-impact SQL

The SQL statements will be ranked according the number of executions and will be tuned in this order. The executions column of the v$sqlarea view and the stats$sql_summary or the dba_hist_sql_summary table can be used to locate the most frequently used SQL. Note that we can display SQL statements by:

· Rows processed: Queries that process a large number of rows will have high I/O and may also have impact on the TEMP tablespace.

· Buffer gets: High buffer gets may indicate a resource-intensive query.

· Disk reads: High disk reads indicate a query that is causing excessive I/O.

· Memory KB: The memory allocation of a SQL statement is useful for identifying statements that are doing in-memory table joins.

· CPU secs: This identifies the SQL statements that use the most processor resources.

· Sorts: Sorts can be a huge slowdown, especially if they're being done on a disk in the TEMP tablespace.

· Executions: The more frequently executed SQL statements should be tuned first, since they will have the greatest impact on overall performance.

Step 2: Determine the execution plan for SQL

As each SQL statement is identified, it will be 'explained' to determine its existing execution plan. There are a host of third-party tools on the market that show the execution plan for SQL statements. The most common way of determining the execution plan for a SQL statement is to use Oracle's explain plan utility. By using explain plan, the Oracle DBA can ask Oracle to parse the statement and display the execution class path without actually executing the SQL statement.

To see the output of an explain plan, you must first create a 'plan table.' Oracle provides a script in $ORACLE_HOME/rdbms/admin called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the plan_table:

sqlplus > @utlxplan
Table created.

sqlplus > create public synonym plan_table for sys.plan_table;
Synonym created.
Most relational databases use an explain utility that takes the SQL statement as input, runs the SQL optimizer, and outputs the access path information into a plan_table, which can then be interrogated to see the access methods. Listing 1 runs a complex query against a database.

EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR

SET STATEMENT_ID = 'RUN1'

INTO plan_table

FOR

SELECT 'T'||plansnet.terr_code, 'P'||detplan.pac1

|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),

sum(plansnet.ytd_d_ty_tm),

sum(plansnet.jan_d_ly),

sum(plansnet.jan_d_ty),

FROM plansnet, detplan

WHERE

plansnet.mgc = detplan.mktgpm

AND

detplan.pac1 in ('N33','192','195','201','BAI',

'P51','Q27','180','181','183','184','186','188',

'198','204','207','209','211')

GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;

This syntax is piped into the SQL optimizer, which will analyze the query and store the plan information in a row in the plan table identified by RUN1. Please note that the query will not execute; it will only create the internal access information in the plan table. The plan tables contains the following fields:

· operation: The type of access being performed. Usually table access, table merge, sort, or index operation

· options: Modifiers to the operation, specifying a full table, a range table, or a join

· object_name: The name of the table being used by the query component

· Process ID: The identifier for the query component

· Parent_ID: The parent of the query component. Note that several query components may have the same parent.

Now that the plan_table has been created and populated, you may interrogate it to see your output by running the following query in Listing 2.

plan.sql - displays contents of the explain plan table

SET PAGES 9999;

SELECT lpad(' ',2*(level-1))||operation operation,

options,

object_name,

position

FROM plan_table

START WITH id=0

AND

statement_id = 'RUN1'

CONNECT BY prior id = parent_id

AND

statement_id = 'RUN1';

Listing 3 shows the output from the plan table shown in Listing 1. This is the execution plan for the statement and shows the steps and the order in which they will be executed.

SQL> @list_explain_plan

OPERATION

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

OPTIONS OBJECT_NAME POSITION

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

SELECT STATEMENT

SORT

GROUP BY 1

CONCATENATION 1

NESTED LOOPS 1

TABLE ACCESS FULL PLANSNET 1

TABLE ACCESS BY ROWID DETPLAN 2

INDEX RANGE SCAN DETPLAN_INDEX5 1

NESTED LOOPS

From this output, we can see the dreaded TABLE ACCESS FULL on the PLANSNET table. To diagnose the reason for this full-table scan, we return to the SQL and look for any plansnet columns in the WHERE clause. There, we see that the plansnet column called 'mgc' is being used as a join column in the query, indicating that an index is necessary on plansnet.mgc to alleviate the full-table scan.

While the plan table is useful for determining the access path to the data, it does not tell the entire story. The configuration of the data is also a consideration. The SQL optimizer is aware of the number of rows in each table (the cardinality) and the presence of indexes on fields, but it is not aware of data distribution factors such as the number of expected rows returned from each query component.

Step 3: Tune the SQL statement

For those SQL statements that possess a sub-optimal execution plan, the SQL will be tuned by one of the following methods:

· Adding SQL 'hints' to modify the execution plan

· Re-write SQL with Global Temporary Tables

· Rewriting the SQL in PL/SQL. For certain queries this can result in more than a 20x performance improvement. The SQL would be replaced with a call to a PL/SQL package that contained a stored procedure to perform the query.

Using hints to tune Oracle SQL

Among the most common tools for tuning SQL statements are hints. A hint is a directive that is added to the SQL statement to modify the access path for a SQL query.

Troubleshooting tip! For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an 'alter session' command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Oracle publishes many dozens of SQL hints, and hints become increasingly more complicated through the various releases of Oracle and on into Oracle.
Note: Hints are only used for de-bugging and you should adjust your optimizer statistics to make the CBO replicate the hinted SQL. Let's look at the most common hints to improve tuning:

· Mode hints: first_rows_10, first_rows_100

· Oracle leading and ordered hints Also see how to tune table join order with histograms

· Dynamic sampling: dynamic_sampling

· Oracle SQL undocumented tuning hints - Guru's only

· The cardinality hint

· Self-order the table joins - If you find that Oracle is joining the tables together in a sub-optimal order, you can use the ORDERED hint to force the tables to be joined in the order that they appear in the FROM clause. See

· Try a first_rows_n hint. Oracle has two cost-based optimizer modes, first_rows_n and all_rows. The first_rows mode will execute to begin returning rows as soon as possible, whereas the all_rows mode is designed to optimize the resources on the entire query before returning rows.
SELECT /*+ first_rows */

A case study in SQL tuning

One of the historic problems with SQL involves formulating SQL queries. Simple queries can be written in many different ways, each variant of the query producing the same result, but with widely different access methods and query speeds.

For example, a simple query such as 'What students received an A last semester'' can be written in three ways, as shown in below, each returning an identical result.

A standard join:

SELECT *

FROM STUDENT, REGISTRATION

WHERE

STUDENT.student_id = REGISTRATION.student_id

AND

REGISTRATION.grade = 'A';

A nested query:

SELECT *

FROM STUDENT

WHERE

student_id =

(SELECT student_id

FROM REGISTRATION

WHERE

grade = 'A'

);

A correlated subquery:

SELECT *

FROM STUDENT

WHERE

0 <

(SELECT count(*)

FROM REGISTRATION

WHERE

grade = 'A'

AND

student_id = STUDENT.student_id

);

Let's wind up with a review of the basic components of a SQL query and see how to optimize a query for remote execution.

Tips for writing more efficient SQL
Space doesn't permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:

· Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.

· Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.

· Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.

· Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.

select book_key from book
where
book_key NOT IN (select book_key from sales);

Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.

select b.book_key from book b, sales s
where
b.book_key = s.book_key(+)
and
s.book_key IS NULL;

· Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e.where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.

· Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:

where salary*5 > :myvalue
where substr(ssn,7,4) = "1234"
where to_char(mydate,mon) = "january"

· Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).

· Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate.

· Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:

where cust_nbr = "123"
where substr(ssn,7,4) = 1234

· Use decode and case - Performing complex aggregations with the "decode" or "case" functions can minimize the number of times a table has to be selected.

· Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count,db_block_size), query parallelism and the number of table/index blocks in the buffer cache.

· Use those aliases - Always use table aliases when referencing columns.