RMAN daily full compressed backup

[oraprod@taw-ora-db scripts]$ cat /home/oraprod/scripts/rman_daily_full.sh
# *====================================================================================+
# |  Author - Tahaluf AlEmaraat - Mohammed Al-Masri
# |                                                       |
# +====================================================================================+
# |
# | DESCRIPTION
# |     Take rman full backup
# | PLATFORM
# |     Linux/Solaris/HP-UX
# +===========================================================================+
#!/bin/bash

. ~/.bash_profile

fullBackup () {
rman log=/backup/CIRC/backups/logs/RMANDailyFull.log << EOF
connect target /
set echo on;
configure controlfile autobackup on;
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk ;
ALLOCATE CHANNEL c2 DEVICE TYPE disk ;
backup AS COMPRESSED BACKUPSET full database tag FullBackup format '/backup/CIRC/backups/%d_%T_%s_%p_FULL.BKP' ;
sql 'alter system archive log current';
backup tag ARCHIVEBackup format '/backup/CIRC/backups/%d_%T_%s_%p_ARCHIVE.BKP' archivelog all delete all input ;
backup tag CONTROLBackup current controlfile format '/backup/CIRC/backups/%d_%T_%s_%p_CONTROL.BKP';
release channel c1;
release channel c2;
}
configure backup optimization clear;
configure controlfile autobackup clear;
#sql 'alter system archive log current';
exit
EOF
}
# Main
fullBackup
export log_name="RMANDailyFull_$(date +%Y%m%d_%H%M%S).log"
mv /backup/CIRC/backups/logs/RMANDailyFull.log /backup/CIRC/backups/logs/$log_name




[oraprod@taw-ora-db scripts]$ crontab -l
0 20 * * * /usr/bin/find /backup/FULL_BACKUP/ -name "*.BKP" -type f -mtime +3 -exec rm -f {} \;
* 22 * * *  /home/oracle/scripts/rman_daily_full.sh
~
~

Delete old RMAN and Archives

[oraprod@taw-ora-db scripts]$ cat /home/oraprod/scripts/delete_old_backups.sh
#!/bin/bash
find /backup/CIRC/archives -name "1_*.dbf" -mtime +14 | xargs rm -rf
export BACKUPS=/backup/CIRC/backups  # The patch of the AUDIT_DUMP_DEST
find $BACKUPS -type f -name '*.BKP' -mtime +7 -exec rm -rf {} \;
find $BACKUPS -type f -name 'c-*' -mtime +7 -exec rm -rf {} \;

How To Set the AUDIT_SYSLOG_LEVEL Paramete

1. Edit /etc/syslog.conf (upto RHEL5) or /etc/rsyslog.conf (RHEL6 onward) to including following lines 

(Must put lines before line "*.info ...", otherwise captured to /var/log/messages, rather than /var/log/oracle-audit)

# Classify Oracle audit log into local1.warning
local1.warning    /var/log/oracle-audit.log
*.info;mail.none;authpriv.none;cron.none                /var/log/messages

2. Restart syslogd  or rsyslogd service
[root@vmxdb01 ~]# service syslogd restart <-- font="" rhel5="">
[root@vmxdb01 ~]# systemctl restart rsyslog.service <-- font="" rhel7="">

3. Modify Oracle parameter audit_syslog_level & audit_trail

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/cdborcl/
                                                 adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576

SQL> alter system set audit_trail=OS scope=spfile;

System altered.

SQL> alter system set audit_syslog_level="local1.warning" scope=spfile;

System altered.

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

Total System Global Area 1342177280 bytes
Fixed Size                  2924160 bytes
Variable Size             855638400 bytes
Database Buffers          469762048 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/cdborcl/
                                                 adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string      LOCAL1.WARNING
audit_trail                          string      OS
unified_audit_sga_queue_size         integer     1048576

4. Verify Oracle Audit Log generated

