ORACLE12CR2 RAC安装部署(REDHAT7.6)六、建库以及收尾工作
【摘要】 建库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)