Sreenu Oracle DBA: November 2016

Sunday, 13 November 2016

How To Register Listener In The Oracle Database

The listener is a process that runs on the database server to receive incoming client connections from application users .

There are two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred as “Registering with the Listener” . The two methods are

1.) Static Instance Registration

2.) Dynamic Instance Registration

First we will discuss about the Static Instance Listener :

This is the very basic method to register listener .We can either add the entries in $ORACLE_HOME\NETWORK\ADMIN\listener.ora file or by using the GUI i.e, through Net Manager. The configuration inside the listener.ora file looks like :

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = noida)

(ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)

(SID_NAME = noida)

)

(SID_DESC =

(GLOBAL_DBNAME = hyd)

(ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)

(SID_NAME = hyd)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = tech-199)(PORT = 1521))

)

and when we check the registration , it shows the status of UNKNOWN :

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 15:26:27

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

Start Date 28-SEP-2011 15:03:39

Uptime 7 days 0 hr. 22 min. 52 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\app\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora

Listener Log File c:\app\oracle\diag\tnslsnr\tech-199\listener\alert\log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))

Services Summary...

Service "hyd" has 1 instance(s).

Instance "hyd", status UNKNOWN, has 1 handler(s) for this service...

Service "noida" has 1 instance(s).

Instance "noida", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL>

The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance.

Now, we will check the Dynamic Instance Listener :

Dynamic Instance Registration : This dynamic registration feature is called service registration. The registration is performed by the PMON process an instance background process of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.

Service registration offers the following benefits :

1.) Simplified configuration : Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note : The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time failover : Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes. To ensure service registration works properly, the initialization parameter file should contain the following parameters:

SERVICE_NAMES for the database service name

INSTANCE_NAME for the instance name

For example:

SERVICE_NAMES=noida.TECH-199

INSTANCE_NAME=noida

Let's have a Demo of Dynamic Listener.

The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.Here i have rename the listener.ora file and stop and start the listener and find that listener supports no services.Check the below:

LSNRCTL> stop

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))

The command completed successfully.

Now start the listener

LSNRCTL> start

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

Log messages written to c:\app\oracle\diag\tnslsnr\tech-199\listener\alert\log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

Start Date 05-OCT-2011 16:21:30

Uptime 0 days 0 hr. 0 min. 7 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Log File c:\app\oracle\diag\tnslsnr\tech-199\listener\alert\log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))

The listener supports no services

The command completed successfully

Here, we find that listener donot support any services.Since it doesnot found the listener.ora file ,and if we try to connect to the Instance then it will throws the error i.e, ORA-12514 :

C:\> tnsping noida

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 16:23:03

Used parameter files:

C:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.0.112)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = noida)))

OK (40 msec)

Now, we try to connect with Instance "NOIDA"

C:\> sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 16:23:45 2011

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Since the tnsping proves that our tnsnames.ora resolution is correct, but it throws the error while connecting to database because the listener doesnot knows anything about the services "NOIDA" . Let's start the instance and check again :

C:\> set ORACLE_SID=noida

SQL> startup

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 222300404 bytes

Database Buffers 33554432 bytes

Redo Buffers 6410240 bytes

Database mounted.

Database opened.

Now check the listener status again :

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

Start Date 05-OCT-2011 16:21:30

Uptime 0 days 0 hr. 19 min. 21 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Log File c:\app\oracle\diag\tnslsnr\tech-199\listener\alert\log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))

Services Summary...

Service "noida.TECH-199" has 1 instance(s).

Instance "noida", status READY, has 1 handler(s) for this service...

Service "noidaXDB.TECH-199" has 1 instance(s).

Instance "noida", status READY, has 1 handler(s) for this service...

Service "noida_DGB.TECH-199" has 1 instance(s).

Instance "noida", status READY, has 1 handler(s) for this service...

The command completed successfully

Here we observe that once the instance is started , when we re-check the listener now knows of service “NOIDA”, with a status of READY . This obviously did not come from listener.ora as the file is renamed. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready.

Now agian connecting to the Instance :

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 18:14:28 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Here by default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information:

- Name of the associated instance

- Current load and maximum load on instance

- Names of DB services provided by database.

- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and PMON process fails then it's not possible to register information to listener periodically. In this case we can do 'Manual service registration' using command:

SQL> ALTER SYSTEM REGISTER;

########################################### All The Best ###########################################

Changing Database Protection Modes In Data Gaurd Environment

Primary Database : DCPROC
Standby Database : PRPROC
Database version : 11gR2

The database is running under Maximum Performance mode.

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN DCPROC PRIMARY MAXIMUM PERFORMANCE

In order to change the protection mode to either MAXIMUM AVAILABILITY/MAXIMUM PROTECTION, we need to have the standby redo logs configured on the standby database. Also, the redo shippment parameter (log_archive_dest_2) on the primary database should be configured to use SYNCHRONOUS (“SYNC”) mode.

Let’s check the number of online redo logs and it’s size on primary database

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
------ ------------------
1 100
2 100
3 100

