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