Notes on Data Guard / Physical Standby in 10g

Primary orcl10g is running. About to create orcl10gsb as standby on the same
Windows machine. Version is 10.1.0.2.0.

Refer to "Creating a Physical Standby Database" at
Ora10gDoc/server.101/b10823/create_ps.htm or
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/create_ps.htm <http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/create_ps.htm>
unless otherwise indicated

Single word database init params can be in single quotes (no need to remove
them); DB_NAME=orcl10g is the same as DB_NAME='orcl10g' as shown in the
generated pfile. log_archive_dest_state_n is enabled by default so no need to
set them (however, at one point after my second time switchover,
log_archive_dest_state_2 becomes deferred and I have to set it to enable for
logs to auto transfer to standby).

Don't use lock_name_space or log_archive_start, which are deprecated in 10g
(get "ORA-32004: obsolete and/or deprecated parameter(s) specified" on
startup). If you have set them and alter system set lock_name_space = '' scope
= spfile, you still get ORA-32004. You have to create pfile and remove them and
create spfile.

I can't figure out the role of the undocumented parameter sp_name (Service
Provider Name). See
metalink.oracle.com/metalink/plsql/ml2_documents.showFOR?p_id=507755.994. I
thought since lock_name_space was deprecated and I'm creating a standby on the
same host as primary, I should use sp_name instead. So I set it to orcl10g and
orcl10gsb for primary and standby, respectively. They showed up as expected in
v$parameter and show parameter. Then I comment out sp_name in pfiles and
generate spfiles for both DBs and start them up. sp_name is gone from primary
instance (v$parameter.isdefault='FALSE'). But it still exists in standby, value
being orcl10g now, in both v$parameter and show parameter
(v$parameter.isdefault='TRUE'). Both DBs function correctly.

Creating standby controlfile succeeds even after starting up primary (see
section 3.2.2). Only thing to remember is copy datafiles to standby side prior
to this, or you may get ORA-1152 "file %s was not restored from a sufficiently
old backup".

On Windows, running "oradim -NEW -SID boston -INTPWD password -STARTMODE
manual" still starts up the service immediately, in spite of the word "manual"
(section 3.2.5 Step 1)

Configure two listeners even on one server, one listening on port 1522. I added
listenersb listener to the same listener.ora file. Starting it by "lsnrctl
start listenersb". I didn't configure broken (dead) connection detection. You
don't need to use Net Configuration Manager (or Net Manager as the manual says)
to add another listener service; the command "lsnrctl start listenersb"
automatically creates a Windows service called
OracleTNSListenerlistenersb, with a harmless warning "Failed
to open service TNSListenerlistenertest>, error 1060.".
(section 3.2.5 Steps 3-5)

At first, STARTUP OPEN READ ONLY (section 3.2.6) told me system datafile was
not restored from a sufficiently old backup (ORA-1152). Did I copy datafiles to
standby after I created controlfile? I don't think so. I notice the logs can't
be shipped from primary to standby. Check TNS listener setup. Make sure sqlplus
" as sysdba" works (or at SQL> prompt, type conn as
sysdba). If not, try setting local_listener on standby. Mine is set to (ADDRESS
= (PROTOCOL = TCP)(HOST = )(PORT = 1522)). Whatever you do, make
Oracle Net work!

Even after startup open read only, v$archive_dest_status.database_mode still
shows MOUNTED_STANDBY, not OPEN_READ-ONLY. But recovery_mode changes from
MANAGED to IDLE. v$database.open_mode changes from MOUNTED to READ ONLY.
v$instance.status changes from MOUNTED to OPEN. v$thread.status or .enabled
remains OPEN and PUBLIC, respectively.

Changing from managed standby to open read only needs to shutdown immediate and
startup open read only (has to be shutdown immediate; just shutdown or shutdown
normal or transactional throws "ORA-16175: cannot shut down database when media
recovery is active"). But going the other way simply needs alter database
recover managed standby database disconnect; no restart is needed. "alter
database mount" is the same as "alter database mount standby database" on
standby. [note1]

After creating both DBs, when accessing standby, set oracle_sid=orcl10gsb in
DOS before launching sqlplus.

If a datafile is created with a name db_file_name_convert can't convert such as
c:newts01.dbf, standby will have a file named like
C:WINDOWSSYSTEM32UNNAMED00007. I can't figure out a way to correct this (to
rename it to C:ORACLEORADATAORCL10GSBNEWTS01.DBF e.g.). So I have to do
this on standby: alter database datafile 'C:WINDOWSSYSTEM32UNNAMED00007'
offline drop; alter database recover managed standby database disconnect;
select * from v$session_wait where event like 'MRP%'; -- Make sure managed
recovery is still running

then drop the tablespace on primary, allowing the propagated redo to drop the
tablespace definition with its UNNAMED00007 datafile in standby.

STANDBY LOGFILES

