Chowdari's Oracle DBA Blog / Weblog: August 2014

Friday, August 29, 2014

CRS-4639 CRS-4124 Oracle High Availability Services startup failed in 11gR2 RAC

Today I have faced an issue with cluster OHASD service has not started automatically when server started. Then I tried to start cluster manually I received "CRS-4124: Oracle High Availability Services startup failed" error.

       
[oracle@rac1 ~]$ cd /u01/app/11.2.0/grid/bin
[oracle@rac1 bin]$ ./crsctl check crs
CRS-4639: Could not contact Oracle High Availability Services
[oracle@rac1 bin]$ 
[oracle@rac1 bin]$ su - root
Password: 
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin
[root@rac1 bin]# ./crsctl start crs
CRS-4124: Oracle High Availability Services startup failed
CRS-4000: Command Start failed, or completed with errors
[root@rac1 bin]#

Then I have verified cluster Oracle High Availability auto start-up is configured or not?

       
[root@rac1 bin]# ./crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@rac1 bin]#

Oracle High Availability Services auto-start is enabled. Then verified OLR accessibility on local node. Viewed ohasd.log and instance alert log files. Found below error messages and found as OHASD server was not running.

       
2014-08-29 18:15:18.620
[client(25949)]CRS-2302:Cannot get GPnP profile. Error CLSGPNP_NO_DAEMON (GPNPD daemon is not running).
2014-08-29 18:15:18.622
[client(25949)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/rac1/client/emcrsp.log.

Parallely I have checked with infrastructure team about this server. They identified some local script has hang. Now I have started OHASD service in background and it's able to start the CRS.

       
[root@rac1 ~]# nohup /etc/init.d/init.ohasd run &
[1] 20959
[root@rac1 ~]#

[root@rac1 bin]# ./crsctl start crs
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[root@rac1 bin]# 
[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 bin]#

Custer has started.

Go with below of some useful 11gR2 RAC high alert issues :-)

Listener INTERMEDIATE status with "Not All Endpoints Registered" in 11gR2 RAC
ORA-04031 unable to allocate 3896 bytes of shared memory in ASM Instance

Thats It. Hope this will help you... :)

Best Regards,
Chowdari.

Monday, August 25, 2014

ORA-04031 unable to allocate 3896 bytes of shared memory in ASM Instance

Today I have faced CHECK TIMED OUT and INTERMEDIATE state errors in listener and ASM disk group services. Then I have started investigation we found "ORA-04031: unable to allocate 3896 bytes of shared memory" error in ASM alert log file.

Sat Aug 23 11:23:00 2014
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_ora_26804658.trc (incident=19712):
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT value FROM v$paramete...","sga heap(1,0)","kglsim object batch")
Use ADRCI or Support Workbench to package the incident.

bash-4.2$ crsctl stat res -t
---------------------------------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS   Local Resources
---------------------------------------------------------------------------------------------------------
ora.DATA.dg
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE INTERMEDIATE PRODDB2 CHECK TIMED OUT
ora.FRA.dg
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE INTERMEDIATE PRODDB2 CHECK TIMED OUT
ora.LISTENER.lsnr
ONLINE ONLINE PRODDB1
ONLINE INTERMEDIATE PRODDB2 Not All Endpoints Registered
ora.OCRVD.dg
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE INTERMEDIATE PRODDB2 CHECK TIMED OUT
ora.asm
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE ONLINE PRODDB2 Started
ora.gsd
OFFLINE OFFLINE PRODDB1
OFFLINE OFFLINE PRODDB2
ora.net1.network
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.ons
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.registry.acfs
ONLINE ONLINE PRODDB1
ONLINE UNKNOWN PRODDB2
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE PRODDB1
ora.cvu
1 ONLINE ONLINE PRODDB1
ora.PRODDB1.vip
1 ONLINE ONLINE PRODDB1
ora.PRODDB2.vip
1 ONLINE ONLINE PRODDB2
ora.ehishyd.db
1 ONLINE ONLINE PRODDB1 Open
2 ONLINE ONLINE PRODDB2 Open
ora.oc4j
1 ONLINE ONLINE PRODDB2
ora.scan1.vip
1 ONLINE ONLINE PRODDB1
bash-4.2$

