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]
$
Thinking Out Loud 