Oracle Database Directories

System Privileges Needed

GRANT create any directory TO <user_name>;
GRANT drop any directory TO <user_name>;

Create A Directory

CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>';

conn / as sysdba

desc dba_directories

set linesize 121
col owner format a15
col directory_name format a20
col directory_path format a70

SELECT * FROM dba_directories;

CREATE OR REPLACE DIRECTORY temp AS '/opt/app/user/';

SELECT * FROM dba_directories;

Grant Read On A Directory

GRANT READ ON DIRECTORY <directory_name> TO <schema_name>

col grantor format a20
col grantee format a20
col table_schema format a20
col table_name format a20
col privilege format a10

SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'EMP';

GRANT READ ON DIRECTORY temp TO testuser;

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Grant Write On A Directory

GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name>

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Revoke Read On A Directory

REVOKE READ ON DIRECTORY <directory_name> FROM <schema_name>

Revoke Write On A Directory REVOKE WRITE ON DIRECTORY <directory_name> FROM <schema_name>

Drop A Directory

DROP DIRECTORY <directory_name>;

SELECT * FROM dba_directories;

DROP DIRECTORY temp;

SELECT * FROM dba_directories;

ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed 6786000K

Got below error while running utlrp

ERROR at line 1:
ORA-12801: error signaled in parallel query server P619, instance (2)
ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed
6786000K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large
pool","unknown object","large pool","PX msg pool")
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4


As it was a “PX” (parallelism) error, and my system has a lot of processors, the problem was that my default parallel_max_servers was 970!
This number would be acceptable if I had enough free memory (specially pga) to accommodate all of them, but I didn’t. My MEMORY_TARGET was the minimal.

So to resolve it, I had to change this parameter to a lower value:

SQL> show parameter parallel_max_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     970


Brought this down to 50

SQL> alter system set parallel_max_servers=50 scope=both sid='*';

System altered.

This stopped the error!!.

Hope this works for you

Oracle : Start Database in No archive log mode.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/app/dbname/oraflsh01/archivelog
Oldest online log sequence     9
Next log sequence to archive   34
Current log sequence           34

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 5.9861E+10 bytes
Fixed Size                  2267792 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         4.8989E+10 bytes
Redo Buffers              131936256 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/app/dbname/oraflsh01/archivelog
Oldest online log sequence     9
Current log sequence           34

Oracle : Clearing Redo Logs

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 4:

ALTER DATABASE CLEAR LOGFILE GROUP 4;

This statement overcomes two situations where dropping redo logs is not possible:

i) If there are only three log groups
ii)The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;

This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.

If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover.

Note:
If you clear an unarchived redo log file, you should make another backup of the database.

If you want to clear an unarchived redo log that is needed to bring an offline tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement.

If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery. Note that tablespaces taken offline normal do not require recovery.

ORACLE : How to generate SQL_ID information

Session related Queries

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15

select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
--  and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/

SQLs Running from longtime
--------------------------
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
      ,opname
      ,target
      ,round(sofar/totalwork*100,2)   as percent_done
      ,start_time
      ,last_update_time
      ,time_remaining
from
       v$session_longops
/

Active Sessions running for more than 1 hour
---------------------------------------------
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

Session details associated with SID and Event waiting for
---------------------------------------------------------
set pages 50000 lines 32767
col EVENT for a40

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

Session details associated with Oracle SID
-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on

Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

Session details from Session longops
-------------------------------------
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;


Session details with SPID
-------------------------
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid')
/
To find Undo Generated For a given session
------------------------------------------
select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid='&sid';

To list count of connections from other machines
------------------------------------------------
select count(1),machine from gv$session where inst_id='&inst_id' group by machine;

To get total count of sessions and processes
--------------------------------------------
select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext thru sqladdress
-------------------------------
select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalue
-------------------------------------------
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

To list long running forms user sessions
----------------------------------------
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username
---------------------------------------------
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs
------------------------------------
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');

To find Sql Text given SQLHASH & SQLADDR
----------------------------------------
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where  ADDRESS ='&addr' order by piece;

Oracle : Running SQLTXPLAIN.sql

Installation:

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:

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
Execute installation script sqlt/install/sqcreate.sql connected as SYS.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql
During the installation you will be asked to enter values for these parameters:

