Overview:

In this article we performing PSU6 upgrade on Oracle 11.2.0.4 using slient mode on NON RAC.

Environment Details

Hostname	Primary server :–lnx-nsm-prod
Operating system	Linux
Database Environment	NSMDB

Software requirement:

p6880880_112000_Linux-x86-64.zip Latest OPatch utility
p20299013_112040_Linux-x86-64.zip PSU6

Download Oracle Opatch and PSU from MOS

Copy downloaded softwares to staging area.

$ cd /u01/app/oracle/software/
$ mkdir 11204

OPactch upgrade

Before applying PSU we need to have latest version of OPatch utility

$ cd /u01/app/oracle/software/11204

$ls -ltr
-rw-r—– 1 oracle oinstall 71580583 Oct 27 17:11 p20299013_112040_Linux-x86-64.zip

-rw-r—– 1 oracle oinstall 98352086 Oct 27 17:11 p6880880_112000_Linux-x86-64.zip

Upgrade OPatch utility enter A when it prompt.

$unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0.4/db/

Archive:p6880880_112000_Linux-x86-64.zip
replace /u01/app/oracle/product/11.2.0.4/db/OPatch/opatch.bat? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: /u01/app/oracle/product/11.2.0.4/db/OPatch/opatch.bat

Verify the OPatch version after upgrade

$cd / u01/app/oracle/product/11.2.0.4/db/OPatch/
lnx-nsm-prod : ./opatch version
OPatch Version: 11.2.0.3.15
OPatch succeeded.

Now we have to perform the PSU6 Upgrade.

PSU6 Upgrade

Unizip the software and go to 20299013 Directory

$unizip p20299013_112040_Linux-x86-64.zip

$cd 20299013

$ls -ltr

total 88

drwxr-xr-x 4 oracle oinstall 4096 Mar 4 2015 17478514

drwxr-xr-x 4 oracle oinstall 4096 Mar 4 2015 18522509

drwxrwxr-x 4 oracle oinstall 4096 Mar 4 2015 20299013

drwxr-xr-x 4 oracle oinstall 4096 Mar 4 2015 19121551

drwxr-xr-x 4 oracle oinstall 4096 Mar 4 2015 18031668

drwxrwxr-x 4 oracle oinstall 4096 Mar 4 2015 19769489

-rw-r–r– 1 oracle oinstall 3068 Mar 4 2015 patchmd.xml

-rw-r–r– 1 oracle oinstall 21 Mar 4 2015 README.txt

-rw-rw-r– 1 oracle oinstall 54747 Apr 12 2015 README.html

Check the opatch version and path is set .

$export PATH=$PATH:$ORACLE_HOME/OPatch
$which opatch

/u01/app/oracle/product/11.2.0.4/db/OPatch/opatch

lnx-nsm-prod: opatch version

OPatch Version: 11.2.0.3.15

OPatch succeeded.

Make DB and Listener down on the server.

$ps -ef |grep smon

oracle 15230 18477 0 10:27 pts/0 00:00:00 grep smon

$ps -ef |grep tns

oracle 15230 18477 0 10:27 pts/0 00:00:00 grep smon

If DB and Listener are ruining.Perform the following step.

Stop DB

$sqlplus / as sysdba
sql> shutdown immediate.

Stop Listener

$lsnrctl stop

Now updrade the PSU

$cd /u01/app/oracle/software/11204/20299013

$opatch apply

Patching component oracle.rdbms.deconfig, 11.2.0.4.0…

Composite patch 20299013 successfully applied.

Log file location: /u01/app/oracle/product/11.2.0.4/db/cfgtoollogs/opatch/opatch2016-10-28_10-28-01AM_1.log

OPatch succeeded.

Check the PSU Upgrade.

$opatch lspatches

20299013;Database Patch Set Update: 11.2.0.4.6 (20299013)

OPatch succeeded.

Conclusion:

In this article we have learnt how to apply PSU6 on 11.2.0.4 software on NON RAC using oracle response file in silent mode.

Oracle 11.2.0.4 installation using Silent Mode on NON RAC

