Setting Password for database listener

Introduction

This is a simple post which explains you how to secure your listener by setting the password authentication.
By default in Oracle 8i/9i database, we can remotely administer the listener using lsnrctl command line utility. In Oracle Database 10g, this feature is disabled by default and we cannot by default administer the listener remotely. However in database 10g, there is a parameter LOCAL_OS_AUTHENTICATION_<listener_name> which can be set to OFF in order to enable remote listener administration.
How to administer listener remotely

Following are the activities we can do in remote listener administration
  • Stop the Listener
  • Set a password and prevent others from controlling the Listener
  • Write trace and log files to any file accessible to the process owner of tnslnsr (usually oracle)
  • Obtain detailed information on the Listener, database, and application configuration
Lets say I have a database sitst01 and listener name is same as DB name.
-bash-3.00$ lsnrctl start sitst01
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:09:36

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /slot/ems3079/oracle/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production

System parameter file is /slot/ems3079/oracle/db_1/network/admin/listener.ora
Log messages written to /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))

STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:09:36
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
My listener.ora file looks like this
-bash-3.00$ cat listener.ora
# listener.ora Network Configuration File: /slot/ems3079/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SITST01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60006sems.us.oracle.com)(PORT = 1600))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCsitst01))
)
)

SID_LIST_sitst01=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sitst01.us.oracle.com)
(SID_NAME=sitst01)
(ORACLE_HOME=/slot/ems3079/oracle/db_1))
(SID_DESC=
(GLOBAL_DBNAME=sitst01_DGMGRL.us.oracle.com)
(SID_NAME=sitst01)
(ORACLE_HOME=/slot/ems3079/oracle/db_1))
)

LOCAL_OS_AUTHENTICATION_SITST01=OFF

Note that I have enabled remote listener authentication using LOCAL_OS_AUTHENTICATION_SITST01=OFF
On second server (sitst02), I am setting this listener alias (sitst01) of first server in listener.ora
listener.ora on second server looks like this
<span style="font-family: monospace;">
</span>

-bash-3.00$ cat listener.ora
# listener.ora Network Configuration File: /slot/ems6826/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SITST02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60011sems.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCsitst02))
)
)

SID_LIST_sitst02=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sitst02.us.oracle.com)
(SID_NAME=sitst02)
(ORACLE_HOME=/slot/ems6826/oracle/db_1))
(SID_DESC=
(GLOBAL_DBNAME=sitst02_DGMGRL.us.oracle.com)
(SID_NAME=sitst02)
(ORACLE_HOME=/slot/ems6826/oracle/db_1))
)

SITST01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60006sems.us.oracle.com)(PORT = 1600))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCsitst01))
)
)

I added sitst01 alias enter in listener.ora of second server
Reload the listener on second server, so that it will know the new listener alias that we have registered
-bash-3.00$ lsnrctl reload sitst02

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:13:23

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60011sems.us.oracle.com)(PORT=1521)))
The command completed successfully

Now lets administer the listener on first server from second server
On the second server, check the status of first listener (sitst01)

-bash-3.00$ lsnrctl status sitst01
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:14:11
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:09:36
Uptime                    0 days 0 hr. 4 min. 35 sec
Trace Level               off
Security                  OFF
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 2 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Instance "sitst01", status READY, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

You can also stop the listener on first server, from second server

-bash-3.00$ lsnrctl stop sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:15:44

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
The command completed successfully

Password Protecting listener

To avoid the above scenario, you can protect the listener using password authentication. You have 2 methods of doing the same
1) Setting the clear text password in listener.ora file
2) Setting the encrypted password using LSNRCTL prompt
We will see both the methods now.
1) Setting clear text password in listener.ora file

This is not a recommended method for setting password. Here is how it works.
1) stop the listener
-bash-3.00$ lsnrctl stop sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:15:44

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
The command completed successfully

2) Add following parameter in listener.ora
PASSWORD_<listener_name>=<password>
Example:
PASSWORDS_SITST01=welcome
3) start the listener
-bash-3.00$ lsnrctl start sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:20:11

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /slot/ems3079/oracle/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /slot/ems3079/oracle/db_1/network/admin/listener.ora
Log messages written to /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:20:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Now try doing any activity from remote server or even this server. It will give error
-bash-3.00$ lsnrctl status sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:20:54

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
TNS-01169: The listener has not recognized the password

In order to carry out any activity on the listener, you need to first set the password at LSNRCTL prompt using “set password” command as shown below
LSNRCTL> set current_listener sitst01
Current Listener is sitst01
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:22:45
Uptime                    0 days 0 hr. 0 min. 34 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 2 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Instance "sitst01", status READY, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
The command completed successfully

