Creating a physical standby database on same host

Creating a physical standby database on same host

Oracle provides two types of standby databases:


1. Physical Standby Database


Archived redo log transferred from primary database will be directly applied to the standby database.

2. Logical Standby Database

we create SQL statements then these statements will be applied to stand by database.
The key advantage for logical standby databases is that they're opened read/write, even while they're in applied mode

It is important to identify unsupported database objects on the primary database before you create a logical standby database.
This is because changes made to unsupported datatypes, table, sequences,
or views on the primary database will not be propagated to
the logical standby database. Moreover, no error message will be returned.

SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;

SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='XXX' AND TABLE_NAME = 'XXXXX';



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

We are creating physical standby database on same host

orcl instance is up and running on archive log mode .

init ora file for orcl instance

=================================
[apt-amd-02:oracle:orcl]$ cat init_orcl.txt
orcl.__db_cache_size=297795584
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=130023424
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/app/oracle/oradata/orcl/orcl/control01.ctl','/opt/app/oracle/oradata/orcl/orcl/control02.ctl','/opt/app/oracle/oradata/orcl/orcl/control03.ctl'
*.core_dump_dest='/opt/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=440401920
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/orcl/udump'
*.log_archive_dest_1='LOCATION=/opt/app/oracle/archive/ MANDATORY'
*.log_archive_dest_2='service=orcl lgwr'
*.FAL_SERVER='standby'
*.FAL_CLIENT='orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.remote_login_passwordfile=EXCLUSIVE



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

*.log_archive_dest_2='service=orcl lgwr'

create following services in tnsnames.ora file

orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)


*.FAL_SERVER='standby'


standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)





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

create init ora file for standby database
and create following directories as mention in init ora file
audit_file_dest
background_dump_dest
control_files
core_dump_dest
user_dump_dest
log_archive_dest_1

[apt-amd-02:oracle:stdby]$ cat init_stdby.txt
orcl.__db_cache_size=377487360
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=146800640
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/stdby/adump'
*.background_dump_dest='/opt/app/oracle/admin/stdby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/app/oracle/oradata/stdby/control_stdby.ctl'
*.core_dump_dest='/opt/app/oracle/admin/stdby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/stdby/udump'
*.log_archive_dest_1='LOCATION=/opt/app/oracle/archive MANDATORY'
*.db_file_name_convert='/opt/app/oracle/oradata/orcl/orcl' '/opt/app/oracle/oradata/stdby'
*.log_file_name_convert='/opt/app/oracle/oradata/orcl/orcl' '/opt/app/oracle/oradata/stdby'
*.fal_client='standby'
*.fal_server=''
*.instance_name='stdby'
*.DB_UNIQUE_NAME=stdby
*.remote_login_passwordfile=EXCLUSIVE

==================================
cd /opt/app/oracle/product/10.2/db/dbs

then create password file for each instance

orapwd file=orapworcl password=orcl entries=5 force=y

orapwd file=orapwstdby password=stdby entries=5 force=y


SQL> startup mount pfile='/home/oracle/init_orcl.txt' ;
ORACLE instance started.

Total System Global Area 440401920 bytes
Fixed Size 2021408 bytes
Variable Size 138414048 bytes
Database Buffers 297795584 bytes
Redo Buffers 2170880 bytes
Database mounted.

sql>alter database create standby controlfile as '/opt/app/oracle/oradata/stdby/control_stdby.ctl' ;


shutdown the main database i.e. orcl

and copy all the file to ....

/opt/app/oracle/oradata/orcl> cp *.* /opt/app/oracle/oradata/stdby/




Now time to start standby database .

ORACLE_SID=stdby
sqlplus / as sysdba

SQL> startup mount pfile='/home/oracle/init_stdby.txt' ;
ORACLE instance started.

Total System Global Area 440401920 bytes
Fixed Size 2021408 bytes
Variable Size 138414048 bytes
Database Buffers 297795584 bytes
Redo Buffers 2170880 bytes
Database mounted.



on standby database create standby logfiles it should be same as main database

ALTER DATABASE ADD STANDBY LOGFILE group 4 '/opt/app/oracle/oradata/stdby/redo01_stb.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 5 '/opt/app/oracle/oradata/stdby/redo02_stb.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 6 '/opt/app/oracle/oradata/stdby/redo03_stb.log' SIZE 50M;


