Skip navigation.

Michael Dinh

Syndicate content Thinking Out Loud
Michael T. Dinh, Oracle DBA
Updated: 3 days 4 hours ago

Find Users with DBA Roles

Thu, 2015-04-02 18:24

This sounds like a simple request doesn’t it?

Simple if there are no roles which are granted DBA role and allow me to show what can go wrong.

Create ROLE “secret”, Grant DBA to “secret”, Grant “secret” to USER “michael”

ARROW:(SYS@hawklas):PRIMARY> create role secret;

Role created.

ARROW:(SYS@hawklas):PRIMARY> grant dba to secret;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY> grant secret to michael identified by michael;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY>

When a simple SQL is used, “secret” is a ROLE NOT USER.

ARROW:(SYS@hawklas):PRIMARY> r
  1  SELECT grantee
  2  FROM dba_role_privs
  3  WHERE granted_role='DBA'
  4  AND NOT regexp_like(grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  5*

GRANTEE
------------------------------
MDINH
HR
GGADMIN
TESTING
SECRET

ARROW:(SYS@hawklas):PRIMARY>

Easy enough, let’s join dba_role_privs and dba_users. Where’s “Michael” at?

ARROW:(SYS@hawklas):PRIMARY> r
  1  SELECT  r.grantee
  2  FROM  dba_role_privs r, dba_users u
  3  WHERE r.grantee=u.username
  4  AND r.granted_role='DBA'
  5  AND NOT regexp_like(r.grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  6  ORDER BY 1
  7*

GRANTEE
------------------------------
GGADMIN
HR
MDINH
TESTING

ARROW:(SYS@hawklas):PRIMARY>

We found “Michael”

ARROW:(SYS@hawklas):PRIMARY> r
  1  WITH u AS (
  2  SELECT username
  3  FROM dba_users
  4  WHERE NOT regexp_like(username,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  5  )
  6  SELECT distinct username
  7  FROM   u, dba_role_privs d
  8  WHERE  username = d.grantee
  9  OR    (d.granted_role='DBA' AND d.granted_role in (SELECT role FROM role_role_privs WHERE granted_role='DBA'))
 10  ORDER BY 1
 11*

USERNAME
------------------------------
GGADMIN
HR
MDINH
MICHAEL
TESTING

ARROW:(SYS@hawklas):PRIMARY>

Note: regexp_like is available from 10g onward.


Be Careful when using FRA with Streams

Fri, 2015-03-27 16:12

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

select state from gv$streams_capture;

STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
WAITING FOR REDO: LAST SCN MINED 442455793041

select thread#, sequence#, status
from v$archived_log
where 442455793041 between first_change#
and next_change# order by 1,2;

   THREAD#  SEQUENCE# S
---------- ---------- -
	 1    1070609 D
	 1    1070609 D
	 1    1070609 D
	 1    1070610 D
	 1    1070610 D
	 2    1153149 D
	 2    1153149 D
	 2    1153149 D

8 rows selected.

Who’s deleting the archived logs? Thanks to Praveen G. who figured this out. From the alert log.

WARNING: The following archived logs needed by Streams capture process
are being deleted to free space in the flash recovery area. If you need
to process these logs again, restore them from a backup to a destination
other than the flash recovery area using the following RMAN commands:
   RUN{
      # <directory/ASM diskgroup> is a location other than the
      # flash recovery area
      SET ARCHIVELOG DESTINATION TO '<directory/ASM diskgroup>';
      RESTORE ARCHIVELOG ...;
   }

restore validate archivelog

Sat, 2015-03-07 13:06

A common mistake I see in backup validation is not validating archivelog or Level 1 backup.

Here I will demonstrate various methods to validate achivelog.

Validate archivelog and does not list details for archivelog backup set. Too little information?

RMAN> restore validate archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)”;

Starting restore at 2015-MAR-07 10:34:02
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2015-MAR-07 10:34:05

Validate archivelog and list details for backupset. Too much information?

RMAN> restore validate preview archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)”;

Starting restore at 2015-MAR-07 10:34:55
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
117     45.54M     DISK        00:00:04     2015-MAR-07 09:57:21
        BP Key: 117   Status: AVAILABLE  Compressed: NO  Tag: TAG20150307T095717
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1

  List of Archived Logs in backup set 117
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    149     351055     2015-MAR-04 22:03:32 371415     2015-MAR-05 14:54:39
  1    150     371415     2015-MAR-05 14:54:39 372594     2015-MAR-05 15:14:04
  1    151     372594     2015-MAR-05 15:14:04 373366     2015-MAR-05 15:34:04
  1    152     373366     2015-MAR-05 15:34:04 374012     2015-MAR-05 15:54:04
  1    153     374012     2015-MAR-05 15:54:04 374560     2015-MAR-05 16:14:05
  1    154     374560     2015-MAR-05 16:14:05 375084     2015-MAR-05 16:34:04
  1    155     375084     2015-MAR-05 16:34:04 375500     2015-MAR-05 16:54:05
  1    156     375500     2015-MAR-05 16:54:05 376122     2015-MAR-05 17:14:03
  1    157     376122     2015-MAR-05 17:14:03 376539     2015-MAR-05 17:34:05
  1    158     376539     2015-MAR-05 17:34:05 376952     2015-MAR-05 17:54:05
  1    159     376952     2015-MAR-05 17:54:05 377664     2015-MAR-05 18:14:04
  1    160     377664     2015-MAR-05 18:14:04 378236     2015-MAR-05 18:34:05
  1    161     378236     2015-MAR-05 18:34:05 378694     2015-MAR-05 18:54:05
  1    162     378694     2015-MAR-05 18:54:05 379347     2015-MAR-05 19:14:04
  1    163     379347     2015-MAR-05 19:14:04 379628     2015-MAR-06 07:16:37
  1    164     379628     2015-MAR-06 07:16:37 379909     2015-MAR-06 07:26:16
  1    165     379909     2015-MAR-06 07:26:16 380968     2015-MAR-06 07:46:16
  1    166     380968     2015-MAR-06 07:46:16 381650     2015-MAR-06 08:06:16
  1    167     381650     2015-MAR-06 08:06:16 382073     2015-MAR-06 08:26:16
  1    168     382073     2015-MAR-06 08:26:16 382491     2015-MAR-06 08:46:20
  1    169     382491     2015-MAR-06 08:46:20 382987     2015-MAR-06 09:06:22
  1    170     382987     2015-MAR-06 09:06:22 383474     2015-MAR-06 09:26:22
  1    171     383474     2015-MAR-06 09:26:22 383894     2015-MAR-06 09:46:21
  1    172     383894     2015-MAR-06 09:46:21 384457     2015-MAR-06 10:06:22
  1    173     384457     2015-MAR-06 10:06:22 384876     2015-MAR-06 10:26:22
  1    174     384876     2015-MAR-06 10:26:22 385294     2015-MAR-06 10:46:28
  1    175     385294     2015-MAR-06 10:46:28 385792     2015-MAR-06 11:06:28
  1    176     385792     2015-MAR-06 11:06:28 386280     2015-MAR-06 11:26:26
  1    177     386280     2015-MAR-06 11:26:26 386698     2015-MAR-06 11:46:27
  1    178     386698     2015-MAR-06 11:46:27 387196     2015-MAR-06 12:06:28
  1    179     387196     2015-MAR-06 12:06:28 387681     2015-MAR-06 12:26:28
  1    180     387681     2015-MAR-06 12:26:28 388098     2015-MAR-06 12:46:32
  1    181     388098     2015-MAR-06 12:46:32 388673     2015-MAR-06 13:06:33
  1    182     388673     2015-MAR-06 13:06:33 389092     2015-MAR-06 13:26:34
  1    183     389092     2015-MAR-06 13:26:34 389508     2015-MAR-06 13:46:33
  1    184     389508     2015-MAR-06 13:46:33 389985     2015-MAR-06 14:06:32
  1    185     389985     2015-MAR-06 14:06:32 390472     2015-MAR-06 14:26:33
  1    186     390472     2015-MAR-06 14:26:33 390888     2015-MAR-06 14:46:32
  1    187     390888     2015-MAR-06 14:46:32 391453     2015-MAR-06 15:06:32
  1    188     391453     2015-MAR-06 15:06:32 391878     2015-MAR-06 15:26:34
  1    189     391878     2015-MAR-06 15:26:34 392298     2015-MAR-06 15:46:40
  1    190     392298     2015-MAR-06 15:46:40 392809     2015-MAR-06 16:06:40
  1    191     392809     2015-MAR-06 16:06:40 393282     2015-MAR-06 16:26:40
  1    192     393282     2015-MAR-06 16:26:40 393699     2015-MAR-06 16:46:40
  1    193     393699     2015-MAR-06 16:46:40 394186     2015-MAR-06 17:06:41
  1    194     394186     2015-MAR-06 17:06:41 394671     2015-MAR-06 17:26:46
  1    195     394671     2015-MAR-06 17:26:46 395087     2015-MAR-06 17:46:45
  1    196     395087     2015-MAR-06 17:46:45 395649     2015-MAR-06 18:06:44
  1    197     395649     2015-MAR-06 18:06:44 396072     2015-MAR-06 18:26:44
  1    198     396072     2015-MAR-06 18:26:44 396489     2015-MAR-06 18:46:46
  1    199     396489     2015-MAR-06 18:46:46 396984     2015-MAR-06 19:06:46
  1    200     396984     2015-MAR-06 19:06:46 397481     2015-MAR-06 19:26:46
  1    201     397481     2015-MAR-06 19:26:46 397897     2015-MAR-06 19:46:45
  1    202     397897     2015-MAR-06 19:46:45 398392     2015-MAR-06 20:06:45
  1    203     398392     2015-MAR-06 20:06:45 398880     2015-MAR-06 20:26:44
  1    204     398880     2015-MAR-06 20:26:44 399299     2015-MAR-06 20:46:46
  1    205     399299     2015-MAR-06 20:46:46 399775     2015-MAR-06 21:06:46
  1    206     399775     2015-MAR-06 21:06:46 400258     2015-MAR-06 21:26:45
  1    207     400258     2015-MAR-06 21:26:45 400680     2015-MAR-06 21:46:44
  1    208     400680     2015-MAR-06 21:46:44 403781     2015-MAR-06 22:06:34

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
118     30.93M     DISK        00:00:02     2015-MAR-07 09:57:33
        BP Key: 118   Status: AVAILABLE  Compressed: NO  Tag: TAG20150307T095717
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1

  List of Archived Logs in backup set 118
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    209     403781     2015-MAR-06 22:06:34 404216     2015-MAR-06 22:26:33
  1    210     404216     2015-MAR-06 22:26:33 404648     2015-MAR-06 22:46:32
  1    211     404648     2015-MAR-06 22:46:32 405189     2015-MAR-06 23:06:32
  1    212     405189     2015-MAR-06 23:06:32 405678     2015-MAR-06 23:26:35
  1    213     405678     2015-MAR-06 23:26:35 406110     2015-MAR-06 23:46:39
  1    214     406110     2015-MAR-06 23:46:39 406628     2015-MAR-07 00:06:38
  1    215     406628     2015-MAR-07 00:06:38 407191     2015-MAR-07 00:26:40
  1    216     407191     2015-MAR-07 00:26:40 407622     2015-MAR-07 00:46:39
  1    217     407622     2015-MAR-07 00:46:39 408298     2015-MAR-07 01:06:39
  1    218     408298     2015-MAR-07 01:06:39 408734     2015-MAR-07 01:26:38
  1    219     408734     2015-MAR-07 01:26:38 409167     2015-MAR-07 01:46:40
  1    220     409167     2015-MAR-07 01:46:40 409684     2015-MAR-07 02:06:39
  1    221     409684     2015-MAR-07 02:06:39 410318     2015-MAR-07 02:26:39
  1    222     410318     2015-MAR-07 02:26:39 410780     2015-MAR-07 02:46:38
  1    223     410780     2015-MAR-07 02:46:38 411462     2015-MAR-07 03:06:38
  1    224     411462     2015-MAR-07 03:06:38 411884     2015-MAR-07 03:26:40
  1    225     411884     2015-MAR-07 03:26:40 412300     2015-MAR-07 03:46:40
  1    226     412300     2015-MAR-07 03:46:40 412794     2015-MAR-07 04:06:39
  1    227     412794     2015-MAR-07 04:06:39 413315     2015-MAR-07 04:26:38
  1    228     413315     2015-MAR-07 04:26:38 413736     2015-MAR-07 04:46:38
  1    229     413736     2015-MAR-07 04:46:38 414223     2015-MAR-07 05:06:40
  1    230     414223     2015-MAR-07 05:06:40 414710     2015-MAR-07 05:26:38
  1    231     414710     2015-MAR-07 05:26:38 415134     2015-MAR-07 05:46:38
  1    232     415134     2015-MAR-07 05:46:38 417948     2015-MAR-07 06:06:26
  1    233     417948     2015-MAR-07 06:06:26 418380     2015-MAR-07 06:26:26
  1    234     418380     2015-MAR-07 06:26:26 418813     2015-MAR-07 06:46:27
  1    235     418813     2015-MAR-07 06:46:27 419405     2015-MAR-07 07:06:26
  1    236     419405     2015-MAR-07 07:06:26 419841     2015-MAR-07 07:26:28
  1    237     419841     2015-MAR-07 07:26:28 420275     2015-MAR-07 07:46:34
  1    238     420275     2015-MAR-07 07:46:34 420777     2015-MAR-07 08:06:33
  1    239     420777     2015-MAR-07 08:06:33 421312     2015-MAR-07 08:26:32
  1    240     421312     2015-MAR-07 08:26:32 421745     2015-MAR-07 08:46:32
  1    241     421745     2015-MAR-07 08:46:32 422279     2015-MAR-07 09:06:32
  1    242     422279     2015-MAR-07 09:06:32 422793     2015-MAR-07 09:26:34
  1    243     422793     2015-MAR-07 09:26:34 423233     2015-MAR-07 09:46:40
  1    244     423233     2015-MAR-07 09:46:40 423510     2015-MAR-07 09:57:16
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
245     1    245     A 2015-MAR-07 09:57:16
        Name: /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc


channel ORA_DISK_1: scanning archived log /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2015-MAR-07 10:34:59

RMAN>

Validate archivelog and list summary for backupset. Just enough information?

RMAN> restore validate preview summary archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)”;