bash-4.2$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE INTERMEDIATE PRODDB2 CHECK TIMED OUT
ora.FRA.dg
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE INTERMEDIATE PRODDB2 CHECK TIMED OUT
ora.LISTENER.lsnr
ONLINE ONLINE PRODDB1
ONLINE OFFLINE PRODDB2
ora.OCRVD.dg
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE INTERMEDIATE PRODDB2 CHECK TIMED OUT
ora.asm
ONLINE INTERMEDIATE PRODDB1 CHECK TIMED OUT
ONLINE ONLINE PRODDB2 Started
ora.gsd
OFFLINE OFFLINE PRODDB1
OFFLINE OFFLINE PRODDB2
ora.net1.network
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.ons
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.registry.acfs
ONLINE ONLINE PRODDB1
ONLINE UNKNOWN PRODDB2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE PRODDB1
ora.cvu
1 ONLINE ONLINE PRODDB1
ora.PRODDB1.vip
1 ONLINE ONLINE PRODDB1
ora.PRODDB2.vip
1 ONLINE ONLINE PRODDB2
ora.ehishyd.db
1 ONLINE ONLINE PRODDB1 Open
2 ONLINE ONLINE PRODDB2 Open
ora.oc4j
1 ONLINE ONLINE PRODDB2
ora.scan1.vip
1 ONLINE ONLINE PRODDB1
bash-4.2$

I came to know that its an ASM instance memory related issue. Checked below memory parameters in ASM instance.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 0
SQL>
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 272M
memory_target big integer 272M
SQL>

Automatic memory management is enabled by default on an Oracle ASM instance and oracle recommends that to use Automatic Memory Management (AMM) for ASM. In Oracle 11gR2 the default value used for MEMORY_TARGET is 272 MB. Here SGA_TARGET is 256MB and 16MB is for PGA_TARGET.

Based on "ORA-04031: unable to allocate 3896 bytes of shared memory" error I get to know that ASM memory parameters in ASM instances are too low and it results in ORA-04031 in the ASM instance.

Based on Oracle Doc ID 437924.1, I have incresed memory_max_target to 4096M and memory_target to 1536M. Then I restarted the ASM instances.

SQL> alter system set memory_max_target=4096M scope=spfile;
SQL> alter system set memory_target=1536M scope=spfile;

After changing above parameters the services came to ONLINE state.

bash-4.2$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.FRA.dg
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.LISTENER.lsnr
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.OCRVD.dg
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.asm
ONLINE ONLINE PRODDB1 Started
ONLINE ONLINE PRODDB2 Started
ora.gsd
OFFLINE OFFLINE PRODDB1
OFFLINE OFFLINE PRODDB2
ora.net1.network
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.ons
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
ora.registry.acfs
ONLINE ONLINE PRODDB1
ONLINE ONLINE PRODDB2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE PRODDB2
ora.cvu
1 ONLINE ONLINE PRODDB2
ora.PRODDB1.vip
1 ONLINE ONLINE PRODDB1
ora.PRODDB2.vip
1 ONLINE ONLINE PRODDB2
ora.ehishyd.db
1 ONLINE ONLINE PRODDB1 Open
2 ONLINE ONLINE PRODDB2 Open
ora.oc4j
1 ONLINE ONLINE PRODDB2
ora.scan1.vip
1 ONLINE ONLINE PRODDB2
bash-4.2$

Hope this will help you.. :)

Best Regards,
Chowdari.

Tuesday, August 12, 2014

Data Guard Physical Standby Database - Part2

Read Data Guard Physical Standby Database - Part1

Here we will continue, setup and verify physical standby database, configuring the data guard broker, setup the Fast Start failover and the Data Guard Observer.