By Netsoftmate on

February 5, 2017

- Uncategorized

Overview:

In this article we performing installaiton of Oracle 11.2.0.4 installation on NON RAC.

Environment Details

Hostname	Primary server :–lnx-nsm-prod
Operating system	Linux
Database Environment	NSMDB

Software requirement:

p13390677_112040_Linux-x86-64_1of7.zip	1.3 GB
p13390677_112040_Linux-x86-64_2of7.zip	1.1 GB
p13390677_112040_Linux-x86-64_3of7.zip	1.1 GB

Download Oracle 11.2.0.4 from MOS

11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER (Patchset)

Copy downloaded Oracle 11.2.0.4 software to staging area.

http://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm

$ cd /u01/app/oracle/software/

$ mkdir 11204

Create oracle user and group

#/usr/sbin/groupadd -g oinstall

#/usr/sbin/groupadd -g dba

#/usr/sbin/useradd -u -g oinstall -G dba oracle

Create Directories structure and give permissions

#mkdir -p /u01/app/oracle

#mkdir -p /u01/app/oracle/product/11.2.0.4/db

#mkdir -p /u01/app/oracle/product/Agent12c/core/12.1.0.4.0

#mkdir -p /u01/app/oracle/product/Agent12c

#mkdir -p /u01/app/11.2.0.4/grid

Permission on directories :

#chown -R oracle:oinstall /u01

#chmod -R 775 /u01

Login with oracle user and edit Profile

#su – oracle

$vi .bash_profile –> Update the below in bash profile.

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export HISTTIMEFORMAT=’%F %T ‘

PS1=`hostname`’ {$PWD}: ‘; export PS1

set -o vi

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db

export AGENT_HOME=$ORACLE_BASE/product/Agent12c/core/12.1.0.4.0

export AGENT_BASE_DIR=$ORACLE_BASE/product/Agent12c

Unzip the oracle software

$cd /u01/app/oracle/software/11204

$unzip p13390677_112040_Linux-x86-64_1of7.zip

$unzipp13390677_112040_Linux-x86-64_2of7.zip

$unzipp13390677_112040_Linux-x86-64_3of7.zip

Installing Oracle 11.2.0.4 in slient mode

$cd /u01/app/oracle/software/11204/database

Update db_rsp file only required fields as below.

ORACLE_HOSTNAME=lnx-nsm-prod

INVENTORY_LOCATION=/u01/app/oraInventory

ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db

ORACLE_BASE=/u01/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0

oracle.install.db.DBA_GROUP=dba

oracle.install.db.OPER_GROUP=dba

Now RunInstaller

$cd /u01/app/oracle/software/11204/database

$./runInstaller -ignoreSysPrereqs -force -silent -responseFile /u01/app/oracle/software/11204/database/db_install_nsm.rsp

Run the sh file at the end of installation

#/u01/app/oraInventory/orainstRoot.sh

#/u01/app/oracle/product/11.2.0.4/db/root.sh

Conclusion:

In this article we have learnt how to install oracle 11.2.0.4 software on NON RAC using oracle response file in silent mode.

Convert Physical Standby to Snapshot Standby using Data Guard Broker

By Netsoftmate on

February 5, 2017

- Uncategorized

Overview:

This article explain the process of converting Physical Standby which is in Read Only mode to a Snapshot Standby which would be in Read Write mode for the needs of disaster recovery testing. This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.

Environment Details:

Hostname	Node1 and Node2
Operating system	Linux
Environment	Production(orcl01 / orcl02) and Standby(orcldr01/orcldr02)
Oracle Home	/u01/app/oracle/product/11.2.0/db

Prerequisites for converting the database

Ensure the FRA is configured.

SQL> sho parameter recover

Note:- Snapshot standby need not have Flashback enabled at db level but if it is enabled then FRA would have already been configured

Ensure the redo transport parameters for the primary and redo apply for the standby are configured:

SQL> set lin 999

SQL> column NAME format a30

SQL> column VALUE format a84

SQL> select NAME, VALUE from v$parameter