Starting restore at 2015-MAR-07 10:36:58
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
117     B  A  A DISK        2015-MAR-07 09:57:21 1       1       NO         TAG20150307T095717
118     B  A  A DISK        2015-MAR-07 09:57:33 1       1       NO         TAG20150307T095717
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
245     1    245     A 2015-MAR-07 09:57:16
        Name: /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc


channel ORA_DISK_1: scanning archived log /oradata/archivelog/hawklas/hawk_ba986d3b_1_871886678_245.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/3nq17j0b_1_1 tag=TAG20150307T095717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2015-MAR-07 10:37:01

RMAN> list backup of archivelog from time “TO_DATE(‘2015-MAR-04 22:03:32′,’YYYY-MON-DD HH24:MI:SS’)” summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
115     B  A  A DISK        2015-MAR-06 07:16:40 1       1       YES        TAG20150306T071638
117     B  A  A DISK        2015-MAR-07 09:57:21 1       1       NO         TAG20150307T095717
118     B  A  A DISK        2015-MAR-07 09:57:33 1       1       NO         TAG20150307T095717

Why is backupset 115 not used in restore validate?
RMAN> list backupset 115; – contains backup for seq 149-163

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
115     3.42M      DISK        00:00:01     2015-MAR-06 07:16:40
        BP Key: 115   Status: AVAILABLE  Compressed: YES  Tag: TAG20150306T071638
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3kq14l6n_1_1

  List of Archived Logs in backup set 115
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    149     351055     2015-MAR-04 22:03:32 371415     2015-MAR-05 14:54:39
  1    150     371415     2015-MAR-05 14:54:39 372594     2015-MAR-05 15:14:04
  1    151     372594     2015-MAR-05 15:14:04 373366     2015-MAR-05 15:34:04
  1    152     373366     2015-MAR-05 15:34:04 374012     2015-MAR-05 15:54:04
  1    153     374012     2015-MAR-05 15:54:04 374560     2015-MAR-05 16:14:05
  1    154     374560     2015-MAR-05 16:14:05 375084     2015-MAR-05 16:34:04
  1    155     375084     2015-MAR-05 16:34:04 375500     2015-MAR-05 16:54:05
  1    156     375500     2015-MAR-05 16:54:05 376122     2015-MAR-05 17:14:03
  1    157     376122     2015-MAR-05 17:14:03 376539     2015-MAR-05 17:34:05
  1    158     376539     2015-MAR-05 17:34:05 376952     2015-MAR-05 17:54:05
  1    159     376952     2015-MAR-05 17:54:05 377664     2015-MAR-05 18:14:04
  1    160     377664     2015-MAR-05 18:14:04 378236     2015-MAR-05 18:34:05
  1    161     378236     2015-MAR-05 18:34:05 378694     2015-MAR-05 18:54:05
  1    162     378694     2015-MAR-05 18:54:05 379347     2015-MAR-05 19:14:04
  1    163     379347     2015-MAR-05 19:14:04 379628     2015-MAR-06 07:16:37

