安装12C小问题及pdb表空间配置

安装12C小问题及pdb表空间配置

1.RPM包

#安装12C需要安装的rpm包，官网搜索，做个记录

bc

binutils-2.23.52.0.1-12.el7（x86_64）

compat-libcap1-1.10-3.el7（x86_64）

compat-libstdc++-33-3.2.3-71.el7（i686）

compat-libstdc++-33-3.2 （x86_64）

glibc-2.17-36.el7（i686）

glibc-2.17-36.el7（x86_64）

glibc-devel-2.17-36.el7（i686）

glibc-devel-2.17-36.el7 （x86_64）

ksh

libaio-0.3.109-9.el7（i686）

libaio-0.3.109-9.el7（x86_64）

libaio-devel-0.3.109-9.el7（i686）

libaio-devel-0.3.109- 9.el7（x86_64）

libX11-1.6.0-2.1.el7（i686）

libX11-1.6.0-2.1.el7（x86_64）

libXau-1.0.8-2.1.el7（i686）

libXau-1.0.8-2.1。 el7（x86_64）

libXi-1.7.2-1.el7（i686）

libXi-1.7.2-1.el7（x86_64）

libXtst-1.2.2-1.el7（i686）

libXtst-1.2.2-1.el7（x86_64）

libgcc-4.8.2-3.el7（i686）

libgcc-4.8.2-3.el7（x86_64）

libstdc ++ - 4.8.2-3.el7（i686）

libstdc ++ - 4.8.2-3.el7（x86_64）

libstdc ++ - devel-4.8.2-3.el7（i686）

libstdc ++ - devel-4.8.2-3.el7（x86_64）

libxcb-1.9-5.el7（i686）

libxcb-1.9-5.el7（x86_64）

make-3.82-19.el7（x86_64）

nfs-utils-1.3.0-0.21.el7.x86_64（对于Oracle ACFS）

net-tools-2.0-0.17.20131004git.el7（x86_64）（用于Oracle RAC和Oracle Clusterware）

smartmontools-6.2-4.el7（x86_64）

sysstat-10.1.5-1.el7（x86_64）

2报错：yum源

说明：端口不通，提交网络人员配置交换机

#报错解决思路：

#Yum安装，提示IP +PORT不通，使用telnet命令（远程节点telnet包上传，解压），提示路由问题

[root@fs-3z3-vm0393 ~]# yum install -y binutils

http://10.135.100.100/centos7.4/repodata/repomd.xml: [Errno 14] curl#7 - "Failed connect to 10.135.100.100:80; No route to host"

Trying other mirror.

#查询yum源

# cat /etc/yum.repos.d/Test_Env_yum_server.repo

[Test_Env_yum_server]

name=Test_Env_yum_server

baseurl=http://10.135.100.100/centos7.4

enabled=1

gpgcheck=0

#使用telnet

#传输介质：连接Yum源IP地址，搜索TEL相关rpm软件包，上传服务器，解压后

#路由端口的问题！！！

[root@fs-3z3-vm0393 ~]# telnet 10.135.100.100 80

Trying 10.135.100.100...

telnet: connect to address 10.135.100.100: No route to host

3权限问题

#自己配置的Oracle环境变量，自动cd 目录，但是目录权限存在问题

[root@hbqzcsrac1 oracle]# su - oracle

Last login: Tue Jun 19 16:39:46 CST 2018 on pts/0

-bash: cd: /picclife/app/oracle: Permission denied

-bash: cd: /picclife/app/oracle: Permission denied

[root@hbqzcsrac1 /]# cd picclife/

[root@hbqzcsrac1 picclife]# ll

total 15625000

drwxr-x--- 5 root root 46 Jun 19 16:09 app

[root@hbqzcsrac1 app]# chown grid.oinstall /picclife/app/

4新系统无法使用 unzip命令，解压缩

$ unzip V840012-01.zip -d /picclife/app/12.2.0/grid/

-bash: unzip: command not found

[root@hbqzcsrac1 ~]# yum install -y unzip

5集群检测：

#无法确定产品清单组：忽略

PRVG-10467 : The default Oracle Inventory group could not be determined.

#PRVG-0449 GI用户软限制未配置，检测不达标

Verifying Soft Limit: maximum stack size ...

Node Name Type Available Required Status

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

hbqzcsrac2 soft 8192 10240 failed

hbqzcsrac1 soft 8192 10240 failed

