Demo Schema Script not updated for Container Database in 12c

This afernoon i was installing OE/HR Demo script supplied with Oracle 12c Binaries. When I try to install OE schema into one of my pluggable Database PDBOE01 it could not complete successfully as script are not compatible with Container/Pluggable database architecture.


you must modify the script before running otherwise you will end up with lots of garbage stuff into your root container database.


You will notice in the log1, user CREATED successfully and then grant failed to find the user created as its looking into the wrong database(root container) etc etc.

Its causing issue because script still connecting to database without TNS string and hence it connect to CDB everytime its re-connect while running the script.

cd /u01/app/oracle/product/12.1.0/dbhome_1/demo/schema/order_entry

$ grep -i  -e 'CONNECT SYS/' -e  'CONNECT OE/' *.sql
coe_xml.sql:CONNECT sys/&pass_sys AS SYSDBA;
coe_xml.sql:CONNECT oe/&pass_oe;
coe_xml.sql:CONNECT sys/&pass_sys AS SYSDBA;
coe_xml.sql:CONNECT oe/&&pass_oe;
createUser.sql:CONNECT OE/&pass_oe
createUser.sql:CONNECT sys/&&pass_sys AS SYSDBA;
createUser.sql:CONNECT OE/&pass_oe
oe_main.sql:CONNECT sys/&pass_sys AS SYSDBA;
oe_main.sql:CONNECT oe/&pass
oe_main.sql:CONNECT sys/&pass_sys AS SYSDBA;
xdb03usg.sql:CONNECT sys/&&pass_sys AS SYSDBA;
xdb03usg.sql:CONNECT OE/&pass_oe

all of above need to updated before running against pluggable database .

Simple script to update all to point to my Pluggable database PDBOE01 i used the following.

for i in `ls`

do

sed -i 's/CONNECT sys\//CONNECT SYS@PDBOE1\//g' $i

sed -i 's/CONNECT oe\//CONNECT oe@PDBOE1\//g' $i

sed -i 's/CONNECT OE\//CONNECT oe@PDBOE1\//g' $i

done

where PDBOE01 is pluggable database TNS entry.

If feel this all could be done with single SED command , comments welcome for suggestion on that.

now all files updated with TNS entry. 

coe_xml.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

coe_xml.sql:CONNECT oe@PDBOE1/&pass_oe;

coe_xml.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

coe_xml.sql:CONNECT oe@PDBOE1/&&pass_oe;

createUser.sql:CONNECT oe@PDBOE1/&pass_oe

createUser.sql:CONNECT SYS@PDBOE1/&&pass_sys AS SYSDBA;

createUser.sql:CONNECT oe@PDBOE1/&pass_oe

oe_main.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

oe_main.sql:CONNECT oe@PDBOE1/&pass

oe_main.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

xdb03usg.sql:CONNECT SYS@PDBOE1/&&pass_sys AS SYSDBA;

xdb03usg.sql:CONNECT oe@PDBOE1/&pass_oe

----------------------- Log1 of run without update (Failed ) -----------------------
SQL> @oe_main.sql

specify password for OE as parameter 1:
Enter value for 1: oracle

specify default tablespeace for OE as parameter 2:
Enter value for 2: OEHR_DATA

specify temporary tablespace for OE as parameter 3:
Enter value for 3: TEMP

specify password for HR as parameter 4:
Enter value for 4: oracle

specify password for SYS as parameter 5:
Enter value for 5: oracle

specify directory path for the data files as parameter 6:
Enter value for 6:
SP2-0137: DEFINE requires a value following equal sign

writeable directory path for the log files as parameter 7:
Enter value for 7:
SP2-0137: DEFINE requires a value following equal sign

specify version as parameter 8:
Enter value for 8: v3

Enter value for log_path:

User dropped.

old   1: CREATE USER oe IDENTIFIED BY &pass
new   1: CREATE USER oe IDENTIFIED BY oracle

User created.

old   1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER oe DEFAULT TABLESPACE OEHR_DATA QUOTA UNLIMITED ON OEHR_DATA

User altered.

old   1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER oe TEMPORARY TABLESPACE TEMP

User altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.
GRANT execute ON sys.dbms_stats TO oe
                                   *
ERROR at line 1:
ORA-01917: user or role 'OE' does not exist


ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0640: Not connected
SP2-0640: Not connected

specify Sample Schema version as parameter 1:

specify password for OE as parameter 2:

PROMPT password for SYS as parameter 3:

SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected

----------------------------------------------------

FATAL : No Bootable medium found , after adding disk to VBOX Virtual Machine

While working on adding disk to my VBOX virtual machine, i come across minor issue, thought to share with community.

FATAL : No Bootable medium found ! System halted

Issue was caused by adding a disk to virtual machine with lower port number then existing (only) disk ,which had operating system installed.

 Checking Storage property of machine revealed the new disk "Linux0001-02.vdi" which was just added been assigned with SATA Port 0 ID, and when machine restated it look for this disk for operating system and there was none as i just added this disk.

To fix the issue, assign a higher number to new added disk, to move this disk down the order.

Click Settings>>Storage>> click on diskname>> change Hard disk Port number from drop down list >> click ok.

Bow we can see new disk is appearing 2nd in the list , making my old disk to be first one to be read.

Try starting the VM again and here we go, it found the operating system it was looking for.

 

How to resize virtual disk in Oracle Virtual Box Vbox

Recently i had to resize one my virtual disk on Oracle V BOX environment , thought to share the command used to resize the disk.

Command on VBOX is VBoxManage.exe

Prcoess to re - configure Oracle EM agent on cloned server

Hi,

Sometime back i come across cloned environment with pre installed oracle grid agent on the server.

This is the Set of Steps I followed to make it quicker as i was dealing with around 100 Server.

 Prcoess to configure EM agent on cloned server, i hope it might be useful to someone facing similar work activity.


### Setup Agent Env.
. oraenv
agent11g

## cd to agent bin and setup hostname
cd /usr/oracle/product/11.1.0/agent11g/oui/bin ;export AGENTHOST=`hostname`

## de-attach old home

./runInstaller -silent -detachHome ORACLE_HOME="/usr/oracle/product/11.1.0/agent11g" ORACLE_HOME_NAME="agent11g"

## clone new home
./runInstaller -clone -forceClone ORACLE_HOME="/usr/oracle/product/11.1.0/agent11g" ORACLE_HOSTNAME=${AGENTHOST}.comp-intern.de ORACLE_HOME_NAME=agent11g -noconfig -silent

run root.sh using root user.

cd ../../sysman/config ; vi emd.properties

update EMD_URL  to 3872

### discovery of targets

agentca -d
emctl secure agent

## check everything is good.

emctl status agent
 

Thanks
krishan

Oracle 12c rman backup gone with drop pluggable database pdb

Today, I was testing backup and restore process for new oracle 12c pluggable database and part of test i thought to test drop and restore of pluggable database.


I have created a small two tablespace database PDB1 from PDB$SEED datbase for this test.

Before attempting the drop , i thought to backup the database so i can restore later when database is droppped and perform recovery operation.


RMAN> backup pluggable database PDB1;

Starting backup at 28-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00021 name=/u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_sysaux_c31rdofq_.dbf
input datafile file number=00020 name=/u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_system_c31rdofp_.dbf
channel ORA_DISK_1: starting piece 1 at 28-OCT-15
channel ORA_DISK_1: finished piece 1 at 28-OCT-15
piece handle=/u01/oadata/fast_recovery_area/CDBORA/232C3E643AA01E17E0530100007FCD5B/backupset/2015_10_28/o1_mf_nnndf_TAG20151028T080754_c31s6cop_.bkp tag=TAG20151028T080754 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 28-OCT-15

Starting Control File and SPFILE Autobackup at 28-OCT-15
piece handle=/u01/oadata/fast_recovery_area/CDBORA/autobackup/2015_10_28/o1_mf_s_894269310_c31s7gmk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-OCT-15

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

5       Full    660.88M    DISK        00:00:26     28-OCT-15      

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20151028T080754

        Piece Name: /u01/oadata/fast_recovery_area/CDBORA/232C3E643AA01E17E0530100007FCD5B/backupset/2015_10_28/o1_mf_nnndf_TAG20151028T080754_c31s6cop_.bkp

  List of Datafiles in backup set 5

  Container ID: 5, PDB Name: PDB1

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  20      Full 1964340    28-OCT-15 /u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_system_c31rdofp_.dbf

  21      Full 1964340    28-OCT-15 /u01/oradata/CDBORA/232C3E643AA01E17E0530100007FCD5B/datafile/o1_mf_sysaux_c31rdofq_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

6       Full    17.20M     DISK        00:00:00     28-OCT-15      

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20151028T080830

        Piece Name: /u01/oadata/fast_recovery_area/CDBORA/autobackup/2015_10_28/o1_mf_s_894269310_c31s7gmk_.bkp

  SPFILE Included: Modification time: 28-OCT-15

  SPFILE db_unique_name: CDBORA

  Control File Included: Ckp SCN: 1964358      Ckp time: 28-OCT-15

SQL> SELECT CON_ID,NAME, OPEN_MODE FROM V$PDBS;

    CON_ID NAME      OPEN_MODE

---------- ------------------------------------------------------------------------------------------ ----------

2 PDB$SEED      READ ONLY

3 PDB2      READ WRITE

4 DPBSALES      READ WRITE

5 PDB1      READ WRITE

SQL> DROP PLUGGABLE DATABASE PDB1;

DROP PLUGGABLE DATABASE PDB1

*

ERROR at line 1:

ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

as it failed to drop without deleting files, I used including datafiles option.

SQL> DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILES;

Pluggable database dropped.

now database was dropped , i thought to go back to my backup and restore .

RMAN> LIST Backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

6       Full    17.20M     DISK        00:00:00     28-OCT-15      

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20151028T080830

        Piece Name: /u01/oadata/fast_recovery_area/CDBORA/autobackup/2015_10_28/o1_mf_s_894269310_c31s7gmk_.bkp

  SPFILE Included: Modification time: 28-OCT-15

  SPFILE db_unique_name: CDBORA

  Control File Included: Ckp SCN: 1964358      Ckp time: 28-OCT-15

I couldn't find my backup in rman ,because information from controlfile is gone. Fortunately my backup file are still there, so its not big issue, i could use those to restore my database. but as current controlfile no longer knows about PDB1 i will have to restore the controlfile of time before dropping the database.

Happy Learning !!!

How to Cleanup huge crf / CHM repository file in Grid Infrastructure home 11.2.0.2 11.2.0.3 11.2.0.4

We got alert on RDBMS home file system is above 90% full and investigation revealed crf directory under grid infrastructure home was above 26G.

once you know its $ORACLE_HOME/crf and you can easily guess these are the files  repository oforacle Cluster Health Monitor

This issue was observed in our two node cluster running Oracle Grid Infrastructure 11g 11.2.0.4.0 on Linux.


---  To confirm / Crosscheck.

$ORACLE_HOME/bin/oclumon manage -get reppath

CHM Repository Path = /cluster/app/11.2.0/grid/crf/db/cmsdb02

Done


[root@cmsdb02 cmsdb02]# cd /cluster/app/11.2.0/grid/crf/db/cmsdb02
[root@cmsdb02 cmsdb02]# ls -lrt
total 25830096
-rw-r----- 1 root root        8192 Sep  2 07:07 repdhosts.bdb
-rw-r----- 1 root root       24576 Sep  2 07:07 __db.001
-rw-r----- 1 root root        8192 Sep  2 07:10 crfconn.bdb
-rw-r--r-- 1 root root   120000000 Sep  2 07:11 cmsdb02.ldb
-rw-r----- 1 root root    16777216 Oct 27 13:11 log.0000030408
-rw-r----- 1 root root    16777216 Oct 27 13:42 log.0000030409
-rw-r----- 1 root root      401408 Oct 27 13:42 __db.002
-rw-r----- 1 root root   329068544 Oct 27 13:42 crfts.bdb
-rw-r----- 1 root root   508063744 Oct 27 13:42 crfloclts.bdb
-rw-r----- 1 root root   399507456 Oct 27 13:42 crfhosts.bdb
-rw-r----- 1 root root   404766720 Oct 27 13:42 crfcpu.bdb
-rw-r----- 1 root root 24340660224 Oct 27 13:42 crfclust.bdb
-rw-r----- 1 root root   407449600 Oct 27 13:42 crfalert.bdb
-rw-r----- 1 root root       57344 Oct 27 13:43 __db.006
-rw-r----- 1 root root     1187840 Oct 27 13:43 __db.005
-rw-r----- 1 root root     2162688 Oct 27 13:43 __db.004
-rw-r----- 1 root root     2629632 Oct 27 13:43 __db.003