RMAN>

Backup for seq 149-163 is in backupset 115 & 117

RMAN> list backup of archivelog from sequence 149 until sequence 163 summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
115     B  A  A DISK        2015-MAR-06 07:16:40 1       1       YES        TAG20150306T071638
117     B  A  A DISK        2015-MAR-07 09:57:21 1       1       NO         TAG20150307T095717

RMAN> list backupset 117; – verified

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
117     45.54M     DISK        00:00:04     2015-MAR-07 09:57:21
        BP Key: 117   Status: AVAILABLE  Compressed: NO  Tag: TAG20150307T095717
        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/3mq17ivt_1_1

  List of Archived Logs in backup set 117
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    149     351055     2015-MAR-04 22:03:32 371415     2015-MAR-05 14:54:39
  1    150     371415     2015-MAR-05 14:54:39 372594     2015-MAR-05 15:14:04
  1    151     372594     2015-MAR-05 15:14:04 373366     2015-MAR-05 15:34:04
  1    152     373366     2015-MAR-05 15:34:04 374012     2015-MAR-05 15:54:04
  1    153     374012     2015-MAR-05 15:54:04 374560     2015-MAR-05 16:14:05
  1    154     374560     2015-MAR-05 16:14:05 375084     2015-MAR-05 16:34:04
  1    155     375084     2015-MAR-05 16:34:04 375500     2015-MAR-05 16:54:05
  1    156     375500     2015-MAR-05 16:54:05 376122     2015-MAR-05 17:14:03
  1    157     376122     2015-MAR-05 17:14:03 376539     2015-MAR-05 17:34:05
  1    158     376539     2015-MAR-05 17:34:05 376952     2015-MAR-05 17:54:05
  1    159     376952     2015-MAR-05 17:54:05 377664     2015-MAR-05 18:14:04
  1    160     377664     2015-MAR-05 18:14:04 378236     2015-MAR-05 18:34:05
  1    161     378236     2015-MAR-05 18:34:05 378694     2015-MAR-05 18:54:05
  1    162     378694     2015-MAR-05 18:54:05 379347     2015-MAR-05 19:14:04
  1    163     379347     2015-MAR-05 19:14:04 379628     2015-MAR-06 07:16:37
  1    164     379628     2015-MAR-06 07:16:37 379909     2015-MAR-06 07:26:16
  1    165     379909     2015-MAR-06 07:26:16 380968     2015-MAR-06 07:46:16
  1    166     380968     2015-MAR-06 07:46:16 381650     2015-MAR-06 08:06:16
  1    167     381650     2015-MAR-06 08:06:16 382073     2015-MAR-06 08:26:16
  1    168     382073     2015-MAR-06 08:26:16 382491     2015-MAR-06 08:46:20
  1    169     382491     2015-MAR-06 08:46:20 382987     2015-MAR-06 09:06:22
  1    170     382987     2015-MAR-06 09:06:22 383474     2015-MAR-06 09:26:22
  1    171     383474     2015-MAR-06 09:26:22 383894     2015-MAR-06 09:46:21
  1    172     383894     2015-MAR-06 09:46:21 384457     2015-MAR-06 10:06:22
  1    173     384457     2015-MAR-06 10:06:22 384876     2015-MAR-06 10:26:22
  1    174     384876     2015-MAR-06 10:26:22 385294     2015-MAR-06 10:46:28
  1    175     385294     2015-MAR-06 10:46:28 385792     2015-MAR-06 11:06:28
  1    176     385792     2015-MAR-06 11:06:28 386280     2015-MAR-06 11:26:26
  1    177     386280     2015-MAR-06 11:26:26 386698     2015-MAR-06 11:46:27
  1    178     386698     2015-MAR-06 11:46:27 387196     2015-MAR-06 12:06:28
  1    179     387196     2015-MAR-06 12:06:28 387681     2015-MAR-06 12:26:28
  1    180     387681     2015-MAR-06 12:26:28 388098     2015-MAR-06 12:46:32
  1    181     388098     2015-MAR-06 12:46:32 388673     2015-MAR-06 13:06:33
  1    182     388673     2015-MAR-06 13:06:33 389092     2015-MAR-06 13:26:34
  1    183     389092     2015-MAR-06 13:26:34 389508     2015-MAR-06 13:46:33
  1    184     389508     2015-MAR-06 13:46:33 389985     2015-MAR-06 14:06:32
  1    185     389985     2015-MAR-06 14:06:32 390472     2015-MAR-06 14:26:33
  1    186     390472     2015-MAR-06 14:26:33 390888     2015-MAR-06 14:46:32
  1    187     390888     2015-MAR-06 14:46:32 391453     2015-MAR-06 15:06:32
  1    188     391453     2015-MAR-06 15:06:32 391878     2015-MAR-06 15:26:34
  1    189     391878     2015-MAR-06 15:26:34 392298     2015-MAR-06 15:46:40
  1    190     392298     2015-MAR-06 15:46:40 392809     2015-MAR-06 16:06:40
  1    191     392809     2015-MAR-06 16:06:40 393282     2015-MAR-06 16:26:40
  1    192     393282     2015-MAR-06 16:26:40 393699     2015-MAR-06 16:46:40
  1    193     393699     2015-MAR-06 16:46:40 394186     2015-MAR-06 17:06:41
  1    194     394186     2015-MAR-06 17:06:41 394671     2015-MAR-06 17:26:46
  1    195     394671     2015-MAR-06 17:26:46 395087     2015-MAR-06 17:46:45
  1    196     395087     2015-MAR-06 17:46:45 395649     2015-MAR-06 18:06:44
  1    197     395649     2015-MAR-06 18:06:44 396072     2015-MAR-06 18:26:44
  1    198     396072     2015-MAR-06 18:26:44 396489     2015-MAR-06 18:46:46
  1    199     396489     2015-MAR-06 18:46:46 396984     2015-MAR-06 19:06:46
  1    200     396984     2015-MAR-06 19:06:46 397481     2015-MAR-06 19:26:46
  1    201     397481     2015-MAR-06 19:26:46 397897     2015-MAR-06 19:46:45
  1    202     397897     2015-MAR-06 19:46:45 398392     2015-MAR-06 20:06:45
  1    203     398392     2015-MAR-06 20:06:45 398880     2015-MAR-06 20:26:44
  1    204     398880     2015-MAR-06 20:26:44 399299     2015-MAR-06 20:46:46
  1    205     399299     2015-MAR-06 20:46:46 399775     2015-MAR-06 21:06:46
  1    206     399775     2015-MAR-06 21:06:46 400258     2015-MAR-06 21:26:45
  1    207     400258     2015-MAR-06 21:26:45 400680     2015-MAR-06 21:46:44
  1    208     400680     2015-MAR-06 21:46:44 403781     2015-MAR-06 22:06:34

RMAN>

RMAN-06023: no backup or copy of datafile # found to restore

Thu, 2015-03-05 20:55

There’s a great note from MOS – Checklist for an RMAN Restore (Doc ID 1554636.1) but how many of you review this before performing a restore?

If you don’t then you are as guilty as I am.

RMAN> restore database until time "TO_DATE('2015-MAR-04 19:53:54','YYYY-MON-DD HH24:MI:SS')" preview summary;

Starting restore at 2015-MAR-05 18:03:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

datafile 5 will be created automatically during restore operation
datafile 6 will be created automatically during restore operation
datafile 7 will be created automatically during restore operation
datafile 8 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/05/2015 18:03:28
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Let’s check backup for datafile 5,6,7,8 – looks good

RMAN> list backup of datafile 5,6,7,8 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
98      B  0  A DISK        2015-MAR-04 19:54:24 1       1       YES        LEVEL0
99      B  0  A DISK        2015-MAR-04 19:54:26 1       1       YES        LEVEL0
100     B  0  A DISK        2015-MAR-04 19:54:27 1       1       YES        LEVEL0
101     B  0  A DISK        2015-MAR-04 19:54:29 1       1       YES        LEVEL0
109     B  1  A DISK        2015-MAR-04 22:03:26 1       1       YES        LEVEL1
110     B  1  A DISK        2015-MAR-04 22:03:28 1       1       YES        LEVEL1
111     B  1  A DISK        2015-MAR-04 22:03:29 1       1       YES        LEVEL1
112     B  1  A DISK        2015-MAR-04 22:03:30 1       1       YES        LEVEL1

RMAN>
RMAN> list backup of datafile 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
94      B  0  A DISK        2015-MAR-04 19:54:13 1       1       YES        LEVEL0
105     B  1  A DISK        2015-MAR-04 22:02:57 1       1       YES        LEVEL1

RMAN>

The until time was not far enough to include backup for data 1 ..

RMAN> restore database until time "TO_DATE('2015-MAR-04 19:54:30','YYYY-MON-DD HH24:MI:SS')" preview summary;

Starting restore at 2015-MAR-05 18:35:56
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
94      B  0  A DISK        2015-MAR-04 19:54:12 1       1       YES        LEVEL0
96      B  0  A DISK        2015-MAR-04 19:54:19 1       1       YES        LEVEL0
95      B  0  A DISK        2015-MAR-04 19:54:16 1       1       YES        LEVEL0
97      B  0  A DISK        2015-MAR-04 19:54:22 1       1       YES        LEVEL0
98      B  0  A DISK        2015-MAR-04 19:54:23 1       1       YES        LEVEL0
99      B  0  A DISK        2015-MAR-04 19:54:26 1       1       YES        LEVEL0
100     B  0  A DISK        2015-MAR-04 19:54:27 1       1       YES        LEVEL0
101     B  0  A DISK        2015-MAR-04 19:54:29 1       1       YES        LEVEL0


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
102     B  A  A DISK        2015-MAR-04 19:54:30 1       1       YES        ARCHIVELOG
Media recovery start SCN is 342495
Recovery must be done beyond SCN 342508 to clear datafile fuzziness
Finished restore at 2015-MAR-05 18:35:56

Where on earth did I get the time “2015-MAR-04 19:54:30″ to begin with?

I used Low Time from ARCHIVELOG backup which was not sufficient for demonstration purpose.

RMAN> list backupset 102;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
102     3.50K      DISK        00:00:00     2015-MAR-04 19:54:30
        BP Key: 102   Status: AVAILABLE  Compressed: YES  Tag: ARCHIVELOG
        Piece Name: /oradata/backup/arc_HAWK_3130551611_20150304_37q10orm_1_1

  List of Archived Logs in backup set 102
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    140     342477     2015-MAR-04 19:53:54 342514     2015-MAR-04 19:54:30

RMAN>

Instead, use “Next Time” which is also the same as “Completion Time” from list backup summary;

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
90      B  A  A DISK        2015-MAR-04 19:53:58 1       1       YES        ARCHIVELOG
91      B  A  A DISK        2015-MAR-04 19:54:00 1       1       YES        ARCHIVELOG
92      B  A  A DISK        2015-MAR-04 19:54:02 1       1       YES        ARCHIVELOG
93      B  A  A DISK        2015-MAR-04 19:54:05 1       1       YES        ARCHIVELOG
94      B  0  A DISK        2015-MAR-04 19:54:13 1       1       YES        LEVEL0
95      B  0  A DISK        2015-MAR-04 19:54:16 1       1       YES        LEVEL0
96      B  0  A DISK        2015-MAR-04 19:54:20 1       1       YES        LEVEL0
97      B  0  A DISK        2015-MAR-04 19:54:22 1       1       YES        LEVEL0
98      B  0  A DISK        2015-MAR-04 19:54:24 1       1       YES        LEVEL0
99      B  0  A DISK        2015-MAR-04 19:54:26 1       1       YES        LEVEL0
100     B  0  A DISK        2015-MAR-04 19:54:27 1       1       YES        LEVEL0
101     B  0  A DISK        2015-MAR-04 19:54:29 1       1       YES        LEVEL0
102     B  A  A DISK        2015-MAR-04 19:54:30 1       1       YES        ARCHIVELOG
103     B  F  A DISK        2015-MAR-04 19:54:34 1       1       NO         TAG20150304T195432
104     B  A  A DISK        2015-MAR-04 22:02:49 1       1       YES        ARCHIVELOG
105     B  1  A DISK        2015-MAR-04 22:02:57 1       1       YES        LEVEL1
106     B  1  A DISK        2015-MAR-04 22:03:04 1       1       YES        LEVEL1
107     B  1  A DISK        2015-MAR-04 22:03:18 1       1       YES        LEVEL1
108     B  1  A DISK        2015-MAR-04 22:03:24 1       1       YES        LEVEL1
109     B  1  A DISK        2015-MAR-04 22:03:26 1       1       YES        LEVEL1
110     B  1  A DISK        2015-MAR-04 22:03:28 1       1       YES        LEVEL1
111     B  1  A DISK        2015-MAR-04 22:03:29 1       1       YES        LEVEL1
112     B  1  A DISK        2015-MAR-04 22:03:30 1       1       YES        LEVEL1
113     B  A  A DISK        2015-MAR-04 22:03:32 1       1       YES        ARCHIVELOG
114     B  F  A DISK        2015-MAR-04 22:03:35 1       1       NO         TAG20150304T220333

RMAN>

Oracle Manual Standby – Applying Log

Fri, 2015-02-20 13:49

If you are running Oracle EE, there are many books on Data Guard. However, for Oracle SE and manual standby, have fun searching.

I wanted to get a better understanding of registering logfile.

Depending on how the standby environment is monitored, not registering logfile may yield incorrect results when checking standby lag.

When logfile is not registered, v$archived_log is not updated.

STANDBY: check status

[oracle@armor:hawk:/home/oracle]
$ sqlplus / as sysdba @stby.sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 20 10:12:58 2015

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  2  from
  3  (
  4    select thread# thrd, max(sequence#) almax from v$archived_log
  5    where resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES'
  6    group by thread#
  7  ) al,
  8  (
  9    select thread# thrd, max(sequence#) lhmax
 10    from v$log_history
 11    where first_time=(select max(first_time) from v$log_history)
 12    group by thread#
 13  ) lh
 14  where al.thrd = lh.thrd
 15  ;

          Thread Last Seq Received Last Seq Applied
---------------- ----------------- ----------------
               1                44               44

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select first_time,applied,archived,registrar,creator,thread#,sequence# from v$archived_log
  2  where resetlogs_change#=(select resetlogs_change# from v$database)
  3  and first_time>sysdate-3/24
  4  order by sequence# desc
  5  ;

NOTE: SRMN - RMAN at standby

