Configure Standby Database / Data Guard Step By Step

1. Install Oracle database with same structure on two machines.

if you are installing on vmware or virtual box then prepare first machine and then clone it.

In this post machine names are machine01 (Primary DB)  and machine02 (Secondary DB).

2.  Make sure database structure  and SID is same on both machines this will help you to create and manage Data gaurd easily.
 
create SID with same name like alidb for both machines.


3.  On first machine  open $0ORACLE_HOME/network/admin/tnsnames.or file
and make two copies of your service entry and change the copied service entries to mdb01 and mdb02 as given below.

mdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )


mdb02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )

4. Now perform same on second machine  open $ORACLE_HOME/network/admin/tnsnames.or file
and make two copies of your service entry and change the copied service entries to mdb01 and mdb02 as given below.

mdb02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )



mdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )



5. on both machines in /etc/hosts register both systems so that both machine can access each other .
     and test connectivity of both service created earlier from both machines.
Example /etc/hosts entry on both machines

192.168.0.143    machine01   machine01.com
192.168.0.144    machine02   machine02.com



6. create standby Redolog files on primary database with formula Total Existing +1. i.e I have 3 so I need to create 4 new files as given below.

Query to check existing log files and there path.

sqlplus / as sysdba

select member from v$logfile;

===========================
Add Standby Redolog file
===========================
alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo04.log' size 50m;

alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo05.log' size 50m;

alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo06.log' size 50m;

alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo07.log' size 50m;

select member from v$logfile;


7. create pfile='$ORACLE_BASE/admin/alidb/pfile/init.ora' from spfile;

8. Now open $ORACLE_BASE/admin/alidb/pfile/init.ora on your machine in text editor and
   Paste following parameters in above pfile opened in last step

###########################################
#Data Guard Parameter for db1 in pfile
###########################################
DB_UNIQUE_NAME=mdb01
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mdb01,mdb02)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/d01/apps/flash_recovery_area/arch_alidb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=mdb01'
LOG_ARCHIVE_DEST_2=
 'SERVICE=mdb02 LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=mdb02'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=Arch_%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=mdb02
FAL_CLIENT=mdb01
STANDBY_FILE_MANAGEMENT=AUTO



9. Create following arch_alidb directory in flash_recovery_area on your both machines .

mkdir   /d01/apps/flash_recovery_area/arch_alidb


10. Now mount primary DB  with new pfile create above and perform following on primary database.

sqlplus  / as sysdba


SQL> shutdown immediate;

sqlplus  / as sysdba

startup mount pfile='$ORACLE_BASE/admin/alidb/pfile/init.ora'


Switch to archivelog mode 

archive log list;

alter database archivelog;

archive log list;


On Primary db create control file that would be used at secondary db.

alter database create standby controlfile as '$ORACLE_HOME/oradata/alidb/control_std.ctl';


Create spfile on primary database.

create spfile from pfile= '$ORACLE_BASE/admin/alidb/pfile/init.ora';


11. Shutdown  Secondary database machines.

sqlplus / as sysdba
shutdown immediate;


12 Now on machine02 and perform following

Copy control file from primary db $ORACLE_HOME/oradata/alidb/control_std.ctl ,  password file and all datafiles redologs except control files (control01,control02) to a folder and move this folder to standby db server
then rename $ORACLE_HOME/oradata/alidb/ to $ORACLE_HOME/oradata/alidb_old db  folder on standby server and  create new folder $ORACLE_HOME/oradata/alidb then move these files there.

Also rename existing control files on standby database and replace control_sdt.clt on the same location with old name.

i.e if control01.clt rename it control01_old.clt .
and paste control_std.ctl in this folder and rename it control01.ctl.

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


13. add following parameters to pfile on standby
you can get pfile from Primary db '$ORACLE_BASE/admin/alidb/pfile/init.ora';

vi $ORACLE_BASE/admin/alidb/pfile/init.ora
change following line as given below



remove earlier added parameters starting from the below line as following part that we add
###########################################
#Data Guard Parameter for db1 in pfile
###########################################

Add following in the '$ORACLE_BASE/admin/alidb/pfile/init.ora' on Secondary db.


###########################################
#Data Guard Parameter db2
###########################################
DB_UNIQUE_NAME=mdb02
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mdb01,mdb02)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/d01/apps/flash_recovery_area/arch_alidb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=mdb02'
LOG_ARCHIVE_DEST_2=
 'SERVICE=mdb01 LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=mDB01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=Arch%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=mDB01
FAL_CLIENT=mDB02
STANDBY_FILE_MANAGEMENT=AUTO

#======================

14. Mount secondary db using following command.

sqlplus / as sysdba

startup mount pfile='$ORACLE_BASE/admin/alidb/pfile/init.ora'

check archive log status with following command is should be enabled.

archive log list


create spfile with this command.

create spfile from pfile= '$ORACLE_BASE/admin/alidb/pfile/init.ora';

15. Open primary DB

alter database open;


16. on standby execute following For Real Time apply.
-------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;


To check the status of standby database issue the following command for applied archived logs.

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

