Cleanup OWB repository and creating OWBSYS Schema with Repository Objects

To cleanup OWB repository and creating OWBSYS Schema with Repository Objects

To cleanup OWB repository

Go to $ORACLE_HOME/owb/UnifiedRepos


cd /u01/app/oracle/product/11.2.0.3/dbhome_1/owb/UnifiedRepos

[oracle@slcm02db01 UnifiedRepos]$ ls -lrt clean_owbsys.sql
-rw-r--r-- 1 oracle oinstall 163 Sep 26  2008 clean_owbsys.sql

2.Connect to owbsys and Stop Oracle Warehouse Builder Control Center service.
SQL> conn owbsys/owbsys
connected
SQL> @?/owb/rtp/sql/stop_service.sql


3.Connect to database and execute the clean_owbsys.sql script to drop all the objects in the existing OWBSYSas

[oracle@slcm02db01 UnifiedRepos]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 00:36:03 2013

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @clean_owbsys.sql
User dropped.


User dropped.


Role dropped.


Role dropped.


Role dropped.

SQL>

Creating an OWBSYS Schema with Repository Objects

1.connect to the database as sysdba and run the cat_owb.sql script to set up OWBSYS in a tablespace in your database.
For example, this command sets up OWBSYS in the USERS tablespace




SQL> @$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql USERS
Enter Tablespace Name for OWBSYS user:

Package created.

Package body created.

Create user OWBSYS with default tablespace USERS and alter tablespace to %OWB% tablespace
   CREATE USER OWBSYS IDENTIFIED BY OWBSYS DEFAULT TABLESPACE USERS

User created.

User OWBSYS has been created.

PL/SQL procedure successfully completed.


Grant succeeded.

Configuring the Control Center Service

If you are upgrading OWB 11.2 on an older Oracle Database release, or if you
integrating OWB with Oracle Business Intelligence Standard Edition , you must run the reset_owbcc_home.sql script to ensure that the
Control Center Service functions correctly. Steps are as below.

1.Connect to the database as sysdba and run the reset_owbcc_home.sql script to force the Control Center Service to
run from Oracle Warehouse Builder 11.2 installation.

SQL> @?/owb/UnifiedRepos/reset_owbcc_home.sql
Enter the full path of the Oracle home for the OWB Control Center install.
If you are installing in a Windows environment, please ensure that the case of the path exactly matches the Oracle install path including the drive letter.

OWB Control Center Home:
/u01/app/oracle/product/11.2.0.3/dbhome_1/owb

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>


Reference from in this article on Metalink Doc ID 434272.1

"How To Update Oracle Warehouse Builder After a Database Cloning (Doc ID 434272.1)"
 

Unlocking the OWBSYS and OWBSYS_AUDIT Accounts

connect to the database as sysdba and execute below commands.

alter user owbsys identified by password account unlock;
alter user owbsys_audit identified by password account unlock;

Setting the OWB Home for Remote Installations
==============================================
You must create a database directory OWB_REMOTE_ADMIN that is accessed by remote
installations, so they can read the server's OWB_HOME/owb/bin/admin directory.

Connect to the database as sysdba and set the OWB_REMOTE_HOME variable using script remote_owb_install.sql as below

SQL> @?/owb/UnifiedRepos/remote_owb_install.sql
Oracle Home for the Remote OWB software installation
/u01/app/oracle/product/11.2.0.3/dbhome_1

Directory created.


Grant succeeded.


Grant succeeded.

SQL>

Creating the First Workspace in the Repository

To use a repository, you must define an initial workspace, and the owner of that
workspace. The following instructions use the Repository Assistant.

To create a workspace:
1. At the command prompt, navigate to the UNIX bin directory:
cd $ORACLE_HOME/owb/bin/unix

2. Start the repository assistant.
 ./reposinst.sh

ORA-15064: communication failure with ASM instance after adding asm disks to existing diskgroup

ASM (ORA-600[kffMapMesgAcquire02]) and Database (ORA-15064: communication failure with ASM instance) [ID 1483294.1]"