FIRST_TIME          APPLIED   ARC REGISTR CREATOR          THREAD#        SEQUENCE#
------------------- --------- --- ------- ------- ---------------- ----------------
2015-02-20 10:09:09 YES       YES SRMN    SRMN                   1               44
2015-02-20 10:03:54 NO        YES SRMN    SRMN                   1               43
2015-02-20 09:58:33 YES       YES SRMN    SRMN                   1               42
2015-02-20 09:52:53 YES       YES SRMN    SRMN                   1               41
2015-02-20 09:47:17 YES       YES SRMN    SRMN                   1               40
2015-02-20 09:45:50 YES       YES SRMN    SRMN                   1               39
2015-02-20 09:41:36 YES       YES SRMN    SRMN                   1               38
2015-02-20 09:21:34 YES       YES SRMN    SRMN                   1               37
2015-02-20 09:01:33 YES       YES SRMN    SRMN                   1               36
2015-02-20 08:41:35 YES       YES SRMN    SRMN                   1               35
2015-02-20 08:21:34 YES       YES SRMN    SRMN                   1               34
2015-02-20 08:01:33 YES       YES SRMN    SRMN                   1               33
2015-02-20 07:41:30 YES       YES SRMN    SRMN                   1               32
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               31
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               30
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               29
2015-02-20 07:41:26 YES       YES SRMN    SRMN                   1               28
2015-02-20 07:41:23 YES       YES SRMN    SRMN                   1               27
2015-02-20 07:24:52 YES       YES SRMN    SRMN                   1               26

19 rows selected.

ARMOR:(SYS@hawk):PHYSICAL STANDBY>

PRIMARY: create tablespace new1

ANGEL:(SYS@hawk):PRIMARY> create tablespace new1;

Tablespace created.

ANGEL:(SYS@hawk):PRIMARY> alter system archive log current;

System altered.

ANGEL:(SYS@hawk):PRIMARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/archivelog/hawk
Oldest online log sequence     44
Next log sequence to archive   46
Current log sequence           46
ANGEL:(SYS@hawk):PRIMARY>

PRIMARY: transfer archive log to STANDBY

[oracle@angel:hawk:/oradata/archivelog/hawk]
$ rsync -avh /oradata/archivelog/hawk/ armor:/oradata/archivelog/hawk/
sending incremental file list
./
hawk_45_1_872097259.arc

sent 170.68K bytes  received 34 bytes  341.42K bytes/sec
total size is 78.17M  speedup is 457.90
[oracle@angel:hawk:/oradata/archivelog/hawk]
$

STANDBY: apply log

[oracle@armor:hawk:/home/oracle]
$ sqlplus / as sysdba @recoverauto.sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 20 10:14:43 2015

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

ARMOR:(SYS@hawk):PHYSICAL STANDBY> recover automatic standby database;
ORA-00279: change 216448 generated at 02/20/2015 10:14:03 needed for thread 1
ORA-00289: suggestion : /oradata/archivelog/hawk/hawk_46_1_872097259.arc
ORA-00280: change 216448 for thread 1 is in sequence #46
ORA-00278: log file '/oradata/archivelog/hawk/hawk_46_1_872097259.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/archivelog/hawk/hawk_46_1_872097259.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
ARMOR:(SYS@hawk):PHYSICAL STANDBY> @@stby.sql
ARMOR:(SYS@hawk):PHYSICAL STANDBY> set echo on numw 16 lines 200 pages 1000
ARMOR:(SYS@hawk):PHYSICAL STANDBY> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  2  from
  3  (
  4    select thread# thrd, max(sequence#) almax from v$archived_log
  5    where resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES'
  6    group by thread#
  7  ) al,
  8  (
  9    select thread# thrd, max(sequence#) lhmax
 10    from v$log_history
 11    where first_time=(select max(first_time) from v$log_history)
 12    group by thread#
 13  ) lh
 14  where al.thrd = lh.thrd
 15  ;

          Thread Last Seq Received Last Seq Applied
---------------- ----------------- ----------------
               1                44               45

NOTE: sequence 45 is not registered with v$archived_log but has been applied

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select first_time,applied,archived,registrar,creator,thread#,sequence# from v$archived_log
  2  where resetlogs_change#=(select resetlogs_change# from v$database)
  3  and first_time>sysdate-3/24
  4  order by sequence# desc
  5  ;

FIRST_TIME          APPLIED   ARC REGISTR CREATOR          THREAD#        SEQUENCE#
------------------- --------- --- ------- ------- ---------------- ----------------
2015-02-20 10:09:09 YES       YES SRMN    SRMN                   1               44
2015-02-20 10:03:54 NO        YES SRMN    SRMN                   1               43
2015-02-20 09:58:33 YES       YES SRMN    SRMN                   1               42
2015-02-20 09:52:53 YES       YES SRMN    SRMN                   1               41
2015-02-20 09:47:17 YES       YES SRMN    SRMN                   1               40
2015-02-20 09:45:50 YES       YES SRMN    SRMN                   1               39
2015-02-20 09:41:36 YES       YES SRMN    SRMN                   1               38
2015-02-20 09:21:34 YES       YES SRMN    SRMN                   1               37
2015-02-20 09:01:33 YES       YES SRMN    SRMN                   1               36
2015-02-20 08:41:35 YES       YES SRMN    SRMN                   1               35
2015-02-20 08:21:34 YES       YES SRMN    SRMN                   1               34
2015-02-20 08:01:33 YES       YES SRMN    SRMN                   1               33
2015-02-20 07:41:30 YES       YES SRMN    SRMN                   1               32
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               31
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               30
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               29
2015-02-20 07:41:26 YES       YES SRMN    SRMN                   1               28
2015-02-20 07:41:23 YES       YES SRMN    SRMN                   1               27
2015-02-20 07:24:52 YES       YES SRMN    SRMN                   1               26

19 rows selected.

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
NEW1

6 rows selected.

ARMOR:(SYS@hawk):PHYSICAL STANDBY>

PRIMARY: create tablespace new2

ANGEL:(SYS@hawk):PRIMARY> create tablespace new2;

Tablespace created.

ANGEL:(SYS@hawk):PRIMARY> alter system archive log current;

System altered.

ANGEL:(SYS@hawk):PRIMARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/archivelog/hawk
Oldest online log sequence     45
Next log sequence to archive   47
Current log sequence           47
ANGEL:(SYS@hawk):PRIMARY>

PRIMARY: transfer archive log to STANDBY

[oracle@angel:hawk:/oradata/archivelog/hawk]
$ rsync -avh /oradata/archivelog/hawk/ armor:/oradata/archivelog/hawk/
sending incremental file list
./
hawk_46_1_872097259.arc

sent 144.59K bytes  received 34 bytes  289.24K bytes/sec
total size is 78.31M  speedup is 541.50
[oracle@angel:hawk:/oradata/archivelog/hawk]
$

STANDBY: register archive log using RMAN

[oracle@armor:hawk:/home/oracle]
$ rman @catalog_arc.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 20 10:16:20 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> set echo on
2> connect target;
3> catalog start with '/oradata/archivelog/hawk/' noprompt;
4> exit
echo set on

connected to target database: HAWK (DBID=3130795691, not open)

using target database control file instead of recovery catalog
searching for all files that match the pattern /oradata/archivelog/hawk/

List of Files Unknown to the Database
=====================================
File Name: /oradata/archivelog/hawk/hawk_46_1_872097259.arc
File Name: /oradata/archivelog/hawk/hawk_45_1_872097259.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/archivelog/hawk/hawk_46_1_872097259.arc
File Name: /oradata/archivelog/hawk/hawk_45_1_872097259.arc

Recovery Manager complete.

STANDBY: apply log

[oracle@armor:hawk:/home/oracle]
$ sqlplus / as sysdba @recoverauto.sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 20 10:16:34 2015

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

ARMOR:(SYS@hawk):PHYSICAL STANDBY> recover automatic standby database;
ORA-00279: change 216754 generated at 02/20/2015 10:15:39 needed for thread 1
ORA-00289: suggestion : /oradata/archivelog/hawk/hawk_47_1_872097259.arc
ORA-00280: change 216754 for thread 1 is in sequence #47
ORA-00278: log file '/oradata/archivelog/hawk/hawk_47_1_872097259.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/archivelog/hawk/hawk_47_1_872097259.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
ARMOR:(SYS@hawk):PHYSICAL STANDBY> @@stby.sql
ARMOR:(SYS@hawk):PHYSICAL STANDBY> set echo on numw 16 lines 200 pages 1000
ARMOR:(SYS@hawk):PHYSICAL STANDBY> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  2  from
  3  (
  4    select thread# thrd, max(sequence#) almax from v$archived_log
  5    where resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES'
  6    group by thread#
  7  ) al,
  8  (
  9    select thread# thrd, max(sequence#) lhmax
 10    from v$log_history
 11    where first_time=(select max(first_time) from v$log_history)
 12    group by thread#
 13  ) lh
 14  where al.thrd = lh.thrd
 15  ;

          Thread Last Seq Received Last Seq Applied
