Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Creating Oracle 11g Database manually

-



Creating Database in 11g is very simple.In 10g you need to create additional
directories bdump,cdump,udump instead of diagnostic dump directory.Below are the
steps:


Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir testdb

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name='testdb'
memory_target=1G
processes = 150
audit_file_dest='/u03/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u03/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u03/testdb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u03/testdb/ora_control1.ctl,/u03/testdb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u03]$ cd /u03/testdb/

[oracle@localhost testdb]$ vi createdb_shaik.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u03/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u03/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u03/testdb/redo3.log' SIZE 10M
DATAFILE
'/u03/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u03/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u03/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u03/testdb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su - oracle

[oracle@localhost ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$export ORACLE_SID=testdb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb
password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_testdb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED

Note:Common issue memory_target not supported,refer the below link for resolving:

http://rafioracledba.blogspot.in/2011/06/ora-00845-memorytarget-not-supported-on.html

Step 8:Execute Create Database script created in Step 3

SQL> @/u03/testdb/createdb_shaik.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql
scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and
pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;


Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

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


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

Comments

Post a Comment

Popular posts from this blog

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

-
Image
Below tutorialdba.com collected and sorted out hundreds of universal License Keys for all major versions of VMware Workstation Pro (not for VMware Workstation Player) 4.x, 5.x, 6.x, 7.x, 8.x, 9.x, 10.x, 11.x, 12.x and v14.x on Windows and Linux platforms (support both 32-bit and 64-bit operating system) in this single post. Besides, we also provide some license keys for VMware other projects. Just enjoy and share them. // 4~14 Universal License Keys // Version License Keys VMware Workstation VMware Workstation 4.x.x ZHDH1-UR90N-W844G-4PTN6 G1NP0-T88AL-M016F-4P8N2 ZC14J-4U16A-0A04G-4MEZP J1WF8-58LDE-881DG-4M8Q3 VMware Workstation 5.x.x LUXRM-WP0DN-A256U-4M9Q3 DJXDR-NDT27-Y2NDU-4YTZK DA925-HP80U-Z8HDC-4WXXP 3KW2W-AYR2C-88M6F-4MDQ2 VMware Workstation 6.x.x A0E8R-YUDFV-6AK2F-4GAN2 CRX0D-VWL0V-7CJ6C-46C7A NA8RX-QPNDU-D2LA9-4WAZL 1H4WM-N21FZ-7GK2A-44U5U 6AJ6N-THY2P-42KEF-4WTFG FK8R9-LPCDT-88H4Y-4WRN3 KAR8R-T8MAL-K8J6A-4WDXQ YJEKW-JMFF4-YA1DC-4WTQP WC5FW-V0CFX
Read more

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

-
Method 1 You can check the mode of the server using "pg_controldata". [pgsql@test~]$ pg_controldata /usr/local/pgsql/data84/ Database cluster state: in archive recovery --> This is Standby Database Database cluster state: in production --> This is Production Database [Master] Method 2 You can use pg_is_in_recovery() which returns True if recovery is still in progress(so the server is running in standby mode or slave) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) If Return    false   so the server is running in primary mode or master postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
Read more

How to Configuration UDEV SCSI Rules In Oracle Linux 5, 6 and 7

-
For Oracle Automatic Storage Manager (ASM) to use disks, it needs to be able to identify the devices consistently and for them to have the correct ownership and permissions. In Linux you can use ASMLib to manage these tasks, but it is seen as an additional layer of complexity and has never really gained any popularity. Instead, many people use the Linux device manager "udev" to perform these tasks. This article presents a brief overview of setting up udev rules with respect to disks for use with ASM in Oracle 11g. The examples are all done using Oracle Linux 5, 6 and 7, so they will be consistent with RHEL and CentOS 5, 6 and 7. Background Essentially, what udev does is apply rules defined in files in the "/etc/udev/rules.d" directory to the device nodes listed in the "/dev" directory. The rules can be defined in a variety of ways, but what we need to do is identify the device and say what we want udev to do with it. In this case I know all my disk
Read more

Linux ctime,mtime,atime,cmin,amin,mmin

-
Unix and therefore Linux uses (at least) 3 different timestamps on modern file systems (see File systems Table for info) in order to date any files. You can use these information to search for files, check logs, manage your backup and more… that’s why it is a must  for any sysadmin to clearly understand this mechanism. This page is aimed at exposing basics knowledge to understand and use files timestamps. 1) Definitions Here are some time stamps related definitions. 1.1 atime This is the Access time : atime is updated whenever file’s data is accessed (a read, write or any other access); this could be done by a system process, interactively by CLI or by a script. 1.2 mtime This is the Modification time : mtime is updated whenever the file’s content changes. This time stamp is not updated by a change of files permissions (e.g : through a chown command). It is usually used for tracking the file content changes (see the Linux time related tools section below for more inf
Read more

aspell

-
aspell --version (return code: 0) @(#) International Ispell Version 3.1.20 (but really Aspell 0.60.7-20110707) aspell --help (return code: 0) Aspell 0.60.7-20110707. Copyright 2000-2011 by Kevin Atkinson. Usage: aspell [options] <command> <command> is one of: -?|usage display a brief usage message help display a detailed help message -c|check <file> to check a file -a|pipe "ispell -a" compatibility mode list produce a list of misspelled words from standard input [dump] config dumps the current configuration to stdout config <key> prints the current value of an option soundslike returns the sounds like equivalent for each word entered munch generate possible root words and affixes expand [1-4] expands affix flags clean [strict] cleans a word list so that every line is a valid word -v|version prints a version line munch-list [simple] [single|mu
Read more