Soon I will post here

Creating Data Guard Physical Standby Database - Part1

Here I will cover the below topics.

Data guard introduction and Architecture
Data Guard Modes
Environment I will use
Implementation glossary
Prepare primary and physical Standby Environments
Create and verify the physical standby database
Active-Standby database & Real-time query
Broker configuration
Set up Fast Start failover and Data Guard Observer
RMAN backups to the standby database
Open standby database in the read-write mode for testing new functionality in the application.

Introduction:

Oracle Data Guard is one of the software solutions provided by Oracle Corporation to maximize the high availability of Oracle databases. Oracle Data Guard maintains one or maximum up to thirty associated standby databases (up to 30 in 11g and up to 9 in 10g) as alternatives to the primary production database. These standby databases are maintained as transaction consistent copies of the production database.

Architecture:

A standby database can be

Physical standby database - A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data, received from the primary database and applies the redo to the physical standby database.

Logical standby database - The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

Active Data Guard standby database - A Physical standby database that is open to read access with up-to-date data from the Primary database.

Snapshot Standby database - A fully read-write standby database that is created by converting a physical standby database into a Read-write snapshot standby database.

Data Guard Modes:

Oracle Data Guard can operate in 3 different modes.

Maximum Performance - This mode emphasizes primary database performance over data protection. It requires ASYNC redo transport so that the LGWR process never waits for acknowledgment from the standby database.

Maximum Availability - This mode emphasizes availability as its first priority and zero data loss protection as a very close second priority. It requires SYNC redo transport, thus primary database performance may be impacted by the amount of time required to receive an acknowledgment from the standby that redo has been written to disk.

Maximum Protection - As its name implies, this mode places utmost priority on data protection. It has the same requirements as Maximum Availability but the primary will not acknowledge a commit to the application unless it receives an acknowledgment from at least one standby database in the configuration that the data needed to recover that transaction is safely on disk. If the primary does not receive an acknowledgment from a SYNC standby database, it will stall and eventually abort, preventing any unprotected commits from occurring.

The environment I will use:

I have 3 VM with a version of Oracle Enterprise Linux 5 installed. Find the below link for install OEL 5 in Oracle VirtualBox.
Oracle Enterprise Linux 5.7 installation on Oracle VirtualBox
I will use the Oracle Database 11g Enterprise Edition Release 2, on the primary database and the standby database. Find the below link for installing oracle 11gR2 software and database creation.
Installation of Oracle Database 11gR2 on Oracle Enterprise Linux 5.7
Oracle software is installed and a database is already created on the primary site.
Oracle software is installed and the database will be created during this demonstration for the standby site.
On the third host, I have installed a version of Oracle database 11g Client. This is the Data Guard Observer host.
The Oracle Home is on the identical path on both nodes: the primary database and the standby database.
Primary database server name is primdb with a database name primdb.
Standby database server name is standb with a database name standb.
The Data Guard Observer server name is obser.

Implementation glossary:

Enable forced logging
Create a password file
Configure standby redo logs
Enable archivelog mode
Set up the primary and standby database initialization parameters
Configure the listener and tnsnames on both the nodes
Create a password file in both the nodes

Prepare primary and physical Standby Environments:

Primary Server: Step 1

Oracle Data Guard requires the primary database to be run in FORCE LOGGING mode. This means that statements using the NOLOGGING option will still generate redo information to maintain the Data Guard standby databases.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRIMDB    READ WRITE

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

SQL>

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed. Move the same Password file to the standby side and rename it with Standby SID.

$ orapwd force=y file=orapwPRIMDB password=sysprimdb

The standby log files are required to store redo received from the primary database. Standby redo logs must be equal or larger to the largest redo log file in the primary database. The standby redo log must have at least one more redo log group than the redo log on the primary database. If we have 'N' redo log groups then should have 'N+1' standby redo logs. It is recommended that the standby redo log is created on the primary database and propagated to the standby for any switch-over or failover operations.