---------------- ----------------- ----------------
               1                46               46

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select first_time,applied,archived,registrar,creator,thread#,sequence# from v$archived_log
  2  where resetlogs_change#=(select resetlogs_change# from v$database)
  3  and first_time>sysdate-3/24
  4  order by sequence# desc
  5  ;

FIRST_TIME          APPLIED   ARC REGISTR CREATOR          THREAD#        SEQUENCE#
------------------- --------- --- ------- ------- ---------------- ----------------
2015-02-20 10:14:03 YES       YES SRMN    SRMN                   1               46
2015-02-20 10:11:03 NO        YES SRMN    SRMN                   1               45
2015-02-20 10:09:09 YES       YES SRMN    SRMN                   1               44
2015-02-20 10:03:54 NO        YES SRMN    SRMN                   1               43
2015-02-20 09:58:33 YES       YES SRMN    SRMN                   1               42
2015-02-20 09:52:53 YES       YES SRMN    SRMN                   1               41
2015-02-20 09:47:17 YES       YES SRMN    SRMN                   1               40
2015-02-20 09:45:50 YES       YES SRMN    SRMN                   1               39
2015-02-20 09:41:36 YES       YES SRMN    SRMN                   1               38
2015-02-20 09:21:34 YES       YES SRMN    SRMN                   1               37
2015-02-20 09:01:33 YES       YES SRMN    SRMN                   1               36
2015-02-20 08:41:35 YES       YES SRMN    SRMN                   1               35
2015-02-20 08:21:34 YES       YES SRMN    SRMN                   1               34
2015-02-20 08:01:33 YES       YES SRMN    SRMN                   1               33
2015-02-20 07:41:30 YES       YES SRMN    SRMN                   1               32
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               31
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               30
2015-02-20 07:41:29 YES       YES SRMN    SRMN                   1               29
2015-02-20 07:41:26 YES       YES SRMN    SRMN                   1               28
2015-02-20 07:41:23 YES       YES SRMN    SRMN                   1               27
2015-02-20 07:24:52 YES       YES SRMN    SRMN                   1               26

21 rows selected.

STANDBY: open read only test

ARMOR:(SYS@hawk):PHYSICAL STANDBY> @openreadonly
ARMOR:(SYS@hawk):PHYSICAL STANDBY> set echo on numw 16 lines 200
ARMOR:(SYS@hawk):PHYSICAL STANDBY> alter database open read only;

Database altered.

ARMOR:(SYS@hawk):PHYSICAL STANDBY> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ARMOR:(SYS@hawk):PHYSICAL STANDBY> startup nomount;
ORACLE instance started.

Total System Global Area       1068937216 bytes
Fixed Size                        2260088 bytes
Variable Size                   281019272 bytes
Database Buffers                780140544 bytes
Redo Buffers                      5517312 bytes
ARMOR:(SYS@hawk):PHYSICAL STANDBY> alter database mount standby database;

Database altered.

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select controlfile_type,open_mode,database_role,db_unique_name,standby_became_primary_scn from v$database
  2  ;

CONTROL OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 STANDBY_BECAME_PRIMARY_SCN
------- -------------------- ---------------- ------------------------------ --------------------------
STANDBY MOUNTED              PHYSICAL STANDBY hawk_sfo                                                0

ARMOR:(SYS@hawk):PHYSICAL STANDBY> @@stby.sql
ARMOR:(SYS@hawk):PHYSICAL STANDBY> set echo on numw 16 lines 200 pages 1000
ARMOR:(SYS@hawk):PHYSICAL STANDBY> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
  2  from
  3  (
  4    select thread# thrd, max(sequence#) almax from v$archived_log
  5    where resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES'
  6    group by thread#
  7  ) al,
  8  (
  9    select thread# thrd, max(sequence#) lhmax
 10    from v$log_history
 11    where first_time=(select max(first_time) from v$log_history)
 12    group by thread#
 13  ) lh
 14  where al.thrd = lh.thrd
 15  ;

          Thread Last Seq Received Last Seq Applied
---------------- ----------------- ----------------
               1                46               46

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select first_time,applied,archived,registrar,creator,thread#,sequence# from v$archived_log
  2  where resetlogs_change#=(select resetlogs_change# from v$database)
  3  and first_time>sysdate-3/24
  4  order by sequence# desc
  5  ;

FIRST_TIM APPLIED   ARC REGISTR CREATOR          THREAD#        SEQUENCE#
--------- --------- --- ------- ------- ---------------- ----------------
20-FEB-15 YES       YES SRMN    SRMN                   1               46
20-FEB-15 YES       YES SRMN    SRMN                   1               45
20-FEB-15 YES       YES SRMN    SRMN                   1               44
20-FEB-15 YES       YES SRMN    SRMN                   1               43
20-FEB-15 YES       YES SRMN    SRMN                   1               42
20-FEB-15 YES       YES SRMN    SRMN                   1               41
20-FEB-15 YES       YES SRMN    SRMN                   1               40
20-FEB-15 YES       YES SRMN    SRMN                   1               39
20-FEB-15 YES       YES SRMN    SRMN                   1               38
20-FEB-15 YES       YES SRMN    SRMN                   1               37
20-FEB-15 YES       YES SRMN    SRMN                   1               36
20-FEB-15 YES       YES SRMN    SRMN                   1               35
20-FEB-15 YES       YES SRMN    SRMN                   1               34
20-FEB-15 YES       YES SRMN    SRMN                   1               33
20-FEB-15 YES       YES SRMN    SRMN                   1               32
20-FEB-15 YES       YES SRMN    SRMN                   1               31
20-FEB-15 YES       YES SRMN    SRMN                   1               30
20-FEB-15 YES       YES SRMN    SRMN                   1               29
20-FEB-15 YES       YES SRMN    SRMN                   1               28
20-FEB-15 YES       YES SRMN    SRMN                   1               27
20-FEB-15 YES       YES SRMN    SRMN                   1               26

21 rows selected.

ARMOR:(SYS@hawk):PHYSICAL STANDBY> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
NEW1
NEW2

7 rows selected.

ARMOR:(SYS@hawk):PHYSICAL STANDBY>

PRIMARY: configuration

ANGEL:(SYS@hawk):PRIMARY> show parameter name

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
cell_offloadgroup_name    string
db_file_name_convert      string
db_name                   string      hawk
db_unique_name            string      hawk
global_names              boolean     FALSE
instance_name             string      hawk
lock_name_space           string
log_file_name_convert     string
processor_group_name      string
service_names             string      hawk
ANGEL:(SYS@hawk):PRIMARY> show parameter convert

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
db_file_name_convert      string
log_file_name_convert     string
ANGEL:(SYS@hawk):PRIMARY> show parameter standby

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
standby_archive_dest      string      ?/dbs/arch
standby_file_management   string      AUTO
ANGEL:(SYS@hawk):PRIMARY> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/HAWK/datafile/o1_mf_system_bgf2mflo_.dbf
/oradata/HAWK/datafile/o1_mf_sysaux_bgf2mj87_.dbf
/oradata/HAWK/datafile/o1_mf_undotbs1_bgf2mkds_.dbf
/oradata/HAWK/datafile/o1_mf_users_bgf2mlf8_.dbf
/oradata/HAWK/datafile/o1_mf_new1_bggyc50z_.dbf
/oradata/HAWK/datafile/o1_mf_new2_bggyg5ky_.dbf

6 rows selected.

ANGEL:(SYS@hawk):PRIMARY>

STANDBY: configuration

