June 14, 2007
Manual Manu al Standby Database under Oracle Standard Edition By Sean Hull Oracle's Standby tec hnology hnology has been rebranded as DataGuardi DataGuardin n recent versions versions of Oracle. Oracle has added a layer of tec hnologyautom hnologyautomation ation on top of the standby t ec hnology, hnology, maki making ng automatic automatic Standbytec hnology hnology seamless. seamless. But But what a bout the folks on Standard Edition Edition Orac Orac le? Arethey left out in the cold? Well, it turns out that it is still possible to create a*manual* standby database on Oracle SE. Here's how you do it. 1. First you need to create the initial standbydatabase. Here are the steps to do that: a. Put the primary database in archivelogmode, if it is not already, and add at least LOG_ARCHIVE_DEST andLOG_ARCHIVE_START to your init.ora. SQL> SHUTDOWN IMMEDIATESQL> STARTUP MOUNTSQL> ALTER SYSTEM ARCHIVE LOG START; b. Next, c reate a hotbackup of t heprim heprimary database. Although you can do t his with RMAN, RMAN, it is probably probably easiest to just do it manuall manually y so you know what is going on. For each tablespace do: SQL> alter tablespace EXAMPLE begin backup;SQL> !cp example01.dbf /my/db/backup/SQL> !cp example02.dbf /my/db/backup/SQL> !cp example03 In the above e xample, xample, the '!'symbol tells sqlplus to run the c omm ommand from the s hell, so we're using the Unix'c Unix'c p' command to make copies of thos e files (whic h are now frozen in backupmode) in another location. c. Now, creat e a standby c ontrolfilefrom ontrolfilefrom prim primary database: ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.ctl'; d. At this point, you want to copyeverything over to the standby server including datafiles, standby controlfile& config files: $ scp /my/db/backup/*.dbf
[email protected]:/export/home/oracle/ e. From the standby machine, editthe standby init.ora file. Use this parameter to tell Oracle where files onthe primary database will be located on the standby. For example if you hadfiles in /ora/oracle on primary, and they are moved to /export/home/oracle onstandby, this would work for you: DB_FILE_NAME_CONVERT='/ora/oracle','/export/home/oracle' Note that you can use MULTIPLEpairs of values here, if you have files in different locations. Alternatively,you can startup and mount the standby database then issue: SQL> alter database rename file '/ora/oracle/myfile.dbf' to /export/home/oracle/myfile.dbf' as an example. Now you're also likely to have anew location for your archived redo log files, and that's where the parameterLOG_FILE_NAME_CONVERT comes into play. Important note, neither of thesetwo parameters work for the ONLINE redolog files. Those you will have torename yourself. If you do not do so, you will get an error at the time youtry to SWITCHOVER your standby database. Such errors are easily remedied byrunning that command. f. Now, it's time to start thestandby instance and mount it. SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standbySQL> atler database mount standby database; g. Almost Almost t here. Lastly, we needto recover t he sta ndby database using the AUTO option. Note that you shouldbui shouldbuild ld a simple simple shell sc ript ript t o st artup sqlplus sqlplus and run these commands. A namelike manual_standby.sh would work well. You can then run this periodically,say every half hour, from cron to apply any new archived redolog files thathave showed up via move_standby.sh below. SQL> recover standby database;AUTO h. Now, of course, you'll want totest your standby database. You do this by starting up in read-only mode. SQL> alter database open read only; i. Don't forget to put it back instandby mode so that when your manual_standby.sh script runs from cron, it won'treturn errors. SQL> shutdown immediate;SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standbySQL> atler database mount stand 2. What scripts should run run via cron on the primary andstandby database? As w e mentioned mentioned earlier, earlier, a script c alled alled manual_standb manual_standby.shwould y.shwould work well well on the standby dat abase. T his script applies new arc hived redologsthat redologsthat have arrived arrived from the production system. Run it every half hour and seehow that works for you. The database must be mounted in standby mode (notread-only) or this script will fail. You'll also also want a sc ript ript on t he product product ion server. Nam Name e itmove_standby.sh, and run run it every t hirty hirty minutes inutes to st art with. This c an use rsynct o move move redolog files files from production to standby. A command like this wouldwork: $ rsync -e ssh -Pazv /ora/oracle/arch/ /ora/oracle/arch/ oracle@remote:/export/home/ora oracle@remote:/export/home/oracle/arch/ cle/arch/ Note that you may want to adjust options to ssh to yourneeds. In addition, this presumes you have ssh autologin configured. Read upon the ssh-keygen command. The .ssh directory conta ins a public public key, which isshipped over to t he st andby machine, machine, and put in the "authorized_keys" "authorized_keys"file. file. ssh will then login login without a password. Rsync uses ssh as the transportmechanism, so it also executes without a password. Rsync is very smart andonly copies blocks and pieces of files that are different, so it is very fast, andalso does checksums to guarantee consistency. 3. Is the standby database behind the productiondatabase? productiondatabase? Yes, keep in mind ind we a re creat ing a manual manual standbydatabase. T he sta ndby database will tend to be behind behind product product ion by about halfthe size of a redolog file. file. So if those files are 100M, and you generate 100Mof transactions in 30 minutes, then on average standby will be fifteen minutesbehind. 4. What types of changes a nd statements on productionwill productionwill not be automatically applied to standby? In database parlance, parlance, any PHYSICAL c hanges to the db, plusany plusany c omm ommands, issues with the NOLOG NOLOGGI GING NG option. option. Physical c hanges includecreation of new tablespaces, adding new datafiles, renaming datafiles, autosizingof datafiles, altering redolog files, altering controlfiles and so on. Inaddition, primary database processes or commands using the UNRECOVERABLE optionwill not be propagated to the standby database. There are specific and detailed instructions for making making someof someof t hese physical c hanges on the standby db manuall manually, y, however in many case srecreating t he entire standby database per the instructions above, might be thebest option.
5. How can we verify that the standby database is up todate? If you already have the manual_standby.sh script runningfrom cron, disable it. Then login with sqlplus and issue: SQL> alter database open read only; Now that you have t he database open read-only, run whateverSQL commands you want to in order to verify some change which you know about onproduct ion. When you are done, shutdown, and startup in standby mode again. Don'tforget to reenable manual_standby.sh in the crontab. 6. What happens if the standby system restarts? You could have it automatically start the standby database. In that case, be sure to just check the logfiles. If you want to do itmanually in those instances, fire up sqlplus and then issue: SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standbySQL> alter database mount standby database; 7. What kind of messages can I expec t to see in thestandby alert.log? The a lert.log is going to have a lot of e xtra messages s incewe are repeatedly t rying to RECOVER when there may or may not be newtransac tion logs. When it does this it will say, "looking for archived logfile1_356.dbf, not found". On the other hand, if it finds it, it will saythat it is applying it. You can use unix commands "grep" and"less" to scan through the alert.log file quickly. 8. What other scripts should be put in place ? a. a script to cleanup old archivedredo logs on primary. b. a sc ript t o c leanup old archivedredo logs on sta ndby c. a script to rotate and archivethe alert.log file when it gets large d. a script to watch the alert.logfile for ORA-xxxxx errors and report them to nagios if it finds any (on bothprimary and standby) e. a script to login (via ssh autologin)and check what the latest archived redolog file is, and then also login to thestandby and check the alert.log file to verify that those transactions havebeen applied. 9. How do we switchover in the ev ent of a failure of theprimary? Switchover *can* be done with a script, however I recommendwith our manual standby database that you (a) monitor for emergencies onproduction and (b) manually perform the failover if necessary. This will avoidfalse positives. Also, it allows you to ship additional redolog data if youhave it available from production. The switchover is a two-step process. a. Apply remaining redo as we havedone before with commands in manual_standby.sh. b. Startup the database normally,in a read-write mode. 10. What network changes need to happen to failover? The listener.ora file should be already configured. You canuse the sa me c onfig as primary with a different IP, or you can give this db adifferent tnsname. For instance, you c ould c all primary SEANA and st andbySEANB. Then in your applicat ion server configs, when you failover, yourdatabase c onnection c onfigurations need to be updated to point t o SEANB. T heapp servers will probably also need to be restarted at this point. 11. Why can't the primary s hip redologs and synchronouschanges? Basically t hey c all it a * manual* standby dat abase f or areason. DataGuard supports options that look like t he following: LOG_ARCHIVE_DEST_3='SERVICE=stby1 LGWR SYNC AFFIRM'LOG_ARCHIVE_DEST_STATE_3=ENABLE Again, these are not available in Oracle SE. 12. Once we've failed over, how do we switch back to theprimary? Switching back to the primary database involves these steps: a. Follow the steps in item 1 aboveto create a standby database on what was the primary system. b. If you want to be perfectlyclean syncing, do the following: SQL> shutdown immediateSQL> startup restrictSQL> alter system switch logfileSQL> shutdown immediate c. Copy over the last archived redologfiles d. Apply them and switchover asdescribed in item 8 above. 13. Are there special init.ora parameters? What makesour standby database special? The main two things that make it a standby databaseare: a. The standby control file(created from primary) - alter database create standby controlfileas '/my/path/to/standby.ctl b. The process of mounting as astandby database - startup nomount pfile=standby.ora - alter database mount standbydatabase; There are of course some init.ora parameters which arespecial for the s tandby database as well: DB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERT So if you do a "shutdown immediate" on thestandby, you would start again with:
SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standbySQL> alter database mount standby database; Conclusion:
Standby database tec hnology in Oracle is a powerful highavailability solution. Even if you're using Oracle SE, you c an still takeadvantage of these features built into Oracle, with just a little s cripting,hand holding, and ample monitoring. Do your research, t est, test , and t estagain on a development s erver. And don't forget t o monitor all your logfilesfor errors. Following these guidelines, you should be in very good shape, at amuch lower cost.
