Oracle Standby Database: Creation from scratch (NO RMAN)

 

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
Supplemental reporting
Testing

If the primary database is destroyed or its data becomes corrupted, you can perform a failover to the standby
database, in which case the standby database becomes the new primary database. You can also open a standby
database with the read-only option, thereby allowing it to function as an independent reporting database.

We are going to see here a standby database creation without rman.
step by step:

1) Query data files,control file, archivelog and spfileIdentificar fichero de datos, control files, archivelog y spfile:

SELECT FILE_NAME FROM DBA_DATA_FILES
UNION ALL
SELECT FILE_NAME FROM DBA_TEMP_FILES;

select name from v$controlfile;

Take a look state of archive logs.
ARCHIVE LOG LIST;

show parameter pfile

2) Archive last redo online and stop database.

ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/standby.ctl’;
create pfile=’/tmp/initstandby’ from spfile;
3) Cold backup. Copy datafiles,control files,archivelog and spfile

select ‘SHUTDOWN IMMEDIATE;’ from dual
union all
select ‘host cp ‘||name||’ &&directorio_destino’ from v$controlfile
union all
select ‘host cp ‘||member||’ &directorio_destino’ from v$logfile
union all
select ‘host cp ‘||name||’ &directorio_destino’ from v$datafile
union all
select ‘startup’ from dual;

we issue a shutdown immediate but put tablespaces offile or begin backup allows
the same way to run a cold backup with out problems.
4) In destination server we put the datafiles,controlfiles,archivelog and pfile

– Only we add two parameters in new pfile (initstandby)
control_files= /oracle/orasys/xgolf/stanbyctl.ctl
STANDBY_ARCHIVE_DEST=’/home4/redologs’

SQL> startup nomount from pfile=’/tmp/initstandby.ora’

5) Starting(mount) Standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

6) In mount state we put standby database in managed recovery,
in this state each archive database recive will be applied

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

In Primary database we can set a new log archive destination for update the standby:

show parameter log_archive_dest;
alter system set log_archive_dest_[N] = ‘SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD’;
alter system set log_archive_dest_state_[N] = ‘ENABLE’;

7) Checking standby is working fine:

SQL> select sequence#, applied, to_char(first_time,’dd/mm/yyyy hh24:mi:ss’),
to_char(next_time,’dd/mm/yyyy hh24:mi:ss’), archived, status from v$archived_log order by sequence#;

SQL> select group#, thread#, sequence#, archived, status from v$standby_log;

SQL> select * from v$managed_standby;

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s