A physical standby database is created from an existing other database. This other database will then be the primary database.
In this text, it is assumed that the primary database uses a spfile.
Getting the primary database ready
The primary database must meet two conditions before a standby database can be created from it:
Use v$database.force_logging to determine if a database is in force logging mode. If not, enable it like so:
alter database force logging;
Use v$database.log_mode to determine if a database is in archive log mode. If not, enable it.
The (local) archive destination should be specified like so:
alter system set log_archive_dest_1='LOCATION=c:\oracle\oradb\arch MANDATORY' scope=both;
Creating the standby database
Copying the datafiles
The standby database is created from the existing datafiles of the primary database. These can be queried from the v$datafile view:
select name from v$datafile;
These files must be copied to the standby database. However, the primary database must be shut down before they can be copied.
After copying the datafiles, the primary database can be started up again.
Creating a standby database control file
A control file needs to be created for the standby system. Execute the following on the primary system:
alter database create standby controlfile as '/some/path/to/a/file'
The created file must meet two conditions:
Creating an init file
A pfile is created from the spfile. This pfile needs to be modified and then be used on the standby system to create an spfile from it.
create pfile='/some/path/to/a/file' from spfile
The following parameters must be modified or added:
Creating an oracle service (if on windows)
If the environment is running on windows, create a new oracle service on the standby system with oradim:
oradim -new -sid stdby -startmode manual
Configuring the listener
In order to enable dead connection time, specify
Creating net service names
net service names must be created on both the primary and standby database that will be used by log transport services. That is: something like to following lines must be added in the tnsnames.ora.
TO_STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stdby_host)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = stdby) ) )
Creating the spfile on the standby database
On the still idle standby database, the pfile is turned into an spfile. Then the instance is started up.
set ORACLE_SID=stdby sqlplus "/ as sysdba" create spfile from pfile='/.../../modified-pfile';
Then, the standby database needs to be started as a physical standby database, however, without recovering:
startup nomount alter database mount standby database;
Creating standby redo logs
On the standby database, standby redo logs can be created (if lgwr transmission is to be used).
alter database add standby logfile '/some/path/file_1.log' size 1M, '/some/path/file_2.log' size 1M, '/some/path/file_3.log' size 1M;
Archiving to the standby from the primary
alter system set log_archive_dest_2='service=to_standby lgwr' scope=both; alter system set log_archive_dest_state_2=enable scope=both;
Putting standby in recovery mode
alter database recover managed standby database disconnect from session;
After everything has been done, verify the physical standby database.