ok, although I wrote that there are plenty of websites and blogs describing how to create a dataguard standby, I’m doing one on my own… specially because my colleague asked me to write them a manual, so I decided to do it here.<\/p>\n
In my example I’m using two Servers with Oracle 12.2 clusterware (Oracle restart) including an ASM instance and a 12.2 db home. On the first server – lets call it the primary node – I’ve also installed a non-CDB Database with the database create assistant (dbca). The name of the database is ORCL, the db_unique_name is ORCLRA (the db_unique_name is quite important for dataguard to distinguish the different sites)<\/p>\n
there are some mandatory things that must be checked or adjusted before we can start to create the standby… the database must be in archivelog mode (dataguard is a log shipping technology, so this is obvious) and we must enable force logging, so we can transport also the changes executed with “nologging”<\/p>\n
\r\nSQL> shutdown immediate;\r\nSQL> startup mount;\r\nSQL> alter database archivelog;\r\nSQL> alter database force logging;\r\nSQL> alter database open;\r\n<\/pre>\n
for those who are asking what the hell standby redo logs are – here a short explanation: the primary database is transferring the redo information to the standby via sql.net… this means there must be an oracle instance receiving this information and store it somewhere. And because oracle wants to separate standby and redo information, there is a new log type called standby log. The easiest way is to create it on the primary site – when the primary becomes a standby, the standby logs are already there… and if the logs are present before duplicating the database, they will be created automatically during the copy \ud83d\ude09 So we are creating one more standby logs than we have redo logs – this is because we have one extra source that could copy data from the primary: there can be a stream from each redo log group and one directly from the log buffer. For further information you may want to watch this<\/a> site…<\/p>\n often forgotten, but in my opinion an important parameter. The default of this parameter is “manual”, which means when adding a datafile on the primary site, you have to do the same on the standby site. If you forget to do so, the replication will stop… and we are humans and humans do forget things sometimes. So just set the parameter to “auto” and oracle will take care of it.<\/p>\n OK, now we need to modify the listener.ora file – oracle dataguard need access to the database via sql.net under any circumstances, even when the database is down. To solve this problem, we have to add a static entry to the listener.ora…<\/p>\n the same must be done on the standby site, too – only change the “GLOBAL_DBNAME” to a proper value – the “_DGMGRL” extension is mandatory for oracle dataguard… regarding the db_unique_name: I usually use the db_name with the suffix RA (for “recovery site A”), RB, RC, etc… and finaly restart or reload the listener to enable the new configuration.<\/p>\n now we are doing the last few steps of preparation… to start the standby database, we need an pfile… so we a just creating one from the primary:<\/p>\n to be able to logon as sys via sql.net, we need the password file on the standby site… so we just copy the file together with the pfile to the other server:<\/p>\n And now just edit the pfile and replace all values containing the db_unique_name with the now value.<\/p>\n to startup the instance, you have to create the audit file destination manually<\/p>\n Oracle dataguard requires an spfile, so we have to create it…<\/p>\n ok, now we have got a primary database in archivelog mode with force logging enabled and a standby instance in nomount state with the same password file, using an spfile. Furthermore, both instances are accessable via sql.net… so lets check if it’s working! if both are working, we can continue, otherwise check the password file and listener entries again. Also checking the sqlnet.ora if ezconnect is allowed could be a good idea.<\/p>\n the next step is the easiest one, although it might take a while, depending on the size of your database… we do an rman duplicate:<\/p>\n when done you should also add the database to the clusterware, so the database will be started automatically after a reboot etc<\/p>\n ok, and now the last step: configure the dataguard broker. First we have to enable the broker on both sites…<\/p>\n and now (maybe wait a few seconds until the broker process is really up) we have to configure it… if you once understand the syntax, it’s really a no brainer.<\/p>\n thats it… you can check your success via the broker (check the lines “Transport LAG” and “Apply Lag”) or by checking the alert.log of the standby database (just check if the media recovery process is waiting for an archivelog in transit)… suspicious people may do both \ud83d\ude09<\/p>\n just tell me if I have missed something…<\/p>\n so far ok, although I wrote that there are plenty of websites and blogs describing how to create a dataguard standby, I’m doing one on my own… specially because my colleague asked me to write them a manual, so I decided to … Continue reading \r\nSQL> select count(*) from v$log;\r\n COUNT(*)\r\n----------\r\n 3\r\n\r\nSQL> select distinct bytes from v$log;\r\n BYTES\r\n----------\r\n 209715200\r\n\r\nSQL> alter database add standby logfile group 11 size 209715200;\r\nSQL> alter database add standby logfile group 12 size 209715200;\r\nSQL> alter database add standby logfile group 13 size 209715200;\r\nSQL> alter database add standby logfile group 14 size 209715200;\r\n<\/pre>\n
\n
\r\nSQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;\r\n<\/pre>\n
\n
\r\nvi $GRID_HOME\/network\/admin\/listener.ora\r\nLISTENER =\r\n (DESCRIPTION_LIST =\r\n (DESCRIPTION =\r\n (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))\r\n (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))\r\n )\r\n )\r\n\r\nENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent\r\nVALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent\r\n\r\n# add static listener entry starts here:\r\nSID_LIST_LISTENER =\r\n (SID_LIST =\r\n (SID_DESC =\r\n (SID_NAME = ORCL)\r\n (GLOBAL_DBNAME=ORCLRA_DGMGRL)\r\n (ORACLE_HOME = \/u00\/app\/oracle\/product\/12.2.0\/db_1)\r\n )\r\n )\r\n<\/pre>\n
\n
\r\nSQL> create pfile='\/tmp\/initORCL.ora' from spfile;\r\n<\/pre>\n
\r\nBASH> scp \/tmp\/init${ORACLE_SID}.ora standby:\/${ORACLE_HOME}\/dbs\/\r\nBASH> scp ${ORACLE_HOME}\/dbs\/orapw${ORACLE_SID} standby:\/${ORACLE_HOME}\/dbs\/\r\n<\/pre>\n\r\n# do on standby site!\r\nBASH> sed -i 's\/ORCLRA\/ORCLRB\/g' ${ORACLE_HOME}\/dbs\/init${ORACLE_SID}.ora\r\n<\/pre>\n\r\nBASH> grep audit_file_dest ${ORACLE_HOME}\/dbs\/init${ORACLE_SID}.ora\r\nBASH> mkdir -p \/u00\/app\/oracle\/admin\/ORCLRB\/adump\r\n<\/pre>\n\n
\r\nBASH> echo "ORCL:\/u00\/app\/oracle\/product\/12.2.0\/db_1:N" >> \/etc\/oratab\r\nBASH> . oraenv <<< ORCL\r\nSQL> startup nomount;\r\nSQL> create spfile='+DATA\/ORCLRB\/spfileORCL.ora' from pfile;\r\nSQL> shutdown;\r\nBASH> mv ${ORACLE_HOME}\/dbs\/init${ORACLE_SID}.ora ${ORACLE_HOME}\/dbs\/init${ORACLE_SID}.ora.bak\r\nBASH> echo "spfile='+DATA\/ORCLRB\/spfileORCL.ora'&amp;quot; ${ORACLE_HOME}\/dbs\/init${ORACLE_SID}.ora\r\nSQL> startup nomount;\r\nSQL> show parameter spfile\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- -----------------------------------------------\r\nspfile string +DATA\/ORCLRB\/spfileorcl.ora\r\n<\/pre>\n\n
\nthe easiest way is just to logon to the database via sqlplus:<\/p>\n\r\nsqlplus sys\/oracle@primary:1521\/orclra_dgmgrl as sysdba\r\nsqlplus sys\/oracle@standby:1521\/orclrb_dgmgrl as sysdba\r\n<\/pre>\n
\n
\r\nrman target sys\/oracle@primary:1521\/orclra_dgmgrl auxiliary sys\/oracle@standby:1521\/orclrb_dgmgrl\r\nRMAN> duplicate target database for standby from active database;\r\n<\/pre>\n
\r\nBASH> srvctl add database -db ORCLRB -oraclehome \/u00\/app\/oracle\/product\/12.2.0\/db_1 -role physical_standby -instance ORCL -startoption MOUNT\r\n<\/pre>\n
\n
\r\nSQL> alter system set dg_broker_start=true;\r\n<\/pre>\n
\r\nBASH> dgmgrl \/\r\nDGMGRL> create configuration 'DG' as primary database is 'ORCLRA' connect identifier is '192.168.56.21:1521\/ORCLRA_DGMGRL';\r\nDGMGRL> add database 'ORCLRB' as connect identifier is '192.168.56.22:1521\/ORCLRB_DGMGRL';\r\nDGMGRL> enable configuration;\r\n<\/pre>\n
\r\nDGMGRL> show database 'ORCLRB'\r\nDatabase - ORCLRB\r\n\r\n Role: PHYSICAL STANDBY\r\n Intended State: APPLY-ON\r\n Transport Lag: 0 seconds (computed 0 seconds ago)\r\n Apply Lag: 0 seconds (computed 0 seconds ago)\r\n Average Apply Rate: 4.00 KByte\/s\r\n Real Time Query: OFF\r\n Instance(s):\r\n ORCL\r\n\r\nBASH> tail \/u00\/app\/oracle\/diag\/rdbms\/orclrb\/ORCL\/trace\/alert_ORCL.log\r\nMedia Recovery Waiting for thread 1 sequence 15 (in transit)\r\n2018-09-06T12:59:27.551097+02:00\r\nRecovery of Online Redo Log: Thread 1 Group 12 Seq 15 Reading mem 0\r\n Mem# 0: +DATA\/ORCLRB\/ONLINELOG\/group_12.274.986057281\r\n Mem# 1: +DATA\/ORCLRB\/ONLINELOG\/group_12.275.986057283\r\n<\/pre>\n
\nBenjamin<\/p>\n","protected":false},"excerpt":{"rendered":"