It can be noticed from below that there are no standby redo log groups configured on the primary database.

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

Add standby redo log groups on the primary database with the same size as that of the online redo log groups.

SQL> alter database add standby logfile group 4 size 100M;

Database altered.

SQL> alter database add standby logfile group 5 size 100M;

Database altered.

SQL> alter database add standby logfile group 6 size 100M;

Database altered.

SQL> alter database add standby logfile group 7 size 100M;

Database altered.

We can now notice that 4 standby redo log groups have been added with the same size as that of the online redo logs. These standby redo logs will not be used on the primary database and will be used only when a switchover takes place.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024
------ ---------------
4 100
5 100
6 100
7 100

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1 311

Standby:

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1 311

Standby database is in sync with the primary database.

Standby database details:

SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------- ------------- ------------------
MOUNTED DRPROC PHYSICAL STANDBY

On the standby database, there are 3 online redo log groups with the size 100M and there are no standby redo log groups.

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
------ ----------------
1 100
3 100
2 100

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

Let’s add standby redo log groups on the standby database but before that, we need to check if MRP (Managed Recovery Process) is running on the standby database and if running, then it needs to be cancelled.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 312

8 rows selected.

Cancel the MRP process on the standby database:

SQL> alter database recover managed standby database cancel;

Database altered.

Add 4 Standby Redo Log (SRL) groups of size same as online redo log groups (100M) on the standby database:

SQL> alter database add standby logfile group 4 size 100M;

Database altered.

SQL> alter database add standby logfile group 5 size 100M;

Database altered.

SQL> alter database add standby logfile group 6 size 100M;

Database altered.

SQL> alter database add standby logfile group 7 size 100M;

Database altered.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024
------ ---------------
4 100
5 100
6 100
7 100

Once the SRLs are added, start the MRP on the standby database

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
------- ----------- ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 312

8 rows selected.

As said earlier, configure the redo shippment parameter (log_archive_dest_2) on the primary database to use SYNCHRONOUS mode.

Primary database:

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------ ------ -----------------------------------------------------------
log_archive_dest_2 string service=DRPROC valid_for=(online_logfiles,primary_role)
db_unique_name=DRPROC

SQL> alter system set log_archive_dest_2='service=DRPROC LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=DRPROC';

System altered.

SQL> show parameter dest_2

NAME TYPE VALUE
--------------------------- ------ --------------------------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string service=DRPROC LGWR AFFIRM SYNC valid_for= (online_logfiles,primary_role) db_unique_name=DRPROC

Shutdown the primary database and mount it.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 180356360 bytes
Database Buffers 20971520 bytes
Redo Buffers 5214208 bytes
Database mounted.
SQL>

Now change the protection mode on the primary database according to the requirement using the below command

“alter database set standby database to maximize {AVAILABILITY | PROTECTION| PERFORMANCE}”

Here, I am changing the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY

SQL> alter database set standby database to maximize availability;

Database altered.

Once the mode is changed, open the primary database and verify the same.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ----------------------
OPEN DCPROC PRIMARY MAXIMUM AVAILABILITY

Check if the standby database is in sync with the primary database

On primary:

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1 316

Standby:

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------- ------------- ---------------- --------------------
MOUNTED DRPROC PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1 316

Standby database is in sync with the primary and also the PROTECTION mode has been changed to MAXIMUM AVAILABILITY.

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”

Symptoms

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”

Errors In Alert Log File

/u01/app/oracle/diag/rdbms/ORADB/ORADB/trace/ORADB_j000_7733378.trc:
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1
Sat Mar 24 23:01:18 2012

Have a look at trace file

*** 2012-03-24 23:00:13.943
*** SESSION ID:(23.6871) 2012-03-24 23:00:13.943
*** CLIENT ID:() 2012-03-24 23:00:13.943
*** SERVICE NAME:(SYS$USERS) 2012-03-24 23:00:13.943
*** MODULE NAME:(DBMS_SCHEDULER) 2012-03-24 23:00:13.943
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2012-03-24 23:00:13.943

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Cause

Table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.

Check the job status

SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;

LOG_DATE STATUS
------------------------------------------
10-MAR-12 11.00.13.501250 PM +00:00 FAILED
03-MAR-12 11.00.10.603238 PM +00:00 FAILED
17-MAR-12 11.00.14.793987 PM +00:00 FAILED
24-MAR-12 11.00.13.980545 PM +00:00 FAILED

Role of BSLN_MAINTAIN_STATS_JOB

This job runs the BSLN_MAINTAIN_STATS_PROG program on the BSLN_MAINTAIN_STATS_SCHED schedule. The program BSLN_MAINTAIN_STATS_PROG will keep the default baseline’s statistics up-to-date

Check the DBSNMP.BSLN_BASELINES table

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP
-------------------------------------------------------------------------
1166314350 FTEST 0 4AC774574F6C7D60D4ADF390356098C1 NX Y ACTIVE 27-NOV-10
1166314350 TEST 0 CEF9782DC11EE02FCB9E1D821B49FB3F HX Y ACTIVE 27-NOV-10