[root@vmxdb01 log]# tail -f /var/log/oracle-audit.log
Apr  9 22:18:27 vmxdb01 journal: Oracle Audit[5190]: LENGTH: "274" SESSIONID:[6] "190024" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[10] "C##DONGHUA" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[4] "1045" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834"
Apr  9 22:18:45 vmxdb01 journal: Oracle Audit[5196]: LENGTH: "283" SESSIONID:[6] "200019" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "SYSTEM" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834" PRIV$USED:[1] "5"
Apr  9 22:18:59 vmxdb01 journal: Oracle Audit[5196]: LENGTH: "227" SESSIONID:[6] "200019" ENTRYID:[1] "1" USERID:[6] "SYSTEM" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "4" LOGOFF$LREAD:[4] "3013" LOGOFF$LWRITE:[2] "20" LOGOFF$DEAD:[1] "0" DBID:[10] "2860248834" SESSIONCPU:[2] "13"
Apr  9 22:18:59 vmxdb01 journal: Oracle Audit[5205]: LENGTH: "288" SESSIONID:[6] "200020" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[10] "C##DONGHUA" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834" PRIV$USED:[1] "5"

Oracle Database RMAN 12.2.0.1.0 -- recover database until available redo;

run
{
restore database
recover database until available redo;
}

EBS R12 How to Limit Active Concurrent Requests by a User

As System Administrator you can limit the number of requests that may be active (status of Running) for an individual user. This ensures that a user cannot monopolize the request queue. For example, if a user with an Active Request Limit of 5 submits 20 requests, only 5 requests will be run at the same time. The remaining requests will be run when the number of active requests for the user drops below 5. Use the Profile Options window to set the Concurrent: Active Request Limit profile. To set a global limit for all users, set this option at the site level. You can then modify limits for individual users by setting this profile option at the User level.

The profile Concurrent: Active Request Limit profile can only be set at Site and User levels and is null by default. Null means unlimited.

ORA-31634: job already exists;ORA-31664: unable to construct unique job name when defaulted

ORA-31634: job already exists;ORA-31664: unable to construct unique job name when defaulted

In the database which I support have a daily export backup and one day the logs reported with the errors

ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

Reason for the Error:

This error normally occurs when you do not specify a unique JOB_NAME for the DataPump job  and for some reason that JOB_NAME already exists in the database, or else if you are running many jobs at the same time (more than 99 jobs) and DataPump cannot create a new job.

Resolution:

To fix this issue you have to cleanup the orphaned datapump jobs.

1) Determine the datapump jobs which exists in the database and the status as NOT RUNNING.

SELECT owner_name, job_name, operation, job_mode, 

   state, attached_sessions 

   FROM dba_datapump_jobs 

   where owner_name='&SCHEMA' and state='NOT RUNNING'

ORDER BY 1,2; 

2) Determine the related master tables

SELECT o.status, o.object_id, o.object_type, 

          o.owner||'.'||object_name "OWNER.OBJECT",o.created,j.state 

     FROM dba_objects o, dba_datapump_jobs j 

    WHERE o.owner=j.owner_name AND o.object_name=j.job_name 

   and o.owner='&SCHEMA' and j.state='NOT RUNNING' ORDER BY 4,2;

3) Drop the tables generated in step 2.

Drop table ;

This will clear the master tables created by orphaned jobs

Catch up Oracle Application Errors DBMS_UTILITY.format_error_stack

DROP public synonym log_errors_tab
/

DROP TRIGGER log_errors_trig
/

DROP TABLE log_errors_tab PURGE
/

CREATE TABLE log_errors_tab
(
    error          VARCHAR2 (30),
    timestamp      DATE,
    username       VARCHAR2 (30),
    osuser         VARCHAR2 (30),
    machine        VARCHAR2 (100),
    process        VARCHAR2 (20),
    program        VARCHAR2 (100),
    error_stack    VARCHAR2 (3000),
    captured_sql   CLOB,
    module        VARCHAR2 (100),
    action        VARCHAR2 (100)
)
/


create public synonym log_errors_tab for sys.log_errors_tab
/