SQL> select GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS, STATUS from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS  STATUS
    ---------     ---------- ---------- ---------- ---------- ---------- ----------------
         1               1                  19                     52428800        512          1  INACTIVE

         2               1                  20                     52428800        512          1  INACTIVE

         3              1                   21                     52428800        512          1  CURRENT


SQL>  
SQL> alter database add standby logfile size 50M;

Database altered.

SQL> 
SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

    GROUP# STATUS  TYPE     MEMBER
---------- ------- ------- ----------------------------------------------
         3         ONLINE   /u01/app/oracle/oradata/primdb/redo03.log

         2         ONLINE   /u01/app/oracle/oradata/primdb/redo02.log

         1         ONLINE   /u01/app/oracle/oradata/primdb/redo01.log

         4         STANDBY  /u01/app/oracle/oradata/primdb/redo04.log

         5         STANDBY  /u01/app/oracle/oradata/primdb/redo05.log

         6         STANDBY  /u01/app/oracle/oradata/primdb/redo06.log

         7         STANDBY  /u01/app/oracle/oradata/primdb/redo07.log

7 rows selected.

SQL>

Follow the steps for enabling archivelog mode - Enable and Disable Archive Log Mode

Next, set the LOG_ARCHIVE_CONFIG parameter. The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is primdb and the DB_UNIQUE_NAME of the standby database is standb. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is standb.

Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.

Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.

SQL> show parameter db_name

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_name                              string     primdb
SQL> show parameter db_unique_name

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_unique_name                       string     primdb
SQL> alter system set log_archive_config='dg_config=(primdb,standb)';

System altered.

SQL> alter system set log_archive_dest_2=
  2  'service=standb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=standb';

System altered.

SQL> alter system set standby_file_management=AUTO;  

System altered.

SQL> 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL> exit;

The listener on the primary site must be configured with a static service entry for the primary database. After modified the listener file restart the listener.

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

LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.98)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=primdb)
          (SID_NAME=primdb)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         )
        )
[oracle@primdb admin]$

Add below TNS entries in both primary and standby databases.

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

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primdb)
    )
  )

STANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standb)
    )

Create password file and copy it to standby side and rename to standby SID.

[oracle@primdb dbs]$ orapwd force=y file=orapwprimdb password=system
[oracle@primdb dbs]$ ls orapwprimdb 
orapwprimdb
[oracle@primdb dbs]$

Standby server step2:

Same version of oracle software is installed in standby server. The new standby database will have standb as the SID. The listener on the standby site must be configured with a static service entry for the standby database. After modified the listener file restart the listener.

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

LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.99)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=standb)
          (SID_NAME=standb)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         )
        )
[oracle@standb admin]$

Create required directories for the Fast recover area and the oradata directories.

[oracle@standb ~]$ mkdir -p $ORACLE_BASE/admin/standb/adump
[oracle@standb ~]# mkdir -p /u01/app/oracle/oradata/standb
[oracle@standb ~]# mkdir -p /u01/app/oracle/fast_recovery_area

In my next part of the documents, we will create and verify physical standby database, then configure the Broker, set up the Fast Start failover, and the Data Guard Observer.

Hope this will help you... :)

Best Regards,

Enable and Disable Archive Log Mode in Oracle 10g/11g

Enable Archive Log Mode:

Follow below steps to enable archive log mode on an Oracle 10g or 11g databases.

[oracle@primdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 20:57:58 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL>

The log mode is No Archivelog mode. The archive destination is USE_DB_RECOVERY_FILE_DEST. You can see by looking at the parameter RECOVERY_FILE_DEST.

SQL> show parameter recovery_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4122M
SQL>

By default, archive logs are written to the flash recovery area. If you want to change that location you can set the parameter LOG_ARCHIVE_DEST_n to the new location which we want to write archive logs.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/arch' scope = both;

System altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/arch
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL>

Now we shutdown the database and open the database in mount state.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 394266824 bytes
Database Buffers 130023424 bytes
Redo Buffers 7942144 bytes
Database mounted.
SQL>

Now you set archive log mode and open the database.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/arch
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL>

Now we can see archivelog mode is enabled.

Disable Archive Log Mode:

Follow below steps to disable archive log mode on an Oracle 10g or 11g databases.

[oracle@primdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 21:05:05 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/arch
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL>

Database is in Archivelog mode. We need to shutdown the database and open in mount state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 394266824 bytes
Database Buffers 130023424 bytes
Redo Buffers 7942144 bytes
Database mounted.
SQL>

Now disable archivelog mode and open the database.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/arch
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL>

Now you can see archivelog mode has been disabled.

Hope this will help you... :)

Best Regards,

XML Clob data type 'Value Error' while creating Materialized views

Today I have faced an issue like after creating materialized view we got <Value Error> for XML Clob data types.

I tried in many ways finally I get to know that its a bug in 11.2.0.3. while creating XML clob data type column in MView use the "xmltype.getclobval(COLUMN)" syntax.

Then I have dropped and recreted the materialized view with xmltype.getclobval option.

Followed below process:

DROP MATERIALIZED VIEW EMP.CHECKLISTMASTER;
CREATE MATERIALIZED VIEW EMP.CHECKLISTMASTER
REFRESH force on demand
AS
SELECT labsubdepartmentid,
processid,
checklistid,
xmltype.getclobval(checklistdetails),
status,
createddate,
createdby,
reasonforupdate,
updateddate,
updatedby,
showinreport,
checklistcode,
checklistname,
approvedby,
approved,
approvalremarks FROM EMP.CHECKLISTMASTER@DBLINK where rownum > 0;

Hope this will help you... :)

Best Regards,

Parameters used in the LOG_ARCHIVE_DEST_n

Brief descriptions of the parameters used in the LOG_ARCHIVE_DEST_n.

SERVICE – Specifies a valid service name for the standby database.

SYNC | ASYNC – Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM – Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

DB_UNIQUE_NAME – Unique name for the standby database

VALID_FOR(logifile,role) –Specifies that a specific log destination is valid only when the database is in a particular role.

Hope this will help you... :)

Best Regards,

Monday, August 11, 2014

ORA-31685 Error while import using IMPDP

Got a ORA-31685 error on schema import with remap option.

ORA-31685: Object type REF_CONSTRAINT:"DIGITAL"."FK_SEVERITY" failed due to insufficient privileges.
Failing sql is: ALTER TABLE "DIGITAL"."DIGITAL_ALLERGYGRPTRANSACTION" ADD CONSTRAINT "FK_SEVERITY" FOREIGN KEY ("SEVERITYID") REFERENCES "EHIS"."ALLERGYSEVERITYMASTER" ("ALLERGYSEVERITYID") ENABLE

I tried in many ways to create FOREIGN KEY but still its showing insufficient privileges error. Finally I get to know that its below grant issue.

grant REFERENCES on EHIS.ALLERGYSEVERITYMASTER to DIGITAL;

Hope this will help you.

Best regards,

Find CPU and Memory info in AIX Machine

Below commands to find memory information in AIX machines:

lparstat -i | grep Memory
lsconf | grep Memory

lparstat:

The lparstat command provides a report of LPAR (Reports logical partition) related information and utilization statistics.

Sample outputs:

bash-3.2$ lparstat -i | grep Memory
Online Memory : 65536 MB
Maximum Memory : 98304 MB
Minimum Memory : 36864 MB
Memory Mode : Dedicated
Total I/O Memory Entitlement : -
Variable Memory Capacity Weight : -
Memory Pool ID : -
Physical Memory in the Pool : -
Unallocated Variable Memory Capacity Weight: -
Unallocated I/O Memory entitlement : -
Memory Group ID of LPAR : -
Desired Memory : 65536 MB
Target Memory Expansion Factor : -
Target Memory Expansion Size : -
bash-3.2$