So on our database we should just have the entry for TEST so we need to delete the entry for FTEST.

Delete the inconsistent entry

SQL> delete from DBSNMP.BSLN_BASELINES where INSTANCE_NAME=’FTEST’;

1 row deleted.

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP
---------------------------------------------------------------
1166314350 TEST 0 CEF9782DC11EE02FCB9E1D821B49FB3F HX Y ACTIVE 27-NOV-10

Now re-run the job.

SQL> exec dbms_scheduler.run_job(‘BSLN_MAINTAIN_STATS_JOB’,false);

PL/SQL procedure successfully completed.

The job has now successfully run.

SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;

LOG_DATE STATUS
---------------------------------------------
10-MAR-12 11.00.13.501250 PM +00:00 FAILED
29-MAR-12 01.11.43.054124 PM +01:00 SUCCEEDED
03-MAR-12 11.00.10.603238 PM +00:00 FAILED
17-MAR-12 11.00.14.793987 PM +00:00 FAILED
24-MAR-12 11.00.13.980545 PM +00:00 FAILED

Saturday, 12 November 2016

Snapshot Standby Database-Read Write Mode

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled. Below are the steps on how to convert a physical standby database to a snapshot standby database and viceversa

Oracle Database version: 11.2.0.3 Enterprise Edition

Primary database: DCPROC

Details with respect to the primary database:

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

------------ ---------------- ---------------- --------------------

OPEN srprim PRIMARY READ WRITE

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)

------- --------------

1 206

Standby database Details:

Oracle database version: 11.2.0.3 Enterprise Edition

Standby database name: DRPROC

Details with respect to the physical standby database:

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

------------ ---------------- ---------------- ----------------

OPEN DRPROC PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

------- --------------

1 206

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

You can observe that the standby database is in sync with the primary database. Below outcome shows that the Flash Recovery Area is configured on the physical standby database.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

--------------------------- ----------- -------------

db_recovery_file_dest string +FRA_NEW

db_recovery_file_dest_size big integer 4122M

Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes

Variable Size 805306608 bytes

Database Buffers 452984832 bytes

Redo Buffers 8847360 bytes

Database mounted.

Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

SQL> alter database convert to snapshot standby;

Database altered.

Step 3: You can now open the snapshot standby database and check its mode.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

----------- --------------- ---------------- ------------------

OPEN DRPROC SNAPSHOT STANDBY READ WRITE

Small Test on the snapshot standby database.

1. Create a user called “SNAPTEST”

2. Create a table called “TEST” whose owner is “SNAPTEST” and insert some records in it. You can also update some of the records as well.

SQL> create user snaptest identified by oracle;

User created.

SQL> grant connect,resource to snaptest;

Grant succeeded.

SQL> conn snaptest/oracle@srps

Connected.

SQL> create table test(code number, name char(20));

Table created.

SQL> insert into test values (100,'ARUN');

1 row created.

SQL> insert into test values(200,'SHIVU');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

CODE NAME

---------- --------------------

100 ARUN

200 SHIVU

SQL> update snaptest.test set code=500 where name='ARUN';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from snaptest.test;

CODE NAME

---------- --------------------

500 ARUN

200 SHIVU

In the mean time, you can also see that the redo data from the primary database is received by the snapshot standby database but would not be applied.

On primary database the latest sequence generated is 208 and that on the standby database, the RFS process is idle for sequence 209.

Primary:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 208

Standby:

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

--------- ------------ ----------

ARCH CLOSING 1

ARCH CONNECTED 0

RFS IDLE 0

RFS IDLE 209

RFS IDLE 0

7 rows selected.

Steps on converting back a snapshot standby database to physical standby database.

Step 1: Shut down the snapshot standby database and open it in Mount mode.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes

Variable Size 805306608 bytes

Database Buffers 452984832 bytes

Redo Buffers 8847360 bytes

Database mounted.

Step 2: Convert the snapshot standby database to physical standby database.

SQL> alter database convert to physical standby;

Database altered.

Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes

Variable Size 805306608 bytes

Database Buffers 452984832 bytes

Redo Buffers 8847360 bytes

Database mounted.

Database opened.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

------------ -------------- ---------------- ----------------

OPEN DRPROC PHYSICAL STANDBY READ ONLY

SQL> alter database recover managed standby database disconnectfrom session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

--------- ------------ ----------

ARCH CONNECTED 0

RFS IDLE 0

MRP0 WAIT_FOR_LOG 213

8 rows selected.

Crosscheck whether the physical standby database is in sync with the primary database.

On Primary database:

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 212

On Standby database:

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 212

You can see below that the transactions that were carried out when the standby database is opened in READ WRITE mode are flushed out after it was converted back to physical standby database mode.

SQL> select * from snaptest.test;

select * from snaptest.test

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select username,account_status from dba_users where username='SNAPTEST';

no rows selected