February | 2015 | Thinking Out Loud

February 20, 2015

Oracle Manual Standby – Applying Log

Filed under: 11g,oracle,RMAN,standby — mdinh @ 7:49 pm
Tags: standby

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

February 16, 2015

Using rsync to clone Goldengate installation

Filed under: GoldenGate — mdinh @ 3:15 pm

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
$

February 10, 2015

Goldengate – start replicat ATCSN or AFTERCSN?

Filed under: DataPump,GoldenGate,RMAN — mdinh @ 3:36 am

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

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

AFTERCSN 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 ATCSN versus AFTERCSN.

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

Comments (5)

Thinking Out Loud

February 20, 2015

Oracle Manual Standby – Applying Log

February 16, 2015

Using rsync to clone Goldengate installation

Commands used:

Demo:

February 10, 2015

Goldengate – start replicat ATCSN or AFTERCSN?

Menu

Follow Blog via Email

Meta

Categories

Archives