where NAME in (‘db_name’, ‘db_unique_name’, ‘control_files’, ‘log_archive_config’,’fal_client’, ‘fal_server’,

‘log_archive_dest_1’, ‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,

‘log_archive_dest_state_1′,’log_archive_dest_state_2’,‘log_archive_dest_state_3’, ‘log_archive_dest_state_4′,’log_file_name_convert’, ‘remote_login_passwordfile’);

Ensure the standby redo log files are configured on the primary and standby database.

SQL> column member format a50

SQL> SELECT a.group#, a.member, b.bytes/1024/1024 bytes_MB FROM v$logfile a, v$log b WHERE a.group# = b.group# union select a.group#, a.member, b.bytes/1024/1024 bytes_MB from v$logfile a, v$standby_log b WHERE a.group# = b.group#;

Checks on the day of planned DR Test

Run the following query to pull information about the database on both Primary and standby

SQL> set lin 175

SQL> column NAME format a10

SQL> column LOG_MODE format a10

SQL> column OPEN_MODE format a10

SQL> COLUMN CONTROLFILE_TYPE format a16

SQL> column PROTECTION_MODE format a20

SQL> column PROTECTION_LEVEL format a20

SQL> column DATABASE_ROLE format a20

SQL> column GUARD_STATUS format a12

SQL> column DATAGUARD_BROKER format a16

SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;

Verifying That the Standby Has Received All Redo, you can carry your individual steps.

On Primary and on standby –> To check if the scn’s are close.

SQL> set numwidth 20

SQL> select current_scn from v$database;

On Standby –> To check if the MRP is running

SQL> select status,process from v$managed_standby where process like ‘%MRP%’;

STATUS PROCESS

———————————————— ————————————

APPLYING_LOG MRP0

On standby –> to Check if all or near to all logs are applied

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

And to check real time apply On primary / standby –> It should say “MANAGED REAL TIME APPLY” in one of the rows.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

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

Compare the sequence# from primary to standby query. If the standby is not receiving the current redo, you cannot convert/switchover. Note that if your primary is a RAC, you should see multiple LGWR to RFS connections, one for each primary thread. You must validate that each primary thread is caught up.

Check the apply is caught up

Once determined the redo stream is current, ensure all redo has been applied to standby database.

On the standby database:

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

Note: if you don’t see MRP0, the apply is not running

To convert/switchover, you must start the apply service and wait for it to catch up with the current redo stream before starting. If you see the MRP0 line but it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap has been resolved.

Verify there are no large Gaps

Identify the current sequence number for each thread on the primary database.

On the Primary:

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

Verify the target physical standby database has applied up to, but not including the logs from the primary query. On the standby the following query should be within 1 or 2 of the primary query result.

On the Standby database:

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = ‘YES’ AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS =’CURRENT’) GROUP BY THREAD#;

Data Guard Broker Configuration completeness check

Ensure Server Parameter File (SPFILE) is used on both Primary and standby database so that the broker can persistently reconcile values between broker properties and any related initialization parameter values:

SQL> show parameter spfile;

Verify value of the DG_BROKER_START initialization parameter is set to TRUE on both primary and standby DB.

SQL> Show parameter DG_BROKER_START;

The data Guard Monitor (DMON) – Broker-controlled process is the main Broker process that is responsible for coordinating all Broker actions as well as maintaining the Broker configuration files. This process is enabled and disabled With DG_BROKER_START. To verify if DMON process running on both Primary and standby:

$ps -ef|grep dmon|grep -v grep

Verify DG_BROKER_CONFIG_FILEn initialization parameters files exists on shared disk for all databases in the Oracle RAC database configuration on Primary and standby:

SQL> show parameter dg_broker_config_file;

Check whether StaticConnectIdentifier is configured on ALL NODES
To enable DGMGRL to restart instances during the course of the switchover / conversion, a static service is required. This can be supplied by explicitly setting the Broker Instance Property StaticConnectIdentifier.

Below static entry should be on all the nodes as below.

–Primary

Node1

SID_LIST_LISTENER =

SID_LIST=