Bug/Fix :

Checking on metalink confirmed its a bug

Bug 12711827 : HUGE CRFCLUST.BDB WHICH CAN NOT BE REDUCED IN FILESYSTEM  11.2.0.2
Bug 14479330 : HUGE SIZE OF CRFCLUST.BDB - ORACLE 11.2.0.3

Look like based bug was was fixed with below in 11.2.0.3 to control the size but there is not fix mentioned for any of above bugs exclusively.

Patch 10165314: CHM/CRF/IPDOS REPOSITORY EXCEEDS 1GB AFTER ADD/REMOVE NODE OR FRESH INSTALL



Workaround/Solution.

temporary workaround of the problem is to delete the CHM repository files and ORA.CRF should be down when you cleanup.


For detail Check metalink  1343105.1 Oracle Cluster Health Monitor (CHM) using large amount of space (more than default)


set oracle ASM home.

$ . oraenv
+ASM1

$ crsctl stat res ora.crf -init
NAME=ora.crf
TYPE=ora.crf.type
TARGET=ONLINE
STATE=ONLINE on cmsdb02


$oclumon manage -get reppath

CHM Repository Path = /cluster/app/11.2.0/grid/crf/db/cmsdb02
Done

# Stop CHM (Cluster health monitor) process ora.crf

$ crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'cmsdb02'
CRS-2677: Stop of 'ora.crf' on 'cmsdb02' succeeded

# Delete the revelent files. Please remember you need root password to delete the files as they owned by root.

$ cd /cluster/app/11.2.0/grid/crf/db/cmsdb02
$ rm *.bdb


# Start CHM (Cluster health monitor) process ora.crf

$ crsctl start res ora.crf -init


# Check process is back up and running


$ crsctl stat res ora.crf -init
NAME=ora.crf
TYPE=ora.crf.type
TARGET=ONLINE
STATE=ONLINE on cmsdb02

Oracle 12c CDB database crash with ORA-600 INTERNAL_PLAN

I have installed Oracle 12c recently on linux x86 64bit linux server.  This is using new Container architecture (i.e CDB)

I connect to server and started using SQLplus ,whithin one minute of database open, i got connect lost error message,  when try to connect again, found database instance is no more, it was crashed.

I tried one more time to startup, just to see the behaviour as same results, it got crashed again as soon as database opened.

When checked the Alert.log found the following ORA-600 causing databse crash.

###############
Setting Resource Manager plan SCHEDULER[0x4459]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc  (incident=16873) (PDBNAME=PDB$SEED):
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01/incident/incdir_16873/CDB01_dbrm_3506_i16873.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 18 01:33:58 2015
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc:
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Fri Sep 18 01:33:58 2015
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_dbrm_3506.trc:
ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN], [], [], [], [], [], [], [], [], [], []
Fri Sep 18 01:33:58 2015
USER (ospid: 3506): terminating the instance due to error 56710
Fri Sep 18 01:33:58 2015
System state dump requested by (instance=1, osid=3506 (DBRM)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb01/CDB01/trace/CDB01_diag_3504_20150918013358.trc
Fri Sep 18 01:33:59 2015
Dumping diagnostic data in directory=[cdmp_20150918013358], requested by (instance=1, osid=3506 (DBRM)), summary=[abnormal instance termination].
Fri Sep 18 01:33:59 2015
Instance terminated by USER, pid = 3506


Problem 

ORA-600:[kgskigetelt_subplan] . is due to bug and as of date(18 Sep 15) no patch or workaround available for this bug.

Bug 20479923 : ORA-00600:[KGSKIGETELT_SUBPLAN] FOLLOWED BY INSTANCE CRASH