Wednesday, June 2, 2010

create logical standby


TO STOP ARCHIVAL GAP

# Start the standby databases and listeners before starting the primary database.
# Shut down the primary database before shutting down the standby database

* Creation of Logical Standby database (oracle 10gr2)

PRIMARY --> STANDBY

***** Preparing the Primary Database for Standby Database Creation *****

1. Enable Forced Logging

SQL> ALTER DATABASE FORCE LOGGING;

2. Configure a Standby Redo Log

- Ensure log file sizes are identical on the primary and standby databases.
- Determine the appropriate number of standby redo log file groups.
- Verify related database parameters and settings.
- Create standby redo log file groups.
SQL> select MEMBER from v$logfile;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/oravl04/oradata/PRIMARY/redo_gE_m01.dbf',
'/oravl04/oradata/PRIMARY/redo_gE_m02.dbf') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/oravl04/oradata/PRIMARY/redo_gF_m01.dbf',
'/oravl04/oradata/PRIMARY/redo_gF_m02.dbf') SIZE 50M;

- Verify the standby redo log file groups were created.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3. Set Primary Database Initialization Parameters

#############################################################################################################
# Instance configured as Primary database (Standby STANDBY )
#############################################################################################################


##########################################
# Standby Parameters #
##########################################

DB_UNIQUE_NAME = PRIMARY
LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(PRIMARY,STANDBY)'
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oravl01/oracle/adm/PRIMARY/arc/local_arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_2 = 'SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
LOG_ARCHIVE_FORMAT = archPRIMARY_%s_%t_%r.dbf
LOG_ARCHIVE_MAX_PROCESSES = 30

Note : Make sure that database is not in Shared server mode.

4. Enable Archiving / create password file / create control file for standby DB (Primary)

SQL> SHUTDOWN IMMEDIATE;
SQL> EXIT

* Create a Password File
orapwd file=$ORACLE_HOME/dbs/orapwPRIMARY PASSWORD=system ENTRIES=10 force=y


SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;

* Create a Control File for Standby Database
QL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oravl01/oracle/STANDBY.ctl';

SQL> ALTER DATABASE OPEN;


* Check datafiles/logfiles status

SQL> select MEMBER from v$logfile;
SQL> select NAME from v$datafile;
SQL> select NAME from v$tempfile;


***** Step-by-Step Instructions for Creating a Physical Standby Database *****

5. Run Cre_OFA.sh to create OFA for new DB instance.

6. Create a Backup Copy of the Primary Database Datafiles ( Primary )

SQL> alter database begin backup;

<-- Copy All datafiles/redo log files/arch files/cnt file to new DB location -->

SQL> alter database end backup;
-- create new Archive locations

mkdir /oravl99/ORACLE/PRIMARY/arc/local_arc
mkdir /oravl99/ORACLE/PRIMARY/arc/standby_arc


7. Prepare an Initialization Parameter File for the Standby Database

##############################################################################
# Logical standby database instance for PRIMARY (Primary DB instance)
##############################################################################

##########################################
# Standby Parameters #
##########################################

DB_UNIQUE_NAME =STANDBY
LOG_ARCHIVE_CONFIG ='DG_CONFIG=(PRIMARY,STANDBY)'
DB_FILE_NAME_CONVERT ='PRIMARY','STANDBY'
LOG_FILE_NAME_CONVERT ='/oravl04/oradata/PRIMARY/','/oravl04/oradata/STANDBY/'
LOG_ARCHIVE_FORMAT =archPRIMARY_%s_%t_%r.dbf
LOG_ARCHIVE_DEST_1 ='LOCATION=/oravl01/oracle/adm/STANDBY/arc/local_arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_2 ='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_3 ='LOCATION=/oravl01/oracle/adm/STANDBY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1 =ENABLE
LOG_ARCHIVE_DEST_STATE_2 =ENABLE
LOG_ARCHIVE_DEST_STATE_3 =ENABLE

REMOTE_LOGIN_PASSWORDFILE =EXCLUSIVE
STANDBY_FILE_MANAGEMENT = AUTO
FAL_SERVER = PRIMARY
FAL_CLIENT = STANDBY

8. Set Up the Environment to Support the Standby Database
- Create a password file.
orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY PASSWORD=system ENTRIES=10 force=y
- Configure listeners for the primary and standby databases.
% lsnrctl stop
% lsnrctl start


9. Start the Physical Standby Database
set db_name=PRIMARY in init file

SQL> STARTUP MOUNT;