bash-3.2$ lsconf | grep Memory
Memory Size: 65536 MB
Good Memory Size: 65536 MB
+ mem0 Memory
bash-3.2$

Below commands to find CPU information in AIX machines:

lsconf | grep Processor
lscfg -vp | grep proc

Sample outputs:

bash-3.2$ lsconf | grep Processor
Processor Type: PowerPC_POWER7
Processor Implementation Mode: POWER 7
Processor Version: PV_7_Compat
Number Of Processors: 6
Processor Clock Speed: 3550 MHz
Model Implementation: Multiple Processor, PCI bus
+ proc0 Processor
+ proc4 Processor
+ proc8 Processor
+ proc12 Processor
+ proc16 Processor
+ proc20 Processor
bash-3.2$
bash-3.2$ lscfg -vp | grep proc
proc0 Processor
proc4 Processor
proc8 Processor
proc12 Processor
proc16 Processor
proc20 Processor
bash-3.2$

Hope this will help you... :)

Best Regards,

To find applied PSU patchsets in Oracle

Using SQL query:

$ sqlplus / as sysdba
sql> select substr(action_time, 1, 30) action_time,
substr(id, 1, 10) id,
substr(action, 1, 10) action,
substr(version, 1, 8) version,
substr(BUNDLE_SERIES, 1, 6) bundle,
substr(comments, 1, 20) comments
from registry$history;

Sample output:

ACTION_TIME ID ACTION   VERSION BUNDLE COMMENT
-------------------------------------- --- ------------ ------------- -----------  -------------
07-MAY-13 05.45.15.553375 PM 0 APPLY 11.2.0.3 PSU Patchset 11.2.0.2.0
07-AUG-14 05.02.57.091195 PM   7 APPLY 11.2.0.3 PSU PSU 11.2.0.3.7

OS command to find patch details: Using below command will find patch set information

bash-4.2$ cd $ORACLE_HOME/OPatch

bash-4.2$ ./opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'
16619892 16619892 Wed Jul 30 11:42:40 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPU
16056266 16056266 Wed Jul 30 11:42:16 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPU
16368108 16056266 Wed Jul 30 11:42:16 IST 2014 RUNNING OWMV1120.PLB IN PSU 112036 GIVING ORA-0095
14727310 14727310 Wed Jul 30 11:42:00 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPU
14275605 14275605 Wed Jul 30 11:41:07 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPU
13923374 13923374 Wed Jul 30 11:40:57 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES
13696216 13696216 Wed Jul 30 11:40:22 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES
13343438 13343438 Wed Jul 30 11:39:06 IST 2014 DATABASE PATCH SET UPDATE 11.2.0.3.1
12925041 16619898 Wed Jul 30 11:38:07 IST 2014 112024GIPSU OLOGGERD CORE DUMP AT 'CRFLOGDB.C
13079948 16619898 Wed Jul 30 11:38:07 IST 2014 CVU FILES NOT BEING PICKED UP FOR 11.2.0.2.4GIPSU
13348650 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.1
(INCLUDES DB PSU 11.2.0.3.1)
13396284 16619898 Wed Jul 30 11:38:07 IST 2014 11.2.0.3 GI PSU 1 HAS-CRS TRACKING BUG
13531373 16619898 Wed Jul 30 11:38:07 IST 2014 11.2.0.3.1GIPSU CVU CHECK FAIL FOR NETWORK
13540563 16619898 Wed Jul 30 11:38:07 IST 2014 MERGE REQUEST ON TOP OF 11.2.0.3.1PSU FOR BUGS
13569812 16619898 Wed Jul 30 11:38:07 IST 2014 11.2.0.3.1GIPSU CVU FAIL TO DETERMINE IF ORACLE
NETWORK HICCUP PSU4 HAS BEEN APP
13696251 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.2
(INCLUDES DB PSU 11.2.0.3.2)
13919095 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.3
(INCLUDES DB PSU 11.2.0.3.3)
14001941 16619898 Wed Jul 30 11:38:07 IST 2014 DOWNGRADE ISSUE FROM 11.2.0.3.1 TO 11.2.0.2 PSU4
14271305 16619898 Wed Jul 30 11:38:07 IST 2014 11.2.0.3.3GIPSU SIHA ORAAGENT.BIN ALWAYS CONSUME
14275572 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.4
(INCLUDES DB PSU 11.2.0.3.4)
15876003 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.5
16315641 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.6
16578706 16619898 Wed Jul 30 11:38:07 IST 2014 11.2.0.3.6GIPSU ROLLBACK WITH QOS FAILED IF
16619898 16619898 Wed Jul 30 11:38:07 IST 2014 GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.7
bash-4.2$

