Database Software

Any help willl be greatly appreciated...

I am trying to convert a standby database that is in "snapshot" mode back to "physical" standby and I am encountering problems in the process from the "DGMGRL" command line.

Both instances are on the same physical machine. Everything was working fine untill I tried to change the db from snapshot to physical. The DGMGRL starts the conversion process and is able to shutdown but when trying to restart the instance is fails and reports that the service is not defined.


Here is the issue I am facing:



C:\app\MMJ\product\11.1.0\db_1\BIN>
C:\app\MMJ\product\11.1.0\db_1\BIN>set ORACLE_SID=I11G1 <======= the primary database

C:\app\MMJ\product\11.1.0\db_1\BIN>dgmgrl
DGMGRL for 32-bit Windows: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password@i11g1sb <===== the standby database currently in snapshot mode
Connected.
DGMGRL> connect sys/password@i11g1 <==== the primary database
Connected.
DGMGRL> convert database 'i11g1sb' to physical standby;
Converting database "i11g1sb" to a Physical Standby database, please wait...
Operation requires shutdown of instance "i11g1sb" on database "i11g1sb"
Shutting down instance "i11g1sb"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "i11g1sb" on database "i11g1sb"
Starting instance "i11g1sb"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "i11g1sb"
You must start instance "i11g1sb" manually
Failed to convert database "i11g1sb"
DGMGRL> show configuration

Configuration
Name: DGConfig1
Enabled: YES
Protection Mode: MaxPerformance
Databases:
i11g1 - Primary database
i11g1sb - Snapshot standby database (disabled)

Fast-Start Failover: DISABLED

Current status for "DGConfig1":
SUCCESS

DGMGRL> exit

C:\app\MMJ\product\11.1.0\db_1\BIN>set ORACLE_SID=I11G1SB

C:\app\MMJ\product\11.1.0\db_1\BIN>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 25 11:40:16 2009

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 426852352 bytes
Fixed Size 1333648 bytes
Variable Size 369100400 bytes
Database Buffers 50331648 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.

==============>>>> as you can see I can start the standby database without any problems and even query the table in which I made some changes. I had added the record with "Region_ID"=30. I have


SQL> select * from hr.regions;

REGION_ID REGION_NAME
---------- -------------------------
30 JAPAC
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL>



The same table on the primary database has the following records in the same table:





Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\MMJ>set ORACLE_HOME=c:\app\mmj\product\11.1.0\db_1

C:\Documents and Settings\MMJ>set ORACLE_SID=i11g1

C:\Documents and Settings\MMJ>
C:\Documents and Settings\MMJ>cd %ORACLE_HOME%

C:\app\MMJ\product\11.1.0\db_1>cd bin

C:\app\MMJ\product\11.1.0\db_1\BIN>
C:\app\MMJ\product\11.1.0\db_1\BIN>
C:\app\MMJ\product\11.1.0\db_1\BIN>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 25 11:43:10 2009

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

SQL> connect / as sysdba
Connected.
SQL>
SQL> select * from hr.regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
20 JAPAC
40 JAPAC

6 rows selected.

SQL>


=======> The TNSPING works fine against both the databases.



C:\app\MMJ\product\11.1.0\db_1\BIN>set O
ORACLE_HOME=c:\app\mmj\product\11.1.0\db_1
ORACLE_SID=I11G1SB
OS=Windows_NT

C:\app\MMJ\product\11.1.0\db_1\BIN>
C:\app\MMJ\product\11.1.0\db_1\BIN>tnsping i11g1sb

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 25-MAR-2009 16:56:42

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

Used parameter files:
c:\app\mmj\product\11.1.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = MHost)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAM
E = I11G1SB)))
OK (230 msec)

C:\app\MMJ\product\11.1.0\db_1\BIN>tnsping i11g1

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 25-MAR-2009 16:56:47

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

Used parameter files:
c:\app\mmj\product\11.1.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = MHost)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAM
E = I11G1)))
OK (30 msec)

C:\app\MMJ\product\11.1.0\db_1\BIN>lsnrctl

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 25-MAR-2009 16:57:01

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener i11g1
Current Listener is i11g1
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MHost)(PORT=1523)))
Services Summary...
Service "I11G1" has 1 instance(s).
Instance "I11G1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0
LOCAL SERVER
Service "I11G1SB" has 1 instance(s).
Instance "I11G1SB", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:55 refused:1
LOCAL SERVER
Service "I11G1SB_DGMGRL" has 1 instance(s).
Instance "I11G1SB", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "i11g1.mhost" has 1 instance(s).
Instance "i11g1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:6 refused:0 state:ready
LOCAL SERVER
Service "i11g1XDB.mhost" has 1 instance(s).
Instance "i11g1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: MHost, pid: 3944>
(ADDRESS=(PROTOCOL=tcp)(HOST=MHost)(PORT=1430))
Service "i11g1_DGB.mhost" has 1 instance(s).
Instance "i11g1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:6 refused:0 state:ready
LOCAL SERVER
Service "i11g1_XPT.mhost" has 1 instance(s).
Instance "i11g1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:6 refused:0 state:ready
LOCAL SERVER
Service "i11g1sb.mhost" has 1 instance(s).
Instance "i11g1sb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "i11g1sbXDB.mhost" has 1 instance(s).
Instance "i11g1sb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: MHost, pid: 7336>
(ADDRESS=(PROTOCOL=tcp)(HOST=MHost)(PORT=1931))
Service "i11g1sb_DGB.MHost" has 1 instance(s).
Instance "i11g1sb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "i11g1sb_XPT.mhost" has 1 instance(s).
Instance "i11g1sb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL>