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 possible. Curious? Come under cat.

In order not to confuse anyone and to preserve the uniformity of form and essence, the article will accept all the designations and requirements of the article mentioned. The theoretical part can be found in the same place, but I will tell you about the features of the implementation of a standby database using Oracle SE and try to highlight the 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 profiles of these users, as well as the entire directory structure and environment variables. Oracle software on the backup server is already installed.

Let's get started
')
1 step:
First, 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 the list for copying as follows:

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

If there is no possibility to stop the base, then it is necessary to make a hot backup. To do this, you can use the RMAN utility, or carry out the procedure in manual mode:
For this you need:

SQL> alter tablespace <TB_name> begin backup;

and copy all the data files in this tablespace.

Important! Do not forget to turn off backup mode after copying all files.

SQL> alter tablespace <TB_name> end backup;

Repeat for all table spaces. Backup received.

Step 2:
Now we need to create a control file for our standby database.
Everything is simple, on the main server:

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '___';

Step 3:
We copy all the files related to our main database to the backup server (data files, control files, password files and redologs), so we get two identical databases on two servers.

4 step:
Replace all control files with standby with the one created in step 2.
The number and addresses can be viewed in the control_files parameter.

SQL> show parameter control_files;

Step 5:
Here we get the main problem with the use of Oracle SE, there is no DataGuard that fully implements the process of transferring and applying archive logs to standby.
Our task is to transfer the archivogues from the main base to the reserve. To transfer logs you can use SSH, or share the folder from the parameter LOG_ARCHIVE_DEST_n and make it mount on our standby, but this is a security issue.

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

Tips:
- In the 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 to automate the process of deleting the applied logs, so that you would not encounter a shortage of space (with the active formation of archivists), but here too there are difficulties. You should remember about the possibility of a violation of the standby server and not delete the applications that have not yet been applied, as well as the need to regularly back up your database so that you do not delete the applied logs before their main backup (for example, on a tape device). These points are already governed by the features of your database operation modes and accessibility / reliability policy. Or, focusing on the load on your database, regularly clean the logs manually.

6 step:
Run standby:

SQL> startup nomount pfile=___;
SQL> atler database mount standby database;
SQL> recover standby database;
AUTO

And everything - standby received, rollback of archive logs occurs.

Important notes:
- When switching from the main base to the reserve one, the main one does not go into standby mode and it needs to be recreated;
- In case of the collapse of the main database, you may lose some of the data that has not been archived (the time interval is governed by the frequency of script processing 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 standby;
- When creating new data files on the main database, they must be copied to the backup;
- Standby DB can be opened in read-only mode, but in this case logging will not happen, and after that it must be returned to its previous state and terminate all sessions;
- To go to the backup database, you open in normal mode, without forgetting to change the addresses (or host name) in tnsnames and listener.ora, so that your applications running from the database would continue to function normally. Or, using the capabilities of network equipment, assign logical ip-addresses to databases;
- On the backup database you will see an error saying that it cannot find the next archive log;
- Know that this journal has not yet been archived on the main database, you can check it by running:

SQL> ALTER SYSTEM SWITCH LOGFILE;

On the main base, wait for the script to work on a schedule (or execute it forcibly) and check the fact that this log is running on standby.

Total:

Such a solution to the problem of organizing standby is more laborious, requires more effort and control of work by the DBA, but is significantly cheaper than the version with DataGuard Oracle EE.