Create Standby Database using CLI
- Anton Els (Deactivated)
- Ankit Arora (Deactivated)
This section will show you an example of creating a standby database. This Create Standby Database process (setup) is also referred to as CSD.
Important
Creating a standby database is performed on the primary server (primary node).
If you are using Oracle RAC, you only need to run the Create Standby Database (CSD) process from one of the primary nodes (1st node recommended). But you must have a DDC file already created on both primary nodes.
The example below will show you how to create a standby database running on standby server "dbvlin102" from the primary database called "proddb" running on the primary server "dbvlin101".
Windows only:
Start the "Dbvisit Standby command console" from the Start Menu ->Programs -> Dbvisit -> Standby.
This will start a Command Line with your session already in the DBVISIT_BASE/standby directory.
From the DBVISIT_BASE/standby directory, execute the "dbvisit_setup.exe" command to start the setup utility.
Linux/Unix only:
Go to the DBVISIT_BASE/standby directory. Start the Dbvisit Standby setup utility using the command: ./dbvisit_setup
oracle@dbvlin101[/usr/dbvisit/standby]: ./dbvisit_setup
=========================================================
Dbvisit Standby Database Technology (7.0.04.11726)
http://www.dbvisit.com
=========================================================
=>dbvisit_setup only needs to be run on the primary server.
Is this the primary server? <Yes/No> [Yes]: Yes
The Dbvisit Standby Menu will now be displayed:
Linux/UNIX and Windows:
Step 1:
To create a standby database, choose option 7.
=========================================================
Dbvisit Standby Database Technology (7.0.04.11726)
http://www.dbvisit.com
Dbvisit Database setup
Default values will be shown in []
Options:
1) New Dbvisit Database setup (combines options 2,3,4)
1a) New Dbvisit RAC Instance setup (combines options 2,3,4)
2) New Dbvisit Database configuration (DDC) file setup
3) New Dbvisit Archive Management Module (AMM) setup
4) New Dbvisit Database repository (DDR) setup
5) Manage Dbvisit Database repository (DDR)
6) Update Dbvisit Database configuration (DDC) file
7) Create Standby Database (and template)
8) Synchronize Standby Database
9) Uninstall Dbvisit Database repository (DDR)
E) Exit
=========================================================
Please enter choice : 7
Step 2:
From the next section, you will se a list of available DDC files. If none exist, you need to first run the menu option 1 above to create a DDC file for the Primary/Standby database pair.
From the list being displayed, choose the primary database you would like create a standby for. In this example a standby database will be created for the database "proddb" which is DDC number 2:
------------------------------------------------------------------------------
=>Creating Standby Database.
Primary database will NOT be shutdown.
Choose database:
The following Oracle database(s) have a Dbvisit Database configuration (DDC)
file on this server:
DDC
===
1) devdb
2) proddb
3) testdb
4) Return to menu
Please enter choice : 2
Is this correct? <Yes/No> [Yes]: Yes
From the above select DDC number 2 and confirm selection.
Step 3:
The primary database must be up and running for the setup to proceed. The following checks are performed:
Configuration differences between the primary DDC file and the DDC file on the standby server. If no DDC file exist on the standby (during a new installation it will not exist on the standby, but only on the primary) or if any differences are found, the primary copy of the DDC file will be copied to the standby server.
- The Oracle Installation is reviewed to ensure the Oracle Database software is installed
- The primary datafile statuses are reviewed
- The Dbvisit Standby Configuration is parsed
- The primary database selected is reviewed to make sure it is in the correct state to proceed with the creation.
- The total size of the primary database is calculated and displayed.
If uncompleted creation of the standby database is detected, a restart option will be available. Standby database can also be created using a saved template
Confirmation is asked if you want to continue with the Create Standby Database (CSD) setup.
Database proddb is up.
>>> Checking Dbvisit Standby for configurational differences between dbvlin101 and
dbvlin102...
Waiting for 5 seconds (RETRIES_WAIT_IN_SEC) until retry.
Dbvisit Standby configurational differences found between dbvlin101 and dbvlin102...
> Transferring 'dbv_proddb.env' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [9859 KB/s] - done.
>>> Checking Oracle installation on dbvlin102 in
/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle...
>>> Checking primary datafiles on dbvlin101...
>>> Validating Dbvisit configuration file on dbvlin102...
>>> Checking if a database is up and running on dbvlin102...
Checks completed OK.
>>> Total database size for proddb is 1.36GB
What would you like to do:
1 - Create standby database (and optionally save settings in template)
2 - Help
3 - Terminate processing
Please enter your choice [1]:
Step 4:
The setup process will ask if you want to make use of ASM storage for the Standby Database. As this environment is not using ASM, the default option is "No". If your primary database is making use of ASM storage and you do want to make use of ASM on the Standby server, please make sure you specify "Yes" to this question.
It is possible to create a standby database using a normal filesystem while the primary database is making use of ASM.
Please check: "Section - Primary on ASM and Standby using Filesystem"
For critical production (primary) and standby environments it is always recommended to create the standby environment the same as the primary.
------------------------------------------------------------------------------- Do you want to create an ASM standby database (with all or some database files in ASM storage)? [No]: No Standby database will be ASM: N Is this correct? <Yes/No> [Yes]: Yes
In this example we are making use of Normal filesystem for the database storage and ASM will not be used.
Step 5:
It is possible to transfer datafiles directly to the standby server, but only if both primary and standby databases are file system based, small, and the primary database does not contain Oracle Managed Files (OMF). It is recommended not to use the Direct Copy option which will place the tablespaces in backup mode, followed by copying the datafiles sequentially to the standby server. This option is only suited for small environments and where a sufficient network capacity is available.
The Recommended approach is to us a three step process where the primary database is backed up using RMAN to local storage (using compressed backups), followed by the backups being transferred to the standby server, from where the backup is used to create the standby database. To use this approach, specify "No" (the default option) when asked if you want to transfer the data files directly to the standby server.
------------------------------------------------------------------------------- Answer Yes to transfer the datafiles directly to the standby server (not recommended for large database or slow networks) Note tablespaces will be placed in backup mode for direct copy. Answer No (recommended) to create an RMAN backup of primary datafiles in a temporary location and transfer the backup to the standby server Do you want to copy database files directly to the standby server? [No]: No Database files will be copied to standby server using temporary location on dbvlin101. Is this correct? <Yes/No> [Yes]: Yes
In the example below we specify "No" and confirm our selection.
Step 6:
It is possible to enable network compression when files are transferred to the standby database server. This is not recommended. The files will already be compressed.
------------------------------------------------------------------------------- Use network compression to compress the database files during transfer? [No]: No Database files will be compressed during transfer: N Is this correct? <Yes/No> [Yes]: Yes
In this example we specify "No" and confirm our selection.
Step 7:
It is possible to create missing filesystem directories on the standby server automatically:
------------------------------------------------------------------------------- Dbvisit Standby provides functionality to create missing filesystem directories on the standby server automatically. Do you want Dbvisit Standby to create missing filesystem directories on the standby server automatically? [Yes]: Yes Dbvisit Standby will create missing filesystem directories automatically: Y Is this correct? <Yes/No> [Yes]: Yes
In this example we specify "Yes" as we do want Dbvisit Standby to create the required directories on the standby server.
Step 8:
Oracle database parameters for the standby database can be customised:
------------------------------------------------------------------------------- The following oracle database parameters will be set in the standby database pfile or spfile: ------------------------------------------------------------------------------- audit_file_dest /u01/app/oracle/admin/proddb/adump compatible 11.2.0.0.0 control_files /u01/app/oracle/oradata/proddb/control01.ctl, /u01/app/oracle/fast_recovery_area/proddb/control02.ctl db_block_size 8192 db_name proddb db_recovery_file_dest /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size 4322230272 diagnostic_dest /u01/app/oracle dispatchers (PROTOCOL=TCP) (SERVICE=proddbXDB) memory_target 629145600 open_cursors 300 processes 150 remote_login_passwordfile EXCLUSIVE spfile OS default undo_tablespace UNDOTBS1 ------------------------------------------------------------------------------- What would you like to do: 1 - Proceed with creating the standby database 2 - Edit oracle database parameters for the standby database pfile/spfile 3 - Terminate processing Please enter your choice [1]: 1 Validating oracle database parameters... please wait SUCCEEDED
In the above example, we agree with the parameters as specified, and specify option 1 after which the parameters are reviewed. If you do want to adjust the parameter use option 2 to edit specific parameters. Remember you can always adjust some of the parameters once the standby database creation is complete.
When running a primary database using ASM and you are looking at creating a standby database using a normal Filesystem based storage, you need to make sure you review the following parameters:
- db_create_file_dest
- db_recovery_file_dest
- db_create_online_log_dest_n
When using ASM these values can be set to specific ASM disk groups on the primary server. But when you are creating the standby database, you need to update these parameters to reflect the standby filesystem structure.
Example, this is the parameters on a primary database using ASM:
- db_create_file_dest=+DATA
- db_recovery_file_dest=+FRA
Now when creating the standby database, you will lets say use d:\oracle\oradata as your primary datafile location (the database files will be created in d:\oracle\oradata\DB_NAME example d:\oracle\oradata\prod where prod is the database name). And with regards to the Flash/Fast recovery area you might be using d:\oracle\fast_recovery_area. The following adjustments to the db_create_file_dest and db_recovery_file_dest should be made:
- db_create_file_dest=d:\oracle\oradata
- db_recovery_file_dest=d:\oracle\fast_recovery_area
If you have the db_create_online_log_dest_n values set you have to adjust them as well.
Step 9:
It is possible to use TRANSPORTABLE MEDIA for very large databases or slow networks. When using this option, a backup of the primary database will be created on local storage (can be attached storage such as an external drive, but this must be mounted/made available by the system administrator). Once the backup is complete, it can be shipped (courier or other methods) to the standby site where it is then attached to the standby server. Once attached you can restart the Dbvisit Standby creation to complete the creation of the standby using this backup. This method is recommended for very large databases as well as when there is an extremely slow network between the primary and standby servers.
------------------------------------------------------------------------------- Do you want to use TRANSPORTABLE MEDIA to transfer the database backup to the standby server? Transportable media is an external device such as a USB drive that is first plugged into the primary server and then manually transferred to the standby site and plugged into the standby server to continue the process. It can be used for large databases or slow networks. Specifying No means the network will be used to transfer the database backup. [No]: No Transportable media will be used: N Is this correct? <Yes/No> [Yes]: Yes
In this example we specify not to use Transportable Media and confirm our selection.
Step 10:
To create the standby database, temporary locations on both primary and standby server must be specified.
Make sure this temporary backup location exist on both the primary and standby servers and can hold a full compressed (RMAN Compressed) backup of the primary database
------------------------------------------------------------------------------- A temporary location must be specified on dbvlin101 where the database will be backed up to first. This location must be big enough to hold RMAN backup of the whole database (1.36GB). Specify the location on this server: [/usr/tmp]: /backup/proddb Directory for temporary location will be: /backup/proddb Is this correct? <Yes/No> [Yes]: Yes ------------------------------------------------------------------------------- A temporary location must be specified on dbvlin102 where the database backup will be copied to before moving to specified locations. Specify location on remote server: [/backup/proddb]: /backup/proddb Directory for temporary will be: /backup/proddb Is this correct? <Yes/No> [Yes]: Yes
In this example we are using the temporary backup location which exist on both the primary and standby servers called /backup/proddb.
Make sure this directory exist and has the correct permission.
Step 11:
The process of creating a standby database can be broken into two steps. You can stop between these steps if required.
------------------------------------------------------------------------------- The creation of the standby database consists of 2 parts: 1) Backup database to temporary location. 2) Transfer backup from temporary location to standby server. The process can be stopped after step 1 and then continued with step 2 at a later date. Do you want to the process to stop between these 2 steps? [No]: No Process will stop between steps: N Is this correct? <Yes/No> [Yes]: Yes
In the above example we choose not to stop between the two steps (recommended) and confirm our selection.
Step 12:
It is possible to specify the locations of standby datafiles, redo logs and tempfiles different as primary server. The next few questions shows you the recommended location. You can adjust the locations by using option 2. In the examples below we want to have the standby database the same as the primary one and we accept the default values provided by the setup:
Primary database contains non-OMF datafiles and/or tempfiles. Creation of non-OMF standby datafiles and/or tempfiles requires valid filesystem locations on the standby server. Review and confirm standby locations to create standby non-OMF datafiles and tempfiles. ------------------------------------------------------------------------------- Primary ===> Standby ------------------------------------------------------------------------------- /u01/app/oracle/oradata/proddb ===> /u01/app/oracle/oradata/proddb ------------------------------------------------------------------------------- What would you like to do: 1 - Confirm standby location(s) are correct and continue 2 - Provide different standby location(s) Please enter your choice [1]: 1 Standby locations validated. Primary database contains non-OMF redo logs. Creation of non-OMF redo logs Dbvisit Standby requires valid filesystem locations on the standby server. Review and confirm standby locations to create standby non-OMF redo logs. ------------------------------------------------------------------------------- Primary ===> Standby ------------------------------------------------------------------------------- /u01/app/oracle/fast_recovery_area/PRODDB/onlinelog ===> /u01/app/oracle/fast_recovery_area/PRODDB/onlinelog ------------------------------------------------------------------------------- What would you like to do: 1 - Confirm standby location(s) are correct and continue 2 - Provide different standby location(s) Please enter your choice [1]: 1 Standby locations validated.
Once the selections are made they are verified.
Step 13:
Provided answers can be stored as a template for future use (re-running creating of a standby database):
------------------------------------------------------------------------------- =>Create standby database template for proddb using provided answers? <Yes/No> [Yes]: Yes
In this example we specify "Yes" to save the above steps as a template so we can re-use this template at a later stage if needed.
Step 14:
The final step is to confirm the start of the Create Standby Database (CSD) process. Once you specify yes (option 1) to continue the standby creation process will start.
The first part of the CSD process is to run a "Pre-Flight Check". What this process does is to create a spfile and start an instance on the standby server. This is to double check that the standby server is ready for the creation of the standby database.
What would you like to do:
1 - Continue with creating a standby database
2 - Terminate creating a standby database. The saved template will be
available for future use
Please enter your choice [1]: 1
>>> Dbvisit will now run a pre-flight check for standby database creation. An attempt will
be made to create a standby (s)pfile using oracle standby database parameters, followed
by trying to start the standby instance. If this step fails, then please double-check
the following items before re-running Dbvisit again:
1) Review the standby database parameters you have supplied and provide valid values
unless a template is used.
2) Recreate the template to provide valid values for standby database parameters if a
template is used.
Running pre-flight check for standby creation, please wait... - done.
Once the "Pre-Flight Check" completes, the Create Standby Database (CSD) process start.
Detail output of the Standby Creation is displayed:
>>> Backing up primary database...
Backing up datafile 1... - done.
Backing up datafile 2... - done.
Backing up datafile 3... - done.
Backing up datafile 4... - done.
>>> Creating standby control file... - done.
Backup of primary database completed.
>>> Transferring backup from dbvlin101 to dbvlin102...
> Transferring 'dbv_proddb_csd_dbf_1_0jov5bt8_1_1.rman' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [9398 KB/s] - done.
> Transferring 'dbv_proddb_csd_dbf_1_0kov5bul_1_1.rman' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [10659 KB/s] - done.
> Transferring 'dbv_proddb_csd_dbf_2_0lov5bur_1_1.rman' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [10537 KB/s] - done.
> Transferring 'dbv_proddb_csd_dbf_3_0mov5bvp_1_1.rman' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [10644 KB/s] - done.
> Transferring 'dbv_proddb_csd_dbf_4_0nov5c04_1_1.rman' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [10379 KB/s] - done.
>>> Restoring standby control files... - done.
>>> Starting standby database proddb on dbvlin102 mount... - done.
>>> Restoring datafiles on dbvlin102...
Restoring datafile 1 - done.
Restoring datafile 2 - done.
Restoring datafile 3 - done.
Restoring datafile 4 - done.
>>> Renaming standby redo logs and tempfiles on dbvlin102... - done.
>>> Performing checkpoint and archiving logs... - done.
>>> Finishing standby database creation... - done.
Standby database created.
To complete creating standby database please run Dbvisit on the primary server first,
then on the standby server, to ensure the standby database is in sync with the primary
database.
Please press <Enter> to continue...
Step 15:
The standby database has been created, the next steps are highlighted, which is to run dbvisit standby on the primary server, followed by running it on the standby server. This is done by running the "dbvisit <DDC>" command first on primary then the standby.
=========================================================
Standby Database creation on dbvlin102 completed.
Next steps:
1) Exit out of dbvisit_setup.
2) Run Dbvisit on this server with command:
dbvisit database_name
3) Run Dbvisit on standby server with command:
dbvisit database_name
Once Dbvisit has been tested, schedule Dbvisit on primary and standby servers
using cron or other scheduling tool.
=========================================================
Please press <Enter> to continue...
Step 16:
The Dbvisit Standby Setup menu is displayed. Press "E" to exit the menu.
=========================================================
Dbvisit Standby Database Technology (7.0.04.11726)
http://www.dbvisit.com
Dbvisit Database setup
Default values will be shown in []
Options:
1) New Dbvisit Database setup (combines options 2,3,4)
1a) New Dbvisit RAC Instance setup (combines options 2,3,4)
2) New Dbvisit Database configuration (DDC) file setup
3) New Dbvisit Archive Management Module (AMM) setup
4) New Dbvisit Database repository (DDR) setup
5) Manage Dbvisit Database repository (DDR)
6) Update Dbvisit Database configuration (DDC) file
7) Create Standby Database (and template)
8) Synchronize Standby Database
9) Uninstall Dbvisit Database repository (DDR)
E) Exit
=========================================================
Please enter choice :
Step 17:
Run Dbvisit first on the primary server as normal. On first run you will get an error message "No License Key Found", similar as to the one below indicating that you need to apply your license key:
=============================================================
Dbvisit Standby Database Technology (7.0.04.11735) (pid 11239)
dbvisit started on dbvlin101: Mon Jan 27 22:44:06 2014 ()
=============================================================
201401272244 - No license key found for Dbvisit Standby version: . Please contact Dbvisit Standby (www.dbvisit.com) to obtain a current license. Run the Dbvisit Standby license command (dbvisit -lic) to update the license.
Dbvisit Standby terminated.
Return code = 2006
(Tracefile required if contacting Dbvisit Standby support: /usr/dbvisit/standby/trace/11239_dbvisit_proddb_201401272244.trc (server:dbvlin101))
No Mail sent as SEND_MAIL_FLAG = N
Apply your license key (or the demo licence key provided on software download):
oracle@dbvlin101[/usr/dbvisit/standby]: ./dbvisit -lic proddb EB57I-DYK7K-9PI2V-1SH0D-AAWGR-2WEDY-ZJJ0F ============================================================= Dbvisit Standby Database Technology (7.0.04.11735) (pid 11331) dbvisit started on dbvlin101: Mon Jan 27 22:46:59 2014 () ============================================================= Dbvisit Standby license information for: proddb customer_seq=1 customer_key_seq=0 license_type=DEMO (0) expiry_date=2014-02-10 =>Update with license key: EB57I-DYK7K-9PI2V-1SH0D-AAWGR-2WEDY-ZJJ0F? <Yes/No> [Yes]: Yes License updated. Status: Valid ============================================================= dbvisit ended on dbvlin101: Mon Jan 27 22:47:03 2014 =============================================================
The key needs to be applied only on Primary and above is just an example/demo key.
Once the license key is applied, run Dbvisit Standby as normal on Primary:
oracle@dbvlin101[/usr/dbvisit/standby]: ./dbvisit proddb
=============================================================
Dbvisit Standby Database Technology (7.0.04.11735) (pid 11392)
dbvisit started on dbvlin101: Mon Jan 27 22:47:56 2014 ()
=============================================================
>>> Obtaining information from standby database (RUN_INSPECT=Y)...
>>> Sending heartbeat message... - done.
>>> Checking Dbvisit Standby for configurational differences between dbvlin101 and
dbvlin102...
No configurational differences found between dbvlin101 and dbvlin102.
>>> Log file(s) for proddb will be transferred from dbvlin101 to dbvlin102...
> Transferring 'o1_mf_1_35_9gdb7647_.arc.gz' to server dbvlin102:7890
Progress: 0%...20%...40%...60%...80%...100% [7551 KB/s] - done.
1 archive log transfer to dbvlin102 for proddb completed.
Last sequence was 35.
No Mail sent as SEND_MAIL_FLAG = N
>>> Dbvisit Archive Management Module (AMM)
Config: number of archives to keep = 0
Config: number of days to keep archives = 7
Config: archive backup count = 0
Config: diskspace full threshold = 80%
Current disk percent full (/u01/app/oracle/fast_recovery_area) = 67%
Number of archive logs deleted = 0
=============================================================
dbvisit ended on dbvlin101: Mon Jan 27 22:48:32 2014
=============================================================
Step 18:
The second last step is to run Dbvisit Standby on the Standby server :
oracle@dbvlin102[/usr/dbvisit/standby]: ./dbvisit proddb
=============================================================
Dbvisit Standby Database Technology (7.0.04.11735) (pid 31798)
dbvisit started on dbvlin102: Mon Jan 27 22:50:11 2014 ()
=============================================================
>>> Sending heartbeat message... - done.
>>> Log file(s) for proddb from dbvlin101 will be applied to dbvlin102
201401272250 - Log seq 35 thread 1 applied to standby database proddb.
No Mail sent as SEND_MAIL_FLAG_DR = N
>>> Dbvisit Archive Management Module (AMM)
Config: number of archives to keep = 0
Config: number of days to keep archives = 7
Config: diskspace full threshold = 80%
Processing /u01/app/oracle/archive/proddb...
Archive log dir: /u01/app/oracle/archive/proddb
Total number of archive files : 7
Number of archive logs deleted = 1
Current Disk percent full : 63%
=============================================================
dbvisit ended on dbvlin102: Mon Jan 27 22:50:18 2014
=============================================================
Step 19:
Schedule Dbvisit Standby to run on a regular basis on the primary and standby server.
The options available for scheduling are:
- Using Unix CRON schedule
- Using Windows Schedular
- Using Dbvserver (Web Based Interface) Built in Schedular