Showing posts with label oracle standby. Show all posts
Showing posts with label oracle standby. Show all posts

Friday, October 6, 2017

11g standby database using dataguard

For 11g databases you need to create a temporary listener with a different port on the target server or else it will show blocked status when database is started at nomount

Primary and standby :

tnsnames.ora on both servers

testDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


Edit or change parameters from db

PRIMARY
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set FAL_SERVER='testDR';
alter system set FAL_CLIENT='test';



--*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
--*.LOG_FILE_NAME_CONVERT='stand','prim'


create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp  inittest.ora oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/

scp  orapwtest oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/


Standby:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='test'
*.FAL_CLIENT='testdr'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump



Standby:

startup nomount

Primary :

rman target sys/password@test auxiliary sys/password@testDR


duplicate target database for standby from active database dorecover;

create spfile from pfile;

shut immediate;

startup;

select open_mode from v$database;

output should be read only

Now START MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;



Verify Dataguard configuration is syncing by using below queries


Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)


************************************************************************************
Steps to activate standby:


STOP MRP
alter database recover managed standby database cancel;


Change Redolog entries in control files before activating standby


on STANDBY
ALTER DATABASE ACTIVATE STANDBY DATABASE;


If you get any errors regards to redolog.. Fix the issue



select * from v$logfile;

select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;


alter database rename file '/orabase/origlogA/test/redo01a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo01b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo02a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo02b.log' to '+REDO';
alter database rename file '/orabase/origlogA/test/redo03a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo03b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo04a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo04b.log' to '+REDO';



Then try to activate standby again

ALTER DATABASE ACTIVATE STANDBY DATABASE;



Use this below to stop archivelog transfer from primary to standby
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

By at No comments:
Labels: , , , , ,

10g standby database using dataguard

Primary and standby :

tnsnames.ora on both servers

testDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


Edit or change parameters from db

PRIMARY
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set FAL_SERVER='testDR';
alter system set FAL_CLIENT='test';



--*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
--*.LOG_FILE_NAME_CONVERT='stand','prim'


create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp  inittest.ora oracle@10.0.1.2:/opt/app/oracle/product/10.2.0/dbs/

scp  orapwtest oracle@10.0.1.2:/opt/app/oracle/product/10.2.0/dbs/




Standby:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='test'
*.FAL_CLIENT='testdr'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'


Create directories

mkdir -p /opt/app/oracle/admin/test/adump
mkdir -p /opt/app/oracle/admin/test/bdump
mkdir -p /opt/app/oracle/admin/test/cdump
mkdir -p /opt/app/oracle/admin/test/udump




Primary:

alter database create standby controlfile as'/export/home/oracle/test_stby.ctl' reuse;

scp  /export/home/oracle/test_stby.ctl oracle@10.0.1.2:/export/home/oracle/


Run incremental/ full backup

Once completed. Make sure the backup location is accessible by standby database.


Standby:

startup nomount


rman target=sys/password@test auxiliary=/

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER; 

create spfile from pfile;

shut immediate;

startup;

select open_mode from v$database;

output should be read only

Now START MRP

alter database recover managed standby database disconnect;


Verify Dataguard configuration is syncing by using below queries


Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)



************************************************************************************
Steps to activate standby:


STOP MRP
alter database recover managed standby database cancel;


Change Redolog entries in control files before activating standby


on STANDBY
ALTER DATABASE ACTIVATE STANDBY DATABASE;


If you get any errors regards to redolog.. Fix the issue


select * from v$logfile;

select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;


alter database rename file '/orabase/origlogA/test/redo01a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo01b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo02a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo02b.log' to '+REDO';
alter database rename file '/orabase/origlogA/test/redo03a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo03b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo04a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo04b.log' to '+REDO';


Then try to activate standby again

ALTER DATABASE ACTIVATE STANDBY DATABASE;



Use this below to stop archivelog transfer from primary to standby
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;


By at No comments:
Labels: , , , , ,
Subscribe to: Posts (Atom)

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...

  • (no title)
    NTP change on  Exadata db nodes # grep ^server /etc/ntp.conf server 10.120.0.10 prefer iburst burst minpoll 4 maxpoll 4 server 10.120....
  • Cleaning Sysaux tablespace
    Cleaning SYSAUX tablespace 1. Running @?/rdbms/admin/awrinfo.sql –> Detailed Info like Schema breakdown, SYSAUX occupants space usage ...
  • ORA-24005 Inappropriate utilities used to perform DDL on AQ table
    You might get this error while dropping a schema or tablespace or objects it self. You need to drop using sys user with following syntax ...