Skip to main content

ORACLE DATABASE CREATE SCRIPT




/* Script creates PVPROD02 database

Actions include;
   Create Database
   Create Tablespaces
   Setup Dictionary
   Create Application Roles
   Create Application Users

*/

------------------------------------------------------------------------------------------
-- set SID
------------------------------------------------------------------------------------------
define CR_SID = PVPROD02

spool create_db_&CR_SID

set time on
set timing on
set echo on
set pagesize 4000
set linesize 2000
set trims on
 
connect / as sysdba

startup  nomount


------------------------------------------------------------------------------------------
-- Create Database
------------------------------------------------------------------------------------------

CREATE DATABASE &CR_SID ARCHIVELOG
    USER SYS IDENTIFIED BY sys4&CR_SID
    USER SYSTEM IDENTIFIED BY system4&CR_SID
    MAXLOGFILES 50
    MAXLOGMEMBERS 4
    MAXDATAFILES 1000
    MAXINSTANCES 8
    MAXLOGHISTORY 20000
    CHARACTER SET UTF8
LOGFILE
  GROUP 01 ('/u4002/oradata/PVPROD02/log_01a.ora', '/u4003/oradata/PVPROD02/log_01b.ora') SIZE 200M,
  GROUP 02 ('/u4002/oradata/PVPROD02/log_02a.ora', '/u4003/oradata/PVPROD02/log_02b.ora') SIZE 200M,
  GROUP 03 ('/u4002/oradata/PVPROD02/log_03a.ora', '/u4003/oradata/PVPROD02/log_03b.ora') SIZE 200M,
  GROUP 04 ('/u4002/oradata/PVPROD02/log_04a.ora', '/u4003/oradata/PVPROD02/log_04b.ora') SIZE 200M,
  GROUP 05 ('/u4002/oradata/PVPROD02/log_05a.ora', '/u4003/oradata/PVPROD02/log_05b.ora') SIZE 200M,
  GROUP 06 ('/u4002/oradata/PVPROD02/log_06a.ora', '/u4003/oradata/PVPROD02/log_06b.ora') SIZE 200M,
  GROUP 07 ('/u4002/oradata/PVPROD02/log_07a.ora', '/u4003/oradata/PVPROD02/log_07b.ora') SIZE 200M,
  GROUP 08 ('/u4002/oradata/PVPROD02/log_08a.ora', '/u4003/oradata/PVPROD02/log_08b.ora') SIZE 200M
--   CHARACTER SET US7ASCII
--   NATIONAL CHARACTER SET AL16UTF16
DATAFILE  '/u4004/oradata/PVPROD02/system_01.dbf' SIZE 600M 
    EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE  '/u4004/oradata/PVPROD02/sysaux_01.dbf' SIZE 500M REUSE
UNDO TABLESPACE UNDO
    DATAFILE '/u4005/oradata/PVPROD02/UNDO_01.dbf' SIZE 1000M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMPTEMP
    TEMPFILE '/u4006/oradata/PVPROD02/temptemp_01.dbf' SIZE 1000M AUTOEXTEND OFF
;


------------------------------------------------------------------------------------------
-- Create Tablespaces
------------------------------------------------------------------------------------------


--CREATE  TABLESPACE TEMP
--      DATAFILE '/u4006/oradata/PVPROD02/temp_01.dbf' SIZE 2000M,
--               '/u4006/oradata/PVPROD02/temp_02.dbf' SIZE 2000M
--      EXTENT MANAGEMENT LOCAL;

--DROP TABLESPACE TEMP;

CREATE TEMPORARY TABLESPACE TEMP
      TEMPFILE '/u4006/oradata/PVPROD02/temp_01.dbf' SIZE 2000M,
               '/u4006/oradata/PVPROD02/temp_02.dbf' SIZE 2000M
      ;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE TEMPTEMP INCLUDING CONTENTS AND DATAFILES;


CREATE TABLESPACE TOOLS LOGGING
      DATAFILE '/u4007/oradata/PVPROD02/tools_01.dbf' SIZE 500M
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO
;

CREATE TABLESPACE USERS LOGGING
      DATAFILE '/u4007/oradata/PVPROD02/users_01.dbf' SIZE 500M
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO
;


------------------------------------------------------------------------------------------
-- ROMT
------------------------------------------------------------------------------------------
CREATE TABLESPACE ROMTAPP LOGGING
      DATAFILE '/u4010/oradata/PVPROD02/romtapp_01.dbf' SIZE 2000M REUSE
      EXTENT MANAGEMENT LOCAL 
      SEGMENT SPACE MANAGEMENT AUTO
;

CREATE TABLESPACE ROMTRPT LOGGING
      DATAFILE '/u4011/oradata/PVPROD02/romtrpt_01.dbf' SIZE 2000M REUSE
      EXTENT MANAGEMENT LOCAL 
      SEGMENT SPACE MANAGEMENT AUTO
;