SID_DESC=

GLOBAL_DBNAME=orcl_DGMGRL.localdomain.com)

ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db)

SID_NAME=orcl01))

)

Node 2

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orcl_DGMGRL.localdomain.com)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db)

(SID_NAME=orcl02))

)

– Standby

Node1

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orcldr_DGMGRL.localdomain.com)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4 db)

SID_NAME=orcldr1))

)

Node 2

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orcl01_DGMGRL.localdomain.com)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db)

(SID_NAME=orcldr2))

)

**This entry makes it possible for the Broker to connect to an idle instance using a remote SYSDBA connection and perform the necessary startup

Ensure Broker configuration is enabled and state of all members as intended is ONLINE

$ dgmgrl /

DGMRL> show configuration
DGMRL> show database verbose orcl

DGMRL> show database verbose orcldr

On the standby, ensure PROPERTY ‘DELAYMINS’=’0’

Ensure property LogXptMode to be same on both Primary and Standby either set to ‘SYNC’ or ‘ASYNC’.

On the standby and primary, ensure all other properties are consisted with Data Guard and Data Guard broker

Verify remote_login_passwordfile is set to ‘EXCLUSIVE’ on both Primary and Standby:

sql>show parameter remote_login_passwordfile

Conversion to Snapshot Standby and back to Physical standby using Data Guard Broker

Converting a physical Standby to Snapshot Standby

Once completed the mandatory & prerequisites checks, connect to the primary database and disable all services so the application cannot establish any sessions to the database.

On standby to be converted to snapshot mode.

Tail Broker and Alert Logs (optional) on all instances:

Locate Broker logs by showing database parameter background_dump_dest:

SQL> SHOW PARAMETER background_dump_dest;

NAME TYPE VALUE

—————————————————-

background_dump_dest string /u01/admin/diag/rdbms/orcldr/orcldr01/trace

Tail the broker logs:

tail -f /u01/admin/diag/rdbms/orcldr/orcldr01/trace/dr*

DGMGRL Convert to snapshot standby: Log onto dgmgrl from primary database server

On standby database, Issue the convert command:

% dgmgrl

DGMGRL> CONNECT SYS/password@standby

DGMGRL> convert database ‘orcldr’ to snapshot standby;

DGMGRL for Linux: Version 11.1.0.7.0 – 64bit Production

Welcome to DGMGRL, type “help” for information.

DGMGRL>

DGMGRL> connect sys@orcldr

Password:

Connected.

DGMGRL> convert database ‘orcldr’ to snapshot standby;

Convert back to Physical Standby

1 Make sure all applications connecting to snapshot standby are down

and then

Tail the broker logs:

%tail -f /u01/admin/diag/rdbms/orcldr/orcldr01/trace/dr*

Convert to Physical standby:

% dgmgrl *** log into dgmgrl from primary database server

DGMGRL> CONNECT SYS/password@standby database

DGMGRL> CONVERT DATABASE orcldr TO PHYSICAL STANDBY ;

Welcome to DGMGRL, type “help” for information.

DGMGRL>

DGMGRL> connect sys@orcldr

Password:

Connected.

DGMGRL> convert database ‘orcldr’ to physical standby;

Ensure Broker configuration is enabled and state of all members as intended is ONLINE:

$ dgmgrl /

DGMRL> show configuration

DGMRL> show database verbose orcl

DGMRL> show database verbose orcldr

Perform the Physical standby check out

On Primary:

Check the alert log of primary database and tail the alert.

Check the archive error destination

SQL> col DEST_NAME for a40

SQL> set lines 200

SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;

Check the current archive log (sequence)

SQL> select thread#,max(sequence#) from v$log where status=’CURRENT’ group by thread# order by 1;

On Standby:

Check the alert log of standby database and tail the alert.

Check the current archive log (sequence) applied.

SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread# order by 1;

Check the status of MRP

$ps -ef |grep mrp

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process=’MRP0′;

PROCESS THREAD# SEQUENCE# STATUS

————————— ———- ———- ————————————

MRP0 1 279486 WAIT_FOR_LOG

