Tag Archives: 11gR2

Run #em12c on #db12c? – Discussion from Oracle Open World (MOS Note: 1920632.1)

Ok Folks, I’ve been here are Oracle Open World for a few days now.  In that time, I’ve had numerous conversations about running Oracle Enterprise Manager 12c on Oracle Database 12c.  I will be honest and say that I’ve enjoyed these conversations; however, after about the fourth time I decided I need to write a quick post on the explanation discussed in these conversations.  

Early this year (August) I wrote a post about the what came out of the OEM CAB in May 2014 and how to get OEM 12c to work on DB12c.  The concept of running OEM 12c on DB12c, pluggable or not, have many people excited and looking forward to configuring OEM to do that very configuration.  Heck, I’ve even installed it for a few customers in that configuration (non-PDB).  So I’m a bit sad in having to say this:  ORACLE DATABASE 12c SUPPORT FOR…

View original post 211 more words

Advertisements

ORA-06512 ‘DBSNMP.BSLN_INTERNAL’

Periodic Stats Gathering Fails in 11gR2 Oracle Database – found in alert.log: ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073

The Fix:

SQL> @?/rdbms/admin/catnsnmp.sql

SQL> @?/rdbms/admin/catsnmp.sql

Dbhk's Blog

Symptoms

1.  BSLN_MAINTAIN_STATS_JOB failed at every SUNDAY

2.  Alert messages got the following

Errors in file /opt/oracle/diag/rdbms/vstbpro/vstbpro1/trace/vstbpro1_j000_7796.trc:
ORA-12012: error on auto execute of job 11762
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073

3. Details of the trace file

Trace file /opt/oracle/diag/rdbms/vstbpro/vstbpro1/trace/vstbpro1_j000_7796.trc
Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option
ORACLE_HOME = /opt/oracle/product/11g
System name:    SunOS
Node name:      wvpdb09
Release:        5.10
Version:        Generic_138888-01
Machine:        sun4u
Instance name: vstbpro1
Redo thread mounted by this instance: 1
Oracle process number: 73
Unix process pid: 7796, image: oracle@wvpdb09 (J000)

*** 2010-04-11 15:00:04.639
*** SESSION ID:(1041.32020) 2010-04-11 15:00:04.639
*** CLIENT ID:() 2010-04-11 15:00:04.639
*** SERVICE NAME:(SYS$USERS) 2010-04-11 15:00:04.639
*** MODULE NAME:(DBMS_SCHEDULER) 2010-04-11 15:00:04.639
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2010-04-11 15:00:04.639

ORA-12012: error on auto execute of job 11762
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Cause

Table DBSNMP.BSLN_BASELINES contains…

View original post 35 more words

When Your Oracle DataGuarded Database is Crashed (unintentionall)

It happens:  You have a 4-node DataGuarded Oracle 11gR2 database and your System Administrators need to take the boxes down for maintenance. But they don’t know that it’s protected under DataGuard, with Fast-start failover enabled (which automatically performs (via DG Observer) the switchover from Primary to Standby, switches roles, reconfigures listeners, and tries to keep everything 99.99995% available.)  So they use good old:

sqlplus “/ as sysdba”

SQL> shutdown immediate;

Database closed.

And now you need to start it back up.  But when your admins get to their familiar old prompt it burps out:

SQL> startup

Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

Now there are a number of blogs which will walk you through the tedious steps of recovering this condition manually via DGMGRL and multiple control file and standby redo log restores to all the targets. But it was 1:00A local time and I didn’t want to spend the rest of the night crawling through these trying to get this beast back on its feet.

So, I returned to the never-ending exploratory world of what is contained in Oracle Enterprise Manager 12cR3 (12.1.0.3.0)  And (tada sound), OEM can handle it all for you relatively automatically.

To get your Primary DB back on-line (to avoid the complaints from the users who have been disconnected)

DGMGRL> connect SYS/<pwd>@<primaryDGDBtarget>

DGMGRL> disable fast_start failover force;

SQL> startup

Database opened.

Now that the Primary is back open and accessible, return to OEM OMS and visit:

Targets -> Database -> (your Primary DB)

Availability -> Data Guard Administration

There’s a text link at the bottom which reads

Additional Administration:

Verify Configuration

That’s your ticket back to green-arrows and healthy DG in 90% of the situations.  It will perform health-checks throughout your configuration, re-created standby redo logs, re-sychronizing disconnected standby databases, shipping archive logs whereever they are needed, repairing disrupted communications, restoring fast-start failover observers, and whatever else you have.

The other alternative is to simply Remove the existing failed Standby database (select, then [Remove] the existing standby database; then visit its host and delete the existing datafiles, redo, archive and tempfiles (you do not have to touch the TNSNAMES or Listener configurations – that will be re-created for you); then use [Add Standby Database] on the same screen to restore functionality (this is good as an if-all-else-fails, so this method of recovery).

Happy Guarding Data!

Changing ORACLE_BASE in Oracle 11gR2 (11.2.0.3.4) Database

During a routine installation, I managed to mis-type the filesystem path for the ORACLE_BASE during execution of the Oracle Universal Installer (./runInstaller).

[nb: ORACLE_BASE by normal definition should be a standard starting filesystem mountpoint under which all other instance-specific data/files for this ORACLE_HOME would be found. Typically contains your diagnostic logs, Fast Recovery Area files, all configuration tool logs, and any Flashback checkpoint saves.

$ORACLE_BASE/diag/.

./asm
./clients
./crs
./diagtool
./lsnrctl
./netcman
./ofm
./rdbms
./tnslsnr

$ORACLE_BASE/admin/<SID>/.

./adump          # Audit data./dpdump       # Data Pump related
./pfile               # init.ora files generated by config tools

–end of note]

Since the service account (oracle) had filesystem permissions to do so, it went ahead and put all of the flashback recovery, diagnostics, and audit trail files (and my backup controlfiles) into the incorrectly named directory.  The database itself was running, but how to correct this without starting all over again?

Start with backing up your init.ora configuration from spfile to pfile format:

SQL> create pfile from spfile;

File created.

(this defaults to $ORACLE_HOME/dbs/init.ora)

Then shutdown services:

SQL> shutdown immediate;

Database closed.

SQL> exit;

#oracle> lsnrctl stop

Now go find all the places you’ll need to modify/correct the ORACLE_BASE filepath declarations:

$ORACLE_HOME/dbs/init<SID>.ora

$ORACLE_HOME/network/admin (or $TNS_ADMIN)

  • listener.ora: ADR_BASE_LISTENER
  • sqlnet.ora: ADR_BASE
    (Note: these two are auto-generated using netca – the Oracle Net Configuration Assistant, if you execute that after making the database and environment-side changes.

$ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml : ORACLE_BASE

All finished editing? Start everything back up:

#oracle> lsnrctl start

SQL> startup pfile=?/dbs/init<SID>.ora  — to use revised settings

SQL> create spfile from pfile;       — copy new settings to spfile

SQL> shutdown immediate;

SQL> startup;                                — uses spfile instead

Now future installs using dbca, dbua and netca should recognize your defined/modified/corrected ORACLE_BASE.