So unless we use “set password” and enter the password we have set in listener.ora we cannot perform any activity.
2) Setting the encrypted password using LSNRCTL prompt
LSNRCTL> set current_listener sitst01
Current Listener is sitst01
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
Password changed for sitst01
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:22:45
Uptime                    0 days 0 hr. 0 min. 34 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 2 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Instance "sitst01", status READY, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Use “change_password” command to set the password for listener.
Now if you see listener.ora file it will have encrypted password updated in it
</pre>
</span></div>
<div>#----ADDED BY TNSLSNR 13-MAY-2010 11:27:55---</div>
<div>PASSWORDS_sitst01 = 125916DFCEFE8F08</div>
<div>#--------------------------------------------</div>
<div><span style="font-family: monospace;">
<pre>

Hope this helps !!
Reference:

Converting Physical Standby to Logical – Oracle Dataguard 10g

This is a simple article on how to convert physical standby to logical standby.

A metalink note ID is provided in the reference section. I used the same metalink note and did the conversion. I will brief the steps that I have done and the outputs of those steps.

Pre-reqs before starting the setup – You have a physical standby functioning correctly and that all the redo logs sequences on primary has been applied on standby

I will list the steps which needs to be done on primary as well as on standby

On standby database

1. Make sure that all the sequences generated on primary side has been applied on standby.

SQL to check that is given below

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
  2  FROM
  3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
  5  WHERE
  6  ARCH.THREAD# = APPL.THREAD#
  7  ORDER BY 1;
    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                    177                   177
2. Cancel the  recovery on standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
On primary database
SQL> show parameters archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      location=/slot/ems3079/oracle/
                                                 db_1/arch/sitst01/
log_archive_dest_10                  string
log_archive_dest_2                   string      service=sitst02
log_archive_dest_3                   string

On primary set the VALID_FOR parameter to make destination 1 as valid for online redo logs and role as all roles. This mean even if we change the role of primary server to standby server, online redo logs will get archived to archive destination 1.

SQL> alter system set log_archive_dest_1='location=/slot/ems3079/oracle/db_1/arch/sitst01/ valid_for=(ONLINE_LOGFILES,ALL_ROLES)';
System altered.
Archive destination 2 is for “service” which will ship archivelogs/redo logs to secondary standby server.
3. Create another archive destination log_archive_dest_3 which will archive only standby redo logs and role should be set to standby_role. This means that in case if we make this primary as standby then standby redo logs will be archived automatically at archive destination 3.
-bash-3.00$ mkdir -p /slot/ems3079/oracle/db_1/arch/standby/
-bash-3.00$ sqlplus "/as sysdba"
SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/slot/ems3079/oracle/db_1/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';
System altered.
4. Run DBMS_LOGSTDBY.BUILD package to create metadata for log miner to apply SQLs on logical standby site.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
After running this an entry will go in redo log file and when that redo log file gets shipped to standby, from that point onwards SQL apply will start on standby.
On standby database
5. Start the recover and change the DB name of standby
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY testdb;
ALTER DATABASE RECOVER TO LOGICAL STANDBY standby
*
ERROR at line 1:
ORA-16254: change db_name to TESTDB in the client-side parameter file (pfile)
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
In the above command we are giving a new name to the database as testdb
We got above error, because we are using pfile on our standby. In case of spfile the database name will automatically be changed to testdb.
Shut down the database and make changes in init.ora pfile.
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
change following parameters in init.ora
*.db_name='testdb'
db_unique_name='testdb'
Mount the database again using pfile.
SQL> STARTUP MOUNT pfile=initsitst02.ora
ORACLE instance started.


Total System Global Area  419430400 bytes
Fixed Size                  1267812 bytes
Variable Size             130025372 bytes
Database Buffers          281018368 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL>
6. Set the archive log destination on standby site as well.
SQL> alter system set log_archive_dest_1='LOCATION=/slot/ems6826/oracle/db_1/dbs/arch/sitst02 VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='service=sitst01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/slot/ems6826/oracle/db_1/dbs/arch/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';
System altered.
Log_archive_dest_1 -> This is for archiving all online redo logs and will work if the role of database is Primary or Standby
Log_archive_dest_2 -> This is for shipping the online redo logs to primary server, in case this becomes primary server and primary becomes standby server. This is called auto role shifting. Till the time this database is standby it will not ship and redo logs to primary.
Log_archive_dest_3 -> This is for archiving all standby redo logs. This will work only when the role of database is Standby. In case this becomes primary, it will not create any standby archive logs at this dest 3.
Following table show you various roles and redo log types that can be used in various situation.