/***************************************************************
------------------------------------------------------------------------------------------
-- TEMP_DBA
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE TEMP_DBA
       TEMPFILE '/u4006/oradata/PVPROD02/TEMP_DBA_01.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_02.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_03.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_04.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_05.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_06.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_07.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_08.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_09.dbf' SIZE 2000M REUSE AUTOEXTEND OFF,
                '/u4006/oradata/PVPROD02/TEMP_DBA_10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF
       EXTENT MANAGEMENT LOCAL
;
************************************************************/




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

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



select name from v$datafile;

------------------------------------------------------------------------------------------
-- Setup Dictionary
------------------------------------------------------------------------------------------

set echo off
set timing off
set time off
spool create_post_db_&CR_SID

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
-- @?/rdbms/admin/catrep.sql 


-- Create PLUSTRACE role
@?/sqlplus/admin/plustrce.sql

connect system/system4&CR_SID

@?/sqlplus/admin/pupbld.sql

set echo on
set timing on
set time on
spool create_post1_db_&CR_SID


------------------------------------------------------------------------------------------
-- Create Application Roles
------------------------------------------------------------------------------------------


--Role of old connect
CREATE ROLE "CONNECT_SCHEMA"  NOT IDENTIFIED;
GRANT CREATE CLUSTER TO "CONNECT_SCHEMA";
GRANT CREATE DATABASE LINK TO "CONNECT_SCHEMA";
GRANT CREATE SEQUENCE TO "CONNECT_SCHEMA";
GRANT CREATE SESSION TO "CONNECT_SCHEMA";
GRANT CREATE SYNONYM TO "CONNECT_SCHEMA";
GRANT CREATE TABLE TO "CONNECT_SCHEMA";
GRANT CREATE VIEW TO "CONNECT_SCHEMA";
GRANT CREATE PROCEDURE TO "CONNECT_SCHEMA";
GRANT CREATE TRIGGER TO "CONNECT_SCHEMA";
GRANT CREATE TYPE TO "CONNECT_SCHEMA";


------------------------------------------------------------------------------------------
-- Create DBA Users
------------------------------------------------------------------------------------------

CREATE USER DBADMIN
IDENTIFIED BY DBADMIN
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
GRANT SELECT ANY DICTIONARY TO DBADMIN;
GRANT UNLIMITED TABLESPACE TO DBADMIN;
GRANT CONNECT TO DBADMIN;
GRANT DBA TO DBADMIN;
GRANT EXP_FULL_DATABASE TO DBADMIN;
GRANT IMP_FULL_DATABASE TO DBADMIN;
GRANT OEM_MONITOR TO DBADMIN;
GRANT SELECT_CATALOG_ROLE TO DBADMIN;




CREATE USER BBUSER IDENTIFIED BY BBUSER PASSWORD EXPIRE
 DEFAULT TABLESPACE USERS  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO BBUSER;

CREATE USER ATRACHTENBERG IDENTIFIED BY ATRACHTENBERG PASSWORD EXPIRE
 DEFAULT TABLESPACE USERS  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, CONNECT_SCHEMA, RESOURCE, DBA, PLUSTRACE TO ATRACHTENBERG;

CREATE USER DPAUL IDENTIFIED BY DPAUL PASSWORD EXPIRE
 DEFAULT TABLESPACE USERS  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, CONNECT_SCHEMA, RESOURCE, DBA, PLUSTRACE TO DPAUL;
GRANT EXECUTE ANY PROCEDURE TO DPAUL;

ALTER USER SYS IDENTIFIED BY harpo;
ALTER USER SYSTEM IDENTIFIED BY chico;



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

connect / as sysdba
shutdown immediate
startup


-- alter user sysman identified by sysman4&CR_SID;

spool create_post2_db_&CR_SID

define default_tablespace='tools'
define temporary_tablespace='temp'
define perfstat_password='perfstat'
@?/rdbms/admin/spcreate
undefine perfstat_password

spool off
quit;

Labels:

Comments

Post a Comment

Popular posts from this blog

CHANGING DBID FOR ORACLE DATABASE 11G

C:\Users\computer>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 21:03:33 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select dbid from v$database;       DBID ---------- 2188161033 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\computer>nid DBNEWID: Release 11.1.0.7.0 - Production on Thu Sep 10 21:04:44 2013 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET ...
Post a Comment
Read more

Difference between AWR, ADDM and ASH reports

AWR: Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. Different Components that uses AWR are: Automatic Database Diagnostic Monitor Undo Advisor SQL Tuning Advisor Segment Advisor Different types of AWR Reports for different purposes: For Single Instance Environment: @$ORACLE_HOME/rdbms/admin/awrrpt.sql For Oracle RAC Environment : @$ORACLE_HOME/rdbms/admin/awrgrpt.sql For a particular SQL Statement : @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql For Comparing the reports : @$ORACLE_HOME/rdbms/admin/awrddrpt.sql ADDM: Automatic Database Diagnostic Monitoring Report analyzes the AWR data on a regular basis, to give you overview of the root cause of the problem which is affecting your database’s performance. It also provides suggestions or recommendations for rectifying any problem identified and lists the areas which are having no issues. ADDM recommends multiple solutions for the DBA to choose from which...
Post a Comment
Read more