Verifying Soft Limit: maximum stack size ...FAILED (PRVG-0449)

Verifying Soft Limit: maximum stack size ...FAILED

hbqzcsrac2: PRVG-0449 : Proper soft limit for maximum stack size was not found

on node "hbqzcsrac2" [Expected >= "10240" ; Found = "8192"].

hbqzcsrac1: PRVG-0449 : Proper soft limit for maximum stack size was not found

on node "hbqzcsrac1" [Expected >= "10240" ; Found = "8192"].

#最大的软限制：查询为8192，非10240

Ulimit -a -查询

#GRID临时生效

ulimit -Ss 10240

#配置文件永久生效 =》 exit 退出会话后，重新登录

/etc/security/limits.conf

oracle soft stack 10240

#发现是由于安装文档中，未指定GRID soft stack

grid hard stack 32768

grid soft stack 10240

#/DEV/SHM检查

hbqzcsrac2: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

hbqzcsrac1: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

CVU在不适用的RHEL7上执行了/ dev / shm安装检查。

MOS 如果在RHEL7上执行/ dev / shm安装检查，可忽略此问题

#可忽略

Verifying /dev/shm mounted as temporary file system ...FAILED (PRVE-0421)

Verifying File system mount options for path /var ...PASSED

#网络问题：不能忽略

Verifying zeroconf check ...FAILED (PRVE-10077)

Verifying ASM Filter Driver configuration ...PASSED

hbqzcsrac1:/picclife/app/12.2.0/grid$ cat /etc/sysconfig/network

#开机是否激活网络：yes配置

# Created by anaconda

NOZEROCONF=yes

hbqzcsrac1:/picclife/app/12.2.0/grid$ ll /etc/sysconfig/network

-rw-r--r--. 1 root root 39 Jun 19 14:13 /etc/sysconfig/network

[root@hbqzcsrac1 ~]# chmod 644 /etc/sysconfig/network

#DNS 在不配置DNS服务器时，可忽略

hbqzcsrac2: PRVG-10048 : Name "hbqzcsrac2" was not resolved to an address of

the specified type by name servers o"10.135.1.21".

hbqzcsrac2: Check for integrity of file "/etc/resolv.conf" failed

mv /etc/resolv.conf /etc/resolv.conf.20180620.bak

#NTP服务可忽略，Oracle有时钟同步进程，二选一即可

二、Oracle图形化安装

安装GI：提示软件目录下有文件：

MV后，继续操作

[INS-32026] The Software Location specified should not

#12c 安装GI ，远程拷贝到节点二时，报错：提示无法将文件拷贝至远程：

#测试节点二主机名称，互信测试，均正常现象

#查询安装日志信息：提示有一个文件不可读，删除后（=），解决问题

These nodes will be ignored and not participate in the configured Grid Infrastructure.

ACTION: Review the log file /tmp/GridSetupActions2018-06-20_01-47-32PM/gridSetupActions2018-06-20_01-47-32PM.log for further details on failure.

SUMMARY:

- PRCF-2031 : Parallel transfer workload distribution error.

PRCF-2023 : The following contents cannot be transferred as they are non-readable.

Directories:

Files:

/picclife/app/12.2.0/grid/cv/rpm/=.

Refer associated stacktrace #oracle.install.commons.util.exception.AbstractErrorAdvisor:

#DBCA建库，选择CDB+PDB模式，未选择OMF功能

#安装过程中报错：error while restoring pdb backup piece

#根据图形界面提示的DBCA日志，查询日志信息

ssistants/dbca/templates/pdbseed.dfb

channel ORA_DISK_1: restoring foreign file 2 to +DATA/hbqzcsrac/pdbseed/system01.dbf

channel ORA_DISK_1: restoring foreign file 4 to +DATA/hbqzcsrac/pdbseed/sysaux01.dbf

channel ORA_DISK_1: restoring foreign file 9 to +DATA/hbqzcsrac/pdbseed/undotbs01.dbf

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 06/20/2018 16:38:05

ORA-19870: error while restoring backup piece /picclife/app/oracle/product/12.2.0/db_1/assistants/dbca/templates/pdbseed.dfb

ORA-19504: failed to create file "+DATA/hbqzcsrac/pdbseed/sysaux01.dbf"

ORA-17502: ksfdcre:4 Failed to create file +DATA/hbqzcsrac/pdbseed/sysaux01.dbf

ORA-15173: entry 'pdbseed' does not exist in directory 'hbqzcsrac'

RMAN>

#匹配MOS ，12.2 DBCA不符合ORA-15173（Doc ID 2320091.1）

恢复pdb备份片时发生DBCA错误，这是具有一个PDB的CDB数据库，对于这种情况，建议在使用asm时使用OMF ，退出安装，勾选OMF功能

#创建操作系统密码文件，报错

提示密码口令简单，忽略，不创建了

#密码文件的作用，在于远程sys登录，实际环境都是本地登录管理，可以无需密码文件

$ orapwd file=orapwhbqzcsra1 entries=10 force=y ignorecase=y password=picclife

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 digit.

#PL/SQL登录 12c环境报错

#本地先配置好PDB，及tnsnames.ora字符串

#本地sqlplus 登录测试OK

sqlplus dbamonitor/dbamonitor_1@10.135.102.249:1521/HBQZCSRAPDB

#提示版本问题：

ORA-28040: No matching authentication protocol

Doc ID 402193.1

network/admin$ vi sqlnet.ora

#Supported since: 11.0

SQLNET.ALLOWED_LOGON_VERSION=11

#用户密码错误：重置密码

SQL> alter user dbamonitor identified by dbamonitor_1;

三、PDB配置

1日志格式化

SQL> alter database add logfile thread 1 group 21('+DATA','+DATA') size 512m;

alter database add logfile thread 1 group 22('+DATA','+DATA') size 512m;

alter database add logfile thread 1 group 23('+DATA','+DATA') size 512m;

alter database add logfile thread 1 group 24('+DATA','+DATA') size 512m;

alter database add logfile thread 1 group 25('+DATA','+DATA') size 512m;

alter database add logfile thread 2 group 31('+DATA','+DATA') size 512m;

alter database add logfile thread 2 group 32('+DATA','+DATA') size 512m;

alter database add logfile thread 2 group 33('+DATA','+DATA') size 512m;

alter database add logfile thread 2 group 34('+DATA','+DATA') size 512m;

alter database add logfile thread 2 group 35('+DATA','+DATA') size 512m;

SQL> alter system archive log current;

SQL> alter system checkpoint;

SQL> alter database drop logfile group 1;

2tnsnames.ora文件配置

network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

#解释说明：节点一配置，三个字符串：一个CDB使用SCAN IP，一个本地PDB 使用节点一服务器本地物理IP，最后一个集群对外连接TNS字符串，使用两个VIP集合：连接目标PDB

#CDB与11g一样，PDB的server_name参数= PDB_NAME 而非数据库server_name参数

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = hbqzcsrac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

HBPDB_1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = HBQZCSRAPDB)

)

)

cshb =

(DESCRIPTION =

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

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = HBQZCSRAPDB)

)

)

#测试：直接连接PDB

sqlplus system/picclife@cshb

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 HBQZCSRAPDB READ WRITE NO

#创建测试用户

SQL> CREATE USER DBAMONITOR IDENTIFIED BY dbamonitor_1;

GRANT CREATE SESSION TO DBAMONITOR;

#测试：直接连接PDB中的测试用户，需要创建

sqlplus dbamonitor/dbamonitor_1@10.135.102.248:1521/HBQZCSRAPDB

#默认sqlplus / as sysdba 直连CDB

#CDB切换PDB

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 HBQZCSRAPDB READ WRITE NO

SQL> alter session set container=&PDB_NAME;

Enter value for pdb_name: HBQZCSRAPDB

old 1: alter session set container=&PDB_NAME

new 1: alter session set container=HBQZCSRAPDB

3创建测试表空间

#目的：在新的12c PDB环境中，创建测试表空间，参照生产库A库

#开启计时

set time on

#创建语法：

CREATE TBLESPACE TABLESPACE_NAME DATAFILE 'XX/.DBF' SIZE 100M SIZE 1M;

OR SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4G,;

OR SIZE 100M AUTOEXTEND OFF; 默认非自动扩展

datafile '+DATA'

#查询数据文件：有序

SQL> select FILE#,name from v$datafile

FILE# NAME

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

1 +DATA/single/system01.dbf

create tablespace tts datafile '+DATA/single/tts.dbf' size 1m autoextend on maxsize 31g;

#12C CDB 与PDB之间表空间是分割开的，共享SYSTEM,SYSAUX

