Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 11, 2015

How to create database using SQLPLUS/Manually/script

Create database using SQLPLUS:



create the parameter file:



[oracle@collabn2 ~]$ cd $ORACLE_HOME/dbs

[oracle@collabn2 dbs]$ ls -lrt
total 40
-rw-r--r-- 1 oracle oinstall 2851 Aug  1 17:57 init.ora

[oracle@collabn2 dbs]$ cp init.ora initPROD2.ora

[oracle@collabn2 dbs]$ vi initPROD2.ora

db_name='PROD2'
memory_target=400m
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD2/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/oradata/PROD2/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/admin'
dispatchers='(PROTOCOL=TCP) (SERVICE=PROD2XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/u01/app/oracle/oradata/PROD2/ora_control1.ctl','/u01/app/oracle/oradata/PROD2/ora_control2.ctl')
compatible ='11.2.0'

:%s/orcl/PROD2/g

Create the necessary directories:


[oracle@collabn2 dbs]$ mkdir -p /u01/app/oracle/oradata/PROD2/flash_recovery_area
mkdir -p /u01/app/oracle/admin/PROD2/adump



create the create_db.sql script:




Page- 2-11

Edit the below SQL command

CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
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 undotbs1
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

to

vi create_db.sql
CREATE DATABASE PROD2
USER SYS IDENTIFIED BY XXXXXX
USER SYSTEM IDENTIFIED BY XXXXXX
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD2/my/redo01a.log','/u01/app/oracle/oradata/PROD2/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/PROD2/my/redo02a.log','/u01/app/oracle/oradata/PROD2/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/PROD2/my/redo03a.log','/u01/app/oracle/oradata/PROD2/my/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/PROD2/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD2/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/PROD2/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/PROD2/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/PROD2/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

I have to replace the variables as below as per my sandbox:

vi create_db.sql

:%s/mynewdb/PROD2/g

:%s/logs/app\/oracle\/oradata\/PROD2/g


Set the environment:


[oracle@collabn2 PROD2]$ env | grep ORA
ORACLE_SID=PROD2
ORACLE_BASE=/u01/app/oracle
ORAENV_ASK=NO
ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD


start the DB in nomount



[oracle@collabn2 PROD2]$ ls -lrt

-rw-r--r-- 1 oracle oinstall 1137 Aug  5 18:17 create_db.sql

[oracle@collabn2 PROD2]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 5 18:18:12 2015

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

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initPROD2.ora';
ORACLE instance started.

Total System Global Area  158662656 bytes
Fixed Size           2211448 bytes
Variable Size         109052296 bytes
Database Buffers       41943040 bytes
Redo Buffers           5455872 bytes


SQL> @create_db.sql

Database created.

Build Dictionary:


SQL> @?/rdbms/admin/catalog.sql


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATALOG      2015-08-05 18:19:53

SQL> @?/rdbms/admin/catproc.sql

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATPROC    2015-08-05 18:29:22

1 row selected.

SQL>
SQL> SET SERVEROUTPUT OFF

SQL> @?/sqlplus/admin/pupbld.sql


SQL>
SQL> -- End of pupbld.sql


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD2     READ WRITE

1 row selected.

Create password file:


[oracle@collabn2 dbs]$ ls -lrt orapw*

[oracle@collabn2 dbs]$ orapwd file=orapwPROD2 password=XXXXX entries=5


[oracle@collabn2 dbs]$ ls -lrt orapwPROD2*
-rw-r----- 1 oracle oinstall 2048 Aug  5 18:34 orapwPROD2


setup listener & tnsnames.ora:


export TNS_ADMIN=$ORACLE_HOME/network/admin

[oracle@collabn2 admin]$ cp samples/tnsnames.ora .


vi  $TNS_ADMIN/tnsnames.ora
PROD2  =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn2.shaiksameer)(PORT = 1621))
    (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = prod2.shaiksameer)
    )



[oracle@collabn2 admin]$ cp samples/listener.ora .

[oracle@collabn2 admin]$ pwd
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin


[oracle@collabn2 admin]$ ls -lrt
total 20
-rw-r--r-- 1 oracle oinstall  187 Aug  1 17:57 shrept.lst
-rw-r----- 1 oracle oinstall  963 Aug  5 18:38 tnsnames.ora
drwxr-xr-x 2 oracle oinstall 4096 Aug  5 18:52 samples
-rw-r--r-- 1 oracle oinstall  337 Aug  6 13:30 listener.ora


vi listener.ora
LISTENER =
 (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=collabn2.shaiksameer)(PORT=1621))
       #(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
 )
SID_LIST_LISTENER=
  (SID_LIST=
       (SID_DESC=
         (GLOBAL_DBNAME=PROD2.shaiksameer)
         (SID_NAME=PROD2)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD)
        )
  )

Start the listener:


[oracle@collabn2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-AUG-2015 07:22:51

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

Starting /u01/app/oracle/product/11.2.0.2/SHAIKPROD/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/collabn2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn2.shaiksameer)(PORT=1621)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=collabn2.shaiksameer)(PORT=1621))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-AUG-2015 07:22:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/collabn2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn2.shaiksameer)(PORT=1621)))
Services Summary...
Service "PROD2" has 1 instance(s).
  Instance "PROD2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
 

[oracle@collabn2 ~]$ tnsping prod2

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 11-AUG-2015 07:23:00

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn2.shaiksameer)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod2)))
OK (0 msec)

No comments: