Oracle Dataguard Broker 12c with password file in ASM

Configure 12c dataguard broker using the below command onPrimary database server

primeprd – > tnsnames for primary database

primedr-> tnsnames for standby database

/home/oracle::11g–>dgmgrl

DGMGRL> connect sys/********

DGMGRL> create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;

Configuration “dg_primeprd” created with primary database “primeprd”

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL> remove configuration;
Removed configuration
Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing.

alter system set log_archive_dest_2=” scope=both sid=’*’;
Disable then Enable the broker parameter on both the Primary and Standby databases.

— Primary
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;

— Standby
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;

On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set, this is the workaround/solution.

/home/oracle::11g–>dgmgrl

DGMGRL> connect sys/**********

DGMGRL> create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;

DGMGRL> enable configuration;
If in Broker you get below error Error: ORA-16664: unable to receive the result from a database

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 40 seconds ago)

Check in Broker Log File.

Mon Jul 04 14:41:46 2016
Error 1017 received logging on to the standby
————————————————————
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191

Failed to send message to site . Error code is ORA-01017.

Re-create password files in ASM in 12c like below, the correct format is pwd<dbuniquename>

Primary

srvctl config database -d primeprd

orapwd file=’+DATAC1/PRIMEPRD/pwdprimeprd’ entries=10 dbuniquename=primeprd password=*** force=y ignorecase=y
Standby

srvctl config database -d primedr
orapwd file=’+DATAC1/PRIMEDR/pwdprimedr’ entries=10 dbuniquename=primedr password=*** force=y ignorecase=y

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 18 seconds ago)

— Try Switchover Now —
DGMGRL> switchover to primedr
Performing switchover NOW, please wait…
Error: ORA-16644: apply instance not available

Failed.
Unable to switchover, primary database is still “primeprd”

DGMGRL> edit database primedr set state=’APPLY-ON’ with apply instance=’primedr1′;

DGMGRL> show database verbose primedr

Database – primedr

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 6.00 KByte/s
Active Apply Rate: 755.00 KByte/s
Maximum Apply Rate: 827.00 KByte/s
Real Time Query: OFF
Instance(s):
primedr1 (apply instance)
primedr2
DGMGRL> switchover to primedr
Performing switchover NOW, please wait…
New primary database “primedr” is opening…
Oracle Clusterware is restarting database “primeprd” …
Switchover succeeded, new primary is “primedr”
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr – Primary database
primeprd – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 40 seconds ago)
DGMGRL> edit database primeprd set state=’APPLY-ON’ with apply instance=’primeprd1′;

DGMGRL> show database verbose primeprd

Database – primeprd

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 307.00 KByte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
primeprd1 (apply instance)
primeprd2

DGMGRL>
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr – Primary database
primeprd – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 55 seconds ago)

Connect to DR site broker to intiate switchover

DGMGRL> switchover to primeprd ;
Performing switchover NOW, please wait…
Operation requires a connection to instance “primeprd1” on database “primeprd”
Connecting to instance “primeprd1″…
Connected as SYSDBA.
New primary database “primeprd” is opening…
Oracle Clusterware is restarting database “primedr” …
Switchover succeeded, new primary is “primeprd

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 11 seconds ago

Advertisements

Install GUI X11 packages and VNC Server on Exadata Compute Nodes

Recently we patched our Exadata machines to the April 2016 QFSP. On running DBCA we got X11 forwarding error and upon further investigation we discovered that X11 related packages RPM are removed in the new compute node patches.

Image version: 12.1.2.3.1.160411

On Oracle support website there is a note about it:

Refer MOS for more information –> Unable to run graphical tools (gui) (runInstaller, dbca and dbua) on Exadata 12.1.2.1.0 – 12.1.2.2.0 (Doc ID 1969308.1)

In addition to adding pack the X11 packages we also installed VNC Server(tigervnc), which can be a useful thing to do some basic GUI stuff.

— Install GUI Packages back in Exadata —

1. Download OEL6 (OEL6.7) Linux from E-delivery website as image

2. FTP file to serve, preferably to Cluster file system to do on both nodes

3. Create a directory and mount the OEL6 media

mkdir /mnt

— IF exists just continue

mount -o loop /ACFS/V77197-01.iso /mnt
4. Change the REPO, Edit /etc/yum.repos.d/Exadata-computenode.repo

Remove old information and add below lines

[ol6_iso]
name=Oracle Exadata DB server
baseurl=file:///mnt
gpgcheck=0
enabled=1
5. Validate the repository

yum list –disablerepo=* –enablerepo=ol6_iso
6. Install the X11 packacges

yum –disablerepo=* –enablerepo=ol6_iso install xorg-x11-xauth

–Update 2 –: starting 12.1.2.2.0 this package is not mandatory for X applications, so you can run below if your imageinfo version is more than it–

–Update 1–: I had a conversation with Oracle Development team after posting this blog post and interestingly they could not re-produce the same scenario in their lab. We upgraded from OL5 to OL6 on the compute nodes with almost 2 years of difference between the image versions. If your compute node versions is already OL6 then most likely you should not encounter the above scenario (that’s what the Oracle development team told me atleast).  If you do encounter it then log a SR with Oracle and get their blessings before proceeding.

yum –disablerepo=* –enablerepo=ol6_iso install xorg-x11-utils
If packages already exist remove the pakages and reinstall since it will fix the library dependencies
yum –disablerepo=* –enablerepo=ol6_iso remove xorg-x11-utils
yum –disablerepo=* –enablerepo=ol6_iso install xorg-x11-utils
7. Edit /etc/ssh/sshd_config to enable X11Forwarding

vi /etc/ssh/sshd_config

Below values should not be commented

X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes

UseDNS yes
8. Restart the SSHD service to reflect the new changes
service sshd restart
Stopping sshd: [ OK ]
Starting sshd: [ OK ]
9. Run any GUI utility to check, export the display if required

export DISPLAY=10.10.10.1:0.0

dbca
10. Install VNC Server

yum –disablerepo=* –enablerepo=ol6_iso install vnc-server
11. Configure VNC Server for oracle user
vi /etc/sysconfig/vncservers

— Add / Edit Parameters like below in file, make sure the 2 lines are uncommented

VNCSERVERS=”2:oracle”
VNCSERVERARGS[2]=”-geometry 800×600 -nolisten tcp -localhost”

— Set VNC password for oracle user —

su – oracle

vncpasswd

Password:
Verify:

— Login back as root user and start the vnc server service —

service vncserver start
New ‘exatestdbadm01.domain.com:2 (oracle)’ desktop is exatestdbadm01.domain.com:2

Creating default startup script /home/oracle/.vnc/xstartup
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/exatestdbadm01.domain.com:2.log

[ OK ]
Login with any VNC viewer to test

— To Stop Service Run —

service vncserver stop
Shutting down VNC server: 2:oracle [ OK ]

 

 

Patching Oracle 12c Multi-tenant to latest PSU now known as DPBP

Before we begin this is a note from the Oracle Website

The name of these bundle patches was changed to “Database Proactive Bundle Patch” in April 2016.

The patches include fixes for both Engineered Systems and for DB In-Memory.
They can be used on both Exadata and non-Exadata systems, and can be used for both RAC and non-RAC configurations.

Quarterly patches are released on all platforms.
Monthly patches may only be released on a subset of platforms.

DB Version : 12.1.0.2

OS : OEL 6 Update 6, Linux x86_64

Single Instance No RAC

Multitenant Option Enabled

1 CDB, 1 PDB

CDB Name: CDBDEV

PDB Name: PDBDEV

The database bundled patches are now called “Database Proactive Bundle Patch” from April 2016.

Patch 22291127 – Database Patch Set Update 12.1.0.2.160419 (Includes CPUApr2016) – Single Instance DB

Patch 22899531: DATABASE PROACTIVE BUNDLE PATCH 12.1.0.2.160419 (APR2016)- DB Plus Grid Infrastructure

Also the naming convention has changed like below

Patch description:  “Database Patch Set Update : 12.1.0.2.160419 (22291127)”

   Created on 6 Apr 2016, 03:46:21 hrs PST8PDT

Sub-patch  21948354; “Database Patch Set Update : 12.1.0.2.160119 (21948354)”

Sub-patch  21359755; “Database Patch Set Update : 12.1.0.2.5 (21359755)”

Sub-patch  20831110; “Database Patch Set Update : 12.1.0.2.4 (20831110)”

Sub-patch  20299023; “Database Patch Set Update : 12.1.0.2.3 (20299023)”

Sub-patch  19769480; “Database Patch Set Update : 12.1.0.2.2 (19769480)”

The left most part denotes “yymmdd” as a post-fix to now which date is the PSU from

  1. Download the latest patches for your platform from Oracle Support Website.
  2. unzip the patch

unzip p22291127_12102_<platform>.zip

cd 22291127

Go to Directory of Patch and Apply the Patch

[oracle@localhost 22291127]$ /home/oracle/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch prereq CheckCOnflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.1.0.1.3

Copyright (c) 2016, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /home/oracle/app/oracle/product/12.1.0/dbhome_1

Central Inventory : /home/oracle/app/oraInventory

   from           : /home/oracle/app/oracle/product/12.1.0/dbhome_1/oraInst.loc

OPatch version    : 12.1.0.1.3

OUI version       : 12.1.0.2.0

Log file location : /home/oracle/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2016-05-13_08-38-21AM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

OPatch succeeded.

  1. Download the latest version of Patch before applying the patches. You can search latest Patch by searching for “Patch 6880880”
  2. Shutdown the Database and Listener
  3. Go to Directory of the Patch and Apply the Patch

[oracle@localhost 22291127]$ /home/oracle/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch apply

Oracle Interim Patch Installer version 12.1.0.1.10

Copyright (c) 2016, Oracle Corporation.  All rights reserved.

Oracle Home       : /home/oracle/app/oracle/product/12.1.0/dbhome_1

Central Inventory : /home/oracle/app/oraInventory

   from           : /home/oracle/app/oracle/product/12.1.0/dbhome_1/oraInst.loc

OPatch version    : 12.1.0.1.10

OUI version       : 12.1.0.2.0

Log file location : /home/oracle/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2016-05-13_08-44-41AM_1.log

Verifying environment and performing prerequisite checks…

OPatch continues with these patches:   19769480  20299023  20831110  21359755  21948354  22291127

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’)

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files…

Applying sub-patch ‘19769480’ to OH ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’

Patching component oracle.rdbms.deconfig, 12.1.0.2.0…

Patching component oracle.xdk, 12.1.0.2.0…

Patching component oracle.tfa, 12.1.0.2.0…

Patching component oracle.rdbms.util, 12.1.0.2.0…

Patching component oracle.rdbms, 12.1.0.2.0…

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

Patching component oracle.xdk.parser.java, 12.1.0.2.0…

Patching component oracle.oraolap, 12.1.0.2.0…

Patching component oracle.xdk.rsf, 12.1.0.2.0…

Patching component oracle.rdbms.rsf, 12.1.0.2.0…

Patching component oracle.rdbms.rman, 12.1.0.2.0…

Patching component oracle.ldap.rsf, 12.1.0.2.0…

Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…

Applying sub-patch ‘20299023’ to OH ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’

ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.tfa, 12.1.0.2.0…

Patching component oracle.rdbms.deconfig, 12.1.0.2.0…

Patching component oracle.rdbms.rsf, 12.1.0.2.0…

Patching component oracle.rdbms, 12.1.0.2.0…

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

Patching component oracle.rdbms.rsf.ic, 12.1.0.2.0…

Patching component oracle.ldap.rsf, 12.1.0.2.0…

Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…

Applying sub-patch ‘20831110’ to OH ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’

Patching component oracle.rdbms, 12.1.0.2.0…

Patching component oracle.oraolap.dbscripts, 12.1.0.2.0…

Patching component oracle.ldap.rsf, 12.1.0.2.0…

Patching component oracle.tfa, 12.1.0.2.0…

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

Patching component oracle.rdbms.rsf, 12.1.0.2.0…

Applying sub-patch ‘21359755’ to OH ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’

Patching component oracle.assistants.server, 12.1.0.2.0…

Patching component oracle.rdbms.rsf, 12.1.0.2.0…

Patching component oracle.rdbms, 12.1.0.2.0…

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

Patching component oracle.tfa, 12.1.0.2.0…

Applying sub-patch ‘21948354’ to OH ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’

Patching component oracle.rdbms.deconfig, 12.1.0.2.0…

Patching component oracle.xdk, 12.1.0.2.0…

Patching component oracle.tfa, 12.1.0.2.0…

Patching component oracle.rdbms, 12.1.0.2.0…

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

Patching component oracle.xdk.parser.java, 12.1.0.2.0…

Patching component oracle.rdbms.rsf, 12.1.0.2.0…

Patching component oracle.xdk.rsf, 12.1.0.2.0…

Applying sub-patch ‘22291127’ to OH ‘/home/oracle/app/oracle/product/12.1.0/dbhome_1’

ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.ldap.rsf, 12.1.0.2.0…

Patching component oracle.ldap.client, 12.1.0.2.0…

Patching component oracle.tfa, 12.1.0.2.0…

Patching component oracle.rdbms, 12.1.0.2.0…

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

Patching component oracle.rdbms.rsf, 12.1.0.2.0…

Patching component oracle.oraolap, 12.1.0.2.0…

Patching component oracle.rdbms.rman, 12.1.0.2.0…

Patching component oracle.oraolap.dbscripts, 12.1.0.2.0…

Composite patch 22291127 successfully applied.

Log file location: /home/oracle/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2016-05-13_08-44-41AM_1.log

OPatch succeeded.

  1. Post- Patch Installation

The datapatch utility will then run the necessary apply scripts to load the modified SQL files into the database. An entry will be added to the dba_registry_sqlpatch view reflecting the patch application. In the dba_registry_sqlpatch view, verify the Status for the APPLY is “SUCCESS”.

Startup the Database

sqlplus “/as sysdba”

startup

alter pluggable database all open;

[oracle@localhost 22291127]$ /home/oracle/app/oracle/product/12.1.0/dbhome_1/OPatch/datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Fri May 13 08:50:32 2016

Copyright (c) 2015, Oracle.  All rights reserved.

Log file for this invocation: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7427_2016_05_13_08_50_32/sqlpatch_invocation.log

Connecting to database…OK

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions…done

Determining current state…done

Current state of SQL patches:

Bundle series PSU:

  ID 160419 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks…

Installation queue:

  For the following PDBs: CDB$ROOT PDB$SEED PDBDEV

    Nothing to roll back

    The following patches will be applied:

      22291127 (Database Patch Set Update : 12.1.0.2.160419 (22291127))

Installing patches…

Patch installation complete.  Total patches installed: 3

Validating logfiles…

Patch 22291127 apply (pdb CDB$ROOT): SUCCESS

  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_CDBDEV_CDBROOT_2016May13_08_52_30.log (no errors)

Patch 22291127 apply (pdb PDB$SEED): SUCCESS

  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_CDBDEV_PDBSEED_2016May13_08_52_51.log (no errors)

Patch 22291127 apply (pdb PDBDEV): SUCCESS

  logfile: /home/oracle/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_CDBDEV_PDBDEV_2016May13_08_52_50.log (no errors)

SQL Patching tool complete on Fri May 13 08:53:04 2016

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

The datapatch will run the SQL load files in CDB and all PDB’s as well.

So that’s it. We have patched the Oracle 12c with CDB and PDB to the latest PSU.

Install and Configure Oracle Application Express Apex 5.0, 4.2.6, 4.2.x with Embedded PL/SQL Gateway

1. Download Software as Zip file from OTN
http://www.oracle.com/technetwork/developer-
tools/apex/downloads/index.html

2.Unzip apex_5.0_en.zip Software to ORACLE_HOME directory,
it will create a directory called Apex

3. Login to SQLPLUS as SYSDBA in database where you will
create the Apex instance. Create a tablespace called
“apex” for the Apex 5 installation

$ cd $ORACLE_HOME/apex

SELECT dbms_xdb.gethttpport FROM dual;

EXEC DBMS_XDB.SETHTTPPORT(0);

4. — Full development environment. Run apexins.sql

@apexins.sql tablespace_apex tablespace_files
tablespace_temp images

tablespace_apex is the name of the tablespace for the
Oracle Application Express application user.

tablespace_files is the name of the tablespace for the
Oracle Application Express files user.

tablespace_temp is the name of the temporary tablespace or
tablespace group.

images is the virtual directory for Oracle Application
Express images. To support future Oracle Application
Express upgrades, define the virtual image directory as
/i/.

@apexins.sql apex apex temp /i/

images is the virtual directory for Oracle Application
Express images. To support future Oracle Application
Express upgrades, define the virtual image directory as
/i/.

@apxldimg.sql APEX_HOME

APEX_HOME is the directory you specified when unzipping
the file, in our case it is the ORACLE_HOME directory

SQL> @apxldimg.sql /u02/app/oracle/product/11.2.0/dbhome_1

5. Run apxchpwd.sql to create Admin account and Reset the
password —
@apxchpwd.sql

Alter/Unlock the anonymous account


alter user anonymous account unlock;

If you get this error ORA-20001: Password validation
failed.

Then there is a bug in Apex 5 for the password validation

–Execute Below Query and Generate DATA as INSERT
statements–

select t.name, t.value--, t.pref_desc
from apex_040100.wwv_flow_platform_prefs t
where t.name in ('ACCOUNT_LIFETIME_DAYS'
,'PASSWORD_ALPHA_CHARACTERS'
,'PASSWORD_PUNCTUATION_CHARACTERS'
,'STRONG_SITE_ADMIN_PASSWORD'
,'USERNAME_VALIDATION')

— Insert Data into APEX_050000 schema and Check again the
values —

select t.name, t.value--, t.pref_desc
from apex_050000.wwv_flow_platform_prefs t
where t.name in ('ACCOUNT_LIFETIME_DAYS'
,'PASSWORD_ALPHA_CHARACTERS'
,'PASSWORD_PUNCTUATION_CHARACTERS'
,'STRONG_SITE_ADMIN_PASSWORD'
,'USERNAME_VALIDATION')

—————————————————-
Configuring the PL/SQL Embedded Gateway
—————————————————-

6. Running the apex_epg_config.sql Configuration Script to
the base directory where Apex software was unzipped. In
our case it is the DB home directory

@apex_epg_config.sql
/u02/app/oracle/product/11.2.0/dbhome_1

7. Conifuringthe Oracle XML DB Protocol Server Port

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(8080);

8. Enabling Network Services in Oracle Database 11g or
Later

By default, the ability to interact with network services
is disabled in Oracle Database 11g Release 1 or 2 or
later. Therefore, if you are running Oracle Application
Express with Oracle Database 11g Release 1 or 2 or later,
you must use the new DBMS_NETWORK_ACL_ADMIN package to
grant connect privileges to any host for the APEX_050000
database user. Failing to grant these privileges

Grant connect privileges to any host for the APEX_050000
database user

— for Apex 5 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_050000
-- the "connect" privilege if APEX_050000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_050000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_050000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

— For Apex 4 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200
-- does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Now access the URL for Apex, becuase we configured the
Embedded PL/SQL Gateway, go to:

http://hostname:port/apex/apex_admin

In case you get below error then check if port 8080 is
taken by some other service on the server, change the port
to a new one and restart the database.

“APEX.ERROR.ERROR_PAGE.UNHANDLED_ERROR”

EXEC DBMS_XDB.SETHTTPPORT(8898);

alter system register;

srvctl stop database -d dbname

srvctl start database -d dbname

RLWRAP SQL*PLUS to have BASH like Functionality in SQLPLUS

So you are an Oracle DBA and have got used to the luxury of BASH shell in nix systems. Where you can scroll up and down and commands from a time long forgotten magically appear. Wouldn’t it be wonderful to have a similar thing for good ole sqlplus. Well here’s how you do it, via an excellent utility on linux called rlwrap.

1. Download rlwrap from this location : http://utopia.knoware.nl/~hlub/uck/rlwrap/

Incase above URL is not available, you can refer the steps in this link to install rlwrap

https://www.pythian.com/blog/rlwrap-utility-dba/

2. untar to a directory and compile the utility

tar xvf rlwrap-0.42.tar.gz

cd rlwrap-0.42

./configure

make

make install

3. Login with Oracle user and add the below alias to your bash profile

alias sqld=’rlwrap sqlplus “/as sysdba”‘

4. Login with sqld command (which is an alias by wrapping sqlplus using rlwrap, you can do this manually as well)

And happily scroll up and down through the sqlplus command line.

 For manually doing it

$ rlwrap sqlplus

Enjoy !

OPatch for 11gR2(11.2.0.3) RAC Database+Grid on Sparc 64-Bit Solaris10

1. Install Latest OPatch clients from support.oracle.com

– As Grid User —

unzip p6880880_112000_SOLARIS64.zip -d $ORACLE_HOME

##Replace All
– As Oracle User —

unzip p6880880_112000_SOLARIS64.zip -d $ORACLE_HOME
##Replace All

 
2. Set PATH variable for OPatch for both Grid and Oracle user profiles

–oracle–

vi .profile

PATH=/oracle/app/oracle/product/11.2.0/dbhome_1/bin:/oracle/app/oracle/product/11.2.0/dbhome_1/OPatch:$PATH

 

–grid–

vi .profile

PATH=/grid/app/11.2.0/grid/bin:/grid/app/11.2.0/grid/OPatch/:$PATH

 

3. Validate Oracle OPatch inventory

— grid

opatch lsinventory -detail -oh $ORACLE_HOME
— Oracle

opatch lsinventory -detail -oh $ORACLE_HOME

 

4. Unzip the patch files as grid user in a shared location between both nodes

unzip p14275572_112030_<platform>.zip
Determine One-off Patch Conflict Detection and Resolution

$ opatch prereq CheckConflictAgainstOHWithDetail -ph <UNZIPPED_PATCH_LOCATION>/14275605 –oh $GRID_HOME
$ opatch prereq CheckConflictAgainstOHWithDetail -ph <UNZIPPED_PATCH_LOCATION>/14275605 –oh $DB_HOME
$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/14275572 –oh $GRID_HOME
$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/14275572/custom/server/14275572 –oh $DB_HOM

 

5. OPatching where GI Home Is Not Shared and ACFS File System Is Not Configured and Database Homes Are Not Shared

As Grid user create ocm.rsp file

cd /grid/app/11.2.0/grid/OPatch/ocm/bin

./emocmrsp

 

As root user

export PATH=$PATH:/grid/app/11.2.0/grid/OPatch
opatch auto /var/tmp/ -ocmrf /grid/app/11.2.0/grid/OPatch/ocm/bin/ocm.rsp

 

Executing /grid/app/11.2.0/grid/perl/bin/perl /grid/app/11.2.0/grid/OPatch/crs/patch11203.pl -patchdir /var -patchn tmp -ocmrf /grid/app/11.2.0/grid/OPatch/ocm/bin/ocm.rsp -paramfile /grid/app/11.2.0/grid/crs/install/crsconfig_params
/grid/app/11.2.0/grid/crs/install/crsconfig_params
/grid/app/11.2.0/grid/crs/install/s_crsconfig_defs

This is the main log file: /grid/app/11.2.0/grid/cfgtoollogs/opatchauto2012-12-02_13-56-59.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /grid/app/11.2.0/grid/cfgtoollogs/opatchauto2012-12-02_13-56-59.report.log

2012-12-02 13:56:59: Starting Clusterware Patch Setup
Using configuration parameter file: /grid/app/11.2.0/grid/crs/install/crsconfig_params
patch /var/tmp/14275572/custom/server/14275572 apply successful for home /oracle/app/oracle/product/11.2.0/dbhome_1
patch /var/tmp/14275605 apply successful for home /oracle/app/oracle/product/11.2.0/dbhome_1
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘csftest-1’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.OCR.dg’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.registry.acfs’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.FRA.dg’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.cvu’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.csftest-1.vip’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.csftest-1.vip’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.FRA.dg’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.registry.acfs’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.oc4j’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.OCR.dg’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.asm’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.ons’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.net1.network’ on ‘csftest-1’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘csftest-1’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘csftest-1’
CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.evmd’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘csftest-1’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.cssd’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.gipcd’ on ‘csftest-1’ succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘csftest-1’
CRS-2677: Stop of ‘ora.gpnpd’ on ‘csftest-1’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘csftest-1’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
The path “/grid/app/11.2.0/grid/bin/acfsrepl_initializer” does not exist
The path “/grid/app/11.2.0/grid/bin/acfsrepl_apply” does not exist
The path “/grid/app/11.2.0/grid/bin/acfsrepl_apply.bin” does not exist
Successfully unlock /grid/app/11.2.0/grid

patch /var/tmp/14275572 apply successful for home /grid/app/11.2.0/grid
patch /var/tmp/14275605 apply successful for home /grid/app/11.2.0/grid
CRS-4123: Oracle High Availability Services has been started.

 

 
6. Patch Post-Installation Instructions
6.1 Apply conflict resolution patches if any

6.2 Load Modified SQl files into the database

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT
Check for Errors in Log –> /oracle/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/catbundle/catbundle_PSU_SWX_APPLY_2012Dec02_14_57_04.log
6.3 If using RMAN catalog then Upgrade Oracle Recovery Manager Catalog

$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
6.4 Patch Post-Installation Instructions for Databases Created or Upgraded after Installation of Patch in the Oracle Home

These instructions are for a database that is created or upgraded after the installation of the patch.

You must execute the steps 6.2 “Loading Modified SQL Files into the Database” for any new database only if it was created by any of the following methods:

Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)

Using a script that was created by DBCA that creates a database from a sample database

There are no actions required for databases that have been upgraded.

 

7. Check if patches applied and compare the output with the one you ran before
$ opatch lsinventory

 

8. Rollback OPatch for both Non-Shared GI and DBMS homes

As Grid user

opatch auto /var/tmp/ -rollback -ocmrf /grid/app/11.2.0/grid/OPatch/ocm/bin/ocm.rsp
For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql
SQL> QUIT
Check the log file for any errors. The log file is found in $ORACLE_BASE/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS.
Ensure that you verify the Oracle Inventory and compare the output with the one you ran before
$ opatch lsinventory

 

 

Schema Replication using Oracle Goldengate 11g (Installation & Configuration)

GoldenGate is touted by Oracle as its future for data replication. It will slowly phase out Oracle Streams and will be more closely coupled with Oracle products. Golden Gate is relatively simpler to use and monitor than Oracle streams. Below I will demonstrate how to configure ASYNC Schema Replication one side using Oracle GoldenGate 11g on a Oracle 11gR2 database.

SID of Source : IVRPROD
SID of Target : IVRDR
Source Server name : ivrhodb
Target Server name : ivrdrdb
Source Schema name : IVR
Target Schema name : IVR
Tables : All tables

1. Tell database to log more (supplemental logging ~10% of more redo
writing) on source database only.

alter database add supplemental log data;

2. GoldenGate user creation on source and target side (if on Unix, on windows use same user in ora_dba group with which you installed Oracle)

useradd -d /data/home/gguser gguser

3. Copy the downloaded installation zip file ogg112101_ggs_Windows_x64_ora11g_64bit.zip) to a directory and unzip it.

4. Set the Environment variables in .bash_profile of user gguser on
both source and target database servers.

— source —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRPROD
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

— Detination —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRDR
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

5. create the tablespace for both databases (source + target ) like:

Create tablespace ggate datafile ‘/u01/oradata/ggate01.dbf’ size 1000M autoextend on next 100M;

6. GoldenGate schema creation into source and target database

create user ggate identified by ggate default tablespace ggate
temporary tablespace TEMP profile DEFAULT;
alter user ggate QUOTA UNLIMITED ON ggate;
grant CONNECT to ggate;
grant CREATE SESSION to ggate;
grant RESOURCE to ggate;
grant SELECT ANY TABLE to ggate;
grant ALTER SESSION to ggate;
grant CREATE TABLE to ggate;
grant FLASHBACK ANY TABLE to ggate;
grant SELECT ANY DICTIONARY to ggate;
grant DBA to ggate;

logis as sysdba on both source and target run following steps…

SQL> grant execute on utl_file to ggate;

SQL> @C:\ggs_Windows_x64_ora11g_64bit/marker_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/ddl_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

And more important grant on dictionay views otherwise extract will keep on abending on both source and target schema (in our case IVR and IVR on both source and target. Schema name is same on both databases)

— on source —

SQL> grant select any dictionary to ivr;

— on target —

SQL> grant select any dictionary to ivr;

7. Go to installation directory and execute ./ggsci to see if your able to run the golden gate console on both source and target

8.
// Name of schema to be replicated will be IVR residing on IVRPROD database, it will be replicated to same schema name in IVRDR database.Make sure the characterset is same on Source and Target databases. //

— Source Configuration —

GGSCI (ivrhodb) 1> create subdirs
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> edit params mgr

port 7809
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrhodb) 1> start mgr
GGSCI (ivrhodb) 1> info all

If manager is running then manager configuration is ok.

GGSCI (ivrhodb) 1> dblogin userid ggate, password ggate
GGSCI (ivrhodb) 1> list tables ivr.*

If you see the list of tables then your configuration is good and you can continue:

GGSCI (ivrhodb) 1> add trandata IVR.*
GGSCI (ivrhodb) 1> info trandata IVR.*

Lets create and configure now the extractor process, edit add these files and save:

GGSCI (ivrhodb) 1> edit params ext1

extract ext1
userid ggate, password ggate
discardfile ./dirrpt/ext1.dsc,purge
reportcount every 15 minutes, rate
exttrail ./dirdat/t1
table IVR.*;

GGSCI (ivrhodb) 1> add extract ext1, tranlog, begin now
GGSCI (ivrhodb) 1> add exttrail ./dirdat/t1, extract ext1, megabytes 100
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> start ext1

Extractor created, now we create the data pump process:

GGSCI (ivrhodb) 1> edit params dpe1

extract dpe1
passthru
rmthost ivrdrdb, mgrport 7809
rmttrail ./dirdat/t1
table IVR.*;

//rmthost is the target server name or ip address. Ensure port 7809 is open on target server

GGSCI (ivrhodb) 1> add extract dpe1, exttrailsource ./dirdat/t1
GGSCI (ivrhodb) 1> add rmttrail ./dirdat/t1, extract dpe1, megabytes 100
GGSCI (ivrhodb) 1> status all

We are not going to start pump process yet, because destination is not configured.

9.
— Destination Configuration —
Target side on your ggsci prompt:

GGSCI (ivrdrdb) 1> create subdirs
GGSCI (ivrdrdb) 1> edit params mgr

port 7809
dynamicportlist 7900-7950
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrdrdb) 1> start mgr
GGSCI (ivrdrdb) 1> status all
GGSCI (ivrdrdb) 1> view report mgr

Manager created and verified, now we are going to create replicat processes. We’ll create the replicat with the 5 minutes lag, purposely for replication to be behind 5 minutes :

GGSCI (ivrdrdb) 1> edit params rep1

replicat rep1
userid ggate, password ggate
discardfile ./dirrpt/rep1.dsc, purge
assumetargetdefs
reportcount every 15 minutes, rate
batchsql
deferapplyinterval 5 mins
map IVR.*, target IVR.*;

GGSCI (ivrdrdb) 1> add replicat rep1, exttrail ./dirdat/t1
GGSCI (ivrdrdb) 1> ADD CHECKPOINTTABLE GGATE.CHKPTAB

That should be all about configuration , now last two steps, lets start data pump process on source side and replicat process on destination.

— on source —
GGSCI (ivrhodb) 1> start dpe1

— on target —
GGSCI (ivrdrdb) 1> start rep1

Now just check statuses on both sides with “status all” command. If everything is running and nothing abandoned you can check event logs on both sides and activity:

stats ext1, totalsonly *, reportrate sec
stats rep1, totalsonly *, reportrate sec
send rep1, status

And we have configured Oracle Golden Gate installation and setup forone asynchronous data replication.