Primary on ASM and Standby using Filesystem

Owned by Ankit Arora (Deactivated)
Last updated: Jul 09, 2014
16 min read

In some cases it might be required to create a standby database in File system, when the primary database is making use of ASM storage.

This section will show you an example on how to create a Non ASM standby database where Primary Database is using ASM.

In this example the setup is as follow:

  • Dbvisit Standby version used: 7.0.14
  • Operating System - Oracle Linux 5.8
  • Oracle Database version 11.2.0.4
  • Oracle Grid Infrastructure 11.2.0.4
  • ASM Disk Groups: +DATA and +FRA
  • Database Name: TSTRAC
  • Primary Server: dbvrlin301
  • Standby Server: dbvrlin305
  • Dbvisit Standby ARCHDEST: /u01/app/oracle/archive/tstrac
  • Primary Server Datafile Locations: +DATA
  • Primary Server Flash Recovery Area: +FRA
  • Standby Server Datafile Locations: /u01/app/oracle/oradata/tstrac
  • Standby Server Flash Recovery Area: /u01/app/oracle/flash_recovery_area


Important

  • Creating a standby database is performed on the primary server.
  • You must have a DDC file already created before you proceed with CSD.
  • Dbvisit Standby is making use of a directory referred to as the ARCHDEST. This location is specific to Dbvisit Standby and is not - and should not, be seen as the same as the Oracle Database Archive destinations.

Note: ASM 10.2. and above is supported, but Dbvisit Standby does not support Graceful Switchover when ASM versions less than 11.1 is used. ASM 11.2 with latest patches applied is recommended.

 

The setup is created using the Dbvisit Standby CLI "dbvisit_setup".

 

On Primary Server:

oracle@dbvrlin301[/usr/dbvisit/standby]: ./dbvisit_setup

=========================================================
     Dbvisit Standby Database Technology (7.0.14.11991)
           http://www.dbvisit.com
=========================================================
=>dbvisit_setup only needs to be run on the primary server.
Is this the primary server? <Yes/No> [Yes]:
=========================================================
     Dbvisit Standby Database Technology (7.0.14.11991)
           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

------------------------------------------------------------------------------
=>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)   TSTRAC
2)   Return to menu
Please enter choice : 1
Is this correct? <Yes/No> [Yes]:
Database TSTRAC is up.

>>> Checking Dbvisit Standby for configurational differences between dbvrlin301 and
    dbvrlin305...
    No configurational differences found between dbvrlin301 and dbvrlin305.

>>> Checking Oracle installation on dbvrlin305 in
    /u01/app/oracle/product/11.2.0/db_1/bin/oracle...
  
>>> Checking primary datafiles on dbvrlin301...

>>> Validating Dbvisit configuration file on dbvrlin305...

>>> Checking if a database is up and running on dbvrlin305...
    Checks completed OK.

>>> Total database size for TSTRAC is 2.81GB
 
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]: 1
-------------------------------------------------------------------------------
Do you want to create an ASM standby database (with all or some database files
in ASM storage)? [Yes]: No
 
Standby database will be ASM: N
Is this correct? <Yes/No> [Yes]:Yes

Make Sure that you provide "Standby database will be ASM: N" , since our standby database is Non ASM.

-------------------------------------------------------------------------------
Use network compression to compress the database files during transfer? [No]:
Database files will be compressed during transfer: N
Is this correct? <Yes/No> [Yes]:Yes
 
-------------------------------------------------------------------------------
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

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.

-------------------------------------------------------------------------------
You choose to create a non ASM standby database. Please change all Oracle
database parameters values pointing to ASM locations to point to valid
filesystem locations on the standby server.
Primary database contains Oracle Managed Files (OMF). These files will be
created as OMF files on the standby server. Please make sure creating of OMF
files is enabled on the standby by setting up parameters db_create_file_dest,
db_create_online_log_dest_n and db_recovery_file_dest to point to valid
filesystem locations. Refer to Oracle documentation for information how to
enable creating of OMF files.