grant all on sys.log_errors_tab to public
/


CREATE OR REPLACE TRIGGER log_errors_trig
    AFTER SERVERERROR
    ON DATABASE
DECLARE
    var_user      VARCHAR2 (30);
    var_osuser    VARCHAR2 (30);
    var_machine   VARCHAR2 (100);
    var_process   VARCHAR2 (20);
    var_program   VARCHAR2 (100);
    sql_text      ora_name_list_t;
    stmt          CLOB;
    n             NUMBER;
    v_module        VARCHAR2 (100);
    v_action        VARCHAR2 (100);
BEGIN
    BEGIN
        SELECT   SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'),--username,
                 osuser,
                 machine,
                 process,
                 program,
                 module,
                 action
          INTO   var_user,
                 var_osuser,
                 var_machine,
                 var_process,
                 var_program,
                 v_module,
                 v_action
          FROM   sys.v_$session
         WHERE   audsid = USERENV ('sessionid');
    EXCEPTION
        WHEN OTHERS
        THEN
            var_user := 'null';
    END;

    IF var_user not in ('SYSMAN','dbsnmp') THEN
        BEGIN
            n := ora_sql_txt (sql_text);

            IF n > 1000
            THEN
                n := 1000;
            END IF;

            FOR i IN 1 .. n
            LOOP
                stmt := stmt || sql_text (i);
            END LOOP;
        EXCEPTION
            WHEN OTHERS
            THEN
                stmt := 'unable to get sql';
        END;

        INSERT INTO log_errors_tab
          VALUES   (DBMS_STANDARD.server_error (1),
                    SYSDATE,
                    var_user,
                    var_osuser,
                    var_machine,
                    var_process,
                    var_program,
                    DBMS_UTILITY.format_error_stack,
                    stmt,v_module,
                 v_action);
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        NULL;
END;
/

ORA-19554: error allocating device, device type: SBT_TAPE, device name:

 run
{
allocate channel t1 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/aml_monthly_tdpo.opt)';
restore controlfile from 'c-420538935-20140331-00';
alter database mount;
}2> 3> 4> 5> 6> 7>

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on t1 channel at 04/06/2014 11:50:52
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2


Solution:
lrwxrwxrwx    1 root     system           19 Apr  6 11:53 libobk64.a -> /usr/lib/libobk64.a

ln -s $ORACLE_HOME/libobk64.a /usr/lib/libobk64.a

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Export: Release 11.2.0.3.0 - Production on Wed Mar 5 15:07:57 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/mksysb/collect_export_05032014/ahbcollect_05032014.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 5
Additional information: 18

Mount as follow:
mount -o rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768  nim_server1:/mksysb /mksysb

Oracle Application server 10g Start Stop scripts

Starting Oracle 10g Application server:
1)Start the listener.
Start the listener from the server where Your Application services are running
$lsnrctl start

2)Start Database.

1)Start the listener.
Start the listener from the server where Your Application services are running
On Windows server:
C:\Users\user>set ORACLE_SID=DB_NAME
SQL>startup

On Unix based server:
$export ORACLE_SID=DB_NAME
SQL>startup

3)Start Infrastructure services:
Make sure you are in Infra Home=>opmn=>bin directory.
$cd Oracle10g/Infra/opmn/bin
$opmnctl startall

4)Start Middle-tier services:
Make sure you are in Middle-tier Home=>opmn=>bin directory.
$cd Oracle10g/MidTier/opmn/bin
$opmnctl startall

Note 1:opmn stands for oracle process manager and notification server.
opmnctl startall:Start all application services like HTTP,OC4J,Wireless.....
opmnctl stopall: Stop all application services like HTTP,OC4J,Wireless.....

Note 2:Individual service or component can also be started/stopped with the help ofopmnctl as follows:
$opmnctl startproc process-type=OC4J Home
$opmnctl startproc component-type=OC4J 
$opmnctl stopproc process-type=OC4J Home
$opmnctl stoproc component-type=OC4J
$opmnctl startproc ias-component=wireless 
$opmnctl stopproc ias-component=wireless
$opmnctl stopproc process-type=OC4J_Wireless
$opmnctl startproc process-type=OC4J_Wireless