Optional Connect Identifier (mandatory when installing in a Pluggable Database)
In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. Entering nothing is the most common setup.
The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.

SQLTXPLAIN password.
Case sensitive in most systems.

SQLTXPLAIN Default Tablespace.
Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

SQLTXPLAIN Temporary Tablespace.
Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

Optional Application User.
This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.

Licensed Oracle Pack. (T, D or N)
You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

Running SQLTXPLAIN

For brief instructions and feedback, please refer to the INSTRUCTIONS.TXT file included on same SQLT.zip downloaded file.

Start by uncompressing latest file SQLT.zip into one dedicated directory from where you can connect into SQL*Plus.

The file sql.txt, whose filename is provided as an inline parameter when SQLTXPLAIN.SQL is executed, has some restrictions and characteristics explained below.  If you were not provided with one sql.txt file, create it and place under same dedicated directory where you placed all SQLT scripts.  SQLTXPLAIN.SQL will try to open file <sql.txt> under same directory where you placed all SQLT scripts.

Restrictions and characteristics for file sql.txt:

It is a plain text file (flat file) with one and only one valid SQL Statement to be explained (SELECT, INSERT, UPDATE or DELETE),
It cannot have empty lines (blank lines),
At the very end of the file, after the very last character of the SQL Statement, one and only one 'carriage return' ('enter' or 'line feed') should be provided, with no spaces before or after it (review file sql0.txt provided as an example),
The SQL Statement should NOT have a semicolon ';' at the end,
If you get an error similar to 'Bind variable "b2" not declared', you have empty lines within the SQL Statement, or at the end (review sql0.txt provided as a correct example),
Do NOT replace bind variables with literals.  Since the SQLTXPLAIN.SQL script does not execute the SQL Statement provided on the <sql.txt> file, there is no need to replace the bind variables on it.  Actually, by replacing the bind variables with literals, the resulting Explain Plan can change substantially and may lead to confusion or false conclusions,
The filename <sql.txt> is NOT hard-coded.  Therefore, if multiple SQL Statements are being diagnosed, use filenames sql1.txt, sql2.txt, sql3.txt, etc.; or any other set of names,
File sql.txt is usually created out of the TKPROF by extracting a specific expensive SQL Statement.  File sql.txt is normally created with a simple Cut&Paste OS command into one new flat file,
File sql0.txt is provided as an example only (use it to test SQLTXPLAIN.SQL on APPS databases)
To execute the SQLTXPLAIN.SQL script, login into SQL*Plus.  If using Oracle APPS, login with APPS USER and password.  If using on a non-APPS database, connect into SQL*Plus with same USER that CAN execute the SQL Statement provided within the <sql.txt> file.  Be aware that the USER executing the SQLTXPLAIN.SQL script must have access to the objects specified in the sql.txt file, PLUS to the 'ALL_', 'DBA_' and 'V$' views (see also SQLTGRANT.SQL).

# sqlplus apps/<apps_pwd>
SQL> START SQLTXPLAIN.SQL sql.txt;
SQLTXPLAIN.SQL creates three files: SQLTXPLAIN.LOG, SQLT_<statement_id>.TXT and a Raw SQL Trace.  The first two are the log file and the actual report.  Use any text editor to review them (TextPad and WordPad work fine).  Be sure your lines are not wrapping around.  These two files are usually created under the same directory from where SQLTXPLAIN.SQL was executed.  On NT, these files may get created under $ORACLE_HOME/bin instead.

In most cases, the *.TXT report is enough to understand a specific Explain Plan.  In some cases, two additional files may be required:

Raw SQL Trace generated by SQLTXPLAIN.SQL.  Generated always under the 'udump' directory on the database server.  Filename and exact location can be found on the report or log files.  Please do not TKPROF this particular raw SQL Trace.
Export file of the table SQLT$STATTAB, which contains the CBO Stats related to the SQL Statement <sql.txt>.  See Migrating CBO Stats across Similar Instances below for details on its use.  To export this table SQLT$STATTAB, use ORACLE_HOME 8.0.6 (if using Oracle APPS), and execute command below.  Keep in mind that exported file is always BINARY and should be treated as such (if XFR as ASCII, this BINARY file gets corrupted).

ORA-01012: not logged on

$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 6 16:44:14 2015

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