- Rename file location in new Control file (If any)

* Check datafiles/logfiles status

SQL> select MEMBER from v$logfile;
SQL> select NAME from v$datafile;
SQL> select NAME from v$tempfile;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/system_1.dbf' to '/oravl02/oradata/STANDBY/system_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/undotbs_1.dbf' to '/oravl02/oradata/STANDBY/undotbs_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/sysaux_1.dbf' to '/oravl02/oradata/STANDBY/sysaux_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/users_1.dbf' to '/oravl02/oradata/STANDBY/users_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/pool_data_1.dbf' to '/oravl02/oradata/STANDBY/pool_data_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/pool_ix_1.dbf' to '/oravl02/oradata/STANDBY/pool_ix_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/tools_1.dbf' to '/oravl02/oradata/STANDBY/tools_1.dbf';
SQL> ALTER DATABASE RENAME FILE '/oravl04/oradata/STANDBY/temp_1.dbf' to '/oravl02/oradata/STANDBY/temp_1.dbf';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

- Start Redo Apply, On the standby database, issue the following command to start Redo Apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Test archival operations to the physical standby database - run on primary database

SQL> ALTER SYSTEM SWITCH LOGFILE;

12. Verify the Physical Standby Database Is Performing Properly

- Identify the existing archived redo log files. - On the standby database
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


- Force a log switch to archive the current online redo log file - On the primary database
SQL> ALTER SYSTEM SWITCH LOGFILE;


* Set ENV time format
SQL> ALTER SESSION set nls_date_FORMAT = 'DD-MON-YY HH:MI:SS';


- Verify the new redo data was archived on the standby database. - On the standby database
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


- Verify new archived redo log files were applied. - On the standby database
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


*************************************************************************************************************************************************
PHYSICAL STANDBY DATABASE IS READY
*************************************************************************************************************************************************

* Creating a Logical Standby Database


13. Stop Redo Apply on the Physical Standby Database

- To stop Redo Apply, issue the following statement on the physical standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


14. Prepare the Primary Database to Support a Logical Standby Database


- Prepare the Primary Database for Role Transitions
update init file (include a LOG_ARCHIVE_DEST_3 destination on the primary database)

- Primary
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/oravl01/oracle/adm/PRIMARY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRIMARY';

- Standby

alter system set LOG_ARCHIVE_DEST_3='LOCATION=/oravl01/oracle/adm/STANDBY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=STANDBY';

- Primary Init

LOG_ARCHIVE_DEST_3='LOCATION=/oravl01/oracle/adm/PRIMARY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_STATE_3 = ENABLE

15. Build a Dictionary in the Redo Data - on Primay database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;


16. Transition to a Logical Standby Database

- Convert to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY STANDBY;

* Note : Got following error which is not the error, you may refer alert log. DB must be renamed using NID utility.

*************************
ERROR at line 1:
ORA-16254: change db_name to STANDBY in the client-side parameter file (pfile)

ORA-16254: change db_name to string in the client-side parameter file (pfile)
Cause: An ALTER DATABASE RECOVER TO LOGICAL STANDBY new-dbname command was successfully executed without a server parameter file (spfile).
Action: The client-side parameter file must be edited so that db_name is set to the given name before mounting the database again
*************************
* Note : if command goes in indefinite mode then then run following command from another session,
(Optional)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
18. Adjust Initialization Parameters for the Logical Standby Database

SQL> SHUTDOWN;

- Create a New Password File

orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY_NEW PASSWORD=sys4get ENTRIES=10 force=y

DB_NAME = STANDBY
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oravl01/oracle/adm/STANDBY/arc VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_2 = 'SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_3 = 'LOCATION=/oravl01/oracle/adm/STANDBY/arc/standby_arc VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
LOG_ARCHIVE_DEST_STATE_3 = ENABLE

SQL> STARTUP MOUNT;

19. Open the Logical Standby Database


SQL> ALTER DATABASE OPEN RESETLOGS;


20. Issue the following statement to begin applying redo data to the logical standby database

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Verify Logical database
SQL> desc dba_logstdby_progress
SQL> select APPLIED_SCN,READ_SCN,NEWEST_SCN from dba_logstdby_progress;

* Optional steps -->


21. Verify the Logical Standby Database Is Performing Properly

- Monitoring Log File Archival Information

a. Determine the status of redo log files.- on primary database

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

b. Determine the most recent archived redo log file. - on primay database

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

