ORACLE12CR2 RAC安装部署(REDHAT7.6)六、建库以及收尾工作

举报
Lucifer三思而后行 发表于 2021/10/28 09:50:54 2021/10/28
【摘要】 建库dbca报错:[BasicStep.handleNonIgnorableError:510]  ORA-01031: insufficient privileges##怀疑可能是oracle用户缺少asmadmin组,授予组之后,点击ignoreusermod -a -G asmadmin oracleCDB实例创建完成。 创建PDB:SQL> create pluggable dat...

建库

dbca

报错:[BasicStep.handleNonIgnorableError:510]  ORA-01031: insufficient privileges

##怀疑可能是oracle用户缺少asmadmin组,授予组之后,点击ignore
usermod -a -G asmadmin oracle

CDB实例创建完成。

创建PDB:

SQL> create pluggable database fdcdb admin user admin identified by oracle;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 FDCDB                          MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 FDCDB                          READ WRITE NO
SQL> alter session set container=fdcdb;

Session altered.

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
FDCDB

SQL> grant dba to admin;

Grant succeeded.

启动PDB的几种方式:

--1.手动登录每个节点去启动pdb
--节点一启动pdb
sqlplus / as sysdba
show pdbs
--启动单个pdb
alter pluggable database fdcdb open;
--启动所有pdb
alter pluggable database all open;

--此时节点二的pdb仍然是mount状态
--需要再次手动启动
alter pluggable database all open;

--2.创建触发器PDB随CDB启动
--使用SYS用户创建如下触发器即可:
CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

添加TNS:

cat <<EOF >> /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora
FDCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fdcdb01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fdcdb)
    )
  )
EOF

测试连接:

[oracle@fdcdb02 ~]$ sqlplus admin/oracle@fdcdb

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 2 12:29:54 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> 
SQL> show con_name

CON_NAME
------------------------------
FDCDB
SQL> show user
USER is "ADMIN"

七、检查集群、数据库、监听运行情况

1、集群运行情况检查:

[root@fdcdb01 ~]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[root@fdcdb01 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.ASMNET2LSNR_ASM.lsnr
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.OCR.dg
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.net1.network
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.ons
               ONLINE  ONLINE       fdcdb01                  STABLE
               ONLINE  ONLINE       fdcdb02                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      fdcdb01                  STABLE
               OFFLINE OFFLINE      fdcdb02                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       fdcdb02                  STABLE
ora.MGMTLSNR
      1        OFFLINE OFFLINE                               STABLE
ora.asm
      1        ONLINE  ONLINE       fdcdb01                  Started,STABLE
      2        ONLINE  ONLINE       fdcdb02                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       fdcdb02                  STABLE
ora.fdcdb01.vip
      1        ONLINE  ONLINE       fdcdb01                  STABLE
ora.fdcdb02.vip
      1        ONLINE  ONLINE       fdcdb02                  STABLE
ora.fdcinst.db
      1        ONLINE  ONLINE       fdcdb01                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       fdcdb02                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       fdcdb02                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       fdcdb02                  STABLE
--------------------------------------------------------------------------------

2、数据库运行情况

[oracle@fdcdb01 ~]$ srvctl status database -d fdcinst
Instance fdcinst1 is running on node fdcdb01
Instance fdcinst2 is running on node fdcdb02


SQL> select inst_id,instance_name,status from gv$instance;

   INST_ID INSTANCE_NAME    STATUS
---------- ---------------- ------------
         1 fdcinst1         OPEN
         2 fdcinst2         OPEN

col name for a30
SQL> select con_id,name,open_mode from gv$pdbs;
    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         4 FDCDB                          READ WRITE
         2 PDB$SEED                       READ ONLY
         4 FDCDB                          READ WRITE

3、监听运行情况

[oracle@fdcdb01 ~]$ ps -ef|grep tns           
root       332     2  0 Apr01 ?        00:00:00 [netns]
oracle   20413 16511  0 13:57 pts/2    00:00:00 grep --color=auto tns
grid     23865     1  0 Apr01 ?        00:00:11 /u01/app/12.2.0/grid/bin/tnslsnr ASMNET2LSNR_ASM -no_crs_notify -inherit
grid     23869     1  0 Apr01 ?        00:00:11 /u01/app/12.2.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
grid     24594     1  0 Apr01 ?        00:00:01 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit

[oracle@fdcdb01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): fdcdb01,fdcdb02

[oracle@fdcdb01 ~]$ srvctl status listener -l ASMNET1LSNR_ASM
Listener ASMNET1LSNR_ASM is enabled
Listener ASMNET1LSNR_ASM is running on node(s): fdcdb01,fdcdb02

[oracle@fdcdb01 ~]$ srvctl status listener -l ASMNET2LSNR_ASM
Listener ASMNET2LSNR_ASM is enabled
Listener ASMNET2LSNR_ASM is running on node(s): fdcdb01,fdcdb02


[grid@fdcdb02 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node fdcdb02

八、配置ORACLE参数

1、用户名密码永久生效

--查看用户天数限制
set line222
col profile for a20
col RESOURCE_NAME for a20
col LIMIT for a20
SELECT * FROM dba_profiles WHERE profile ='DEFAULT' AND resource_name = 'PASSWORD_LIFE_TIME';

PROFILE              RESOURCE_NAME        RESOURCE LIMIT                COM INH IMP
-------------------- -------------------- -------- -------------------- --- --- ---
DEFAULT              PASSWORD_LIFE_TIME   PASSWORD 180                  NO  NO  NO

--更改为无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

--已修改为无限制
SQL> SELECT * FROM dba_profiles WHERE profile ='DEFAULT' AND resource_name = 'PASSWORD_LIFE_TIME';

PROFILE              RESOURCE_NAME        RESOURCE LIMIT                COM INH IMP
-------------------- -------------------- -------- -------------------- --- --- ---
DEFAULT              PASSWORD_LIFE_TIME   PASSWORD UNLIMITED            NO  NO  NO

--修改PDB的用户密码天数限制
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> SELECT * FROM dba_profiles WHERE profile ='DEFAULT' AND resource_name = 'PASSWORD_LIFE_TIME';

PROFILE              RESOURCE_NAME        RESOURCE LIMIT                COM INH IMP
-------------------- -------------------- -------- -------------------- --- --- ---
DEFAULT              PASSWORD_LIFE_TIME   PASSWORD UNLIMITED            NO  NO  NO

2、sqlnet.ora配置允许低版本client连接

cat <<EOF >>$ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
EOF

3、开启归档、配置删除归档脚本

--设置归档路径
SQL> alter system set log_archive_dest_1='location=+DATA' sid='*';

System altered.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=+DATA
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +DATA
Oldest online log sequence     8
Current log sequence           9

--查看pdbs启动状态
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 FDCDB                          READ WRITE NO
SQL> 
--关闭一节点所有pdb
SQL> alter pluggable database all close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 FDCDB                          MOUNTED

--查询节点2的pdbs状态
SQL> select inst_id,con_id,name,open_mode from gv$pdbs;

   INST_ID     CON_ID NAME                                                                                                                             OPEN_MODE
---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ----------
         1          2 PDB$SEED                                                                                                                         READ ONLY
         1          4 FDCDB                                                                                                                            MOUNTED
         2          2 PDB$SEED                                                                                                                         READ ONLY
         2          4 FDCDB                                                                                                                            READ WRITE


--关闭节点2的pdbs
SQL> alter pluggable database all close;

Pluggable database altered.


--关闭数据库实例fdcinst
[oracle@fdcdb02 ~]$ srvctl stop database -d fdcinst -o immediate

--开启到mount状态
[oracle@fdcdb02 ~]$ srvctl start database -d fdcinst -o mount

--开启归档模式
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

--两节点开启到open状态
alter database open;

--布置归档删除脚本
mkdir -p /home/oracle/script/log
vi /home/oracle/script/del_arch.sh
#!/bin/bash
. ~/.bash_profile
DAY_TAG=`date +"%Y-%m-%d"`

rman target / nocatalog msglog /home/oracle/pst/script/log/del_arch_$DAY_TAG.log<<EOF
crosscheck archivelog all;
delete noprompt archivelog until time '(sysdate-7)';
delete noprompt force archivelog until time 'SYSDATE-10';
EOF

--写入crontab
chmod +x /home/oracle/script/del_arch.sh
echo "00 00 * * * /home/oracle/script/del_arch.sh" >> /var/spool/cron/oracle

4、配置ORACLE数据库随主机启动自动启动

--查看当前数据库是否随主机启动
[root@fdcdb01 ~]# crsctl stat res ora.fdcinst.db -p|grep AUTO_START
AUTO_START=restore

--设置随主机启动
[root@fdcdb01 ~]# crsctl modify res ora.fdcinst.db -attr AUTO_START=always -unsupported

[root@fdcdb01 ~]# crsctl stat res ora.fdcinst.db -p|grep AUTO_START
AUTO_START=always
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。