Connected.
SQL> startup mount
ORA-01012: not logged on

Solution : Shutdown Abort the database and start it again to solve ora-01012

Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit 
Disconnected

$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 6 16:52:19 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Oracle User Data Size

SQL> set pagesize 10000
SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL TOTAL OF "Size of Each Segment in MB" ON REPORT
SQL> select segment_type, sum(bytes/1024/1024) "Size of Each Segment in MB" from dba_segments where owner='SYS' group by segment_type order by 1;

SEGMENT_TYPE          Size of Each Segment in MB
------------------             --------------------------
CLUSTER                                       41.375
INDEX                                        274.8125
INDEX PARTITION                    76.1875
LOB PARTITION                            .0625
LOBINDEX                                     9.625
LOBSEGMENT                           32.4375
NESTED TABLE                               .625
ROLLBACK                                      .375
TABLE                                         470.875
TABLE PARTITION                         92.5
TABLE SUBPARTITION                       2
TYPE2 UNDO                               12.375
                           --------------------------
TOTAL                                         1013.25

Golden Gate Time Since Chkpt Unknown!!

Golden Gate in Time Since Chkpt display unknown solution

An abnormal phenomenon

[oracle@localhost ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT12345     00:00:00      unknown    
EXTRACT     RUNNING     EXT67889     00:00:00      unknown    
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown    


2) Tried to close the abnormal process restart

GGSCI (localhost.localdomain) 2> stop *

Sending STOP request to EXTRACT EXT12345 ...

ERROR: sending message to EXTRACT EXT12345 (Timeout waiting for message).

Sending STOP request to EXTRACT EXT67889 ...

ERROR: sending message to EXTRACT EXT67889 (Timeout waiting for message).

Sending STOP request to EXTRACT PUMP1234 ...

ERROR: sending message to EXTRACT PUMP1234 (Timeout waiting for message).

Sending STOP request to EXTRACT PUMP5678 ...

ERROR: sending message to EXTRACT PUMP5678 (Timeout waiting for message).

Sending STOP request to REPLICAT REP12345 ...

ERROR: sending message to REPLICAT REP12345 (Timeout waiting for message).

GGSCI (localhost.localdomain) 3> stop mgr!

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (localhost.localdomain) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                          
EXTRACT     RUNNING     EXT12345     00:00:00      unknown    
EXTRACT     RUNNING     EXT67889     00:00:00      unknown    
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown    

GGSCI (localhost.localdomain) 5> kill EXT12345

ERROR: Manager not currently running.

GGSCI (localhost.localdomain) 6> kill EXT67889

ERROR: Manager not currently running.


GGSCI (localhost.localdomain) 7> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                          
EXTRACT     RUNNING     EXT12345     00:00:00      unknown    
EXTRACT     RUNNING     EXT67889     00:00:00      unknown    
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown  

GGSCI (localhost.localdomain) 8> exit
- Use stop the process, stop MGR, kill  processes are not shut down these processes

3) The system processes the system level kill related ogg
[oracle@localhost OGG]$ ps -ef|grep /opt/OGG
oracle    7479     1  0 Nov10 ?        00:03:31 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/EXT12345.prm REPORTFILE /opt/OGG/dirrpt/EXT12345.rpt PROCESSID EXT12345 USESUBDIRS
oracle    7480     1  0 Nov10 ?        00:02:30 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/EXT67889.prm REPORTFILE /opt/OGG/dirrpt/EXT67889.rpt PROCESSID EXT67889 USESUBDIRS
oracle    7483     1  0 Nov10 ?        00:00:01 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/PUMP1234.prm REPORTFILE /opt/OGG/dirrpt/PUMP1234.rpt PROCESSID PUMP1234 USESUBDIRS
oracle    7485     1  0 Nov10 ?        00:00:03 /opt/OGG/replicat PARAMFILE /opt/OGG/dirprm/REP12345.prm REPORTFILE /opt/OGG/dirrpt/REP12345.rpt PROCESSID REP12345 USESUBDIRS
oracle    7518     1  0 Nov10 ?        00:00:01 ./server -p 7847 -k -l /opt/OGG/ggserr.log
oracle    7677     1  0 Nov10 ?        00:00:15 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/PUMP5678.prm REPORTFILE /opt/OGG/dirrpt/PUMP5678.rpt PROCESSID PUMP5678 USESUBDIRS
oracle 25261 25112 0 24:48 pts / 1     0:00:00 grip  / opt / OGG
[oracle@localhost OGG]$ kill -9 7479 7480 7482 7483 7485  7518 7677
[oracle@localhost OGG]$ ps -ef|grep /opt/OGG
oracle 25264 25112 0 24:48 pts / 1     0:00:00 grip  / opt / OGG