ARMOR:(SYS@hawk):PHYSICAL STANDBY> show parameter name

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
cell_offloadgroup_name    string
db_file_name_convert      string
db_name                   string      hawk
db_unique_name            string      hawk_sfo
global_names              boolean     FALSE
instance_name             string      hawk
lock_name_space           string
log_file_name_convert     string
processor_group_name      string
service_names             string      hawk
ARMOR:(SYS@hawk):PHYSICAL STANDBY> show parameter convert

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
db_file_name_convert      string
log_file_name_convert     string
ARMOR:(SYS@hawk):PHYSICAL STANDBY> show parameter standby

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
standby_archive_dest      string      ?/dbs/arch
standby_file_management   string      AUTO
ARMOR:(SYS@hawk):PHYSICAL STANDBY> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/HAWK_SFO/datafile/o1_mf_system_bgf4o5x7_.dbf
/oradata/HAWK_SFO/datafile/o1_mf_sysaux_bgf4on2c_.dbf
/oradata/HAWK_SFO/datafile/o1_mf_undotbs1_bgf4of05_.dbf
/oradata/HAWK_SFO/datafile/o1_mf_users_bgf4oq4b_.dbf
/oradata/HAWK_SFO/datafile/o1_mf_new1_bggydmsq_.dbf
/oradata/HAWK_SFO/datafile/o1_mf_new2_bggyj2n3_.dbf

6 rows selected.

ARMOR:(SYS@hawk):PHYSICAL STANDBY>

PRIMARY: drop tablespace and register/apply log to STANDBY – review alert log

[oracle@armor:hawk:/u01/app/oracle/diag/rdbms/hawk_sfo/hawk/trace]
$ cat alert_hawk.log
Fri Feb 20 11:23:18 2015
ALTER DATABASE RECOVER  automatic standby database
Media Recovery Start
 started logmerger process
Fri Feb 20 11:23:18 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Media Recovery Log /oradata/archivelog/hawk/hawk_55_1_872097259.arc
Recovery deleting file #5:'/oradata/HAWK_SFO/datafile/o1_mf_new1_bggydmsq_.dbf' from controlfile.
Deleted Oracle managed file /oradata/HAWK_SFO/datafile/o1_mf_new1_bggydmsq_.dbf
Recovery dropped tablespace 'NEW1'
Recovery deleting file #6:'/oradata/HAWK_SFO/datafile/o1_mf_new2_bggyj2n3_.dbf' from controlfile.
Deleted Oracle managed file /oradata/HAWK_SFO/datafile/o1_mf_new2_bggyj2n3_.dbf
Recovery dropped tablespace 'NEW2'
Media Recovery Log /oradata/archivelog/hawk/hawk_56_1_872097259.arc
Errors with log /oradata/archivelog/hawk/hawk_56_1_872097259.arc
Errors in file /u01/app/oracle/diag/rdbms/hawk_sfo/hawk/trace/hawk_pr00_13085.trc:
ORA-00308: cannot open archived log '/oradata/archivelog/hawk/hawk_56_1_872097259.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-279 signalled during: ALTER DATABASE RECOVER  automatic standby database  ...
ALTER DATABASE RECOVER    CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER    CANCEL
[oracle@armor:hawk:/u01/app/oracle/diag/rdbms/hawk_sfo/hawk/trace]
$

Using rsync to clone Goldengate installation

Mon, 2015-02-16 09:15

You may be thinking, why clone Goldengate and why now just download it?
The exact version and patch level might not be available.
Too lazy to search for it and many other reasons you can come up with.

Why use rsync and not tar – scp? I wanted to refresh memory of using rsync.

Commands used:

local source /u01/app/ggs01/ and remote target arrow:/u01/app/ggs03/

rsync -avh --delete --dry-run --exclude 'dirdatold' /u01/app/ggs01/ arrow:/u01/app/ggs03/
rsync -avh --delete --exclude 'dirdatold' /u01/app/ggs01/ arrow:/u01/app/ggs03/

Note:
/u01/app/ggs01/ means synch contents of directory to target
/u01/app/ggs01 means create ggs01 directory and sync contents to target

Demo:

Source: /u01/app/ggs01 and dirdata is symbolic link

oracle@arrow:las:/u01/app/ggs01
$ ls -ld dir*
drwxr-x---. 2 oracle oinstall 4096 Jan 13 13:12 dirchk
lrwxrwxrwx. 1 oracle oinstall   15 Feb 15 06:20 dirdat -> /oradata/backup
drwxr-x---. 2 oracle oinstall 4096 Jul 22  2014 dirdatold
drwxr-x---. 2 oracle oinstall 4096 Apr 26  2014 dirdef
drwxr-x---. 2 oracle oinstall 4096 Apr  4  2014 dirjar
drwxr-x---. 2 oracle oinstall 4096 Apr 26  2014 dirout
drwxr-x---. 2 oracle oinstall 4096 Feb 12 15:35 dirpcs
drwxr-x---. 2 oracle oinstall 4096 Jan 13 12:55 dirprm
drwxr-x---. 2 oracle oinstall 4096 Feb 12 15:36 dirrpt
drwxr-x---. 2 oracle oinstall 4096 Apr 26  2014 dirsql
drwxr-x---. 2 oracle oinstall 4096 Sep 25 08:56 dirtmp

Target: arrow:/u01/app/ggs03/

oracle@arrow:las:/u01/app/ggs01
$ ls -l /u01/app/ggs03/
total 0

Let’s do a dry run first.

oracle@arrow:las:/u01/app/ggs01

$ rsync -avh --delete --dry-run --exclude 'dirdatold' /u01/app/ggs01/ arrow:/u01/app/ggs03/
oracle@arrow's password:

sending incremental file list
./

.....

output ommited for brevity

dirout/
dirpcs/
dirprm/
dirprm/esan.prm
dirprm/jagent.prm
dirprm/mgr.prm
dirrpt/
dirrpt/ESAN.rpt
dirrpt/ESAN0.rpt
dirrpt/ESAN1.rpt
dirrpt/ESAN2.rpt
dirrpt/ESAN3.rpt
dirrpt/ESAN4.rpt
dirrpt/ESAN5.rpt
dirrpt/ESAN6.rpt
dirrpt/ESAN7.rpt
dirrpt/ESAN8.rpt
dirrpt/ESAN9.rpt
dirrpt/MGR.rpt
dirrpt/MGR0.rpt
dirrpt/MGR1.rpt
dirrpt/MGR2.rpt
dirrpt/MGR3.rpt
dirrpt/MGR4.rpt
dirrpt/MGR5.rpt
dirrpt/MGR6.rpt
dirrpt/MGR7.rpt
dirrpt/MGR8.rpt
dirrpt/MGR9.rpt
dirsql/
dirtmp/


sent 6.96K bytes  received 767 bytes  15.44K bytes/sec
total size is 237.10M  speedup is 30704.36 (DRY RUN)

oracle@arrow:las:/u01/app/ggs01
$ ls -l /u01/app/ggs03/
total 0

Perform actual rsync

oracle@arrow:las:/u01/app/ggs01

$ rsync -avh --delete --exclude 'dirdatold' /u01/app/ggs01/ arrow:/u01/app/ggs03/
oracle@arrow's password:

sending incremental file list
./

.....

output ommited for brevity

dirout/
dirpcs/
dirprm/
dirprm/esan.prm
dirprm/jagent.prm
dirprm/mgr.prm
dirrpt/
dirrpt/ESAN.rpt
dirrpt/ESAN0.rpt
dirrpt/ESAN1.rpt
dirrpt/ESAN2.rpt
dirrpt/ESAN3.rpt
dirrpt/ESAN4.rpt
dirrpt/ESAN5.rpt
dirrpt/ESAN6.rpt
dirrpt/ESAN7.rpt
dirrpt/ESAN8.rpt
dirrpt/ESAN9.rpt
dirrpt/MGR.rpt
dirrpt/MGR0.rpt
dirrpt/MGR1.rpt
dirrpt/MGR2.rpt
dirrpt/MGR3.rpt
dirrpt/MGR4.rpt
dirrpt/MGR5.rpt
dirrpt/MGR6.rpt
dirrpt/MGR7.rpt
dirrpt/MGR8.rpt
dirrpt/MGR9.rpt
dirsql/
dirtmp/

