Step 1: Source the environment variables.
[oracle@dc dbs]$ hostname dc.localdomain [oracle@dc dbs]$ id uid=501(oracle) gid=503(oinstall) groups=503(oinstall),504(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 [oracle@dc dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@dc dbs]$ env | grep ORA ORACLE_SID=TEST ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 |
Step 2: Create pfile for new
database which is to be created and create directory structure at OS in pfile.
[oracle@dc dbs]$ cat initTEST.ora TEST.__db_cache_size=327155712 TEST.__java_pool_size=4194304 TEST.__large_pool_size=8388608 TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment TEST.__pga_aggregate_target=318767104 TEST.__sga_target=469762048 TEST.__shared_io_pool_size=0 TEST.__shared_pool_size=121634816 TEST.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/TEST/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='TEST' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)' *.log_archive_dest_1='LOCATION=/u01/app/ valid_for=(all_logfiles,all_roles) db_unique_name=TEST' *.log_archive_dest_state_1='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS' |
Step 3: Create SQL
script for creating database.
Note: Minimum size for all below data files should be greater than 300MB
otherwise you will receive Bootstrap Errors and some packages will not get
created. If you did so then your instance gets terminated.
$cat create.sql CREATE DATABASE TEST MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/TEST/redo1.log' SIZE 500M, GROUP 2 '/u01/app/oracle/oradata/TEST/redo2.log' SIZE 500M, GROUP 3 '/u01/app/oracle/oradata/TEST/redo3.log' SIZE 500M DATAFILE '/u01/app/oracle/oradata/TEST/system.dbf' size 100m, '/u01/app/oracle/oradata/TEST/usr04.dbf' size 10m sysaux datafile '/u01/app/oracle/oradata/TEST/sysaux.dbf' size 100m undo tablespace undotbs datafile '/u01/app/oracle/oradata/TEST/undo.dbf' size 50m CHARACTER SET US7ASCII ; |
Step 4: Start the database
in nomount stage and run the script.
[oracle@dc dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 12:54:30 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 221331456 bytes Fixed Size 2251856 bytes Variable Size 163578800 bytes Database Buffers 50331648 bytes Redo Buffers 5169152 bytes SQL> @create.sql Database created. |
Step 5: Run catalog.sql and catproc.sql scripts to create
dictionary views.
Describe any dictionary view before executing above scripts, you
will receive below error message. But you will be able to describe V$
views(Dynamic Performance Views).
SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL> desc v$database; Name Null? Type ----------------------- -------- ------------ DBID NUMBER NAME VARCHAR2(9) CREATED DATE RESETLOGS_CHANGE# NUMBER RESETLOGS_TIME DATE PRIOR_RESETLOGS_CHANGE# NUMBER .... .... .... [oracle@dc admin]$ hostname dc.localdomain [oracle@dc admin]$ id uid=501(oracle) gid=503(oinstall) groups=503(oinstall),504(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 [oracle@dc admin]$ cd $ORACLE_HOME/rdbms/admin [oracle@dc admin]$ ls -ltr catalog.sql catproc.sql -rw-r--r--. 1 oracle oinstall 59322 Apr 24 2008 catalog.sql -rw-r--r--. 1 oracle oinstall 20094 Feb 24 2012 catproc.sql [oracle@dc admin]$ sqlplus / as sysdba SQL> @catalog.sql This Creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms; also calls the scripts CATAUDIT.SQL, CATESP.SQL, and CATLDR.SQL Snap from catalog.sql script: Comment created. Comment created. Synonym created. Grant succeeded. PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATALOG 2017-12-22 13:06:58 The above script will create below dictionary tables: SQL> select tname from tab where tname like '%DBA_%'; TNAME ------------------------------ DBA_2PC_NEIGHBORS DBA_2PC_PENDING DBA_ALL_TABLES DBA_ARGUMENTS DBA_ASSEMBLIES DBA_ASSOCIATIONS DBA_AUDIT_EXISTS DBA_AUDIT_OBJECT DBA_AUDIT_SESSION DBA_AUDIT_STATEMENT DBA_AUDIT_TRAIL DBA_CATALOG DBA_CLUSTERS DBA_CLUSTER_HASH_EXPRESSIONS DBA_CLU_COLUMNS DBA_COLL_TYPES DBA_COL_COMMENTS DBA_COL_PENDING_STATS DBA_COL_PRIVS DBA_CONNECT_ROLE_GRANTEES DBA_CONSTRAINTS DBA_CONS_COLUMNS DBA_CONS_OBJ_COLUMNS DBA_DB_LINKS DBA_DIMENSIONS DBA_DIM_ATTRIBUTES DBA_DIM_CHILD_OF DBA_DIM_HIERARCHIES DBA_DIM_JOIN_KEY DBA_DIM_LEVELS DBA_DIM_LEVEL_KEY DBA_DIRECTORIES DBA_EDITIONING_VIEWS DBA_EDITIONING_VIEWS_AE DBA_EDITIONING_VIEW_COLS DBA_EDITIONING_VIEW_COLS_AE DBA_EDITIONS DBA_EDITION_COMMENTS DBA_ENCRYPTED_COLUMNS DBA_EXP_FILES DBA_EXP_OBJECTS DBA_EXP_VERSION DBA_EXTERNAL_LOCATIONS DBA_EXTERNAL_TABLES DBA_IDENTIFIERS DBA_INDEXES DBA_INDEXTYPES DBA_INDEXTYPE_ARRAYTYPES DBA_INDEXTYPE_COMMENTS DBA_INDEXTYPE_OPERATORS DBA_IND_COLUMNS DBA_IND_EXPRESSIONS DBA_IND_PARTITIONS DBA_IND_PENDING_STATS DBA_IND_SUBPARTITIONS DBA_INVALID_OBJECTS DBA_JOIN_IND_COLUMNS DBA_LIBRARIES DBA_LOBS DBA_LOB_PARTITIONS DBA_LOB_SUBPARTITIONS DBA_LOB_TEMPLATES DBA_LOG_GROUPS DBA_LOG_GROUP_COLUMNS DBA_METHOD_PARAMS DBA_METHOD_RESULTS DBA_MINING_MODELS DBA_MINING_MODEL_ATTRIBUTES DBA_MINING_MODEL_SETTINGS DBA_MINING_MODEL_TABLES DBA_MVIEW_AGGREGATES DBA_MVIEW_ANALYSIS DBA_MVIEW_COMMENTS DBA_MVIEW_DETAIL_PARTITION DBA_MVIEW_DETAIL_RELATIONS DBA_MVIEW_DETAIL_SUBPARTITION DBA_MVIEW_JOINS DBA_MVIEW_KEYS DBA_NESTED_TABLES DBA_NESTED_TABLE_COLS DBA_OBJECTS DBA_OBJECTS_AE DBA_OBJECT_TABLES DBA_OBJ_AUDIT_OPTS DBA_OBJ_COLATTRS DBA_OLDIMAGE_COLUMNS DBA_OPANCILLARY DBA_OPARGUMENTS DBA_OPBINDINGS DBA_OPERATORS DBA_OPERATOR_COMMENTS DBA_OPTSTAT_OPERATIONS DBA_PARTIAL_DROP_TABS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_PART_KEY_COLUMNS DBA_PART_LOBS DBA_PART_TABLES DBA_PENDING_CONV_TABLES DBA_PLSQL_OBJECT_SETTINGS DBA_PRIV_AUDIT_OPTS DBA_PROCEDURES DBA_PROFILES DBA_PROXIES DBA_PUBLISHED_COLUMNS DBA_RECYCLEBIN DBA_REFS DBA_RESUMABLE DBA_REWRITE_EQUIVALENCES DBA_ROLES DBA_ROLE_PRIVS DBA_ROLLBACK_SEGS DBA_SECONDARY_OBJECTS DBA_SEQUENCES DBA_SERVICES DBA_SOURCE_TABLES DBA_SQLJ_TYPES DBA_SQLJ_TYPE_ATTRS DBA_SQLJ_TYPE_METHODS DBA_STMT_AUDIT_OPTS DBA_STORED_SETTINGS DBA_SUBPARTITION_TEMPLATES DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS DBA_SUBPART_KEY_COLUMNS DBA_SUBSCRIBED_COLUMNS DBA_SUBSCRIBED_TABLES DBA_SUBSCRIPTIONS DBA_SUMMARIES DBA_SUMMARY_AGGREGATES DBA_SUMMARY_DETAIL_TABLES DBA_SUMMARY_JOINS DBA_SUMMARY_KEYS DBA_SYNONYMS DBA_SYS_PRIVS DBA_TABLES DBA_TAB_COLS DBA_TAB_COLUMNS DBA_TAB_COL_STATISTICS DBA_TAB_COMMENTS DBA_TAB_HISTGRM_PENDING_STATS DBA_TAB_HISTOGRAMS DBA_TAB_MODIFICATIONS DBA_TAB_PARTITIONS DBA_TAB_PENDING_STATS DBA_TAB_PRIVS DBA_TAB_STATS_HISTORY DBA_TAB_STAT_PREFS DBA_TAB_SUBPARTITIONS DBA_TSTZ_TABLES DBA_TSTZ_TAB_COLS DBA_TYPES DBA_TYPE_ATTRS DBA_TYPE_METHODS DBA_TYPE_VERSIONS DBA_UNUSED_COL_TABS DBA_UPDATABLE_COLUMNS DBA_USERS DBA_USTATS DBA_VARRAYS DBA_VIEWS DBA_VIEWS_AE 163 rows selected. The catproc.sql runs all scripts required for or used with PL/SQL: CATPRC.SQL, CATRSNAP.SQL, CATRPC.SQL, STANDARD.SQL, DBMSSTDX.SQL, PIPDL.SQL, PIDIAN.SQL, DIUTIL.SQL, PISTUB.SQL, DBMSSNAP.SQL, DBMSLOCK.SQL, DBMSPIPE.SQL, DBMSALRT.SQL, SBMSOTPT.SQL, DBMSDESC.SQL SQL> @catproc.sql DOC>###################################################################### DOC>###################################################################### DOC> The following PL/SQL block will cause an ORA-20000 error and DOC> terminate the current SQLPLUS session if the user is not SYS. DOC> Disconnect and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC># PL/SQL procedure successfully completed. Session altered. PL/SQL procedure successfully completed. View created. View created. Comment created. |
Step 6: Now compile the invalid objects and restart the
database.
[oracle@dc admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin [oracle@dc admin]$ ls -ltr *utlrp.sql -rw-r--r--. 1 oracle oinstall 3221 Aug 15 2003 utlrp.sql [oracle@dc admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 13:23:19 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select owner,object_type,status,count(*) from dba_objects where status='INVALID' group by owner,object_type,status order by owner; OWNER OBJECT_TYPE STATUS COUNT(*) --------- ------------------- ------- ---------- PUBLIC SYNONYM INVALID 12 SYS FUNCTION INVALID 7 SYS PACKAGE INVALID 2 SYS PACKAGE BODY INVALID 1 SYS TYPE BODY INVALID 3 SYS VIEW INVALID 22 6 rows selected. SQL> @utlrp.sql |
Step 7: Run pubbld.sql script by system user.
While connecting to SQL*Plus , sometimes you will get the message "Warning:
Product user profile information not loaded".
How to load this product user profile?
SQL*Plus "Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM"
You may need to run PUPBLD.SQL as SYSTEM"
The PRODUCT_USER_PROFILE (PUP) table provides product-level
security that supplements the user-level security provided by the SQL GRANT and
REVOKE commands and user roles. To create the PUP table, log in to SQL*Plus as
the SYSTEM user and run PUPBLD.SQL
SP2-0623 - Error accessing PRODUCT_USER_PROFILE.
Warning: Product user profile information not loaded! You may need to run
PUPBLD.SQL as SYSTEM
Cause - The PRODUCT_USER_PROFILE table has not been built in the SYSTEM account.
Resolution - The exact format of the file extension and location of the file are system dependent.
Cause - The PRODUCT_USER_PROFILE table has not been built in the SYSTEM account.
Resolution - The exact format of the file extension and location of the file are system dependent.
The script must be run as user SYSTEM. The product_user_profile must
be owned by SYSTEM, and you will get an error if you run pupbld.sql as SYS.
#Query to check DBID of database in Oracle SQL> select name,open_mode,dbid from v$database; [oracle@dc TEST]$ id uid=501(oracle) gid=503(oinstall) groups=503(oinstall),504(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 [oracle@dc TEST]$ cd $ORACLE_HOME/sqlplus/admin [oracle@dc admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin [oracle@dc admin]$ ls -ltr total 20 -rw-r--r--. 1 oracle oinstall 2118 Feb 16 2003 pupbld.sql -rw-r--r--. 1 oracle oinstall 813 Mar 7 2006 plustrce.sql -rw-r--r--. 1 oracle oinstall 368 Apr 10 2011 glogin.sql -rw-r--r--. 1 oracle oinstall 226 Jul 17 2013 libsqlplus.def drwxr-xr-x. 2 oracle oinstall 4096 Nov 14 22:32 help [oracle@dc admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 15:24:34 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "SYS" SQL> conn system/manager Connected. SQL> @?/sqlplus/admin/pupbld.sql View created. Grant succeeded. Synonym dropped. Synonym created. Synonym created. Synonym dropped. Synonym created. |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !
Thank you for your comment !