4) Restart the process all ogg
[oracle@localhost OGG]$ ggsci

Command Interpreter Oracle GoldenGate for  Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.



GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                          
EXTRACT     ABENDED     EXT12345     00:00:00      unknown    
EXTRACT     ABENDED     EXT67889     00:00:00      unknown    
EXTRACT     ABENDED     PUMP1234     00:00:00      unknown    
EXTRACT     ABENDED     PUMP5678     00:00:00      unknown    
REPLICAT    ABENDED     REP12345     00:00:00      unknown    

- Process status or abnormal

GGSCI (localhost.localdomain) 2> start mgr

Manager started.


GGSCI (localhost.localdomain) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT12345     00:00:00      unknown    
EXTRACT     RUNNING     EXT67889     00:00:00      unknown    
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown
   
- The process up, but Time Since Chkpt or incorrect

GGSCI (localhost.localdomain) 4> stop EXT12345

Sending STOP request to EXTRACT EXT12345 ...
Request processed.


GGSCI (localhost.localdomain) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     STOPPED     EXT12345     unknown       00:00:02  
EXTRACT     RUNNING     EXT67889     00:00:00      unknown    
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown    

- Close the EXT12345 test, the normal state

GGSCI (localhost.localdomain) 6> start EXT12345

Sending START request to MANAGER ...
EXTRACT EXT12345 starting


GGSCI (localhost.localdomain) 7> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT12345     unknown       00:00:14  
EXTRACT     RUNNING     EXT67889     00:00:00      unknown    
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown    

--Lag Abnormal, wait for recovery

GGSCI (localhost.localdomain) 8> stop EXT67889

Sending STOP request to EXTRACT EXT67889 ...

Recovery is not complete.  This normal stop will wait and checkpoint recovery's
work when recovery has finished. To force Extract to stop now,
use the SEND EXTRACT EXT67889, FORCESTOP command.
- Because there is no complete recovery led to the prompt, you can ignore, wait

GGSCI (localhost.localdomain) 9> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT12345     unknown       00:00:02  
EXTRACT     STOPPED     EXT67889     01:51:12      00:00:01  
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      unknown    


GGSCI (localhost.localdomain) 10> start EXT67889

Sending START request to MANAGER ...
EXTRACT EXT67889 starting


GGSCI (localhost.localdomain) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT12345     99:53:02      00:00:01  
EXTRACT     RUNNING     EXT67889     01:51:12      00:00:10  
EXTRACT     RUNNING     PUMP1234     00:00:00      unknown    
EXTRACT     RUNNING     PUMP5678     00:00:00      unknown    
REPLICAT    RUNNING     REP12345     00:00:00      00:00:00  



GGSCI (localhost.localdomain) 15> stop PUMP1234

Sending STOP request to EXTRACT PUMP1234 ...
Request processed.


GGSCI (localhost.localdomain) 16> start PUMP1234

Sending START request to MANAGER ...
EXTRACT PUMP1234 starting


GGSCI (localhost.localdomain) 17> stop PUMP5678

Sending STOP request to EXTRACT PUMP5678 ...
Request processed.


GGSCI (localhost.localdomain) 18> start PUMP5678

Sending START request to MANAGER ...
EXTRACT PUMP5678 starting


GGSCI (localhost.localdomain) 19> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT12345     00:00:00      00:00:01  
EXTRACT     RUNNING     EXT67889     00:00:00      00:00:10  
EXTRACT     RUNNING     PUMP1234     00:00:00      00:00:04  
EXTRACT     RUNNING     PUMP5678     00:00:00      00:00:05  
REPLICAT    RUNNING     REP12345     00:00:00      00:00:05  

- Restart all exceptions process, ogg work

GGSCI (localhost.localdomain) 20>

5) Summarizes the processing steps
forced off mgr, system-level kill processes related ogg, ogg open primary process, restart the relevant processes