When you are stopping the services this order becomes reverse,so you have to follow the below steps:

1)Stop Middle-tier services:
Make sure you are in Middle-tier Home=>opmn=>bin directory.
$cd Oracle10g/MidTier/opmn/bin
$opmnctl stopall

2)Stop Infrastructure services:
Make sure you are in Infra Home=>opmn=>bin directory.
$cd Oracle10g/Infra/opmn/bin
$opmnctl stopall

3)Stop Database:
On Windows server:
C:\Users\user>set ORACLE_SID=DB_NAME
SQL>shutdown immediate

On Unix based server:
export ORACLE_SID=DB_NAME
SQL>shutdown immediate

4)Stop the listener:
Stop the listener from the server where Your Application services are running
$lsnrctl stop

EBS R12.x You have insufficient privileges for the current operation.

update ICX_PARAMETERS set SESSION_COOKIE_DOMAIN = NULL;
commit;

RMAN Duplicate database without connection to source/target

Source DB
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/database_%d_%u_%s';
sql 'alter system archive log current';
backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s';
backup current controlfile format '/u02/oracle/backup/Control_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
}

-- Copy the password file from Source DB server to destination DB Server
-- Change parameters in the destination pfile
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTARCXDB)'
*.log_file_name_convert='/u02/oracle/oradata/TEST','/u02/oracle/oradata/TESTARC'
*.db_file_name_convert='/u02/oracle/oradata/TEST','/u02/oracle/oradata/TESTARC'
*.db_name='TESTARC'

Destination DB Server
connect auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
duplicate target database to TESTARC backup location '/u02/oracle/backup';
}

Golden Gate command examples

How to Invoke?

[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

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, includingcheckpoints 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 e:\ogg\dirdat\ex

ggsci> INFO RMTTRAIL trail_name  -- To retrieve configuration information for a remote trail

ggsci> info rmttrail *

ggsci> info rmttrail d:\ogg\dirdat\ex

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> edit params e:\gg\dirprm\replp.prm

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 params e:\prm\replp.prm

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> view report c:\ogg\dirrpt\orders.rpt

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"]

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 /oracle/gg11/dirdat/hr

ggsci> add extract fin, vam                        -- VAM - Vendor Access Module

ggsci> add extract fin, vamtrailsource /ogg/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 C:\OGG10G\dirdat\lt

ggsci> add replicat t_rep, exttrail /oracle/gg11/dirdat/lt, checkpointtable gg_owner.checkpoint

ggsci> add replicat initload, specialrun

ggsci> add replicat sales, exttrail /oracle/gg11/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 /oracle/gg11/dirdat/lt, extract s_extr

ggsci> add exttrail C:\OGG10G\dirdat\et, extract emp_ext

ggsci> add exttrail c:\ogg\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 C:\OGG10G\dirdat\hr, extract extr

ggsci> add rmttrail /u01/app/oracle/ogg/dirdat/ms, extract msextr

ggsci> add rmttrail /u01/app/oracle/ogg/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*

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 c:\ogg\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 c:\ogg\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 /home/ogg/dirdat/et

ggsci> DELETE RMTTRAIL trail_name    -- To delete the record of checkpoints associated with a trail on a remote system

ggsci> delete rmttrail /home/ogg/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 /u01/app/oracle/gg/dirdat

ggsci> shell dir dirprm\*

ggsci> shell rm ./dat*

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 /u01/app/oracle/gg/startup.txt

START MANAGER
START EXTRACT EXT3
START EXTRACT DPUMP
INFO ALL

ggsci> OBEY /u01/app/oracle/gg/startup.txt

ggsci> OBEY ./mycommands.txt

ggsci> EXIT

Source:

http://oracledbacentre.blogspot.com/p/ebruary-6-2012-ggsci-oracle-goldengate.html