sent 237.14M bytes  received 4.40K bytes  31.62M bytes/sec
total size is 237.10M  speedup is 1.00

oracle@arrow:las:/u01/app/ggs01
$ ls -ld /u01/app/ggs03/dir*
drwxr-x---. 2 oracle oinstall 4096 Jan 13 13:12 /u01/app/ggs03/dirchk

lrwxrwxrwx. 1 oracle oinstall   15 Feb 15 06:20 /u01/app/ggs03/dirdat -> /oradata/backup

drwxr-x---. 2 oracle oinstall 4096 Apr 26  2014 /u01/app/ggs03/dirdef
drwxr-x---. 2 oracle oinstall 4096 Apr  4  2014 /u01/app/ggs03/dirjar
drwxr-x---. 2 oracle oinstall 4096 Apr 26  2014 /u01/app/ggs03/dirout
drwxr-x---. 2 oracle oinstall 4096 Feb 12 15:35 /u01/app/ggs03/dirpcs
drwxr-x---. 2 oracle oinstall 4096 Jan 13 12:55 /u01/app/ggs03/dirprm
drwxr-x---. 2 oracle oinstall 4096 Feb 12 15:36 /u01/app/ggs03/dirrpt
drwxr-x---. 2 oracle oinstall 4096 Apr 26  2014 /u01/app/ggs03/dirsql
drwxr-x---. 2 oracle oinstall 4096 Sep 25 08:56 /u01/app/ggs03/dirtmp
oracle@arrow:las:/u01/app/ggs01
$

Did it work?

oracle@arrow:las:/u01/app/ggs01
$ cd /u01/app/ggs03/
oracle@arrow:las:/u01/app/ggs03
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

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



GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     ESAN        00:01:02      788:32:31
REPLICAT    STOPPED     RLAS_SAN    00:00:00      4989:14:29


GGSCI (arrow.localdomain) 2> exit

The above was from a neglected test environment
Delete details for extract/replicat at dirchk

oracle@arrow:las:/u01/app/ggs03
$ cd dirchk/
oracle@arrow:las:/u01/app/ggs03/dirchk
$ ll
total 8
-rw-r-----. 1 oracle oinstall 2048 Jan 13 13:12 ESAN.cpe
-rw-r-----. 1 oracle oinstall 2048 Jul 22  2014 RLAS_SAN.cpr
oracle@arrow:las:/u01/app/ggs03/dirchk
$ rm *
oracle@arrow:las:/u01/app/ggs03/dirchk
$ cd ../dirpcs/
oracle@arrow:las:/u01/app/ggs03/dirpcs
$ ll
total 0
oracle@arrow:las:/u01/app/ggs03/dirpcs
$ cd ..
oracle@arrow:las:/u01/app/ggs03
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

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



GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (arrow.localdomain) 2> start mgr

Manager started.


GGSCI (arrow.localdomain) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (arrow.localdomain) 4> exit

Now, what’s wrong?

oracle@arrow:las:/u01/app/ggs03
$ tail ggserr.log
2015-02-12 15:36:02  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2015-02-12 15:36:02  ERROR   OGG-00664  Oracle GoldenGate Manager for Oracle, mgr.prm:  OCI Error during OCIServerAttach (status = 12541-ORA-12541: TNS:no listener).
2015-02-12 15:36:02  ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING.
2015-02-12 15:36:04  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2015-02-15 09:44:51  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2015-02-15 09:45:31  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2015-02-15 09:45:41  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2015-02-15 09:45:43  ERROR   OGG-00664  Oracle GoldenGate Manager for Oracle, mgr.prm:  OCI Error during OCIServerAttach (status = 12541-ORA-12541: TNS:no listener).
2015-02-15 09:45:43  ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING.
2015-02-15 09:45:44  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.

oracle@arrow:las:/u01/app/ggs03
$ cat dirprm/mgr.prm
PORT 7901
DYNAMICPORTLIST 15100-15120

USERID ggs@san, PASSWORD *****

PURGEOLDEXTRACTS /u01/app/ggs01/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
PURGEMARKERHISTORY MINKEEPDAYS 5, MAXKEEPDAYS 7, FREQUENCYHOURS 24
PURGEDDLHISTORY MINKEEPDAYS 5, MAXKEEPDAYS 7, FREQUENCYHOURS 24

AUTOSTART ER *
AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60

CHECKMINUTES 1
LAGINFOMINUTES 0
LAGCRITICALMINUTES 1

oracle@arrow:las:/u01/app/ggs03
$ vi dirprm/mgr.prm

oracle@arrow:las:/u01/app/ggs03
$ cat dirprm/mgr.prm
PORT 7901
DYNAMICPORTLIST 15100-15120

-- USERID ggs@san, PASSWORD 888

PURGEOLDEXTRACTS /u01/app/ggs01/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
PURGEMARKERHISTORY MINKEEPDAYS 5, MAXKEEPDAYS 7, FREQUENCYHOURS 24
PURGEDDLHISTORY MINKEEPDAYS 5, MAXKEEPDAYS 7, FREQUENCYHOURS 24

AUTOSTART ER *
AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60

CHECKMINUTES 1
LAGINFOMINUTES 0
LAGCRITICALMINUTES 1
oracle@arrow:las:/u01/app/ggs03
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

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



GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (arrow.localdomain) 2> start mgr

Manager started.


GGSCI (arrow.localdomain) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (arrow.localdomain) 4> exit
oracle@arrow:las:/u01/app/ggs03
$

Don’t forget Oracle libraries are required to run Goldengate

oracle@arrow:las:/u01/app/ggs03
$ ldd ggsci
        linux-vdso.so.1 =>  (0x00007fff95ffa000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00000039e6000000)
        libgglog.so => /u01/app/ggs03/./libgglog.so (0x00007f862ca8d000)
        libggrepo.so => /u01/app/ggs03/./libggrepo.so (0x00007f862c923000)
        libdb-5.2.so => /u01/app/ggs03/./libdb-5.2.so (0x00007f862c688000)
        libicui18n.so.38 => /u01/app/ggs03/./libicui18n.so.38 (0x00007f862c327000)
        libicuuc.so.38 => /u01/app/ggs03/./libicuuc.so.38 (0x00007f862bfee000)
        libicudata.so.38 => /u01/app/ggs03/./libicudata.so.38 (0x00007f862b012000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00000039e6800000)
        libxerces-c.so.28 => /u01/app/ggs03/./libxerces-c.so.28 (0x00007f862aafa000)
        libantlr3c.so => /u01/app/ggs03/./libantlr3c.so (0x00007f862a9e4000)

        libnnz11.so => /u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.so (0x00007f862a616000)
        libclntsh.so.11.1 => /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1 (0x00007f8627ba0000)

        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00000039f1c00000)
        libm.so.6 => /lib64/libm.so.6 (0x00000039e7400000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000039f1800000)
        libc.so.6 => /lib64/libc.so.6 (0x00000039e6400000)
        /lib64/ld-linux-x86-64.so.2 (0x00000039e5c00000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00000039f3400000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f862799d000)

oracle@arrow:las:/u01/app/ggs03
$ env |egrep 'HOME|LD'
OLDPWD=/home/oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
oracle@arrow:las:/u01/app/ggs03
$ unset ORACLE_HOME
oracle@arrow:las:/u01/app/ggs03
$ export LD_LIBRARY_PATH=/lib:/usr/lib
oracle@arrow:las:/u01/app/ggs03
$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
oracle@arrow:las:/u01/app/ggs03
$

Goldengate – start replicat ATSCN or AFTERSCN ?

Mon, 2015-02-09 21:36

When using Goldengate to instantiate target database from an Oracle source database, replicat process can be started to concide with extract based the method used for instantiation, e.g. RMAN or datapump.

ATSCN is used to start replicat if RMAN is used to instantiate target.
From Database Backup and Recovery Reference, UNTIL SCN specifies an SCN as an upper limit.
RMAN restore or recover up to but not including the specified SCN.

AFTERSCN is used to start replicat if datapump is used to instantiate target.
The export operation performed is consistent as of FLASHBACK_SCN.

Hope this helps to clear up when to use ATSCN versus AFTERSCN.

Referemce:
Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database –  Doc ID 1276058.1