#UNDO表空间也不同

#CDB/PDB查询使用的UNdo表空间，查询结果相同

undo_tablespace UNDOTBS2

#查询PDB测试12c数据文件大小

select tablespace_name,bytes/1024/1024/1024 g,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 g from dba_data_files

TABLESPACE_NAME G AUTOEX G

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

SYSTEM .87890625 YES 31.9999847

SYSAUX 3.52539063 YES 31.9999847

UNDOTBS1 21.3232422 YES 31.9999847

UNDOTBS2 .004882813 YES 31.9999847

#查询生产库的资料信息

select tablespace_name,round(sum(bytes)/1024/1024/1024,2)g,AUTOEXTENSIBLE,round(sum(MAXBYTES)/1024/1024/1024,2)g,count(*) from dba_data_files group by tablespace_name,AUTOEXTENSIBLE;

TABLESPACE_NAME G AUTOEX G COUNT(*)

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

BACKUP_TS 30 NO 0 1

UNDO_2 32 YES 32 1

USERS 9.61 YES 32 1

LIFEDATA_T_L 3000 NO 0 1

UNDOTBS1 25.01 YES 32 1

ILOG 10 YES 32 1

SYSTEM .29 YES 32 1

SYSAUX 1.38 YES 32 1

UNDO_2 30 NO 0 1

UNDOTBS1 30 NO 0 1

TB1 10 NO 0 5=>5个数据文件每个20G 非自动扩展！！！

RM_DEPLOY_TBS 100 NO 0 5

12 rows selected.

#查询生产库表空间管理方式

select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT CONTENTS EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPAC

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

SYSTEM 8192 65536 PERMANENT LOCAL SYSTEM MANUAL

SYSAUX 8192 65536 PERMANENT LOCAL SYSTEM AUTO

UNDOTBS1 8192 65536 UNDO LOCAL SYSTEM MANUAL

TEMP 8192 1048576 1048576TEMPORARY LOCAL UNIFORM MANUAL

USERS 8192 65536 PERMANENT LOCAL SYSTEM AUTO

#create tablespace

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 HBQZCSRAPDB READ WRITE NO

#创建测试数据表空间

set timing on

create tablespace tb1 datafile '+data' size 20g autoextend off;

Elapsed: 00:00:56.28

alter tablespace tb1 add datafile '+data' size 20g autoextend off;

alter tablespace tb1 add datafile '+data' size 20g autoextend off;

alter tablespace tb1 add datafile '+data' size 20g autoextend off;

alter tablespace tb1 add datafile '+data' size 20g autoextend off;

4创建UNDO表空间

#UNDO表空间，PDB使用自己的，需要配置UNDO LOCAL本地模式

#查询UNDO表空间是否本地，创建UNDO，修改PDB默认UNDO

select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';

#查询PDB undo 模式，共享还是本地，本地环境【图形化，勾选了每个PDB独自拥有UNDO表空间】

LOCAL_UNDO_ENABLED TRUE

SQL> create undo tablespace pdb_undo datafile '+data' size 31g;

Elapsed: 00:01:46.25

SQL> alter system set undo_tablespace=pdb_undo;

#查询UNdo表空间

select con_id,tablespace_name from cdb_tablespaces where tablespace_name like '%UNDO%' order by con_id;

#虽然图形化，勾选，每个PDB肚子拥有UNDO，但是并未真实安装分配，所以手工分配

5 创建TEMP表空间

#查询临时表空间数据量

sys@FKDB2>select tablespace_name,round(sum(bytes)/1024/1024/1024,2)g,AUTOEXTENSIBLE,round(sum(MAXBYTES)/1024/1024/1024,2)g,count(*) from dba_temp_files group by tablespace_name,AUTOEXTENSIBLE;

TABLESPACE_NAME G AUTOEX G COUNT(*)

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

TEMP 210 NO 0 7 --PDB独有

TEMP 32YES 32 1 --CDB

#查询pdb数据库默认临时表空间：添加7个临时表空间，设置为数据库默认临时表空间

CREATE TEMPORARY TABLESPACE temp_pdb TEMPFILE '+data' SIZE 30g;

alter database default temporary tablespace temp_pdb;

alter tablespace temp_pdb add tempfile '+data' size 30g;

alter tablespace temp_pdb add tempfile '+data' size 30g;

alter tablespace temp_pdb add tempfile '+data' size 30g;

alter tablespace temp_pdb add tempfile '+data' size 30g;

alter tablespace temp_pdb add tempfile '+data' size 30g;

alter tablespace temp_pdb add tempfile '+data' size 30g;

6同步测试环境PDB，与生产环境的PDB的表空间，相同

#查询生产系统表空间

select tablespace_name,round(sum(bytes)/1024/1024/1024,2)g,AUTOEXTENSIBLE,round(sum(MAXBYTES)/1024/1024/1024,2)g,count(*) from dba_data_files group by tablespace_name,AUTOEXTENSIBLE;

TABLESPACE_NAME G AUTOEX G COUNT(*)

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

BACKUP_TS 30 NO 0 1

UNDO_2 32 YES 32 1

USERS 9.61 YES 32 1

LIFEDATA_T_L 3000 NO 0 1

UNDOTBS1 25.01 YES 32 1

ILOG 10 YES 32 1

SYSTEM .29 YES 32 1

SYSAUX 1.38 YES 32 1

UNDO_2 30 NO 0 1

UNDOTBS1 30 NO 0 1

TB1 100 NO 0 5

RM_DEPLOY_TBS 100 NO 0 5

12 rows selected.

#查询数据库默认永久表空间：无需修改，都是users

SQL> select property_value from database_properties where

property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

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

USERS

#需要创建的三个表空间的类型：管理方式：查询：

select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,bigfile from dba_tablespaces where tablespace_name in ('LIFEDATA_T_L','ILOG','RM_DEPLOY_TBS')

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT CONTENTS EXTENT_MAN ALLOCATION SEGMENT_SPAC BIGFIL

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

ILOG 8192 65536 PERMANENT LOCAL SYSTEM AUTO NO

RM_DEPLOY_TBS 8192 65536 PERMANENT LOCAL SYSTEM AUTO NO

LIFEDATA_T_L 8192 65536 PERMANENT LOCAL SYSTEM AUTO YES

#一个BIG FILE类型的表空间，自动管理类型：3000G LIFEDATA_T_L

#两个small fIle类型表空间，标准块大小无特殊情况：

一个10g大小，自动扩展最大32g ILOG

另一个表空间20g，五个数据文件，非自动扩展 RM_DEPLOY_TBS

#由于空间过大，查询ASM磁盘组的剩余空间

SQL> select GROUP_NUMBER,NAME,TYPE,TOTAL_MB/1024 TOTAL_G,FREE_MB/1024 FREE_G from v$asm_diskgroup;

GROUP_NUMBER NAME TYPE TOTAL_G FREE_G

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

1 CRS EXTERN 50 15.8203125

2 DATA EXTERN 5120 4753.32031

#创建表空间，一个数据文件，分配10g，非自动扩展

set timing on

create tablespace ILOG datafile ‘+data’ size 10g autoextend on;

#创建表空间，五个数据文件，总分配100g，非自动扩展

create tablespace RM_DEPLOY_TBS datafile '+data' size 20g autoextend off;

SQL> alter tablespace RM_DEPLOY_TBS add datafile '+data' size 20g autoextend off;

SQL> alter tablespace RM_DEPLOY_TBS add datafile '+data' size 20g autoextend off;

SQL>alter tablespace RM_DEPLOY_TBS add datafile '+data' size 20g autoextend off;

SQL>alter tablespace RM_DEPLOY_TBS add datafile '+data' size 20g autoextend off;

Elapsed: 00:01:05.19

#查询生产系统bigfile类型表空间，真实分配的大小

select sum(bytes)/1024/1024/1024 "G" from dba_segments where tablespace_name='LIFEDATA_T_L';

G

----------

2935.89246

#创建BIG FILE表空间 分配1T

#直接创建分配1t Bigfile表空间，需要1个小时的时间，容易创建失败！

#可以考虑使用自动扩展

#或者使用size 30g resize 慢慢扩大

SQL> create bigfile tablespace LIFEDATA_T_L datafile '+data' size 1024G autoextend off;

Tablespace created.

Elapsed: 00:58:34.88

#检查两端对比：

SQL> select tablespace_name,round(sum(bytes)/1024/1024/1024,2)g,AUTOEXTENSIBLE,round(sum(MAXBYTES)/1024/1024/1024,2)g,count(*) from dba_data_files group by tablespace_name,AUTOEXTENSIBLE;

OK