Applies to:
Oracle Server - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
Symptoms

 4 node RAC - 112030 (no Interim patches) - Solaris

While attempting to a disk to an ASM disk group, ASM2 gave the following errors and was unresponsive (all queries hung):

alert_+ASM2.log:
~~~~~~~~~~~~~~~~~~
...
Mon Jun 18 12:23:18 2012
Errors in file
/vzwhome/oracle/app/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_ckpt_332.trc 
(incident=104134):
ORA-00600: internal error code, arguments: [kffMapMesgAcquire02], [], [], [], [], [], [], [], [], [], [], []
Mon Jun 18 12:23:18 2012
Dumping diagnostic data in directory=[cdmp_20120618122318], requested by
(instance=4, osid=5601 (LMD0)), summary=[incident=41683].
Incident details in:
/vzwhome/oracle/app/oracle/diag/asm/+asm/+ASM2/incident/incdir_104134/+ASM2_ck
pt_332_i104134.trc
Dumping diagnostic data in directory=[cdmp_20120618122324], requested by
(instance=2, osid=332 (CKPT)), summary=[incident=104134].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file
/vzwhome/oracle/app/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_ckpt_332.trc:
ORA-00600: internal error code, arguments: [kffMapMesgAcquire02], [], [], [],
[], [], [], [], [], [], [], []
CKPT (ospid: 332): terminating the instance due to error 469
Mon Jun 18 12:23:27 2012
ORA-1092 : opitsk aborting process
Mon Jun 18 12:23:27 2012
License high water mark = 24
Mon Jun 18 12:23:29 2012
System state dump requested by (instance=2, osid=332 (CKPT)),
summary=[abnormal instance termination].
System State dumped to trace file
/vzwhome/oracle/app/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_diag_304.trc
Mon Jun 18 12:23:30 2012
Instance terminated by CKPT, pid = 332
USER (ospid: 9254): terminating the instance
Instance terminated by USER, pid = 9254
...


All databases on node2 crashed:


alert_mydb2.log:
~~~~~~~~~~~~~~~~~~
...
Mon Jun 18 12:23:25 2012
NOTE: ASMB terminating
Errors in file /logs/diag/rdbms/mydb/mydb2/trace/mydb2_asmb_3979.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
...


Curiously, +ASM4's lmd0, reported ORA-4031 (unable to allocate 3768 bytes of shared memory ("shared pool","unknown object","sga heap (1,0)","ges enqueues") just before +ASM2 departed the cluster.


Cause



When disks are added into the diskgroup and rebalance is going on, the ASM instances need to use more DLM locks and thus consume more
SGA memory. With shared_pool_size being set to 128 MB, this is not enough and will cause the ORA-4031 in DLM daemons.
In ASM, we will hit [kffMapMesgAcquire02] when this happens.




Solution

When using Automatic Memory Management.  Set shared_pool_size, large_pool_size or any other instance's memory parameter,  to a higher value.
Please unset these and let AMM (via memory_target) manage the instance memory components automatically.

Check to see if you have enough share memory.

Reference : http://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm
"
Automatic Memory Management

Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The shared memory should be sized to be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on that computer.

To determine the amount of shared memory available, enter the following command:

# df -h /dev/shm/"


oracle@oraclenode2:~/logs
$ df -h /dev/shm/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                  12G  4.2G  7.6G  36% /dev/shm


It looks like we have 7.6G of free shared memory available for us.


 So..

 SQL> ALTER SYSTEM RESET large_pool_size SCOPE=SPFILE SID='*';

System altered.

SQL> ALTER SYSTEM SET memory_max_target=1G SCOPE=SPFILE SID='*';

System altered.

SQL> ALTER SYSTEM SET memory_target=1G SCOPE=SPFILE SID='*';

System altered.


Restart database instance and asm

Oracle Client Install For Window XP 32bit or 64bit

Note: 

These steps require Administrative Privileges on your personal computer. 
If your account does not have Administrative Privileges (or you are unsure) please contact your local help desk for someone to come and run this install on your pc as the Administrator.

