SEE ALSO ./Create12cStandby.txt Notes on Data Guard / Physical Standby for Oracle 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, unless otherwise indicated Single word database init params can be in single quotes; 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. (Not important: Metalink forum thread 507755.994 says "LOCK_NAME_SPACE is being deprecated in favor of SP_NAME" but I can't figure out the role of the undocumented parameter sp_name (Service Provider Name). 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.) (Not important: Creating standby controlfile succeeds even after starting up primary (see section 3.2.2). It's better to 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 "/@orcl10gsb as sysdba" works (or at SQL> prompt, type conn /@orcl10gsb 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 cancel recovery or 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." [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 that db_file_name_convert does no conversion on such as c:\newts01.dbf, the standby may create a file named like C:\WINDOWS\SYSTEM32\UNNAMED00007. I can't figure out a way to correct this (to rename it to C:\ORACLE\ORADATA\ORCL10GSB\NEWTS01.DBF e.g.). So I have to do this on standby: alter database datafile 'C:\WINDOWS\SYSTEM32\UNNAMED00007' 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. [note2] STANDBY LOGFILES Add standby logfile (standby redo logs or SRLs) 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:\ORACLE\ORADATA\ORCL10G\REDO04.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. Switching logfile on primary still switches logfile on standby (and v$archive_gap is still empty), because redo transport is still on, even though managed recovery is not (and 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. On the standby, 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)' (these mysterious SHAD processes only exist on Windows and are probably just short for "shadow".) LNS0 Network Server 0 shows up on primary if LGWR is set up to transfer logfiles to standby. SWITCHOVER 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 as PRIMARY_ROLE (or PRIMARY_ROLES) in VALID_FOR clause of log_archive_dest_n parameter. In fact, this 'PRIMARY_INSTANCE' is an OPS/RAC concept, where you can set active_instance_count to e.g. 1 and the later started instances would assume 'SECONDARY_INSTANCE'. 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. LOGS NOT SHIPPED TO STANDBY In emergency, copy missing logs to standby 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. 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 within 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 redo logs (SRL) to be created. I have SRLs 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 SRLs 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 _______________ [note1] I probably need to verify the words in this paragraph. At least in 9i non-managed standby (where all archive logs are manually applied), changing from mounted standby to read only doesn't need recover cancel or shutdown. [note2] This problem is not reproduced in 10.2.0.3.