Conclusion:

From this article we have learnt how to convert Physical standby database to Snapshot standby database using Data guard Broker method.

Convert Physical Standby to Snapshot standby

By Netsoftmate on

February 5, 2017

- Uncategorized

Overview:

This article explain the process of converting Physical Standby in Read Only mode to a Snapshot Standby.Snapshot Standby open DB in Read Write mode for disaster recovery testing. This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.

Environment Details:

Hostname	Node1 and Node2
Operating system	Linux
Environment	Production and Standby
Oracle Home	/u01/app/oracle/product/11.2.0/db

Prerequisites for converting the database

Ensure the FRA is configured.

SQL> sho parameter recover

Note:- Snapshot standby need not have Flashback enabled at db level but if it is enabled then FRA would have already been configured

Ensure the redo transport parameters for the primary and redo apply for the standby are configured:

SQL> set lin 999

SQL> column NAME format a30

SQL> column VALUE format a84

SQL> select NAME, VALUE from v$parameter

where NAME in (‘db_name’, ‘db_unique_name’, ‘control_files’, ‘log_archive_config’,’fal_client’, ‘fal_server’,

‘log_archive_dest_1’, ‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,

‘log_archive_dest_state_1′,’log_archive_dest_state_2’,‘log_archive_dest_state_3’, ‘log_archive_dest_state_4′,’log_file_name_convert’, ‘remote_login_passwordfile’);

Ensure the standby redo log files are configured on the primary and standby database.

SQL> column member format a50

Checks on DR Test

Run the following query to pull information about the database on both Primary and standby

SQL> set lin 175

SQL> column NAME format a10

SQL> column LOG_MODE format a10

SQL> column OPEN_MODE format a10

SQL> COLUMN CONTROLFILE_TYPE format a16

SQL> column PROTECTION_MODE format a20

SQL> column PROTECTION_LEVEL format a20

SQL> column DATABASE_ROLE format a20

SQL> column GUARD_STATUS format a12

SQL> column DATAGUARD_BROKER format a16

SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;

Verifying That the Standby Has Received All Redo, you can carry your individual steps.

On Primary and on standby –> To check if the scn’s are close.

SQL> set numwidth 20

SQL> select current_scn from v$database;

On Standby –> To check if the MRP is running

SQL> select status,process from v$managed_standby where process like ‘%MRP%’;

STATUS PROCESS

———————————————— ————————————

APPLYING_LOG MRP0

On standby –> to Check if all or near to all logs are applied

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

And to check real time apply On primary / standby –> It should say “MANAGED REAL TIME APPLY” in one of the rows.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

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

Check the apply is caught up

Once determined the redo stream is current, ensure all redo has been applied to standby database.

On the standby database:

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

Note: if you don’t see MRP0, the apply is not running

Verify there are no large Gaps

Identify the current sequence number for each thread on the primary database.

On the Primary:

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

On the Standby database:

Convert to Physical standby to Snapshot Standby manual way

On standby to be converted to snapshot mode.

Tail the Alert Logs (optional) on all instances:

Locate alert logs by showing database parameter background_dump_dest:

SQL> SHOW PARAMETER background_dump_dest;

% tail -f /u01/app/diag/rdbms/standby/standby1/trace/alert*

Stop MRP

SQL> alter database recover managed standby database cancel;

Database alerted

Record SCN and timestamp

SQL> col current_scn format 99999999999999

SQL> select current_scn from v$database;

Manual SQLPLUS Convert to snapshot standby:

For standalone

SQL> shutdown immediate

SQL> startup mount

For RAC

$srvctl stop database -d DBNAME

$srvctl start database -d DBNAME -o mount

SQL> select open_mode, database_role from v$database;

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> shutdown immediate

SQL> startup mount

SQL> select open_mode, database_role from v$database;

SQL> ALTER DATABASE OPEN;

SQL> select open_mode, database_role from v$database;

Convert to Snapshot Standby back to Physical standby manual way

For RAC

$srvctl stop database -d DBNAME

$srvctl startup database -d DBNAME -o mount

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