c. Determine the most recent archived redo log file at each destination.- on primary database

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

d. Find out if archived redo log files have been received. - on primary database
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);


22. Applying Redo Data to Logical Standby Databases


- Starting SQL Apply
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;


To start redo apply immediate
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


- Stopping SQL Apply on a Logical Standby Database

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

To stop immediate redo apply
SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
*************************************************************************************************************************************************
LOGICAL STANDBY DATABASE IS READY
*************************************************************************************************************************************************


select APPLIED_SCN,READ_SCN,NEWEST_SCN from dba_logstdby_progress;

select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,DICT_BEGIN,DICT_END from DBA_LOGSTDBY_LOG;

select * from DBA_LOGSTDBY_PROGRESS;

Alter database register logical logfile '/oravl99/ORACLE/STANDBY/arc/standby_arc/archSTANDBY_49_1_654631187.dbf';



-------------------------------


column Archive_dest format a50
column Error format a10

set linesize 100
set pagesize 10000



desc dba_logstdby_log;

*std by

select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from dba_logstdby_log;

* primary

select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;

=================

FORCE LOGGING Option

The database or tablespaces in the database should be put into FORCE LOGGING mode before creating the backup for the standby database. Either a database or all of its tablespaces should be put into this mode but not both.

The following statement will put a tablespace in FORCE LOGGING mode:

ALTER TABLESPACE FORCE LOGGING;

The FORCE LOGGING mode can be cancelled at the database level using the following statement:

ALTER DATABASE NO FORCE LOGGING;

The FORCE LOGGING mode can be cancelled at the tablespace level using the following statement:

ALTER TABLESPACE NO FORCE LOGGING;

Temporary tablespaces and temporary segments have no effect during FORCE LOGGING mode because these objects do not generate any redo. Undo tablespaces are in FORCE LOGGING mode by default, so they cannot be put into FORCE LOGGING mode. Oracle will generate an error if an attempt is made to put a temporary tablespace or undo tablespace into FORCE LOGGING mode.
==========================================
trouble shooting
==========================================

Check the alert logs on both sides.

1. run on primary to detect failures :-

select destination, status, fail_date, valid_now
from v$archive_dest
where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2','LOG_ARCHIVE_DEST_3','LOG_ARCHIVE_DEST_4')
and (status 'VALID' or VALID_NOW 'YES');

2. run on standby to get exact position of rollforward :-

select thread#, to_char(snapshot_time,'dd-mon-yyyy:hh24:mi'),
to_char(applied_time,'dd-mon-yyyy:hh24:mi'),
to_char(newest_time,'dd-mon-yyyy:hh24:mi') from V$STANDBY_APPLY_SNAPSHOT;

====================================================================================
DATA GUARD
====================================================================================

SYS@dg AS SYSDBA> ALTER SYSTEM SET DG_BROKER_START=TRUE;

System altered.

ON PRIMARY

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> CREATE CONFIGURATION 'FAILOVER' AS PRIMARY DATABASE IS 'BG' CONNECT IDENTIFIER IS 'BG';
Configuration "NAME" created with primary database "DG"
DGMGRL>

DGMGRL> ADD DATABASE 'BG' AS CONNECT IDENTIFIER IS 'BG' MAINTAINED AS PHYSICAL;
Database "BG" added

====================================================================================
SWITCH OVER
====================================================================================
http://www.orafaq.com/node/2078
http://www.fadalti.com/oracle/database/How%20to%20create%20a%20%20logical_standby_database.htm
primary

1)
KILL ALL SESSION;
ALTER SYSTEM SWITCH LOGFILE;
SYS@dg AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2)ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

IMMIDEATLY ISSUE ON PRIMARY

3)
SYS@dg AS SYSDBA> shutdown immediate;
SYS@dg AS SYSDBA> startup mount;

4) ON STANDBY AFTER STEP TO 2 COMPLETE

SYS@bg AS SYSDBA> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@bg AS SYSDBA> startup

5)SWITCH LOGFILE ON FORMER STANDBY MEANS CURRENT PRIMARY

ALTERY SYSTEM SWITCH LOGFILE;

6) VARIFY BY

ARCHIVE LOG LIST ON BOTH DATABASES

7) ON FORMER PRIMARY MEANS CURRENT STANDBY

SYS@dg AS SYSDBA> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

----------------------------------references-----------------------------------------------

http://www.orafaq.com/node/2078
http://www.fadalti.com/oracle/database/How%20to%20create%20a%20%20logical_standby_database.htm