How to create standby database in oracle 11g

How do I create a standby database in Oracle 11g Standard Edition?

 export ORACLE_SID=TEST  sqlplus / as sysdba  alter database create standby controlfile as ‘/ora00/temp/rman/control01. ctl’; (above command shall create a control01. ctl in “/ora00/temp/rman” folder.  alter system archive log current;  exit This shall generate one archived log in the archived log directory.

How do I create a standby control file?

To create the standby database control file:

Connect to the primary database and create the control file for your standby database. For example, to create the standby control file as / oracle/dbs/stbycf. ctl on the primary site, enter the following: SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/oracle/dbs/stbycf.

What is standby database?

A standby database is a database replica created from a backup of a primary database. By applying archived redo logs from the primary database to the standby database, you can keep the two databases synchronized. A standby database has the following main purposes: Disaster protection. Protection against data corruption.

How do I start a physical standby database?

8.1. 1 Starting Up a Physical Standby Database
  1. Start the database: SQL> STARTUP NOMOUNT;
  2. Mount the standby database: SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

How do I connect to a standby database?

We need to connect with the connect string: “justinb/PWD@DBNAME as sysdba”. The latter connect string allows you to authenticate using the Oracle password file, which you have to use with a standby because the database is mounted, not opened.

How do I know if my standby database is primary?

How To Find All Standby Databases for a Primary Database in Data Guard. You can find all standby databases for a primary database in data guard by using one of the queries below. Standby -> Returns the unique name of the current database. Primary -> Returns the unique name of the other database in the Environment.

How do I restore a standby database?

Recover the database using the AUTOMATIC option: SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE; The AUTOMATIC option automatically generates the name of the next archived redo log file needed to continue the recovery operation.

How do I start recovery in standby database?

8.1. 1 Starting Up a Physical Standby Database
  1. Start and mount the physical standby database: SQL> STARTUP MOUNT;
  2. Start Redo Apply or real-time apply: To start Redo Apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 2> DISCONNECT FROM SESSION;

How do I check if standby is enabled?

On the primary database, check the last sequence that is generated. SQL> select max(sequence#) from V$archived_log; 4. On the standby database, check the last sequence that is applied.

Using DGbrocker.

  1. Set proper ORACLE_SID, ORACLE_HOME.
  2. dgmgrl.
  3. connect /
  4. show configuration;
  5. show database ‘DB name’;

How do I stop MRP standby?

To stop the managed recovery, the Cancel command can be issued on the standby database. The complete statement is: ALTER DATABASE RECOVER MANAGED Oracle instance CANCEL; This statement will stop the MRP.

How do I manually add a datafile in Oracle standby database?

Recreate the datafile as below [ STANDBY ]

SQL> alter database create datafile ‘/oracle/app/oracle/product/12.1. 0.2/dbhome_1/dbs/UNNAMED00005′ as new; database altered. If you dont have OMF files, then get the exact datafile name from primary and recreate.

How do I resize a datafile in standby database in Oracle?

Now add a sum of more than the amount available on the standby disk by RESIZING the file on PRIMARY to the tablespace. SQL> Alter database datafile ‘/opt/oracle/oradata/dgtest9i/dropme. dbf’ RESIZE 1024M; Database altered.

How do I use archive logs manually in standby database?

Register Archive log file manually in standby Database
  1. Let us assume, the archive log file devdb12_1_283_801988635. arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.
  2. Run the below command in standby database.
  3. Verify the standby database and see if the copied archive log file is applied.

How do I check my archive log gap in standby database?

Resolving Archive Gap Sequence on Physical Standby Databases

If the recovery process on the physical standby site is stopped, query the v$archive_gap view to find the gap sequence. The query find_gap. sql from code depot will assist in finding out if there is any archive log gap in the database.

How do I manually use redo logs?

To apply the archived redo logs in the archive gap

Start up and mount the standby database (if it is not already mounted). For example, enter: SQL> STARTUP NOMOUNT PFILE=/oracle/admin/pfile/initSTBY.

How do I use archive log?

Copy the archive logs from primary to standby (Dbvisit ARCHDEST location), rename the files and then run dbvisit to apply logs. Copy the archive logs from primary to standby (Dbvisit ARCHDEST location), use RMAN to catalog the files and then apply them using RMAN.

How do I use archive logs in RMAN?

Restore RMAN to new server and refresh with Archivelog apply
  1. create pfile=’E:\eccprod. ora’ from spfile;
  2. rman target / backup incremental level 0 TAG ‘ECC_DAILY_FULL’ database filesperset 4; backup archivelog all not backed up 1 times; exit;
  3. Copy RMAN backup files and pfile to Target Database directory in same structure.

Where is RMAN backup in Oracle?

Use the LIST command to view information about backups stored in the Oracle Recovery Manager (RMAN) repository. The information includes backups of data files, individual tablespaces, archived redo log files, and control files. You can also use this command to display information about expired and obsolete backups.

How do I backup my Oracle archive logs?

To backup all archived redo log files in the database, use this command:
  1. RMAN> backup archivelog all; Additionally, backup of archived redo log files may be done after every backup command.
  2. SQL> select.
  3. RMAN> backup tablespace users plus archivelog; Starting backup at 17-AUG-09.