Create DR Database for Oracle Manually
It is possible to use directly RMAN to create manually a standby database without shutting down the primary database. You can then use such standby database for synchronization with Dbvisit
The assumption in this example is that the primary and standby database servers are exactly the same:
- Same Operating System and version
- Same Database Software and Patch Levels ($ORACLE_HOME/OPatch/opatch lsinventory)
- Same directory structure is used
- Same Operating System GIDs and UIDs for oracle relater accounts
- You do have available space for a full backup on your primary and standby server
Preliminary steps:
1. Create the same Oracle directory structure on the standby server as on the primary server.
2. Copy the database parameter (pfile/spfile) file for the production database on the production server to the standby server into $ORACLE_HOME/dbs
3. Copy the /etc/oratab file from the production server to the standby server, of make sure the entry for the database you want to create the standby database for is in the standby oratab file.
4. Copy the password file $ORACLE_HOME/dbs/orapw<$ORACLE_SID> from the production server to the standby server.
5. Create a directory on the primary and standby server where the full backup of the database can be kept. In this example /u02/app/oracle/dbvisit_backup will be used.
On the primary server:
1. Set the Oracle environment
Do this on the production database (with the oraenv command in Linux/UNIX) - in the example the database is called "MEGA":
[oracle@czlin0211 oracle]$ . oraenv ORACLE_SID = [MEGA] ? The Oracle base has been set to /u01/app/oracle
2. Determine current archivelog sequence on primary and create pfile:
You need to find out current sequence number of your primary database via sqlplus
[oracle@czlin0211 oracle]$ sqlplus / as sysdba SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /tmp Oldest online log sequence 103 Next log sequence to archive 106 Current log sequence 106 SQL> create pfile='/u02/app/oracle/dbvisit_backup/initMEGA.ora' from spfile;
In our case we need to note and remember "106"
3. Run the following RMAN commands to backup the primary database
Note that TAGs are used to make it easier to restore. For more detail on RMAN and using "tags" please see the Oracle documentation
Make sure to change the Sequence number (106) in the RMAN script corresponding to your archive sequence.
Using common sense, change "maxpiecesize" size as well - if you have slow network and few TB database, it's better to split the backup into more smaller files
[oracle@czlin0211 oracle]$ rman target /
run{
sql "alter system archive log current";
allocate channel ch1 type disk maxpiecesize 1024m;
backup as compressed backupset database format '/u02/app/oracle/dbvisit_backup/db_%U' tag 'DBVCSD_BK';
backup current controlfile for standby format '/u02/app/oracle/dbvisit_backup/standby-ctl';
sql "alter system archive log current";
backup archivelog from sequence 106 format '/u02/app/oracle/dbvisit_backup/arc_%U' tag 'DBVCSD_ARC';
release channel ch1;
}
4. Copy all backup files
Copy all files located in /u02/app/oracle/dbvisit_backup to same location on standby server via scp:
[oracle@czlin0211 oracle]$ scp /u02/app/oracle/dbvisit_backup/* czlin0212:/u02/app/oracle/dbvisit_backup
On the standby server:
1. Startup Standby database nomount
Set the environment for the standby database, then create spfile and start the standby database in nomount mode:
[oracle@czlin0212 oracle]$ . oraenv ORACLE_SID = [MEGA] ? The Oracle base has been set to /u01/app/oracle
Now you have a chance to edit the standby database init parameters in pfile /u02/app/oracle/dbvisit_backup/initMEGA.ora - for example memory allocation, etc ...
[oracle@czlin0212 oracle]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 29 12:31:46 2023 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/u02/app/oracle/dbvisit_backup/initMEGA.ora'; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 2147480480 bytes Fixed Size 9687968 bytes Variable Size 536870912 bytes Database Buffers 1593835520 bytes Redo Buffers 7086080 bytes SQL>
2. Restore the backup you copied
Restore the backup on standby server via RMAN:
[oracle@czlin0212 oracle]$ rman target /
run{
restore standby controlfile from '/u02/app/oracle/dbvisit_backup/standby-ctl';
sql 'alter database mount';
restore database from tag 'DBVCSD_BK';
recover database from tag 'DBVCSD_ARC';
}
Very important is that the recovery part completes without any errors, like so:
... ... Starting recover at 29-AUG-23 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 29-AUG-23 RMAN>
RMAN has started the standby database in mount mode which is correct for Dbvisit Standby to continue.
3. Create corresponding configuration in dbvcontrol GUI
Now you can create the DDC file in the dbvcontrol GUI (in detail described in: Create Oracle Configuration ):
Take special care, that SID and unique database name corresponds to the standby database you created manually.
Once created, DDC in the dashboard will look like so:
4. Backup & send archivelog to verify that standby database can be correctly recovered.
We need to perform this via command line. On primary run:
[oracle@czlin0211 oracle]$ /usr/dbvisit/standbymp/oracle/dbvctl -d MEGA -R
=============================================================
Dbvisit Standby Database Technology (11.4.0) (pid 3040210)
dbvctl started on czlin0211: Tue Aug 29 13:09:02 2023
=============================================================
Note FORCE_LOGGING is disabled in the primary database.
>>> Resynch option given. Dbvisit Standby configuration will be copied to czlin0212...
>>> Transferring Log file(s) from MEGA on czlin0211 to czlin0212:
thread 1 sequence 116 (1_116_1145654994.dbf)... done
thread 1 sequence 117 (1_117_1145654994.dbf)... done
=============================================================
dbvctl ended on czlin0211: Tue Aug 29 13:09:05 2023
=============================================================
on standby run:
[oracle@czlin0212 oracle]$ /usr/dbvisit/standbymp/oracle/dbvctl -d MEGA ============================================================= Dbvisit Standby Database Technology (11.4.0) (pid 928715) dbvctl started on czlin0212: Tue Aug 29 13:09:45 2023 ============================================================= >>> Applying Log file(s) from czlin0211 to MEGA on czlin0212: thread 1 sequence 116 (1_116_1145654994.arc)... done thread 1 sequence 117 (1_117_1145654994.arc)... done Last applied log(s): thread 1 sequence 117 Next SCN required for recovery 135844738 generated at 2023-08-29:12:26:30 +02:00. Next required log thread 1 sequence 118 ============================================================= dbvctl ended on czlin0212: Tue Aug 29 13:09:48 2023 =============================================================
5. Final step is to enable the automated standby update.
On primary run:
[oracle@czlin0211 oracle]$ /usr/dbvisit/standbymp/bin/dbvagentmanager auto-update modify --enable --set-interval-seconds 300 --noconfirm
Refreshing the dbvcontrol GUI will show automated standby enabled and there's now a gap time displayed
This step concludes the manual creation of DR database for Dbvisit Standby