Download Oracle Client from here
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html?ssSourceSiteId=ocomen

How to tell whether it is 32bit or 64bit window..
http://windows.microsoft.com/en-US/windows-vista/32-bit-and-64-bit-Windows-frequently-asked-questions


Find Correct Window Version (32bit or 64bit).  Download “Instant Client Package – Basic”


1. Create the directory “C:\instantclient_11_2”
      Right click “Start” -> Explore
       Click “Local Disk (C:)”
       Right click on the right hand side -> New -> Folder -> type “instantclient_11_2” then hit enter

2.     Drag and drop downloaded file into the “C:\instantclient_11_2” folder in the open file explorer

3. Unzip all of the zipped files.
          Right click on each zip file -> Extract All -> Next
     ***  Extract to “C:\”
                  NB:  files will get put in C:\instantclient_11_2

4. Go to:  Start -> right click My Computer -> Properties -> Advanced -> Environment Variables
   System variables -> Path -> Edit
      add “C:\instantclient_11_2;” to the beginning of the value -> OK
   System variables -> New
      Variable name: “TNS_ADMIN”
      Variable value: “C:\instantclient_11_2”


5.  You will need to create sqlnet.ora and tnsnames.ora
Entry.  Your database administrator DBA will able to provide these files. (In text)


Thanks.

Can not startup database after db_recovery_file_dest_size.

What I have..
Window 2008 64bit Server
Oracle 11gR2 11.2.0.3 non cluster single instance

Situation

I can not startup database after db_recovery_file_dest is full.

Solution

I need to create a pfile from spfile.


D:\>set ORACLE_SID=ORCLDB

D:\>set ORACLE_HOME=D:\app\Administrator\product\11.2.0\orcl
D:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 3 09:46:39 2012

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

Connected to an idle instance.


SQL> create pfile from spfile;

File created.

D:\app\Administrator\product\11.2.0\prod_1\orcl\INITorcldb.ORA

is created


Edit INITorcldb.ORA and increase  "db_recovery_file_dest_size"


save it.

then

Now copy backup your change of pfile to spfile
SQL> create spfile from pfile;

File created.


D:\app\Administrator\product\11.2.0\prod_1\database\SPFILEPBHCPROD.ora is
modified

You may >Startup   your databse now.



Foot Notes:
For backup strategy, you may use RMAN to backup an SPFILE,  or back them up yourself.
PFILE is simply a text based file, which means you can copy it to another directory without affecting the Oracle instance.  This is the easiest way to backup a PFILE.

So when you backup an SPFILE, you will want to convert it to a PFILE.  You can do this with the following syntax.

SQL> create pfile from spfile;

ORA-29516: Aurora assertion failure during 11.2.0.3 RAC database creation

 Oracle Server - Enterprise Edition - Version 11.2.0.3
Linux x86
Oracle El 5.3

Symptoms

I was creating brand new RAC on two servers with grid.  Grid installed fine but when I runinstall Oracle 11.2.0.3 with option software+creation of database.  At the close to 75% completion. I was getting  "ORA-29516: Aurora assertion failure: Assertion failure at joez.c:3311 " .  I didn't see any more informations from cfgtools installation log.

Diagnosis

Try searching oracle metalink, found few articles related to same error but it is not helpful.  Articles and oracle forum that other people had was related after they had created database already.  My case is brand new install.
Oracle article said to Disable the JIT compiler:

Alter system set JAVA_JIT_ENABLED=FALSE scope=both;

But i dont' have database created yet.

Solution

During the database creation, you will need to quickly login to your database with sysdba.

1.  Set/export your oracle sid to your RAC database name. 
export ORACLE_SID=RAC
. oraenv

2. ps -ef | grep pmon for few time to see if your pmon_RAC1 has been created and running.

3.  When you see pmon_RAC1 process then right away export ORACLE_SID=RAC1.

4. sqlplus / as sysdba 

5.  Alter system set JAVA_JIT_ENABLED=FALSE

note: this steps must be completed before the ORA-29516  error pop up.