This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 17, 2013 8:08 AM by FreddieEssex RSS

database creation

1003614 Newbie
Currently Being Moderated
Hi,

database:oracle 11g
OS:Linux

how to create database manually?which method is suggestable for database creation (dbca/manual creation)?

thanks,
Mike.
  • 1. Re: database creation
    KR10822864 Pro
    Currently Being Moderated
    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
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    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
    DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
    TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
    SIZE 20M REUSE
    UNDO TABLESPACE undotbs
    DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
    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.
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/create003.htm

    Edited by: KR10822864 on Apr 17, 2013 5:53 AM
  • 2. Re: database creation
    FreddieEssex Pro
    Currently Being Moderated
    Whatever floats your boat.....

    I tend to use manual method as I find it faster than phaffing about with dbca.

    Or you can use dbca to initially create your scripts for you and then re-run these to create your database.
  • 3. Re: database creation
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    If you don't know, or for educational, dbca is good enough.
    Otherwise, the doc is always your best friend :
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/create003.htm#ADMIN11073

    Nicolas.
  • 4. Re: database creation
    DK2010 Guru
    Currently Being Moderated
    Hi,

    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.
    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
    May it help you
  • 5. Re: database creation
    %bala% Explorer
    Currently Being Moderated
    Go for manual method with above given scripts so that you will get to know insights into the database creation.
    You can customise scripts and check out the alert log file for detailed process of database creation.
  • 6. Re: database creation
    sybrand_b Guru
    Currently Being Moderated
    The best method is to use dbca, as it will protect you against syntax errors, and invalid parameters, and will run everything automatically.
    I never use manual creation!

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 7. Re: database creation
    1003614 Newbie
    Currently Being Moderated
    thank you to all for quick reply.
  • 8. Re: database creation
    Sunny kichloo Expert
    Currently Being Moderated
    Also don't forget to close this thread if your issue is resolved.
  • 9. Re: database creation
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    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.

    Nicolas.
  • 10. Re: database creation
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Sunny kichloo wrote:
    Also don't forget to close this thread if your issue is resolved.
    Funny enough, you even did not post anything in that thread except this.

    Nicolas.
  • 11. Re: database creation
    Sunny kichloo Expert
    Currently Being Moderated
    I saw this thread after OP last reply.
  • 12. Re: database creation
    EdStevens Guru
    Currently Being Moderated
    N Gasparotto wrote:
    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.

    Nicolas.
    Well, yes and no.

    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.
  • 13. Re: database creation
    FreddieEssex Pro
    Currently Being Moderated
    Hello Nicolas,

    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 3.2.2.1, "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??
  • 14. Re: database creation
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    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.

    Nicolas.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points