select sequence#,applied from v$archived_log order by 1;

===================== Stand by created ======================-

YuM on RHEL 7

RHEL7: Create a local repository.

Prerequisites

Configuration Procedure

Create a directory for the RHEL 7 distribution:

# mkdir -p /var/www/html/rhel7

Note: By creating the local repository in the /var/www/html directory, you will be ready to make it available on the network after installing an Apache webserver at a later stage.

Mount the previously downloaded Rhel 7 distribution  (called here rhel-workstation-7.1-x86_64-dvd.iso) and copy it locally:

# mount -o loop rhel-workstation-7.1-x86_64-dvd.iso /mnt
# cd /mnt
# tar cvf - . | (cd /var/www/html/rhel7; tar xvf -)
# cd /; umount /mnt

Go to the directory where the repository configuration is:

# cd /etc/yum.repo.d

Create the rhel7.repo file and type:

[rhel7]
name=rhel7
baseurl=file:///var/www/html/rhel7/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-rhel-7

Clean up the yum configuration and check it:

# yum clean all
# yum repolist all

If you regularly add new packages in your local repository, don’t forget to update it. To do that, install the createrepo package and execute it with the repository path as argument:

# yum install -y createrepo
# createrepo /var/www/html/rhel7
# yum clean all
# yum repolist all

Configure Oracle Enterprise Manager on Windows

This post will Specially help you to run Oracle Enterprise Manager on Windows 7,Windows 8 and Windows 2012

If you are receiving following error this will help.

CONFIG: OpenSCManager FAILED: Access is denied.

Tips

0. Always Assign Static IP to your Machine in windows host file.
1. Change IP to Machine name as listed below.
2. Run CMD as administrator
3. Execute Drop EM Repo Command
4. Unlock DBSNMP and SYSMAN  users
5. Execute Create EM Repo Command

These are the steps:

1. Change the %ORACLE_HOME%\network\admin\listener.ora file from an IP number to machine name.

2. Change the %ORACLE_HOME%\network\admin\tnsnames.ora file from an IP number to a machine name.

3. Change the C:\WINDOWS\System32\drivers\etc\hosts file by adding this line beneath the default localhostline (for the new Hostname value):

127.0.0.1       localhost 192.168.10.22  win7-22

4. Change the Windows hostname by right click mycomputer >properties>change settings>change
and change the computername

5. Reboot the machine to set the networking. Unlock the SYSMAN account because it ensures that emca can drop it and all dependencies. Then, drop the em configuration with the following commands.

Start cmd As administrator

C:\Data> set ORACLE_SID=orcl
C:\Data> emca -deconfig dbcontrol db -repos drop

6. You’ll receive the following prompts, enter the Oracle SID and Port number without double quotes but you must enter all passwords with double quotes (at least in Oracle Database 11g):

Oracle Database 11gR1


STARTED EMCA at Jul 13, 2008 8:26:42 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y




Oracle Database 11gR2


STARTED EMCA at Sep 3, 2012 7:40:07 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
 
Do you wish to continue? [yes(Y)/no(N)]: y




7. If you failed to unlock the SYSMAN account in step #5, you should drop the SYSMAN user manually. If youdon’t drop the SYSMAN schema, you’ll raise an error when you try to recreate it:


CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17
 
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17




The Java stack trace will look like this, more or less based on version and release:


at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1530)
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:880)
at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java
at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java
at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:492)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:218)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:147)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:222)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:535)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1215)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:519)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:468)




Drop the user and dependent on version a few other objects, like:


DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;




8. You can then create the em environment with the following syntax:


C:\Data> emca -config dbcontrol db -repos create




9. Again, you’ll receive the following prompts, enter the Oracle SID and Port number without double quotes but you must enter all passwords with double quotes (at least in Oracle Database 11g):

C:\>emca -config dbcontrol db -repos create

STARTED EMCA at Oct 15, 2015 2:57:02 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:

Database SID: ow113

Listener port number: 1521

Listener ORACLE_HOME [ C:\oracle\product\11.2.0\dbhome_1 ]:

Password for SYS user:

Password for DBSNMP user:

Password for DBSNMP user: Oct 15, 2015 2:57:22 PM oracle.sysman.emcp.util.Genera

lUtil initSQLEngineLoacly

WARNING: ORA-28000: the account is locked

Password for SYSMAN user: Terminate batch job (Y/N)? y

C:\Windows\system32>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 15 14:57:34 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user DBSNMP account unlock;

User altered.

SQL> exit

C:\>emca -config dbcontrol db -repos create

STARTED EMCA at Oct 15, 2015 3:03:55 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:

Database SID: ow113

Listener port number: 1521

Listener ORACLE_HOME [ C:\oracle\product\11.2.0\dbhome_1 ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Password for SYSMAN user: Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

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

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\11.2.0\dbhome_1

Local hostname ................ SUPPER-1049

Listener ORACLE_HOME ................ C:\oracle\product\11.2.0\dbhome_1

Listener port number ................ 1521

Database SID ................ ow113

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

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

Do you wish to continue? [yes(Y)/no(N)]: y