srvctl stop database -d DBNAME

For Standalone

SQL> shutdown immediate

SQL> startup mount

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> shutdown immediate

Restart database

For standalone

SQL> startup mount

For RAC

srvctl start database -d DBNAME -o mount

Start MRP and open read only

SQL> alter database open read only;

SQL> alter database recover managed standby database using current logfile disconnect;

Perform the Physical standby check out

On Primary:

Check the alert log of primary database and tail the alert.
Check the archive error destination

sql>col DEST_NAME for a40

sql>set lines 200

sql>select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;

Check the current archive log (sequence)

sql>select thread#,max(sequence#) from v$log where status=’CURRENT’ group by thread# order by 1;

On Standby:

Check the alert log of standby database and tail the alert.

Check the current archive log (sequence) applied.

sql>select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread# order by 1;

Check the status of MRP

$ps -ef |grep mrp

sql>select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process=’MRP0′;

PROCESS THREAD# SEQUENCE# STATUS

————————— ———- ———- ————————————

MRP0 1 279486 WAIT_FOR_LOG

Conclusion:

From this article we have learnt how to convert Physical standby database to Snapshot standby database using manual method.

Installing and Configuring Oracle GoldenGate Studio 12c

By Netsoftmate on

January 5, 2017

- Uncategorized

Overview
Don’t want to design or build Oracle GoldenGate architecture from Command line? Oracle GoldenGate Studio is the answer for you.
In 2015, Oracle has released a new tool which provides a Graphical User Interface (GUI) for designing and building Oracle GoldenGate Architecture called “Oracle GoldenGate Studio”.

Installing Oracle GoldenGate Studio consists of following steps:

Ensure Java JDK 1.8 is installed
Install Oracle GoldenGate Studio
Configure Oracle GoldenGate Studio repository
Start and Connect to Oracle GoldenGate Studio

In this artcile I will demonstrate how to Install and Configure Oracle GoldenGate Studio 12c in detail.

Environment

Oracle Enterprise Linux 6.8
Oracle RDBMS 11.2.0.4

Assumption
It is assumed that you have a database created which will be used as respository for OGG Studio

Downloading and Installing latest JDK

Get the current Java version installed.

[root@dm01db01 software]# java -version
java version “1.7.0_91”
Java(TM) SE Runtime Environment (build 1.7.0_91-b32)
Java HotSpot(TM) 64-Bit Server VM (build 24.91-b03, mixed mode)

Java JDK 1.7 and earlier versions doesn’t work with the JAR files for Studio.
Java JDK 1.8 is required for GoldenGate Studio Installation.

To download the required JDK, navigate to the following URL and download the Java SE JDK:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Copy the downloaded rpm the server using WinScp and apply it.

[root@dm01db01 ]# cd /u01/app/oracle/software
[root@dm01db01 software]# ls -ltr
-rw-r–r– 1 root root 166040563 Jan 3 03:11 jdk-8u111-linux-x64.rpm

[root@dm01db01 software]# rpm -i jdk-8u111-linux-x64.rpm
Unpacking JAR files…
tools.jar…
plugin.jar…
javaws.jar…
deploy.jar…
rt.jar…
jsse.jar…
charsets.jar…
localedata.jar…

Verify the Java version

[root@dm01db01 software]# java -version
java version “1.8.0_111”
Java(TM) SE Runtime Environment (build 1.8.0_111-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)

Downloading Oracle GoldenGate Studio Software

Open a web browser and enter www.oracle.com in the address bar

This will bring you to the Oracle website home page

On this page hover on the download tab and click on “middleware”

Click GoldenGate

Accept the agreement and select the desired Oracle GoldenGate version for your platform.

Here I am downloading “fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip”

Sing in if prompted. The download will begin

Copy the zip file from your desktop to the server using WinScp

Drag and Drop the file from left to right in to a directory

Here I am copying the file to /u01/app/oracle/software location

File copy is in progress

Once the file transfer is completed, you will see the file on server as below

dm01db01-orcldb1 {/u01/app/oracle/software}:ls -ltr

