DB INIT IN WINDOWS (FOR 12C)

 

Uat       oracleDB-Uat  192.168.63.121        Windows server 2012 R2         2核12G，硬盘160G

 内置用户是oaadmin,(建议将这个账号改称administrator ,然后使用用户来安装数据库)

administrator/

123

 

 

 

 

With Windows, you log in to a user account with Administrator privileges to install the Oracle Database software. You can also specify an Oracle Home User (standard Windows User Account, not Administrator account) during installation. On Linux and UNIX systems, you must create and use a software owner user account, and this user must belong to the Oracle Inventory group.

 

 

                       

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

oracle 密码与windows 密码一致

编码：AL32UTF8

数据库名unityuat

PDB     pdbunityuat

system/oracle123

非归档模式

 

show con_name

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

select con_id,dbid,name,open_mode from v$pdbs;

 

alter pluggable database PDBUNITYUAT open;

 

CREATE TRIGGER open_all_pdbs AFTER STARTUP ON DATABASE

BEGIN

    EXECUTE IMMEDIATE 'alter pluggable database all open' ;

END open_all_pdbs ;

 

alter session set  container=pcndba2;

 

 

DTARUAT =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER_dtaruat))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.145)(PORT = 15021))

    (ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.27.145)(PORT = 15022))

   )

  )

SID_LIST_DTARUAT=

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = dtaruat)

      (ORACLE_HOME = /opt/oracle12c/product/12.1.0)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = pdtaruat)

      (SID_NAME = dtaruat)

      (ORACLE_HOME = /opt/oracle12c/product/12.1.0)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = pTESTtuat)

      (SID_NAME = dtaruat)

      (ORACLE_HOME = /opt/oracle12c/product/12.1.0)

    )

  )

SECURE_REGISTER_dtaruat= (IPC)

SECURE_CONTROL_dtaruat=(TCPS,IPC)

ADMIN_RESTRICTIONS_dtaruat= ON

DIAG_ADR_ENABLED_dtaruat= OFF

 

 

 

 

 

1、配置监听

 

首先要明确，所有的PDB都使用1个监听，配置多个实际上启动时也只有第1个有意义。

LISTENER=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))

  )

 

接下来使用SID_LIST_LISTENER来进行静态注册服务。

SID_LIST_LISTENER=

(SID_LIST=

  (SID_DESC =

  (GLOBAL_DBNAME = ora12c)

  (SID_NAME = ora12c)

  )

 

  (SID_DESC =

  (GLOBAL_DBNAME = pdborcl)#该服务是我配置的pdb信息

   (SID_NAME = ora12c)

  )

)

 

ADR_BASE_LISTENER= /opt/oracle

 

 

 

2、tnsnames.ora配置

 

观察发现，在tnsnames中配置pdb跟CDB，即原来11g的配置完全一样。这里SERVICE_NAME = pdborcl使用得是PDB的名字，可以在v$pdbs中查看。

 

 ORA12C=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora12c)

    )

  )

 

 

pdborcl=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = pdborcl)

    )

  )

 

 

 

3、为了保险，检查下sqlnet.ora

 

NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)

 

这一句是为了保证优先使用TNSNAMES解析。

 

配置完，在PDB启动的情况下（怎么启动这里不多讲了），就可以直接连接到PDB了。（Oracle12c是没有scott用户的，我自己在PDB下面新建的）。

 

 

 

 

 

 

win2012关闭防火墙

• win2012操作系统

百度经验:jingyan.baidu.com

方法/步骤

1. 1

点击右下角窗口图标，如图

步骤阅读

点击管理工具，如图

点击高级安全windows防火墙，如图

步骤阅读

1. 4

点击windows防火墙属性，再在防火墙状态选择关闭选项即可，如图

 

 

APPLY PSU and  OJVM PSU

http://www.cnblogs.com/jyzhao/p/5895617.html

set ORACLE_HOME=C:\app\oracle\product\12.1.0\dbhome_1

set PATH=%ORACLE_HOME%\perl\bin;%ORACLE_HOME%\OPatch;%PATH%

C:\app\oracle\product\12.1.0\dbhome_1\OPatch\opatch apply

设置环境变量：

1. 在弹出的窗口中点击顶部的“计算机”标签，然后在出现的菜单中点击“系统属性”选项。
2. 在新的页面中，点击左侧导航栏中的“高级系统设置”。
3. 在弹出的页面中， 点击下部的“环境变量”按钮。弹出环境变量的管理页面。
4. 在环境变量的管理页面中，在下部列表框中找到Path变量， 单击选中， 然后点击下面的“编辑”按钮。
5. 弹出的页面有两个输入框， 在“变量值”输入框的末尾添加你要增加的路径，格式为 ;全路径，即分号加路径。注意这里分号必须是英文分号。比如我在安装Python2.7后添加的部分如图所示。

for ojvm only  (30 minutes)

           

Steps	Single Tenant    (non-CDB/PDB)	Steps	Multitenant    (CDB/PDB)
1	%   sqlplus /nolog	1	%   sqlplus /nolog
2	SQL>   Connect "/as sysdba"	2	SQL>   Connect "/as sysdba"
3	SQL>   shutdown	3	SQL>   shutdown
4	SQL> startup upgradeFoot 1	4	SQL> startup upgradeFoot 1
5	SQL>   quit	5	SQL>   alter pluggable database all open upgrade ;Foot 2
6	%   cd %ORACLE_HOME%\OPatch	6	SQL>   quit
7	%   datapatch -verbose	7	%   cd %ORACLE_HOME%\OPatch
8     9     10     11	%   sqlplus /nolog     SQL> Connect "/as sysdba"     SQL> shutdown     SQL> startup	8 9     10 11 12 13	%   datapatch -verbose     % sqlplus /nolog SQL>   Connect "/as sysdba" SQL>   shutdown SQL>   startupFoot3 SQL>   alter pluggable database all open;

 

 

INIT TABLESPACE AND USERS

ngtest1:/dtaruatdb/change/env [dtaruat] >more initpTESTtuat.sql

spool init_pTESTtuat.log

set echo on time on lines 120 pages 100

 

@env_pTESTtuat_uat.sql

 

alter session set container=pTESTtuat;

 

CREATE OR REPLACE FUNCTION 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, 'Password same as or similar to user');

   END IF;

 

   -- Check for the minimum length of the password

   IF length(password) < 4 THEN

      raise_application_error(-20002, 'Password length less than 4');

   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') THEN

      raise_application_error(-20002, 'Password too simple');

   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, 'Password should contain at least one digit, one character and one punctuation');

   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;

             GOTO findpunct;

         END IF;

      END LOOP;

   END LOOP;

   IF ischar = FALSE THEN

      raise_application_error(-20003, 'Password should contain at least one \

              digit, one character and one punctuation');

   END IF;

   -- 3. Check for the punctuation

   <<findpunct>>

   ispunct:=FALSE;

   FOR i IN 1..length(punctarray) LOOP

      FOR j IN 1..m LOOP

         IF substr(password,j,1) = substr(punctarray,i,1) THEN

            ispunct:=TRUE;

             GOTO endsearch;

         END IF;

      END LOOP;

   END LOOP;

   IF ispunct = FALSE THEN

      raise_application_error(-20003, 'Password should contain at least one \

              digit, one character and one punctuation');

   END IF;

 

   <<endsearch>>

   -- Check if the password differs from the previous password by at least

   -- 3 letters

   IF old_password IS NOT NULL THEN

     differ := length(old_password) - length(password);

 

     IF abs(differ) < 3 THEN

       IF length(password) < length(old_password) THEN

         m := length(password);

       ELSE

         m := length(old_password);

       END IF;

 

       differ := abs(differ);

       FOR i IN 1..m LOOP

         IF substr(password,i,1) != substr(old_password,i,1) THEN

           differ := differ + 1;

         END IF;

       END LOOP;

 

       IF differ < 3 THEN

         raise_application_error(-20004, 'Password should differ by at \

         least 3 characters');

       END IF;

     END IF;

   END IF;

   -- Everything is fine; return TRUE ;  

   RETURN(TRUE);

END;

/

 

GRANT EXECUTE ON verify_function TO PUBLIC;

 

 

 

 

--CREATE PROFILE  APP_PROFILE  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              1/24

--PASSWORD_REUSE_MAX               DEFAULT

--PASSWORD_VERIFY_FUNCTION         VERIFY_FUNCTION

--PASSWORD_LOCK_TIME               DEFAULT

--PASSWORD_GRACE_TIME              DEFAULT;

 

CREATE PROFILE APP_PROFILE LIMIT

COMPOSITE_LIMIT                  UNLIMITED

SESSIONS_PER_USER                UNLIMITED

CPU_PER_SESSION                  UNLIMITED

CPU_PER_CALL                     UNLIMITED

LOGICAL_READS_PER_SESSION        UNLIMITED

LOGICAL_READS_PER_CALL           UNLIMITED

IDLE_TIME                        UNLIMITED

CONNECT_TIME                     UNLIMITED

PRIVATE_SGA                      UNLIMITED

FAILED_LOGIN_ATTEMPTS            UNLIMITED

PASSWORD_LIFE_TIME               UNLIMITED

PASSWORD_REUSE_TIME              UNLIMITED

PASSWORD_REUSE_MAX               UNLIMITED

PASSWORD_VERIFY_FUNCTION         ORA12C_STRONG_VERIFY_FUNCTION

PASSWORD_LOCK_TIME               1

PASSWORD_GRACE_TIME              7;

 

CREATE PROFILE SUP_PROFILE LIMIT

COMPOSITE_LIMIT                  UNLIMITED

SESSIONS_PER_USER                15

CPU_PER_SESSION                  UNLIMITED

CPU_PER_CALL                     UNLIMITED

LOGICAL_READS_PER_SESSION        UNLIMITED

LOGICAL_READS_PER_CALL           UNLIMITED

IDLE_TIME                        UNLIMITED

CONNECT_TIME                     UNLIMITED

PRIVATE_SGA                      UNLIMITED

FAILED_LOGIN_ATTEMPTS            10

PASSWORD_LIFE_TIME               365

PASSWORD_REUSE_TIME              365

PASSWORD_REUSE_MAX               20

PASSWORD_VERIFY_FUNCTION         ORA12C_STRONG_VERIFY_FUNCTION

PASSWORD_LOCK_TIME               1

PASSWORD_GRACE_TIME              5

 

 - PASSWORD_REUSE_TIME 1800 --这个特性限制口令在多少天内不能重复使用 

 

CREATE TABLESPACE &TBS_TESTt_DATA LOGGING

DATAFILE '/dtaruatdb/data/dtaruat/pTESTtuat/TESTt_data_f01.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 4G EXTENT MANAGEMENT LOC

AL; 

 

CREATE TABLESPACE &TBS_TESTt_IDX LOGGING

DATAFILE '/dtaruatdb/data/dtaruat/pTESTtuat/TESTt_idx_f01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 4G EXTENT MANAGEMENT LOCA

L;

 

create user &v_TESTtdata_un  identified by &v_TESTtdata_pw  default tablespace &TBS_TESTt_DATA profile APP_PROFILE;

create user &v_TESTtusr_un   identified by &v_TESTtusr_pw   default tablespace &TBS_TESTt_DATA profile APP_PROFILE;

create user &v_TESTtquery_un identified by &v_TESTtquery_pw default tablespace &TBS_TESTt_DATA profile APP_PROFILE;

create user &v_TESTtpatch_un identified by &v_TESTtpatch_pw default tablespace &TBS_TESTt_DATA profile APP_PROFILE;

 

CREATE ROLE DATAROLE;

grant create session, create procedure, create table, create cluster, create sequence, create view, create synonym, create dat

abase link, alter session, create trigger to

 DATAROLE;

 

grant DATAROLE to &v_TESTtdata_un;

 

CREATE ROLE APPSROLE;

grant create session, create synonym, alter session to APPSROLE;

 

grant APPSROLE to &v_TESTtusr_un;

grant APPSROLE to &v_TESTtquery_un;

grant APPSROLE to &v_TESTtpatch_un;

 

alter user &v_TESTtdata_un quota unlimited on &TBS_TESTt_DATA;

alter user &v_TESTtusr_un quota unlimited on &TBS_TESTt_DATA;

alter user &v_TESTtpatch_un quota unlimited on &TBS_TESTt_DATA;

 

alter user &v_TESTtdata_un quota unlimited on &TBS_TESTt_IDX;

alter user &v_TESTtusr_un quota unlimited on &TBS_TESTt_IDX;

alter user &v_TESTtpatch_un quota unlimited on &TBS_TESTt_IDX;

 

spool off