bash-4.2$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/11.2.0/db_home
Central Inventory : /u01/app/grid/oraInventory
from : /u01/app/oracle/11.2.0/db_home/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/11.2.0/db_home/cfgtoollogs/opatch/opatch2014-08-11_20-49-44PM_1.log

Lsinventory Output file location : /u01/app/oracle/11.2.0/db_home/cfgtoollogs/opatch/lsinv/lsinventory2014-08-11_20-49-44PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.

Interim patches (2) :

Patch 16619892 : applied on Wed Jul 30 11:42:40 IST 2014
Unique Patch ID: 16379600
Patch description: "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Created on 25 Jun 2013, 08:41:59 hrs PST8PDT
Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
12960925, 14088346, 14469008, 14038787, 14301592, 12834027, 16703112
12764337, 13772618, 14390252, 14263036, 10133521, 13561750, 13588248
12894807, 14841812, 13742435, 13773133, 13742434, 9659614, 12829021
13742433, 12585543, 13742438, 13742437, 14841558, 13561951, 13742436
12905058, 13503598, 12582664, 16344871, 13632717, 13098318, 13343438
12861463, 13913630, 13026410, 12849688, 13080778, 14467061, 16530565
13103913, 13737746, 13457582, 13742464, 12873183, 13645917, 13099577
13036331, 14548763, 12693626, 12656535, 13907462, 13624984, 12678920
12401111, 16742095, 13377816, 13855490, 13910420, 13338048, 12913474
12755116, 13489024, 13860201, 11063191, 11877623, 12964067, 16306019
13250244, 15905421, 12847466, 13791364, 12797765, 14613900, 13038684
13582702, 12617123, 14023636, 13814739, 12923168, 13035360, 13420224
16175381, 12646784, 12857027, 13340388, 14409183, 12583611, 14589750
12794305, 10350832, 14512189, 13584130, 14480674, 14480675, 12998795
13680405, 14480676, 12588744, 10242202, 13467683, 12594032, 13645875
16794238, 16794239, 13001379, 12797420, 13787482, 13257247, 14841409
13041324, 13366202, 13385346, 13011409, 13326736, 14664355, 13981051
16794243, 16794244, 16794241, 15862018, 16794242, 15862017, 15862016
16794240, 13466801, 14205448, 15862019, 15841373, 16619892, 12791981
14571027, 14063281, 14063280, 13362079, 13732226, 13719081, 13384182
15862020, 8547978, 15862021, 15862023, 15862022, 13496884, 14220725
15862024, 13958038, 9703627, 14053457, 9858539, 12940620, 13923995
12780098, 13354082, 14207163, 14062797, 14052474, 14062796, 14062795
13534412, 14062794, 13579992, 14062793, 16056266, 14062792, 12345082
13550185, 14176879, 16314469, 12880299, 16314468, 16314467, 16314466
12612118, 13059165, 13502183, 11071989, 15869211, 13593999, 9706792
16294378, 13397104, 13916709, 13524899, 13848402, 9761357, 13657605
16314470, 13936424, 14110275, 9873405, 12535346, 12974860, 14727310
12971775, 14472647, 14398795, 16014985, 13696216, 13493847, 13807411
16382353, 12917230, 13685544, 13857111, 12983611, 12938841, 14207317
13786142, 13499128, 14546673, 14127231, 11708510, 13699124, 14040433
16299830, 14546575, 13705338, 12662040, 13596521, 12658411, 16368108
14262913, 13724193, 12718090, 13790109, 12959852, 16382448, 14035825
12919564, 12780983, 12912137, 12950644, 13454210, 14258925, 13544396
13810393, 13903046, 13923374, 13063120, 13572659, 13370330, 12731940
16231699, 13427062, 9095696, 14275605, 12772404, 14459552, 13911821
13464002, 15853081, 13914613, 13528551, 13612575, 12620823, 14076523
14668670, 13358781, 13632809, 16694777, 13649031, 14263073, 16279401
14226599, 14138130, 13527323, 13804294, 13492735, 13015379, 12925089
12395918, 7509451, 13332439, 13718279, 13035804, 13812031, 6690853
13616375, 11715084, 14273397, 14644185, 13559697, 13448206, 13419660
14546638, 13399435, 13070939, 12976376, 12845115, 11840910, 13566938
13430938, 12748240, 12879027, 16279211, 10263668, 13476583, 13484963, 13554409