-rw-r–r– 1 oracle oinstall 2042373304 Jan 3 04:42 fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip

Steps to Install Oracle GoldenGate Studion

Make sure Java is set in your path properly.

dm01db01-orcldb1 {/home/oracle}:whereis java

java: /usr/bin/java

Here I am using Oracle user to install GoldenGate Studio software

[oracle@dm01db01 software]# java -version

java version “1.8.0_111”

Java(TM) SE Runtime Environment (build 1.8.0_111-b14)

Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)

Unzip the Oracle GoldenGate Studio software

dm01db01-orcldb1 {/u01/app/oracle/software}:unzip fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip

Archive: fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip

inflating: fmw_12.2.1.2.6_oggstudio.jar

inflating: fmw_122126_readme.htm

dm01db01-orcldb1 {/u01/app/oracle/software}:ls -ltr

-rw-r–r– 1 oracle oinstall 9032 Dec 5 16:49 fmw_122126_readme.htm

-r-xr-xr-x 1 oracle oinstall 2042431392 Dec 5 16:55 fmw_12.2.1.2.6_oggstudio.jar

-rw-r–r– 1 oracle oinstall 2042373304 Jan 3 04:42 fmw_12.2.1.2.6_oggstudio_Disk1_1of1.zip

Start the x-window and set the DISPLAY variable to launch the installation window

dm01db01-orcldb1 {/u01/app/oracle/software}:export DISPLAY=10.10.193.2:0.0

Start the Oracle GoldenGate Studio software installation as follows:

dm01db01-orcldb1 {/u01/app/oracle/software}:java -jar fmw_12.2.1.2.6_oggstudio.jar

Launcher log file is /tmp/OraInstall2017-01-03_05-02-43AM/launcher2017-01-03_05-02-43AM.log.

Extracting the installer . . . . . . . . . . . . . . . . . . . . Done

Checking if CPU speed is above 300 MHz. Actual 2527.001 MHz Passed

Checking monitor: must be configured to display at least 256 colors. Actual . Passed

Checking swap space: must be greater than 512 MB. Actual 16378 MB Passed

Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required)

Checking temp space: must be greater than 300 MB. Actual 8262 MB Passed

Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2017-01-03_05-02-43AM

Log: /tmp/OraInstall2017-01-03_05-02-43AM/install2017-01-03_05-02-43AM.log

Logs successfully copied to /u01/app/oraInventory/logs.

The Installer wizard will be started

This is the first step out of 8 steps. Click Next

Select “Skip Auto Updates” and Click Next

Enter the Oracle GoldenGate Studio home for software installation

Select installation type as “Complete Install (Default)”. Click Next

Prerequisite checks are performed. Click Next

The installation Summary is displayed

Now the Software Installation progress

Click Finish

Configure Oracle GoldenGate Studio repository

The Oracle GoldenGate Studio store entire replication design and deployment details in a database repository. You must install the repository schema on a certified database before using Oracle GoldenGate Studio. The repository is created using the Oracle Repository Creation Utility (RCU).

Make sure you created a database for storing the Oracle GoldenGate Studio objects before proceeding. Here I already have a database created by the name “orcldb” which I will be using as a repository.

Make sure the DISPLAY is set properly and Launch the Repository Creation Utility as follows:

dm01db01-orcldb1 {/u01/app/oracle/software}:cd /u01/app/oracle/product/oggstudio12c/oracle_common/bin/

dm01db01-orcldb1 {/u01/app/oracle/product/oggstudio12c/oracle_common/bin}:./rcu

RCU Logfile: /tmp/RCU2017-01-03_07-10_453443440/logs/rcu.log

On the home page. Click next

Select “Create Repository” and “System Load and Product Load”. Click Next

Enter the Oracle Database server details and Click next

Prerequisites checks are performed. Click ok

Enter the “Create New Prefix”. Click next

Prerequisite checks are performed. Click Ok

Enter the password for all Schemas. Here I am select “Use same password for all schemas”

Enter the Password for “Supervisor” user. Click Next

Click Next