The following oracle database parameters will be set in the standby database pfile or spfile:
-------------------------------------------------------------------------------
audit_file_dest                         /u01/app/oracle/admin/TSTRAC/adump
compatible                              11.2.0.4.0
db_block_size                           8192
db_create_file_dest                     +DATA
db_name                                 TSTRAC
db_recovery_file_dest                   +FRA
db_recovery_file_dest_size              16106127360
diagnostic_dest                         /u01/app/oracle
dispatchers                             (PROTOCOL=TCP) (SERVICE=TSTRACXDB)
memory_max_target                       788529152
memory_target                           788529152
open_cursors                            300
processes                               150
remote_login_passwordfile               EXCLUSIVE
sga_max_size                            788529152
spfile                                  OS default
standby_file_management                 MANUAL
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]: 2
 
Enter full parameter name: []: db_create_file_dest
WARNING: Do NOT use single or double quotes to enclose parameter values!
Separate multiple values with a comma. If a comma is part of a value, escape it
with a backslash.
 
Enter new value (leave blank to set to null or default Oracle value): []: /u01/app/oracle/oradata/tstrac
Validating ... please wait


The following oracle database parameters will be set in the standby database pfile or spfile:
-------------------------------------------------------------------------------
audit_file_dest                         /u01/app/oracle/admin/TSTRAC/adump
compatible                              11.2.0.4.0
db_block_size                           8192
db_create_file_dest                     /u01/app/oracle/oradata/tstrac
db_name                                 TSTRAC
db_recovery_file_dest                   +FRA
db_recovery_file_dest_size              16106127360
diagnostic_dest                         /u01/app/oracle
dispatchers                             (PROTOCOL=TCP) (SERVICE=TSTRACXDB)
memory_max_target                       788529152
memory_target                           788529152
open_cursors                            300
processes                               150
remote_login_passwordfile               EXCLUSIVE
sga_max_size                            788529152
spfile                                  OS default
standby_file_management                 MANUAL
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]: 2

Enter full parameter name: []: db_recovery_file_dest
WARNING: Do NOT use single or double quotes to enclose parameter values!
Separate multiple values with a comma. If a comma is part of a value, escape it
with a backslash.
 

Enter new value (leave blank to set to null or default Oracle value): []: /u01/app/oracle/flash_recovery_area
Validating ... please wait


The following oracle database parameters will be set in the standby database pfile or spfile:
-------------------------------------------------------------------------------
audit_file_dest                         /u01/app/oracle/admin/TSTRAC/adump
compatible                              11.2.0.4.0
db_block_size                           8192
db_create_file_dest                     /u01/app/oracle/oradata/tstrac
db_name                                 TSTRAC
db_recovery_file_dest                   /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size              16106127360
diagnostic_dest                         /u01/app/oracle
dispatchers                             (PROTOCOL=TCP) (SERVICE=TSTRACXDB)
memory_max_target                       788529152
memory_target                           788529152
open_cursors                            300
processes                               150
remote_login_passwordfile               EXCLUSIVE
sga_max_size                            788529152
spfile                                  OS default
standby_file_management                 MANUAL
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

Verify the values provided for "db_create_file_dest" and "db_recovery_file_dest" before you proceed with creating the standby database.

 

In this example:

Standby Datafile Location is changed to "/u01/app/oracle/oradata/tstrac" from "+DATA" in Primary Database.

And

Standby Recovery Location is changed to "/u01/app/oracle/flash_recovery_area" from "+FRA" in Primary Database.

-------------------------------------------------------------------------------
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

-------------------------------------------------------------------------------
A temporary location must be specified on dbvrlin301 where the database will be
backed up to first.
This location must be big enough to hold RMAN backup of the whole database
(2.81GB).

Specify the location on this server: [/usr/tmp]: /usr/tmp

Directory for temporary location will be: /usr/tmp

Is this correct? <Yes/No> [Yes]: Yes

-------------------------------------------------------------------------------
A temporary location must be specified on dbvrlin305 where the database backup
will be copied to before moving to specified locations.

Specify location on remote server: [/usr/tmp]: /usr/tmp

Directory for temporary will be: /usr/tmp

Is this correct? <Yes/No> [Yes]: Yes

-------------------------------------------------------------------------------
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

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
-------------------------------------------------------------------------------
+DATA/tstrac/onlinelog    ===> /u01/app/oracle/oradata/tstrac/TSTRAC/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.

-------------------------------------------------------------------------------
=>Create standby database template for TSTRAC1 using provided answers? <Yes/No>
[Yes]: Yes


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.


