Hi Tom

Could u please list out the steps of creating more than one database in a server/OS. Assuming that there is a database existing already.

Thanx in advance

Hi Tom

I am trying to create Oracle 10gR2 database from template (including data) create by DBCA and moved it to another machine in same directory (/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates).

These three files where created by dbca:
wngdrp_temp.dbc
wngdrp_temp.ctl
wngdrp_temp.dfb

and when I ran dbca in different machine having same OS giving SID as wngdrp, I got this error:
ORA-01503: CREATE CONTROL FILE failed
ORA-00200: control file could not be created
ORA-00202: control file: ‘u01/app/oracle/oradata/wngdrp’
ORA-27056: could not delete file

I copied control file wngdrp_temp.ctl into this directory and reran dbca. Still stuck there only.

Could you please direct me to documents where I could find example for creating Oracle 10g database from template using dbca.

Thanks and regards.

Hi Tom

Thanks for reply and I am running dbca as ORACLE, same as while installing Oracle software.

DBCA runs upto copying database files, first step, and then fails on second step, Creating & starting Oracle instance.
That time progress indicater is 38%.

Thanks and regards.

Hi Tom

User creating database has read/write access all of the way down that directory and the software ownere is oracle who ran the dbca.

Does it mean DBCA can creater database from template on same machine only?

Thanks and regards

Hi Tom

i have oracle 9i on win xp..please clarify some of my doubts.
1)
while creating a new database from command line ,do we need to shut down the older database or we can keep it running as it is..

2)
while creating a new database manually on windows when it says enviornment variable not defined do we have to create that variable as "user variable for administrators or "
system variables" from system properties 's advanced settings


3)whenever i try to open the database it shows this error,although it mounts fine)
ORA-12709: error while loading create database character set.
my nls_lang is null in all places in registry but if i change it to AMERICAN_AMERICA.WE8MSWIN1252 then also it shows the error message


Please guide

Hi Tom ,

like below as you said  i have tried to change the oracle_home also..no go..please help



C:\Documents and Settings\Administrator>echo %ORACLE_HOME%
C:\Oracle9\product\9.0

C:\Documents and Settings\Administrator>echo %ORACLE_BASE%
C:\Oracle9

C:\Documents and Settings\Administrator>echo %ORACLE_SID%
defdb

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Mon Jun 19 10:14:13 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

SQL> startup pfile=C:\Oracle9\product\9.0\database\initdefdb.ora
ORA-01031: insufficient privileges
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup pfile=C:\Oracle9\product\9.0\database\initdefdb.ora
ORACLE instance started.

Total System Global Area  118255568 bytes
Fixed Size                   282576 bytes
Variable Size              83886080 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
ORA-12709: error while loading create database character set


SQL>

 

Tom,

DB Version: 10g
OS : AIX 5.2

I need to install a second instance of Oracle on the existing server.

Could you guide me any link how to create instance?

Regards,
Dawar

Tom,

This is our business requirement to install another instance.

this will be use by our third party application.

I am thinking to use dbca command to create the instance.

Do I need to take any precaution steps with dbca?
(Before or after installation of instance)

cheers,
Dawar

Tom,

I am creating another instance remotely.
As I mentioned earlier one instance is exits.

Does environment varibale set to db1 when I start dbca?

I am able to run dbca when environment variable set to db1.
But I want different ORACLE_HOME & ORACLE_SID.

So I export ORACLE_HOME & ORACLE_SID for #2 DB.

But I found in dbca (configuration assitance) its pickup ORACLE_HOME for DB1.


Cheers,
Dawar

Tom,


I created second instance.

I used dbca and took every thing default.
I only defined new ORACLE_SID.


ORACLE_HOME for db1 was

/u01/app/oracle/version10.2

so its create new SID folder after ORACLE_HOME and put all files.

My question is how to logon to db2 via sqlplus.

Please note:
ORACLE_HOME is set for db1.

cheers,
Dawar

Tom,

Sorry for ur time,
it was very straight foward.

I set ORACLE_SID=db2

and its connect to the new instance via sqlplus from command prompt.

Tom,


Is it possible to create another instance with new ORACLE_HOME?

I created new instance by using dbca utility.

So ORACLE_HOME are same in both instances.
But ORACLE_SIDs are different.

Its works fine.

Purpose:
Actually our vendor will import application data on this newly created instance.(11)

This application is not same as the application exists on instance (1) or existed DB.

So I wonder probably it is a good idea to keep both in different ORACLE_HOMEs.

cheers,
Dawar

Hi Tom,

We are to create a master script set to homogenize our database creation (10G).

I know there are two documented ways to create an oracle database, manual (do it yourself) or by using DBCA.