SQL> !ps -ef|grep ora_
oracle 4027 1 0 10:35 ? 00:00:00 ora_pmon_stdby
oracle 4029 1 0 10:35 ? 00:00:00 ora_psp0_stdby
oracle 4031 1 0 10:35 ? 00:00:00 ora_mman_stdby
oracle 4033 1 0 10:35 ? 00:00:00 ora_dbw0_stdby
oracle 4035 1 0 10:35 ? 00:00:00 ora_lgwr_stdby
oracle 4037 1 0 10:35 ? 00:00:00 ora_ckpt_stdby
oracle 4039 1 0 10:35 ? 00:00:00 ora_smon_stdby
oracle 4041 1 0 10:35 ? 00:00:00 ora_reco_stdby
oracle 4043 1 0 10:35 ? 00:00:00 ora_cjq0_stdby
oracle 4046 1 0 10:35 ? 00:00:00 ora_mmon_stdby
oracle 4048 1 0 10:35 ? 00:00:00 ora_mmnl_stdby
oracle 4052 1 0 10:35 ? 00:00:00 ora_d000_stdby
oracle 4055 1 0 10:35 ? 00:00:00 ora_s000_stdby
oracle 4085 1 0 10:35 ? 00:00:00 ora_arc0_stdby
oracle 4087 1 0 10:35 ? 00:00:00 ora_arc1_stdby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> !ps -ef|grep ora_
oracle 4027 1 0 10:35 ? 00:00:00 ora_pmon_stdby
oracle 4029 1 0 10:35 ? 00:00:00 ora_psp0_stdby
oracle 4031 1 0 10:35 ? 00:00:00 ora_mman_stdby
oracle 4033 1 0 10:35 ? 00:00:00 ora_dbw0_stdby
oracle 4035 1 0 10:35 ? 00:00:00 ora_lgwr_stdby
oracle 4037 1 0 10:35 ? 00:00:00 ora_ckpt_stdby
oracle 4039 1 0 10:35 ? 00:00:00 ora_smon_stdby
oracle 4041 1 0 10:35 ? 00:00:00 ora_reco_stdby
oracle 4043 1 0 10:35 ? 00:00:00 ora_cjq0_stdby
oracle 4046 1 0 10:35 ? 00:00:00 ora_mmon_stdby
oracle 4048 1 0 10:35 ? 00:00:00 ora_mmnl_stdby
oracle 4052 1 0 10:35 ? 00:00:00 ora_d000_stdby
oracle 4055 1 0 10:35 ? 00:00:00 ora_s000_stdby
oracle 4085 1 0 10:35 ? 00:00:00 ora_arc0_stdby
oracle 4087 1 0 10:35 ? 00:00:00 ora_arc1_stdby
oracle 6481 1 3 11:01 ? 00:00:00 ora_mrp0_stdby <<<<<<---- this process will start


now standby is ready

If you need to stop log apply services:

SQL> alter database recover managed standby database cancel;


===

now start the main database


SQL> startup pfile='/home/oracle/init_orcl.txt' ;
ORACLE instance started.

Total System Global Area 440401920 bytes
Fixed Size 2021408 bytes
Variable Size 138414048 bytes
Database Buffers 297795584 bytes
Redo Buffers 2170880 bytes

Database open



PROTECTION_MODE : Protection mode set for the database
PROTECTION_LEVEL : Actual, real time mode in affect for the database; this will change after outages/faults



Usage Notes

The redo data generated by a transaction must have been received by every enabled destination that has the SYNC attribute
before that transaction can commit.

The redo data generated by a transaction need not have been received at a destination that has the ASYNC attribute before that
transaction can commit. This is the default behavior if neither SYNC or ASYNC is specified.



AFFIRM and NOAFFIRM

Controls whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log:
AFFIRM—specifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log.

NOAFFIRM—specifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log.
the default is AFFIRM when the SYNC attribute is specified and NOAFFIRM when the ASYNC attribute is



PROTECTION_MODE : Protection mode set for the database
PROTECTION_LEVEL : Actual, real time mode in affect for the database; this will change after outages/faults


MAXIMUM AVAILABILITY is another ‘no-data-loss’ protection mode. It can be argued that it can be placed somewhere between the ‘no-data-loss’ and ‘minimal-data-loss’ category. The setup of maximum availability protection is the same as maximum protection. In MAXIMUM AVAILABILITY mode, the primary database does not halt if it cannot transmit redo data to at least one participating standby database. During this period the protection mode is switched to MAXIMUM PERFORMANCE, the lowest level of data protection.

