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

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



My Cloud Certifications:

GIAC Cloud Penetration Tester (GCPN)

GIAC Cloud Security Automation (GCSA)

GIAC Security Essentials (GSEC)

Certified Kubernetes Administrator (CKA)

Cloud Certified Security Professional (ISC2)

CyberSecurity Certified Professional (ISC2)

AWS Certified Solutions Architect Associate

Azure Certified Architect Expert

Azure Certified Architect

Azure Certified Administrator

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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)
Posted by sameer at 7:34 AM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)