Golden Gate Commands

select * from table(logmnr$always_suplog_columns('SCHEMAUSER','T'));

GRANT CREATE SESSION,ALTER SESSION to gguser identified by oracle;
GRANT CONNECT,SELECT ANY DICTIONARY,FLASHBACK ANY TABLE,SELECT ANY TABLE to gguser;
GRANT SELECT on dba_clusters to gguser;
GRANT EXECUTE on DBMS_FLASHBACK to gguser;
GRANT SELECT ANY TRANSACTION to gguser;
GRANT EXECUTE on utl_file TO gguser;
EXEC dbms_goldengate_auth.grant_admin_privilege('gguser');

--Command Used to check status.
info all
SEND replicat rha02, STATUS
STATS replicat rha02, totalsonly *, reportrate min/hr/sec
SEND extract xha01, SHOWTRANS
SEND extract xha01, STATUS
STATS extract xha01, totalsonly *, reportrate sec
[oracle@dbserver1 ggs]$ ./logdump
Logdump 2 >open dirdat/OUT/ex000004
Logdump 3 >fileheader detail
Logdump 4 >ghdr on
Logdump 6 >detail on
Logdump 7 >usertoken detail
Logdump 8 >reclen 128

Reclen set to 128
Logdump 9 >pos 0

Reading forward from RBA 0

Logdump 19 >n
Logdump 34 >count
Logdump 52 >filter include iotype delete

Logdump 53 >n
Logdump  >detail on
Logdump  >ggstoken detail
Logdump  >filter csn = 9889898
Logdump  >n

shell ls -l


ggsci> list tables cust*

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 77878, begin 2013-03-03 12:00:00

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
ggsci> add checkpointtable gg_owner.checkpoint

ggsci> start replicat rep
ggsci> start replicat fin, atcsn 5435435       -- commit sequence number (CSN)
ggsci> start replicat fin, aftercsn 0X0000071:0002334D:0

-- To start multiple Extract and Replicat groups as a unit
ggsci> start er *rep*
ggsci> stop er *ext*

ggsci> status extract extr_hr
ggsci> status extract ext*, tasks
ggsci> status extract *ext*, allprocesses

ggsci> alter extract fin, begin 2013-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 12342:1234:1


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

RMAN Duplicate database

# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO DB11G
  SPFILE
  NOFILENAMECHECK;

# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DB11G
  UNTIL TIME 'SYSDATE-4'
  SPFILE
  NOFILENAMECHECK;

# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO DB11G
  SPFILE
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK;

scp parallel Linux script

Please test before it use:

#!/bin/ksh
cnt=1
RHOST=srv245005
CCMD="ls /db/remote_backup_area"

for BackupFile in 'ssh -q oracle@${RHOST} ${CCMD}'; do
        echo "attempting to copy file: " $BackupFile
       
        #scp -pq $hostname:/db/remote_backup_area/$file /db/local_dump_area &
        (
      echo "starting $file `date`" >/tmp/status/${file}.log
      scp -pq $hostname:/db/remote_backup_area/$file /db/local_dump_area 2>>/tmp/status/${file}.log
      echo "done $file `date`" >>/tmp/status/${file}.log
    ) &
        cnt=$(( $cnt + 1 ))
        if [ cnt -eq 10 ] then
           wait
           cnt=1
        fi

done
wait
exit

List RMAN backup

LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
LIST BACKUP SUMMARY;
LIST BACKUP;
LIST BACKUP BY FILE;
LIST COPY;
LIST INCARNATION;
LIST FAILURE ALL;
list copy of database archivelog all;
list backup of tablespace SYSTEM;

 rman target / catalog rman/rman@rcat log '/oracle/log/mlog.f'

To restrict by object, use list copy or list backup with the of listObjList condition. For example, enter:

list backup of database;     # lists backups of all files in database
list copy of datafile '/oracle/dbs/tbs_1.f'; # lists copy of specified datafile
list backup of tablespace SYSTEM; # lists all backups of SYSTEM tablespace
list copy of archivelog all;  # lists all archived redo logs and copies of logs
list backup of controlfile; # lists all control file backups


 You can also restrict your search by specifying a combination of tag, device type, filename pattern, or time options. For example, enter:

list backup tag 'weekly_full_db_backup';    # by tag
list copy of datafile '/oracle/dbs/tbs_1.f' type 'sbt_tape';   # by type
list backup like '/oracle/backup/tbs_4%';    # by filename pattern
list backup of archivelog until time 'SYSDATE-30';   # by time
list copy of datafile 2 completed between '10-DEC-1998' and '17-DEC-1998'; # by tim


# must allocate maintenance channel for crosscheck
allocate channel for maintenance type disk;
crosscheck backup;  # crosschecks all backups
change datafile copy 100,101,102,103,104,105,106,107 crosscheck; # specified by key
change archivelog copy 50,51,52,53,54 crosscheck;  # specified by key
release channel;
   


report need backup days = 7 database;  # needs at least 7 days of logs to recover
report need backup days = 30 tablespace system;
report need backup days = 14 datafile '/oracle/dbs/tbs_5.f';

report need backup incremental = 1 database;
report need backup incremental = 3 tablespace system;
report need backup incremental = 5 datafile '/oracle/dbs/tbs_5.f';


report schema at time 'SYSDATE-14';
report schema at scn 1000;
report schema at logseq 100;
   

list backup of tablespace tbs_1 completed before 'Nov 1 1998 00:00:00';

list backup of database device type 'sbt_tap
list copy of datafile 2 tag weekly_df2_copy like '/copy/%';

Recovery Archivelogs
=================================

SET ARCHIVELOG DESTINATION TO '/fs3/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 201 UNTIL SEQUENCE 300;
restore archivelog logseq 11645;
RESTORE ARCHIVELOG FROM SEQUENCE 20 UNTIL SEQUENCE 28;
restore archivelog from scn 2324906766 until scn 2324970620;

Oracle Incremental backup

Each data block in a datafile contains a system change number (SCN), which is the SCN at which the most recent change was made to the block. During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN of the parent incremental backup. If the SCN in the input data block is greater than or equal to the checkpoint SCN of the parent, then RMAN copies the block.

A level 1 incremental backup can be either of the following types:

• A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
• A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Incremental backups are differential by default.

Info about RMAN backup set.

prompt
prompt Available backup sets contained in the control file.
prompt Includes available and expired backup sets.
prompt 

SELECT
    bs.recid                                              bs_key
  , DECODE(backup_type
           , 'L', 'Archived Logs'
           , 'D', 'Datafile Full'
           , 'I', 'Incremental')                          backup_type
  , device_type                                           device_type
  , DECODE(   bs.controlfile_included
            , 'NO', null
            , bs.controlfile_included)                    controlfile_included
  , sp.spfile_included                                    spfile_included
  , bs.incremental_level                                  incremental_level
  , bs.pieces                                             pieces
  , TO_CHAR(bs.start_time, 'mm/dd/yy HH24:MI:SS')         start_time
  , TO_CHAR(bs.completion_time, 'mm/dd/yy HH24:MI:SS')    completion_time
  , bs.elapsed_seconds                                    elapsed_seconds
  , bp.tag                                                tag
  , bs.block_size                                         block_size
FROM
    v$backup_set                           bs
  , (select distinct
         set_stamp
       , set_count
       , tag
       , device_type
     from v$backup_piece
     where status in ('A', 'X'))           bp
 ,  (select distinct
         set_stamp
       , set_count
       , 'YES'     spfile_included
     from v$backup_spfile)                 sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY
    bs.recid
/

Info about RMAN backup piece.

prompt
prompt Available backup pieces contained in the control file.
prompt Includes available and expired backup sets.
prompt 

SELECT
    bs.recid                                            bs_key
  , bp.piece#                                           piece#
  , bp.copy#                                            copy#
  , bp.recid                                            bp_key
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                           status
  , handle                                              handle
  , TO_CHAR(bp.start_time, 'mm/dd/yy HH24:MI:SS')       start_time
  , TO_CHAR(bp.completion_time, 'mm/dd/yy HH24:MI:SS')  completion_time
  , bp.elapsed_seconds                                  elapsed_seconds
FROM
    v$backup_set    bs
  , v$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
ORDER BY
    bs.recid
  , piece#
/