Patch 16619898 : applied on Wed Jul 30 11:38:07 IST 2014
Unique Patch ID: 16436752
Patch description: "Grid Infrastructure Patch Set Update : 11.2.0.3.7 (16742216)"
Created on 26 Jun 2013, 21:46:53 hrs PST8PDT
Bugs fixed:
16619898, 16315641, 15876003, 14275572, 13919095, 13696251, 13348650
12659561, 14305980, 14277586, 13987807, 14625969, 13825231, 12794268
13000491, 13498267, 15934834, 11675721, 14082976, 12771830, 14515980
14085018, 13943175, 14102704, 14171552, 12594616, 13879428, 12897902
12726222, 12829429, 13079948, 13090686, 12995950, 13251796, 13582411
12990582, 13857364, 15856610, 13082238, 12947871, 13256955, 13037709
14535011, 12878750, 14048512, 11772838, 13058611, 13001955, 13440962
13727853, 13425727, 12885323, 12870400, 14212634, 14407395, 13332363
13430626, 13811209, 12709476, 14168708, 14096821, 14626717, 13460353
13694885, 12857064, 12899169, 13111013, 12558569, 13323698, 10260842
13085732, 16555186, 10317921, 16077216, 13869978, 12914824, 13789135
12730342, 12950823, 13355963, 13531373, 14268365, 13776758, 12720728
13620816, 13023609, 16578706, 13024624, 13039908, 15911134, 13036424
13938166, 13011520, 13569812, 12758736, 13001901, 13077654, 13430715
13550689, 13806545, 13634583, 14271305, 12538907, 13947200, 12996428
13066371, 13483672, 12897651, 13540563, 12896850, 13241779, 12728585
12876314, 12925041, 12650672, 12398492, 12848480, 13652088, 16307750
12917897, 12975811, 13653178, 13371153, 14800989, 10114953, 14001941
11836951, 14179376, 12965049, 14773530, 12765467, 12950415, 15998768
13339443, 13965075, 16210540, 14307855, 12784559, 14242977, 13955385
12704789, 13745317, 13074261, 12971251, 13993634, 13523527, 13719731
13396284, 12639013, 12867511, 12959140, 14748254, 13912274, 12829917
12349553, 12849377, 12934171, 13843080, 14496536, 13899736, 13924431
12680491, 13334158, 10418841, 12832204, 13838047, 13002015, 14639430
15920201, 12791719

Rac system comprising of multiple nodes
Local node = eWHJKCdb5
Remote node = eWHJKCdb6
--------------------------------------------------------------------------------
OPatch succeeded.
bash-4.2$

Hope this will help you... :)

Best Regards,

Chowdari's Oracle DBA Blog / Weblog

Pages