Physical Standby DB for Oracle SE

In the comments to the querct article “Once again about Oracle standby”, the question arose about the possibility of creating a standby server on Oracle SE. The answer is perhaps. Curious? Come under the cat.

In order not to confuse anyone and to preserve the uniformity of form and essence, all designations and requirements from the mentioned article will be accepted in the article . The theoretical part can be learned in the same place, I’ll tell you about the features of the implementation of standby base using Oracle SE and try to highlight possible “pitfalls”.

First you need: 2 different servers, for the main and backup databases, with identical OS, users and user groups - owners of all Oracle files and directories, as well as the presence of absolutely identical profile of these users, as well as the entire directory structure and environment variables are the same. Oracle software is already installed on the backup server.

Let's get started.

1 step:
To begin with, we need to copy all the files of the main database to the backup server. Depending on the mode of operation of the base and its volume, there are different options. The easiest, but not always acceptable way, is to make a cold backup. The difficulty is that for this you need to stop the database - if you have this opportunity - great. Stop the database and just copy the data files. And also make backup copies of control files (just in case) and operational logs.

You can get a list of what you need for copying this way: If there is no way to leave the database, then you need to make a hot backup. To do this, you can use the RMAN utility, or carry out the procedure in manual mode: To do this, you must: and copy all the data files included in this table space.

SQL> select name from v$datafile;
SQL> select member from v$logfile;





SQL> alter tablespace begin backup;



Important! Do not forget to disable backup mode after copying all files. Repeat for all table spaces. Backup is received. Step 2: Now we need to create a control file for our standby database. Everything is simple here, on the main server: step 3: Copy all the files related to our main database to the backup server (data files, control files, password file and redologs), i.e. we get two identical databases on two servers. Step 4: Replace all control files on standby with the one created in step 2. The quantity and addresses can be viewed in the control_files parameter. 5 step:

SQL> alter tablespace end backup;







SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'имя_файла_для_стендбая';









SQL> show parameter control_files;


Here we get the main problem with using Oracle SE, the very DataGuard is missing, which fully implements the process of transferring and using archive logs on standby.
Our task is to transfer archives from the main database to the backup one. You can use SSH to transfer logs, or share the folder from the LOG_ARCHIVE_DEST_n parameter and mount it on our standby, but this is already a security issue.

You can implement periodic transfer with the simplest shell script listed in the cron schedule. Examples of such scripts can be found on the network, or write yourself.

Tips:
- In a script, it is desirable to implement the process of logging the work of the script itself so that you can always track its work;
- I also recommend automating the process of deleting applied logs so that you do not encounter a lack of space (with the active formation of archive logs), but there are also difficulties here. You should remember about the possibility of disruption of the standby server and not delete the ones that have not been applied yet, as well as the need to regularly backup your database so that you do not delete the logs used before their main backup (for example, to a tape device). These points are already regulated by the features of your database operating modes and the availability / reliability policy. Or, focusing on the load on your database, regularly clean the logs manually.

Step 6:
Run standby: And that's it - standby is received, the archive logs are rolling. Important notes:

SQL> startup nomount pfile=имя_файла_для_стендбая;
SQL> atler database mount standby database;
SQL> recover standby database;
AUTO





- When switching from the main database to the backup one, the main one does not go into standby mode and must be recreated;
- If the main database crashes, you may lose some of the data that has not been archived (the time period is regulated by the frequency of script execution in cron);
- The main base when working on Oracle SE (without using DataGuard) does not perceive the backup as such and it is necessary to monitor the work of standby;
- When creating new data files on the main database, they must be copied to the backup;
- Standby database can be opened in read-only mode, but in this case logging will not occur, and after that it must be returned to its previous state and all sessions should be completed;
- To switch to the backup database, you open in normal mode, without forgetting to change the address (or host name) in tnsnames and listener.ora so that your database applications continue to function normally. Or, using the capabilities of network equipment, assign bases logical IP addresses;
- On the backup database, you will see an error stating that it cannot find the next archive log;
- Know that this journal has not yet been archived to the main database, you can check it by doing the following:

SQL> ALTER SYSTEM SWITCH LOGFILE;

On the main database, wait for the script to be worked out according to the schedule (or execute it forcibly) and check the fact that this log is rolled onto standby.

Total:

A similar solution to the standby organization problem is more time-consuming, requires more effort and control by the database administrator, but is much cheaper than the DataGuard Oracle EE option.

Also popular now: