1 2 Previous Next 17 Replies Latest reply: Apr 17, 2013 10:08 AM by FreddieEssex RSS

    database creation

    1003614
      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
          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
            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
              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
                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
                  cherrydba
                  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
                    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
                      thank you to all for quick reply.
                      • 8. Re: database creation
                        Sunny kichloo
                        Also don't forget to close this thread if your issue is resolved.
                        • 9. Re: database creation
                          Nicolas.Gasparotto
                          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
                            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
                              I saw this thread after OP last reply.
                              • 12. Re: database creation
                                EdStevens
                                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
                                  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
                                    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