I would prefer to create a first script set with DBCA and modify this as few as absolutely necessary (eg. removing constants like database names). That way, I would get a standardized, documented, oracle supported & supplied and homogenized script set for free. I could also easily exclude (comment out) some options like oracle text and install it later on.

My colleagues want to go the manual way. They argue DBCA creates to many subscripts. DBCA might also run unneeded oracle supplied database scripts (like "owminst.plb"). They want to keep full control of all oracle supplied scripts run on the database. If some options like oracle text are needed afterwards they could also use DBCA to justify the installation. So, they are about to skim over the documentation and create our own proprietary script set.

What do you think?

A good reason for having full control over the installation is that by default, DBCA installs options that are extra cost (partitioning and data mining come to mind). We're having to incurr downtime to de-install the option so if we get audited, we'll be safe.

You can choose what to install in DBCA. Always choose a custom database and uncheck all the options. It is hard to explain without screen shots. I use DBCA to create plain database with no sample schemas, no partitioning, no JVM, no XML DB...

You are confusing software installation and database creation. The software installation is where the options are involved, not the creation of the database. The software install should only be done once per machine (minus patches) The database creation is just creating files.

It might be more error-prone to do it yourself, no? You might forget a script or run it in wrong order. Additionally, you have to work through the manual database creation chapter within administration guide.

I guess using DBCA is highly recommended by oracle too?

Tom:

Can you advise on what to use for some options in dbca.
I am creating an 11gR2 database for OLTP application.
I will export a 9i schema and import it into this new 11g datbase.


1) Database Location Files (Screen 7)

Shall I select "Use Common location for all database files" or "Oracle managed files"?


2) Under Management options (Screen 4), there is an option to enable daily disk backups to Recovery area.

Under Recover Configuration screen later there is an option to specify flash recovery area for backups.


How are the two options different and do you recommend using them for production?

Tom:

These are two different machines: AIX to linux.
I cant upgrade the DB.

From what i see is that most peopl select standard directory and not oracle managed files.

Since this will be tested for a couple of months i dont want the daily backups.

WOuld flash recovery area take a lot of space? can i enable it later when i go live.

Tom,

the OP may have meant producing a sql plus script from the original database. The benefit of this is that it enables the user's particular structures to be built at the press of a button in any database.

I've successfully used this method for several years,and it works very well, giving easy build and upgrades. If that is what's wanted, then sql plus can be used to generate the sql plus build scripts. Samples available on request.

Tom,

that's precisely what I was alluding to.
I build our databases entirely from script, and they are indeed under strict version control.

I didn't mean script the database as in what the DBCA does, but rather script our schemas in a blank database, which has been produced by the DBCA (or indeed another script).

Obviously using scripts for duplicating a database is daft, since as you so rightly say, RMAN does that anyway.

Tom:

Do you normally use "oracle managed files" in your database systems?

http://docs.oracle.com/cd/B10501_01/server.920/a96521/omf.htm


What cases do you recommend using it or not using it?

Tom:

The DBA did not know what OMF is all about. I know your answer about finding another one. Good luck...

Anyway i read this article and got full understanding about what it does and I decided not to use it for production system. It is not really much to drop a datafile and create a new datafile when you create/drop a tablespace (for tablespace OMF).

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_8.shtml

------------------------------------------------------------
step1. | First you create your own oracle service like that.
------------------------------------------------------------

go to cmd command line program
c:\>oradim -new -sid lrh -startmode auto

------------------------------------------------------------
step2. | second we start the instance which we have created in the first step.
-------------------------------------------------

to to cmd command.

c:\>set ora_sid=lrh
c:\>sqlplus\nolog

--------------------------------------------------------------------------------------------
step3. | 3rd we will goes for creating Folders
-------------------------------------------------------------------------------------------
-goes to c drive
-there folder create like that (mydb) name
-open mydb folder and create other folder inside them such as
udump,adump,datafiles,log,udump,temp,flash_recovery_area,pfile,cdump,archive and so on

------------------------------------------------------------------------------------------
step4. | 4th we will goes to sql/plsql program and open it and login on sys as sysdba/password
------------------------------------------------------------------------------------------

startup nomount pfile='c:\mydb\initlrh.ora';