Add standby logfile on primary and standby (trying to add it on orcl10gsb
throws ORA-01156: recovery in progress may need access to files, so I guess
recovery is needed first). This is recommended by Oracle (see
ora10gdoc/server.101/b10823/log_transport.htm section 5.6.2) alter database add
standby logfile group 4 'C:ORACLEORADATAORCL10GREDO04.LOG' size 10485760;
Then you see it in v$standby_log but not v$log. It shows up in v$logfile (where
type = 'STANDBY' instead of 'ONLINE'). Note:219344.1, but not standard
documentation, says that standby redo logs are only used if LGWR is used to
transfer logs to standby (alter system set log_archive_dest_2 =
'SERVICE=orcl10gsb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)
DB_UNIQUE_NAME=orcl10gsb LGWR'). If using ARCH to transfer, v$standby_log.dbid
and .status both show 'UNASSIGNED'.

PROCESS

MRP0 (Managed Standby Recovery) shows up in v$bgprocess on standby (select *
from v$bgprocess where paddr != '00') even after this background process is
killed (I killed the OS thread using orakill and another time using Windows
Process Explorer). The instance stays up. But the entry in v$session and
v$process for MRP is gone. Strangely, switching logfile on primary still
switches logfile on standby and v$archive_gap is still empty, as if managed
recovery is still on (but v$archive_dest_status.recovery_mode changes to
'IDLE'). Starting up MRP simply means alter database recover standby database
disconnect (running this command twice without changing recovery mode in
between throws "ORA-01153: an incompatible media recovery is active").

MRP0 shows up in v$bgprocess even when the recovery is performed by a
foreground ("alter database recover managed standby database", no "disconnect"
so the sqlplus session hangs there). But for this session,
v$session.type='USER', .status='ACTIVE', event='MRP wait on archivelog arrival'
(sometimes 'control file sequential read'). To stop the hanging, have to "alter
database recover managed standby database cancel" in another session.

There's no OS process (or Windows thread) named like RFS. A server process is
used as RFS instead. You may see the trace file in udump named like *rfs*.trc.
To identify the process or session, match v$process.spid with
v$managed_standby.pid where process='RFS'. Its v$process.program='ORACLE.EXE
(SHAD)' (I see another mysterious SHAD process in here)

LNS0 Network Server 0 shows up on primary if LGWR is set up to transfer
logfiles to standby.

SWITCHOVER

Do make sure there's no row in v$archive_gap, v$archived_log where applied='NO'
returns no rows on standby (if standby used to be primary, then append
sequence#>[certain number] to where clause), and v$log on both DBs show the
same sequence# for current log.

v$database.switchover_status shows 'NOT ALLOWED' on primary but alter database
commit to switchover to physical standby still works! (Why?)

On standby, either the original or the new one after switchover,
v$instance.instance_role is always 'PRIMARY_INSTANCE'. This is an unfortunate
choice of wording as if it meant the same PRIMARY_ROLE (or PRIMARY_ROLES) in
VALID_FOR clause of log_archive_dest_n parameter. In fact, this 'PRIMARY_ROLE'
is an OPS/RAC concept, where you can set active_instance_count to e.g. 1 and
the later started instances would assume 'SECONDARY_ROLE'.

If alter database commit to switchover to primary throws "ORA-16139: media
recovery required", then recover standby database. If you get "ORA-01153: an
incompatible media recovery is active", shutdown and startup mount.

REAL-TIME APPLY

Use LGWR instead of ARCH to transport logs. Have to cancel current managed
recovery and then alter database recover managed standby database using current
logfile disconnect. (If not cancelled, that command throws ORA-01153: an
incompatible media recovery is active.)
v$archive_dest_status.recovery_mode='MANAGED REAL TIME APPLY'.

LOGS NOT SHIPPED TO STANDBY

In emergency, copy missing logs to standby, restart the standby database in
mount and recover standby database, manually applying logs. When you run out of
logs and get "ORA-00308: cannot open archived log", alter database recover
managed standby database. If logs are still not automatically shipped to
standby, check Oracle Net and log_archive_dest_state_n (must be 'enable', not
'defer'). If no delay is allowed in shipping logs, add NODELAY to
log_archive_dest_n on primary (but preferably on both so it stays like this
after switchover).

ORACLE NET

For some reason, starting the Windows services OracleOraDb10g_home1TNSListener
and OracleOraDb10g_home1TNSListenerlistenersb in service control panel (or
equivalently using DOS command net start ...) does not start the listeners.
Have to stop the services and use commands lsnrctl start. Then listener
services are registered by pmon in 1 minute.

PROTECTION MODES

According to ora10gdoc/server.101/b10823/log_transport.htm#1226160, "5.6.3
Setting the Data Protection Mode of a Data Guard Configuration", "Table 5-2
Minimum Requirements for Data Protection Modes", maximum protection and
availability require LGWR, SYNC, AFFIRM to be set in log_archive_dest_n and
standby logfiles (SLF) to be created. I have SLFs but I don't have AFFIRM or
even SYNC set in log_archive_dest_n and I can still alter database set standby
database to maximize availability. Not sure if SLGs matter or not. Looks like
only LGWR is required. Omitting LGWR is not allowed, though. Also see
http://groups.google.com/groups?threadm=clnuv2%243jt%241%40nntp.fujitsu-siemens.com <http://groups.google.com/groups?threadm=clnuv2%243jt%241%40nntp.fujitsu-siemens.com>

_______________
[note1] I probably need to verify the words in this paragraph. At least in 9i
non-managed standby (where archive logs are manually applied), changing from
mounted standby to read only doesn't need shutdown. Also, "alter database
mount" won't work. "alter database mount standby database" is needed.