Ora CreaDB
main resize tbs DN Crea_DB

.profileSID

export ORACLE_HOME=/app/oracle/product/12.1.0
export ORACLE_BASE=/app/oracle
export PATH=$PATH:$ORACLE_HOME/bin:.
sid=`echo ${$ORACLE_SID} | tr '[:upper:]' '[:lower:]'`

alias OFA='cd /app/oracle/admin/$ORACLE_SID'
alias OH=' cd $ORACLE_HOME'
alias RMAN='rman target / catalog rman${sid}/rman${sid}@reporman'
alias SQL='sqlplus "/as sysdba"'
alias TA='tail -1000f /app/oracle/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log'
alias autoload='typeset -fu'
alias cd..='cd ..'
alias cl=clear
alias command='command '
alias cp='cp -i'
alias functions='typeset -f'
alias history='fc -l'
alias integer='typeset -i'
alias la='ls -la'
alias ll='ls -l'
alias local=typeset
alias lt='ls -lrt'
alias nohup='nohup '
alias oadhoc='cd $ORACLE_BASE\/admin/$ORACLE_SID/adhoc'
alias oadmin='cd $ORACLE_BASE\/admin/$ORACLE_SID'
alias oarch='cd $ORACLE_BASE\/admin/$ORACLE_SID\/arch'
alias obase='cd $ORACLE_BASE'
alias obdump='cd $ORACLE_BASE\/admin/$ORACLE_SID\/bdump'
alias ocdump='cd $ORACLE_BASE\/admin/$ORACLE_SID\/cdump'
alias ocreate='cd $ORACLE_BASE\/admin/$ORACLE_SID\/create'
alias ocron='cd $ORACLE_BASE\/admin/$ORACLE_SID\/cron'
alias oexp='cd $ORACLE_BASE\/admin/$ORACLE_SID\/exp'
alias ohome='cd $ORACLE_HOME'
alias onetad='cd $ORACLE_HOME\/network\/admin'
alias opfile='cd $ORACLE_BASE\/admin/$ORACLE_SID\/pfile'
alias orman='cd $ORACLE_BASE\/admin/$ORACLE_SID\/RMAN'
alias oudump='cd $ORACLE_BASE\/admin/$ORACLE_SID\/udump'
alias r='fc -e -'
alias stop='kill -STOP'
alias suspend='kill -STOP $$'
set -o vi
export PS1="`hostname`@\$ORACLE_SID:\$PWD # "
export PATH=$PATH:/usr/sbin:/usr/local/bin:/usr/openwin/bin

stty erase ^H

OFA

#!/bin/sh

if [[ -n $1 ]] 
then
    SID=$1
elif [[ -n "$ORACLE_SID" ]]
then 
    SID=$ORACLE_SID
else 
    printf "%s: definisci ORACLE_SID oppure passa il SID come parametro.\nEs. %s OOM_PRE\n\n" "$0" "$0"
    exit 2
fi

sid=`echo ${SID} | tr '[:upper:]' '[:lower:]'`

printf "%s: Creazione OFA per SID=%s. Premi CTRL+C per abortire, o INVIO per continuare: " "$0" "$SID"
read n

OLD_UMASK=`umask`
umask 0027

mkdir -p /${sid}/${SID}

mkdir -p /app/oracle/admin/${SID}/adhoc
mkdir -p /app/oracle/admin/${SID}/adump
mkdir -p /app/oracle/admin/${SID}/bdump
mkdir -p /app/oracle/admin/${SID}/cdump
mkdir -p /app/oracle/admin/${SID}/cron
mkdir -p /app/oracle/admin/${SID}/diag
mkdir -p /app/oracle/admin/${SID}/dpdump
mkdir -p /app/oracle/admin/${SID}/exp
mkdir -p /app/oracle/admin/${SID}/fra
mkdir -p /app/oracle/admin/${SID}/pfile
mkdir -p /app/oracle/admin/${SID}/RMAN
mkdir -p /app/oracle/admin/${SID}/scripts

mkdir -p /app/oracle/audit
mkdir -p /app/oracle/cfgtoollogs/dbca/${SID}
mkdir -p /app/oracle/product/12.1.0/dbs

mkdir -p /ora01_scm/${SID}/cntl
mkdir -p /ora01_scm/${SID}/data
mkdir -p /ora01_scm/${SID}/index
mkdir -p /ora02_scm/${SID}/cntl
mkdir -p /ora02_scm/${SID}/data
mkdir -p /ora02_scm/${SID}/index

mkdir -p /redo_scm/redo_a/${SID}
mkdir -p /redo_scm/redo_b/${SID}

umask ${OLD_UMASK}

link simbolici OFA

#!/bin/sh
[[ -z "$ORACLE_SID" ]] && { echo "ORACLE_SID non definita."; exit 2; }

SID=$ORACLE_SID

cd /app/oracle/admin/${SID}/bdump
ln -s /app/oracle/admin/${SID}/diag/rdbms/${sid}/${SID}/trace/alert_${SID}.log alert_${SID}.log

cd /app/oracle/admin/${SID}/pfile
ln -s $ORACLE_HOME/dbs/orapw${SID} orapw${SID}
ln -s $ORACLE_HOME/dbs/spfile${SID}.ora spfile${SID}.ora

Function FW_VERIFY_FUNCTION

CREATE OR REPLACE FUNCTION "SYS"."FW_VERIFY_FUNCTION"
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';
   -- Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20001, 'La password non puo'' essere uguale all''utente');
   END IF;
   -- Check for the minimum length of the password
   IF(length(password) < 8)or(length(password) > 10) THEN
      raise_application_error(-20002, 'La lunghezza della password deve essere compresa tra 8 e 10 caratteri');
   END IF;
   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user',
     'password', 'oracle', 'computer', 'abcd', 'manager', 'pippo', 'pluto') THEN
      raise_application_error(-20002, 'Password troppo facile');
   END IF;
   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'La password deve contenere almeno un numero');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'La password deve contenere almeno un carattere');
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

PROFILE "FW_USER_APP"

CREATE PROFILE "FW_USER_APP"
    LIMIT
         COMPOSITE_LIMIT DEFAULT
         SESSIONS_PER_USER DEFAULT
         CPU_PER_SESSION DEFAULT
         CPU_PER_CALL DEFAULT
         LOGICAL_READS_PER_SESSION DEFAULT
         LOGICAL_READS_PER_CALL DEFAULT
         IDLE_TIME DEFAULT
         CONNECT_TIME DEFAULT
         PRIVATE_SGA DEFAULT
         FAILED_LOGIN_ATTEMPTS UNLIMITED
         PASSWORD_LIFE_TIME UNLIMITED
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION "FW_VERIFY_FUNCTION"
         PASSWORD_LOCK_TIME UNLIMITED
         PASSWORD_GRACE_TIME UNLIMITED;

Default TBS

CREATE TABLESPACE "DATA" DATAFILE '/ora01_xxx/XXX_XXX/data/data_01.dbf' size 2G;
CREATE TABLESPACE "INDEX" DATAFILE '/ora01_xxx/XXX_XXX/index/index_01.dbf' size 2G;

resize TEMP & UNDO

ALTER DATABASE TEMPFILE '/ora01_xxx/XXX_XXX/data/temp01.dbf' AUTOEXTEND OFF;
ALTER DATABASE TEMPFILE '/ora01_xxx/XXX_XXX/data/temp01.dbf' RESIZE 1G;
 
ALTER DATABASE DATAFILE '/ora01_xxx/XXX_XXX/data/undotbs01.dbf' AUTOEXTEND OFF;
ALTER DATABASE DATAFILE '/ora01_xxx/XXX_XXX/data/undotbs01.dbf' RESIZE 1G;

resize SGA

alter system set SGA_TARGET=&sz_gbscope=spfile;
alter system set SGA_MAX_SIZE=&sz_gb scope=spfile;
shut immediate
startup

listener

LSNR_XXX_XXX =
      (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= IPC)(KEY= XXX_XXX))
        (ADDRESS= (PROTOCOL = TCP)(HOST = db000sid.intranet.fw)(PORT = 1531))
      )

SID_LIST_LSNR_XXX_XXX =
 (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = XXX_XXX)
      (ORACLE_HOME = /app/oracle/product/12.1.0)
      (SID_NAME = XXX_XXX)
    )
 )

tnsnames

XXX_XXX =
  (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = db000sid.intranet.fw)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XXX_XXX)
    )
  )

Cloud Control discovery

alter user dbsnmp account unlock;
alter user dbsnmp identified by "4g0st0";

https://co805oem.intranet.fw:7799/em/faces/logon
sysman/ss1#dmOEM
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License