Click ok

Click ok

On this page, tablespace mapping is displayed. Click next

System load in progress

Click Close to complete the RCU process

Start and Connect to Oracle GoldenGate Studio

Now let’s start the oggstudio and connect to the repository

Make sure the DISPLAY variable is set properly so the installer can be launced.

Execute the oggstudio utitilty to start the Oracle GoldenGate Studio

dm01db01-orcldb1 {/u01/app/oracle/product/oggstudio12c/oggstudio/bin}:./oggstudio

Oracle GoldenGate Studio
Copyright (c) 1997, 2016, Oracle and/or its affiliates. All rights reserved.

Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/oracle/.oggstudio/12.2.1.2.0/product.conf
/usr/java/jdk1.8.0_111

Note: When you start the oggstudio for the first time, it will ask you for the jdk location and stores in it product.conf file. The furture start will not ask you for the jdk location again.

The Oracle GoldenGate Studio page appears as below

For the first time it will ask you, if you would like to import preferences from previous installation. Click No.

The load will continue as follows

Click on the Plug symbol to connect to the repository database

Enter the Supervisor and it’s password

Finally the oggstudio is started and you are connected the repository

Conclusion

In this article we have learned how to install and configure Oracle GoldenGate Studio 12c. The OGG Studio is very useful for the folks who can to design and build Oracle GoldenGate architecture using GUI interface.

Rebuild Physical Standby Using RMAN Incremental Backup

By Netsoftmate on

January 4, 2017

- Uncategorized

Overview:
In my previous article I have shown you how to rebuild a physical standby database using RMAN full database backup. Please refer below link for same.

Rebuild Physical Standby using RMAN full backup

Rolling forward a standby using RMAN full database backup can be time consuming and take several hours based on the size of the database. RMAN offers a flexibility to take the SCN based backup that can be used to incrementally roll forward a standby database. Using RMAN incremental backup to roll forward a standby database is the easiest and quickest way to sync a standby database.

Environment Details

Hostname	Primary server :– lnx-nsm-prod01/ lnx-nsm-prod02 Standby Server :– lnx-nsm-dr01/ lnx-nsm-dr02
Operating system	Linux
Database Environment	NSMDB and NSMDR

Get the current SCN from standby database, we will use this SCN for the incremental backup from primary.

SQL> select current_scn from v$database;

Take a control file backup from NSMDB primary.

$ rman target /

RMAN> backup current controlfile for standby format ‘/backup/nsmdb/nsmdb_stby_ctl’;

Create backup directory and take an incremental backup from primary, use the SCN from standby in step 1.

$ mkdir -p /backup/nsmdb/

$ rman target /

RMAN> run {

allocate channel c1 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c2 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c3 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c4 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c5 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c6 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c7 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

allocate channel c8 device type disk format ‘/backup/nsmdb/nsmdb_%U’;

backup incremental from scn scn_from_step_1 database;

}

Get datafile file# and name from primary database, and spool output to text file f1.txt for reference.

SQL> select file#, name from v$datafile order by 1;

Copy the backup pieces to standby hosts in the same location.

$ mkdir -p /backup/nsmdb

Stop mrp at the standby.

DGMGRL> edit database nsmdr set state=APPLY-OFF;

Get datafile file# and name from standby database, and spool output to text file f2.txt for reference.

SQL> select file#, name from v$datafile order by 1;

Bring down standby database, all instances.

$ Srvctl stop database –d nsmdr

Start the standby database instance in nomount and restore the controlfile.

$ rman target /

RMAN> startup nomount

RMAN> restore standby controlfile from ‘/backup/nsmdb/nsmdr_stby_ctl’;

Mount the standby database, stop MRP, catalog the backup pieces.

SQL> alter database mount standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

$ rman target /

RMAN> sql ‘alter database mount’;

RMAN> catalog start with ‘/backup/nsmdb’;

Catalog data files and switch the files to copy.

RMAN> catalog start with ‘+DATA/nsmdr/datafile’;

RMAN> SWITCH DATABASE TO COPY;

Recover the standby database.