How to create Sample Schemas in Oracle 11g database – ORA-19613

May 10, 2013 by 2 Comments 

The Oracle Database Sample Schemas provide a common platform for examples in each release of Oracle Database. They are easy to use for testing and training purposes. All Oracle Database documentation and training materials refer to the Sample Schemas environment.

Following are Oracle Sample Schemas

- OE (Order Entry schema) – useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including non-scalar data types.

- HR (Human Resources schema) – useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

- PM (Product Media schema) is dedicated to multimedia data types.

- IX (Information Exchange main schema) includes a set of schemas for demonstrate Oracle Advanced Queuing capabilities

- SH (Sales History schema) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

- SCOTT – old-fashioned famous schema with its two prominent tables EMP and DEPT that is used by Oracle for many years.

- BI – includes only synonyms on SH schema

With this simplicity I personally found difficulties in creating Sample Schemas in Oracle 11g database. In fact I could not do it in a standard way with Oracle installation binaries before 11.2.0.3. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually.

1. Create a new fresh Oracle database with following prerequisites

- with US7ASCII character set
- with database components (JVM, XML DB, Multimedia, Spatial)
- WITHOUT Sample Schemas component
- No new tablespaces should be created after the database creation

2. Create Sample Schemas manually following the procedure below

cd $ORACLE_HOME/demo/schema
sqlplus /nolog
@mkplug.sql
Enter passwords for input parameters 1-7 and the following parameters:
Enter value for 8: example.dmp
Enter value for 9: example01.dfb
Enter value for 10: /opt/app/oracle/oradata/example01.dbf
Enter value for 11: /opt/app/oracle/product/11.2.0/dbhome_1/demo/schema/
Enter value for 12: /opt/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/

All directories above should be correct and correspond to your $ORACLE_HOME which is my case is /opt/app/oracle/product/11.2.0/dbhome_1

As a result of executing the above SQL script, a new tablespace EXAMPLE is attached to a database with file number 5, and the Sample Schemas will show up in the database.

3. This procedure does not work in case one of the below

- non-US7ASCII database
- example tablespace was created during the database creation
- at least one new tablespace is created after the database creation.

You might get following error while executing the script if the next available database file number <> 5:

declare
*
ERROR at line 1:
ORA-19583: conversation terminated due to error
ORA-19870: error while restoring backup piece
/opt/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/example01.dfb
ORA-19615: some files not found in backup set
ORA-19613: datafile 6 not found in backup set
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 5824
ORA-06512: at line 33

4. If SCOTT user is missing

After the successful SQL script execution some may miss a famous SCOTT user. Unfortunately it might not be a part of Sample Schemas data file before Oracle binaries 11.2.0.3. You can create it later using a below SQL script:

$ORACLE_HOME/rdbms/admin/utlsampl.sql

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

2 Responses to “How to create Sample Schemas in Oracle 11g database – ORA-19613”
  1. Mark says:

    Could you explain a little more about whats going on with parameters 8,9,10, 11 and above all 12?

    I’ve tried this 3 times with no success. An explanantion of whta exactly the install script is doing or looking for might help me. I’m an Oracle newbie who needs these sample db’s to practice with..I’ve no Data!!

  2. Kirill Loifman says:

    Yes, Mark. I will try.

    First if you installing Oracle DB 11.2.0.3+, creating example schemas can be done easier during the DB creation procedure.
    If it’s not done or you install 11.2.0.2 and below, please read carefully my article and all the task prerequisites.
    Regarding parameters, I found a mistype in my parameter value 10 and updated the post.
    So you can try again.
    Parameters’ descriptions are below:
    - parameter 8: metadata import file for tablesapce EXAMPLE; should be located in $ORACLE_HOME/assistants/dbca/templates
    - parameter 9: database backup file for tablespace EXAMPLE; should be located in $ORACLE_HOME/assistants/dbca/templates
    - parameter 10: database file for tablespace EXAMPLE that will be created as a result of the script
    - parameter 11: OUTOUT log directory (ending “/” important); required for script run
    - parameter 12: OUTPUT dump file directory (ending “/” important); required for script run

    Hope it helps

    – Kirill

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email