System requirements
Operating System Oracle
Linux 6
IP Address 192.168.1.120 (primary)
SID DB11G
Db_unique_name DB11G
Database Oracle 11.2.0.1
Hostname ora11g.localdomain
Operating System Oracle
Linux 6
IP Address 192.168.1.121 (standby)
SID DB11G
Db_unique_name DB11G_STBY
Database Oracle 11.2.0.1
Hostname standby.localdomain
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM
v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
If the database is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE
LOGGING;
Initialization
Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to
"DB11G" on the primary database.
SQL> show parameter
db_name
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_name string
DB11G
SQL> show parameter
db_unique_name
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_unique_name string
DB11G
SQL>
The DB_NAME of the standby
database will be the same as that of the primary, but it must have a
different DB_UNIQUE_NAME value.
The DB_UNIQUE_NAME values
of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this
example, the standby database will have the value "DB11G_STBY".
ALTER SYSTEM SET
LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';
Set suitable remote archive log destinations. In this case I'm
using the flash recovery area for the local location, but you could specify an
location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote
location reference the standby location.
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be
set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
ALTER SYSTEM SET
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET
LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET
FAL_SERVER=DB11G_STBY;
ALTER SYSTEM SET
STANDBY_FILE_MANAGEMENT=AUTO;
Remember, some of the parameters are not modifiable, so the database
will need to be restarted before they take effect.
Service
Setup
Entries for the primary and standby databases are needed in the
"$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You
can create these using the Network Configuration Utility (netca) or manually.
The following entries were used during this setup.
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G)
)
)
DB11G_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.121)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G)
)
)
Backup
Primary Database
If you are planning to use an active duplicate to create the
standby database, then this step is unnecessary. For a backup-based duplicate,
or a manual restore, take a backup of the primary database.
$ rman target=/
RMAN> BACKUP DATABASE
PLUS ARCHIVELOG;
Create
Standby Controlfile and PFILE
Create a controlfile for the standby database by issuing the
following command on the primary database.
ALTER DATABASE CREATE
STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
Create a parameter file for the standby database.
CREATE
PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
Amend the PFILE making the entries relevant for the standby
database. I'm making a replica of the original server, so in my case I only had
to amend the following parameters.
*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.fal_client='DB11G_STBY'
*.log_archive_dest_2='SERVICE=db11g
NOASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
Standby
Server Setup (Manual)
Copy
Files
Create the necessary directories on the standby server.
$ mkdir -p
/u01/app/oracle/oradata/DB11G
$ mkdir -p
/u01/app/oracle/flash_recovery_area/DB11G
$ mkdir -p
/u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to
all locations.
$ scp oracle@192.168.1.120:/tmp/db11g_stby.ctl
/u01/app/oracle/oradata/DB11G/control01.ctl
$ cp
/u01/app/oracle/oradata/DB11G/control01.ctl
/u01/app/oracle/fast_recovery_area/DB11G/control02.ctl
$ # Archivelogs and backups
$ scp -r oracle@192.168.1.120:/u01/app/oracle/flash_recovery_area/DB11G/archivelog
/u01/app/oracle/flash_recovery_area/DB11G
$ scp -r oracle@192.168.1.120:/u01/app/oracle/flash_recovery_area/DB11G/backupset
/u01/app/oracle/flash_recovery_area/DB11G
$ # Parameter file.
$ scp oracle@192.168.1.120:/tmp/initDB11G_stby.ora
/tmp/initDB11G_stby.ora
$ # Remote login password
file.
$ scp oracle@192.168.1.120:$ORACLE_HOME/dbs/orapwDB11G
$ORACLE_HOME/dbs
Notice, the backups were copied across to the standby server as
part of the FRA copy. If your backups are not held within the FRA, you must
make sure you copy them to the standby server and make them available from the
same path as used on the primary server.
Add the below lines in listener file on both servers
SID_LIST_LISTNER =
(SID_LIST =
(SID_NAME = DB11G)
(ORACLE_HOME =
/u01/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = DB11G)
)
)
Listener looks like below on primary server
SID_LIST_LISTENER =
(SID_LIST =
(SID_NAME = DB11G)
(ORACLE_HOME =
/u01/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = DB11G)
)
)
LISTERNER =
(DISCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY =
EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP) (HOST =
192.168.1.120)(PORT = 1521))
)
)
Start
Listener
Make sure the listener is started on the standby server.
$ lsnrctl start
Restore
Backup
Create the SPFILE form the amended PFILE.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba
SQL> CREATE SPFILE FROM
PFILE='/tmp/initDB11G_stby.ora';
Restore the backup files.
$ export ORACLE_SID=DB11G
$ rman target=/
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
Create
Redo Logs
Create online redo logs for the standby. It's a good idea to match
the configuration of the primary server.
ALTER SYSTEM SET
STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE
('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE
('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE
('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET
STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby
redo logs on both the standby and the primary database (in case of
switchovers). The standby redo logs should be at least as big as the largest
online redo log and there should be one extra group per thread compared the
online redo logs. In my case, the following standby redo logs must be created
on both servers.
ALTER DATABASE ADD STANDBY
LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY
LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY
LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY
LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
Once this is complete, we can start the standby process.
Check the database mode and make sure you can login to standby
database from primary.
SQL> col host_name format a16
SQL> SELECT INSTANCE_NAME, HOST_NAME, OPEN_MODE FROM v$instance,v$database;
SQL> conn sys/oracle_4U@DB11G_STBY as sysdba
SQL> SELECT INSTANCE_NAME, HOST_NAME, OPEN_MODE FROM v$instance,v$database;
SQL> SELECT INSTANCE_NAME, HOST_NAME, OPEN_MODE FROM v$instance,v$database;
create table and insert few rows on primary database
SQL> CREATE TABLE romin AS SELECT * FROM dba_objects;
SQL> INSERT INTO romin SELECT * FROM romin;
SQL> commit;
SQL> INSERT INTO romin SELECT * FROM romin;
SQL> commit;
Verify current archive log status on primary database.
SQL> ALTER SESSION SET
nls_date_format='DD-MON-YYYY HH24:MI:SS';
SQL> SELECT sequence#,
first_time, next_time
FROM
v$archived_log
ORDER BY sequence#;
Start
managed recovery process on standby database and verify log archive status
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> ALTER SESSION SET
nls_date_format='DD-MON-YYYY HH24:MI:SS';
SQL> SELECT sequence#,
first_time, next_time
FROM
v$archived_log
ORDER BY sequence#;
If
you would like to sync data in Real-Time and to see data on table, you have to apply
below commands. And run standby database as read-only mode.
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN
READ ONLY;
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
SQL> SELECT sequence#,
first_time, next_time
FROM
v$archived_log
ORDER BY sequence#;
Thank you
No comments:
Post a Comment