VALID_FOR Definition Primary Role Physical Standby Role Logical Standby Role

ONLINE_LOGFILE, PRIMARY_ROLE

Active

Inactive

Invalid

ONLINE_LOGFILE, STANDBY_ROLE

Inactive

Invalid

Active

ONLINE_LOGFILE, ALL_ROLES

Active

Invalid

Active

STANDBY_LOGFILE, PRIMARY_ROLE

Error

Error

Error

STANDBY_LOGFILE, STANDBY_ROLE

Invalid

Active

Active

STANDBY_LOGFILE ALL_ROLES

Invalid

Active

Active

ALL_LOGFILES, PRIMARY_ROLE

Active

Inactive

Invalid

ALL_LOGFILES, STANDBY_ROLE

Invalid

Active

Active

ALL_LOGFILES, ALL_ROLES

Active

Active

Active

Above table is referred from Oracle Documentation
Start the database.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
7. Start the SQL apply on logical standby
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
*
ERROR at line 1:
ORA-16239: IMMEDIATE option not available without standby redo logs
We got the above error, because we didn't create standby redo log files
Create standby redo logs as shown below.
SQL> alter database add standby logfile group 4 ('/slot/ems6826/oracle/oradata/sitst02/redo4.dbf') size 100M;
Database altered.
SQL> alter database add standby logfile group 5 ('/slot/ems6826/oracle/oradata/sitst02/redo5.dbf') size 100M;
Database altered.
SQL> alter database add standby logfile group 6 ('/slot/ems6826/oracle/oradata/sitst02/redo6.dbf') size 100M;
Database altered.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
8. Sanity check logical standby
Run following on standby database
SQL> select name, database_role from v$database;
NAME      DATABASE_ROLE
--------- ----------------
STANDBY   LOGICAL STANDBY
On primary
SQL> select DEST_ID, THREAD#, SEQUENCE#, ARCHIVED, APPLIED from v$archived_log where DEST_ID = 2 ;
   DEST_ID    THREAD#  SEQUENCE# ARC APP
---------- ---------- ---------- --- ---
         2          1        179 YES YES
         2          1        181 YES YES
         2          1        182 YES YES
         2          1        183 YES YES
         2          1        184 YES YES
         2          1        185 YES YES
         2          1        186 YES YES
         2          1        187 YES YES
         2          1        188 YES YES
         2          1        189 YES YES
         2          1        190 YES NO

On standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;


SEQUENCE# FIRST_TIME         NEXT_TIME          DIC DIC
---------- ------------------ ------------------ --- ---
189 02-MAY-10 02:17:59 02-MAY-10 02:20:32 NO  NO
190 02-MAY-10 02:20:32 02-MAY-10 02:20:33 NO  NO


SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;


TYPE                             HIGH_SCN STATUS
------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COORDINATOR                       5846054 ORA-16116: no work available
READER                            5846054 ORA-16240: Waiting for logfile (thread# 1, sequence# 191)
BUILDER                           5846050 ORA-16116: no work available
PREPARER                          5846049 ORA-16116: no work available
ANALYZER                          5845790 ORA-16117: processing
APPLIER                           5845733 ORA-16116: no work available
APPLIER                           5845739 ORA-16116: no work available
APPLIER                           5845784 ORA-16116: no work available
APPLIER                           5845790 ORA-16116: no work available
APPLIER                           5845429 ORA-16116: no work available

SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;


EVENT_TIM
---------
STATUS
--------------------------------------------------------------------------------
EVENT
--------------------------------------------------------------------------------
02-MAY-10
ORA-16111: log mining and apply setting up

SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;


FILE_NAME
--------------------------------------------------------------------------------
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC    THREAD#
---------- ------------- ------------ --------- --- --- ----------
/slot/ems6826/oracle/db_1/dbs/arch/standby/1_189_713333254.dbf
189       5845702      5845832 02-MAY-10 NO  NO           1

/slot/ems6826/oracle/db_1/dbs/arch/standby/1_190_713333254.dbf
190       5845832      5846054 02-MAY-10 NO  NO           1


SQL> SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';


Session altered.

SQL> SQL> SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES'WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE#;


SEQUENCE# FIRST_TIME         APPLIED
---------- ------------------ -------
189 02-MAY-10 02:17:59 CURRENT
190 02-MAY-10 02:20:32 CURRENT


SQL> SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';


NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
coordinator state
IDLE

transactions ready
26

transactions applied
26


NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
coordinator uptime
564
References:
Metalink note ID : 738643.1