Wednesday 18 September 2013

Step by step to configure Data Guard in Oracle 11g in OEL environment

---The following scripts is tested in my lab....

--primary---

archive log list;

select name from v$database;

show parameters unique

ALTER DATABASE FORCE LOGGING;

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

select group#,type,member from v$logfile;

select bytes from v$log;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/ORCL/stby04.log' size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/ORCL/stby05.log' size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/ORCL/stby06.log' size 50m;

SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;

CREATE PFILE FROM SPFILE;
---Do necessary changes in initORCL.ora file---
shutdown immediate;

startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory
SQL>

mkdir -p /u01/app/oracle/fast_recovery_area/ORCL/archivelog/

startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

archive log list;
alter system switch logfile;

mkdir -p /u01/rmanback
rman target=/
backup full database format '/u01/rmanback/%d_%U.bckp' plus archivelog format '/u01/rmanback/%d_%U.bckp';

configure channel device type disk format '/u01/rmanback/%U';
BACKUP CURRENT CONTROLFILE FOR STANDBY;
BACKUP ARCHIVELOG ALL;


--Standby---

mkdir -p /u01/rmanback

scp /u01/rmanback/*  oracle@192.168.56.4: /u01/rmanback/

scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl

mkdir -p /u01/app/oracle/diag/rdbms/orcl/ORCLS/trace

mkdir -p /u01/app/oracle/diag/rdbms/orcl/ORCLS/cdump

mkdir -p /u01/app/oracle/diag/rdbms/orcl/ORCLS/trace

mkdir -p /u01/app/oracle/oradata/ORCLS

mkdir -p /u01/app/oracle/fast_recovery_area/ORCL/onlinelog
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLS/archivelog/
mkdir -p /u01/app/oracle/admin/ORCLS/adump


---Do necessary changes in initORCLS.ora file---

---configure tnsnames.ora and listener in primary

[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCLS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcls)
    )
  )

  [oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
 )
 )
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = ORCL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
 (SID_NAME = ORCL)
 )
 )


---configure tnsnames.ora and listener in Standby

Create a passwordfile for the standby:

orapwd file=$ORACLE_HOME/dbs/orapwORCLS password=sys123




[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.4)(PORT = 1521))
 )
 )
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = ORCLS)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
 (SID_NAME = ORCLS)
 )
 )

 --- Now do tnsping from both node
 tnsping orcl
 tnsping orcls

 ----Standby Server--
 copy the initORCLS.ora file to dbs location

startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCLS.ora'

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCLS.ora';
shutdown immediate
startup nomount;

rman target=sys/sys123@orcl auxiliary=/

----output should be as below----

[oracle@standby dbs]$ rman target=sys/sys123@orcl auxiliary=/

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Aug 15 14:21:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1351299913)
connected to auxiliary database: ORCL (not mounted)
-------------------------------------------

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

----Start the redo apply:

alter database recover managed standby database disconnect from session;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

----Primary---

alter system switch logfile;
alter system archive log current;

archive log list;

----Standby---

archive log list;


SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG  ORDER BY SEQUENCE#;

---Stop the managed recovery process on the standby:

alter database recover managed standby database cancel;



-----Test Database status---
---Primary
select database_role from v$database;
---Standby
select database_role from v$database;


No comments:

Post a Comment