----------------------------------------------------------------------------------------------------
step5.| copy and past this code in another notepade page and saved them on the name of initlrh.ora
----------------------------------------------------------------------------------------------------
background_dump_dest='C:\MYDB\bdump'
compatible='10.1.0.2.0'
control_files='C:\MYDB\DATAFILES\control01.ctl',
'C:\MYDB\DATAFILES\control01.ct2.ctl',
'C:\MYDB\DATAFILES\control01.ct3.ctl'
core_dump_dest='C:\MYDB\cdump'
db_block_size=8192
db_cache_size=25165824
db_domain=''
db_file_multiblock_read_count=16
db_name='LRH'
db_recovery_file_dest='C:\MYDB\flash_recovery_area'
db_recovery_file_dest_size=2147483648
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
java_pool_size=50331648
job_queue_processes=10
large_pool_size=8388608
open_cursors=300
pga_aggregate_target=25165824
processes=150
shared_pool_size=83886080
sort_area_size=65536
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='C:\MYDB\udump'
.............................................................................

step6 | copy and past in SQL/plsql page and press enter.

............................................................................

create database LRH
user sys identified by sys
user system identified by system
logfile group 1('c:\mydb\datafiles\redo1.log','c:\mydb\datafiles\redo2.log') size 10m,
group 2('c:\mydb\datafiles\redoa1.log','c:\mydb\datafiles\redoa2.log') size 10m
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE 'c:\mydb\datafiles\system01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNlimited
sysaux DATAFILE 'c:\mydb\datafiles\sysaux01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 1m MAXSIZE unlimited
UNDO TABLESPACE undotbs1
datafile 'c:\mydb\datafiles\undo01.dbf' size 10m
default temporary tablespace temp
tempfile 'c:\mydb\datafiles\temp01.dbf' size 10m

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

step7. | complete the database creation this two query must be run on sys as syddba
and without that your database in not completed.

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

sql>@?/rdbms/admin/catalog.sql ----- for used data dictionary
ql>@?/rdbms/admin/catproc.sql ----- for used pl/sql

------------------------------------------------------------------------------------------
step1. | First you create your own oracle service like that.
------------------------------------------------------------------------------------------

go to cmd command line program
c:\>oradim -new -sid lrh -startmode auto

-------------------------------------------------------------------------------------------
step2. | second we start the instance which we have created in the first step.
-------------------------------------------------------------------------------------------

to to cmd command.

c:\>set ora_sid=lrh
c:\>sqlplus\nolog

--------------------------------------------------------------------------------------------
step3. | 3rd we will goes for creating Folders
-------------------------------------------------------------------------------------------
-goes to c drive
-there folder create like that (mydb) name
-open mydb folder and create other folder inside them such as
udump,adump,datafiles,log,udump,temp,flash_recovery_area,pfile,cdump,archive and so on


----------------------------------------------------------------------------------------------------
step4.| copy and past this code in another notepade page and saved them on the name of initlrh.ora
----------------------------------------------------------------------------------------------------
background_dump_dest='C:\MYDB\bdump'
compatible='10.1.0.2.0'
control_files='C:\MYDB\DATAFILES\control01.ctl',
'C:\MYDB\DATAFILES\control01.ct2.ctl',
'C:\MYDB\DATAFILES\control01.ct3.ctl'
core_dump_dest='C:\MYDB\cdump'
db_block_size=8192
db_cache_size=25165824
db_domain=''
db_file_multiblock_read_count=16
db_name='LRH'
db_recovery_file_dest='C:\MYDB\flash_recovery_area'
db_recovery_file_dest_size=2147483648
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
java_pool_size=50331648
job_queue_processes=10
large_pool_size=8388608
open_cursors=300
pga_aggregate_target=25165824
processes=150
shared_pool_size=83886080
sort_area_size=65536
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='C:\MYDB\udump'

------------------------------------------------------------------------------------------
step5. | 4th we will goes to sql/plsql program and open it and login on sys as sysdba/password
------------------------------------------------------------------------------------------

startup nomount pfile='c:\mydb\initlrh.ora';
.............................................................................

step6 | copy and past in SQL/plsql page and press enter.

............................................................................

create database LRH
user sys identified by sys
user system identified by system
logfile group 1('c:\mydb\datafiles\redo1.log','c:\mydb\datafiles\redo2.log') size 10m,
group 2('c:\mydb\datafiles\redoa1.log','c:\mydb\datafiles\redoa2.log') size 10m
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE 'c:\mydb\datafiles\system01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNlimited
sysaux DATAFILE 'c:\mydb\datafiles\sysaux01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 1m MAXSIZE unlimited
UNDO TABLESPACE undotbs1
datafile 'c:\mydb\datafiles\undo01.dbf' size 10m
default temporary tablespace temp
tempfile 'c:\mydb\datafiles\temp01.dbf' size 10m

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

step7. | complete the database creation this two query must be run on sys as syddba
and without that your database in not completed.

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

sql>@?/rdbms/admin/catalog.sql ----- for used data dictionary
ql>@?/rdbms/admin/catproc.sql ----- for used pl/sql