When Data Guard is in MAXIMUM PERFORMANCE mode, data can potentially be lost if the primary database crashes and the redo logs have not been transferred to any archival destination. The protection mode is upgraded to MAXIMUM AVAILABILITY on next log switch if the log transfer service has resumed transmission of the redo data to at least one RAC Grid participating in maximum availability configuration and all the archive gap sequences have been resolved.


The redo data generated by a transaction must have been received by every enabled destination that has the SYNC attribute
before that transaction can commit.

* The redo data generated by a transaction need not have been received at a destination that has the ASYNC attribute before that
transaction can commit. This is the default behavior if neither SYNC or ASYNC is specified.





* FAL_CLIENT specifies the TNS network services name for the standby database (which is sent to the FAL server process by log apply services) that should be used by the FAL server process to connect to the standby database. The syntax would be:

FAL_CLIENT=

* FAL_SERVER specifies the TNS network service name that the standby database should use to connect to the FAL server
process. The syntax would be:

FAL_SERVER=

Note that the value provided for FAL_SERVER is not limited to defining only one FAL server; it can define multiple FAL servers (possible a FAL server that is running on another standby database) that are separated by commas as in the following example:

FAL_SERVER=primary_db,standby_db2

FAL server is a background process that is generally located on the primary database server (however it can also be found on
another standby database). FAL server is responsible for servicing incoming requests from the FAL client.

When the LGWR or ARC process on the primary initiates a connection with the standby, the standby listener responds by spawning a process called the remote file server (RFS). The RFS process will create a network connection with the processes on the primary and will sit waiting for data to arrive. Once data begins arriving from the primary, the RFS process will place it into either standby redo logs or archive redo logs. You should think of standby redo logs as exact twins to online redo logs, except for the fact that they are only active when a database is in the standby role. In essence, they are just a separate pool of redo logs. The RFS process will pick the first available standby redo log and begin placing changes into that log. When a log switch occurs on the primary, we switch standby redo logs and the RFS process will go to the next available standby redo log. The standby redo log that we were into prior to the log switch will be archived by the standby database and that archive will be applied by log apply services. Standby redo logs are wonderful things, and Oracle highly recommends that you use them. If you plan on setting one of the higher-level protection modes or plan on using real-time apply , the use of standby redo logs is mandatory.



Register a missing log file

alter database register physical logfile '';


If FAL doesn't work and it says the log is already registered

alter database register or replace physical logfile '';

alter database register or replace physical logfile '';



If that doesn't work, try this...

shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;


==

Disable/Enable archive log destinations

alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';



SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

PROCESS CLIENT_P SEQUENCE# STATUS
------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 204 WAIT_FOR_LOG
RFS LGWR 204 WRITING
RFS N/A 0 RECEIVING




Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:


set linesize 200
col DATABASE_ROLE format a20
col INSTANCE format a10
col OPEN_MODE format a15
col PROTECTION_MODE format a20
col PROTECTION_LEVEL format a20
col SWITCHOVER_STATUS format a20

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;


DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- ---------- --------------- -------------------- -------------------- --------------------
PRIMARY cccdb READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE



Determining Which Log Files Were Not Received by the Standby Site

issue the following query on the 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#);

THREAD# SEQUENCE#
---------- ----------
1 12
1 13
1 14





See how up to date a physical standby is Run this on the primary

set numwidth 15
select max(sequence#) current_seq from v$log
/

Then run this on the standby

set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status
/



Display info about all log destinations To be run on the primary

set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4

select ds.dest_id id
, ad.status
, ds.database_mode db_mode
, ad.archiver type
, ds.recovery_mode
, ds.protection_mode
, ds.standby_logfile_count "SRLs"
, ds.standby_logfile_active active
, ds.archived_seq#
from v$archive_dest_status ds
, v$archive_dest ad
where ds.dest_id = ad.dest_id
and ad.status != 'INACTIVE'
order by ds.dest_id
/


List any standby redo logs

set lines 100 pages 999
col member format a70
select st.group#
, st.sequence#
, ceil(st.bytes / 1048576) mb
, lf.member
from v$standby_log st
, v$logfile lf
where st.group# = lf.group#
/





Check which logs are missing Run this on the standby...

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#)
/



SQL> select name, open_mode, protection_mode, database_role, dataguard_broker, force_logging from v$database;