This content has been marked as final. Show 17 replies
how to create database manually?sample script:
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
SIZE 20M REUSE
UNDO TABLESPACE undotbs
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
which method is suggestable for database creation (dbca/manual creation)?Manual creation is the best one.
if you dont have any GUI problems dbca is easy method.
refer below link for more info.
Edited by: KR10822864 on Apr 17, 2013 5:53 AM
You can choose any of them but DBCA is easy to use.
while in the manually creation you need to run several scripts after create DB command.
May it help you
sample script: CREATE DATABASE "testdb" MAXLOGFILES 72 MAXLOGMEMBERS 2 MAXDATAFILES 120 MAXINSTANCES 1 CHARACTER SET "US7ASCII" NATIONAL CHARACTER SET "AL16UTF16" DATAFILE '/u01/app/oradatatestdb/testdb_system01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE '/u01/app/oradatatestdb/testdb_sysaux01.dbf' SIZE 500M REUSE UNDO TABLESPACE UNDOTBS1 datafile '/u01/app/oradatatestdb/testdb_undotbs01.dbf' size 100M DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oradatatestdb/testdb_temp01a.dbf' size 100M LOGFILE GROUP 1 ('/u01/app/oradatatestdb/testdb_redo101.log', '/u01/app/oradatatestdb/testdb_redo102.log') SIZE 50M, GROUP 2 ('/u01/app/oradatatestdb/testdb_redo201.log', '/u01/app/oradatatestdb/testdb_redo202.log') SIZE 50M; run some sql file connect / as sysdba @$ORACLE_HOME/rdbms/admin/catalog.sql @$ORACLE_HOME/rdbms/admin/catproc.sql @$ORACLE_HOME/rdbms/admin/dbmspool.sql @$ORACLE_HOME/rdbms/admin/catexp.sql @$ORACLE_HOME/rdbms/admin/catblock.sql @$ORACLE_HOME/sqlplus/admin/plustrce.sql
N Gasparotto wrote:Well, yes and no.
One of the "problem" I can see with dbca is that you have to apply on the database the PSU/CPU that you previously applied onto the ORACLE_HOME. It's not the case with manual db creation.
If you select the dbca option 'custom database' instead of one of the templates, the database will be created 'manually' under the covers. You can see this if, when you get to the end of the dialogs, instead of selecting 'create database' select only 'create scripts'. Start following the scripts and you'll see it is actually creating the db from scratch, beginning with the CREATE DATABASE command. So the database is not built from a pre-staged backup the way it is with the template databases. But of course, then you are creating the database "manually" ... just using dbca to create the scripts for you instead of creating them "manually".
Actually, this is my preferred method of creating a database. At one time I had actually saved a set of those scripts as a template, and then prepped them by running a script to globally change the sid/database name accordingly.
From memory in the past on older CPU patches, you would have to apply the CPU patch to a database if it was created after the patch had been applied to the ORACLE_HOME. The documentation for one of the later PSU patches states the following, which backs up what you said.
These instructions are for a database that is created or upgraded after the installation of this SPU patch.You must execute the steps in Section 220.127.116.11, "Loading Modified SQL Files into the Database" for any new database only if it was created by any of the following methods:
•Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)
•Using a script that was created by DBCA that creates a database from a sample database
There are no actions required for databases that have been upgraded.>
In the past I have checked for PSU/CPU patches by querying the registry$history table. When a database is created out of a patched home, this table has zero rows and it is only populated when you run the catbundle script.
So even if this table is empty but the database was created using the "create database" statement then the patch has been implicitly applied??
It's a bit pedantic but right.
I should have say, dbca and using the template (or preconfigured), to me that's the common sense when saying 'dbca'. It seems this is also recommended in the doc : If you must create a new database, then Oracle recommends that you install a preconfigured database, which is faster and easier. You can customize the database after it has been created. (http://docs.oracle.com/cd/E11882_01/server.112/e10897/install.htm#CHDICDHE)
But you are right, there's the other option that you mentioned.