>>> Backing up primary database...
    Backing up datafile 1... - done.
    Backing up datafile 2... - done.
    Backing up datafile 3... - done.
    Backing up datafile 4... - done.
    Backing up datafile 5... - done.
    Backing up datafile 6... - done.
    Backing up datafile 7... - done.
    Backing up datafile 8... - done.
|
>>> Creating standby control file... - done.
Backup of primary database completed.


>>> Transferring backup from dbvrlin301 to dbvrlin305...
  > Transferring 'dbv_TSTRAC_csd_dbf_1_6tp7p944_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [10721 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_1_6up7p957_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [11905 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_2_6vp7p95n_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [11170 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_3_70p7p96l_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [12028 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_4_71p7p970_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [10956 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_5_72p7p97u_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [10831 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_6_73p7p98j_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [11989 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_7_74p7p98t_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [10363 KB/s] - done.

  > Transferring 'dbv_TSTRAC_csd_dbf_8_75p7p998_1_1.rman' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [11413 KB/s] - done.

>>> Restoring standby control files... - done.

>>> Starting standby database TSTRAC on dbvrlin305 mount... - done.

>>> Restoring datafiles on dbvrlin305...
    Restoring datafile 1 - done.
    Restoring datafile 2 - done.
    Restoring datafile 3 - done.
    Restoring datafile 4 - done.
    Restoring datafile 5 - done.
    Restoring datafile 6 - done.
    Restoring datafile 7 - done.
    Restoring datafile 8 - done.
|
>>> Renaming standby redo logs and tempfiles on dbvrlin305... - 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...
=========================================================
     Standby Database creation on dbvrlin305 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...
=========================================================
     Dbvisit Standby Database Technology (7.0.14.11991)
           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 : E

 

The Standby Database has been created successfully, you can now send logs to the standby from the primary server:

 

Run Dbvisit on Primary:

oracle@dbvrlin301[/usr/dbvisit/standby]: ./dbvisit TSTRAC
=============================================================
Dbvisit Standby Database Technology (7.0.14.12112) (pid 9102)
dbvisit started on dbvrlin301: Thu May  8 14:59:34 2014 ()
=============================================================

>>> Obtaining information from standby database (RUN_INSPECT=Y)...

>>> Sending heartbeat message... - done.

>>> Checking Dbvisit Standby for configurational differences between dbvrlin301 and
    dbvrlin305...
    No configurational differences found between dbvrlin301 and dbvrlin305.

>>> Log file(s) for TSTRAC1 will be transferred from dbvrlin301 to dbvrlin305...
  > Transferring 'thread_1_seq_757.903.847029943.gz' to server dbvrlin305:7890
    Progress: 0%...20%...40%...60%...80%...100% [12636 KB/s] - done.
    1 archive log transfer to dbvrlin305 for TSTRAC1 completed.
    Last sequence was 757.
    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 (+FRA/tstrac/archivelog/2014_05_08) = 94%

    *** SITUATION IS CRITICAL! ***

    Threshold of 80% on +FRA/tstrac/archivelog/2014_05_08 is less than the currently
    available disk space of 94%.

Disk space is sparse (94% full) and it is not allowed to remove oldest archives from
-> +FRA/tstrac/archivelog/2014_05_08

This is due to DELETE_ARCHSOURCE = N being set in DDC file.
    No Mail sent as SEND_MAIL_FLAG = N
    Number of archive logs deleted = 0

=============================================================
dbvisit ended on dbvrlin301: Thu May  8 14:59:56 2014
=============================================================

 

Run Dbvisit now on the standby to apply the logs:

oracle@dbvrlin305[/usr/dbvisit/standby]: ./dbvisit TSTRAC
=============================================================
Dbvisit Standby Database Technology (7.0.14.12112) (pid 2245)
dbvisit started on dbvrlin305: Thu May  8 15:03:38 2014 ()
=============================================================

>>> Sending heartbeat message... - done.

>>> Log file(s) for TSTRAC from dbvrlin301 will be applied to dbvrlin305
201405081503 - Log seq 757 thread 1 applied to standby database TSTRAC.
    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/tstrac...
    Archive log dir: /u01/app/oracle/archive/tstrac
    Total number of archive files   : 0
    Number of archive logs deleted = 0
    Current Disk percent full       : 48%

=============================================================
dbvisit ended on dbvrlin305: Thu May  8 15:03:43 2014
=============================================================

 

 

Â