在上篇中讲了11g RAC中手工建库:
Oracle 11g RAC 中手工建库 示例
http://www.cndba.cn/dave/article/226
这篇看下12c RAC中如何手工建库。
1 创建初始化文件
Oracle RAC 中spfile是存放在磁盘组里的,而本地的pfile里面是个指针,指向spfile。http://www.cndba.cn/dave/article/227
[oracle@rac1 dbs]$ cat initcndba1.ora SPFILE='+DATA/cndba/spfilecndba.ora'[oracle@rac1 dbs]$
所以在我们手工建库的时候,需要先建本地的pfile,用pfile来建库,最后在修改过去。
RAC中的pfile如下:
[oracle@rac1 dbs]$ pwd /u01/oracle/12.1.0/db_1/dbs[oracle@rac1 dbs]$ cat initdave1.ora *._gc_policy_time=0 *._gc_undo_affinity=FALSE *.audit_file_dest='/u01/oracle/admin/dave/adump' *.audit_trail='db' *.cluster_database=FALSE *.compatible='12.1.0.2.0' *.control_file_record_keep_time=30 *.control_files='+DATA','+DATA' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_files=1000 *.db_name='dave' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4785m *.diagnostic_dest='/u01/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=daveXDB)' *.enable_pluggable_database=true dave2.instance_number=2 dave1.instance_number=1 *.memory_target=888m *.open_cursors=300 *.open_links=100 *.processes=300 *.remote_login_passwordfile='exclusive' dave2.thread=2 dave1.thread=1 dave2.undo_tablespace='UNDOTBS2' dave1.undo_tablespace='UNDOTBS1'[oracle@rac1 dbs]$
2 创建相关目录
要保证所有pfile中出现的目录,都要存在,创建完之后,注意检查权限。
另外是rac 环境,需要在所有节点上创建。http://www.cndba.cn/dave/article/227http://www.cndba.cn/dave/article/227
[oracle@rac1 dbs]$ mkdir -p /u01/oracle/admin/dave/adump[oracle@rac2 ~]$ mkdir -p /u01/oracle/admin/dave/adump
3 使用pfile 将DB启动到nomount
[oracle@rac1 dbs]$ export ORACLE_SID=dave1[oracle@rac1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 2 18:47:38 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 931135488 bytes Fixed Size 2930992 bytes Variable Size 675284688 bytes Database Buffers 247463936 bytes Redo Buffers 5455872 bytes SQL>
4 执行创建脚本
这里卡了很久,创建的时候都是报ORA-65005的错误,上MOS上搜了下,有一篇说是bug,不过和我这里不太像。
SQL> !oerr ora 65005 65005, 00000, "missing or invalid file name pattern for file - %s" // *Cause: Either source or replacement file name pattern was missing or // invalid in a SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause. // *Action: Correct the SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause // and reissue the statement.
使用OMF 无果的情况下,只好使用了别名的方式来创建了。
--先创建目录:
ASMCMD> mkdir +DATA/DAVE/DATAFILE/PDBSEED
然后执行命令:http://www.cndba.cn/dave/article/227
CREATE DATABASE dave USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('+DATA/DAVE/ONLINELOG/redo01.log') SIZE 50M BLOCKSIZE 512, GROUP 2 ('+DATA/DAVE/ONLINELOG/redo02.log') SIZE 50M BLOCKSIZE 512, GROUP 3 ('+DATA/DAVE/ONLINELOG/redo03.log') SIZE 50M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '+DATA/DAVE/DATAFILE/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '+DATA/DAVE/DATAFILE/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '+DATA/DAVE/DATAFILE/user01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '+DATA/DAVE/DATAFILE/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs1 DATAFILE '+DATA/DAVE/DATAFILE/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('+DATA/DAVE/DATAFILE','+DATA/DAVE/DATAFILE/PDBSEED');
创建完毕之后库已经读写了:
SQL> select name,open_mode from v$database; NAME OPEN_MODE ------------------ ---------------------------------------- DAVE READ WRITE
5 执行善后脚本
用SYS用户执行脚本创建相应视图和对象:
--约5分钟:[oracle@rac1 ~]$ export ORACLE_SID=dave1[oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -s -e -d $ORACLE_HOME/rdbms/admin -b catalog1 catalog.sql catcon: ALL catcon-related output will be written to catalog1_catcon_16643.lst catcon: See catalog1*.log files for output generated by scripts catcon: See catalog1_*.lst files for spool files, if any catcon.pl: completed successfully --约40分钟:[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -s -e -d $ORACLE_HOME/rdbms/admin -b catproc1 catproc.sql catcon: ALL catcon-related output will be written to catproc1_catcon_18620.lst catcon: See catproc1*.log files for output generated by scripts catcon: See catproc1_*.lst files for spool files, if any catcon.pl: completed successfully --约3分钟:[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -s -e -d $ORACLE_HOME/rdbms/admin -b owminst1 owminst.plb catcon: ALL catcon-related output will be written to owminst1_catcon_31700.lst catcon: See owminst1*.log files for output generated by scripts catcon: See owminst1_*.lst files for spool files, if any catcon.pl: completed successfully --约2分钟:[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u sys/oracle -s -e -d $ORACLE_HOME/rdbms/admin -b catclust1 catclust.sql catcon: ALL catcon-related output will be written to catclust1_catcon_505.lst catcon: See catclust1*.log files for output generated by scripts catcon: See catclust1_*.lst files for spool files, if any catcon.pl: completed successfully[oracle@rac1 admin]$ 用system用户运行以下脚本: -- 约1分钟[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u system/oracle -s -e -d $ORACLE_HOME/sqlplus/admin -b pupbld1 pupbld.sql catcon: ALL catcon-related output will be written to pupbld1_catcon_2374.lst catcon: See pupbld1*.log files for output generated by scripts catcon: See pupbld1_*.lst files for spool files, if any catcon.pl: completed successfully[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u system/oracle -s -e -d $ORACLE_HOME/sqlplus/admin/help -b helpus1 helpus.sql catcon: ALL catcon-related output will be written to helpus1_catcon_3956.lst catcon: See helpus1*.log files for output generated by scripts catcon: See helpus1_*.lst files for spool files, if any catcon.pl: completed successfully[oracle@rac1 admin]$[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -u system/oracle -s -e -d $ORACLE_HOME/sqlplus/admin/help -b hlpbld1 hlpbld.sql catcon: ALL catcon-related output will be written to hlpbld1_catcon_4227.lst catcon: See hlpbld1*.log files for output generated by scripts catcon: See hlpbld1_*.lst files for spool files, if any catcon.pl: completed successfully[oracle@rac1 admin]$
6 创建SPFILE 和 PFILE
注意我们之前创建的时候,没有指定具体的控制文件的位置,所以需要先修改创建的pfile:
[oracle@rac1 dbs]$ cat initdave1.ora …… *.control_files='+data/dave/controlfile/Current.303.894744217', '+data/dave/controlfile/Current.304.894744215' ……
SPFILE 保存在磁盘组里:
SQL> conn / as sysdba Connected. SQL> create spfile='+DATA/dave/' from pfile='/u01/oracle/12.1.0/db_1/dbs/initdave1.ora'; File created.
分别在2个节点的$ORACLE_HOME/dbs 目录下创建PFILE文件:
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs[oracle@rac1 dbs]$ ls hc_anqing1.dat hc_dave1.dat initcndba.ora init.ora orapwdave1 _rm_dup_anqing1.dat hc_cndba.dat initanqing1.ora initdave1.ora orapwanqing1 orapwdave2 snapcf_dave1.f[oracle@rac1 dbs]$ touch initcndba1.ora
--进ASM 查看spfile 位置
ASMCMD> pwd +data/dave/parameterfile ASMCMD> ls spfile.314.894749305
修改pfile如下:http://www.cndba.cn/dave/article/227
[oracle@rac1 dbs]$ vim initdave1.ora[oracle@rac1 dbs]$ cat initdave1.ora SPFILE='+data/dave/parameterfile/spfile.314.894749305'[oracle@rac1 dbs]$
同样在节点2创建initcndba2.ora 文件:
[oracle@rac2 dbs]$ cat initdave2.ora SPFILE='+data/dave/parameterfile/spfile.314.894749305'
7 配置节点2
创建节点2的undo表空间undotbs2
SQL> create undo tablespace undotbs2 datafile '+data' size 200m autoextend on maxsize unlimited; Tablespace created.
创建thread 2的3组redo日志组并启用thread 2
SQL> alter database add logfile thread 2 ('+data') size 50m; SQL> alter database add logfile thread 2 ('+data') size 50m; SQL> alter database add logfile thread 2 ('+data') size 50m; SQL> alter database enable thread 2; SQL> set lin 120 SQL> col member for a50 SQL> select a.group#,a.thread#,b.member from v$log a,v$logfile b where a.group#=b.group#; GROUP# THREAD# MEMBER ---------- ---------- -------------------------------------------------- 1 1 +DATA/DAVE/ONLINELOG/redo01.log 2 1 +DATA/DAVE/ONLINELOG/redo02.log 3 1 +DATA/DAVE/ONLINELOG/redo03.log 4 2 +DATA/DAVE/ONLINELOG/group_4.316.894749631 5 2 +DATA/DAVE/ONLINELOG/group_5.317.894749637 6 2 +DATA/DAVE/ONLINELOG/group_6.318.894749645 6 rows selected.
修改参数cluster_database开启群集
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options[oracle@rac1 dbs]$ export ORACLE_SID=dave1[oracle@rac1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 2 21:36:14 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 931135488 bytes Fixed Size 2930992 bytes Variable Size 675284688 bytes Database Buffers 247463936 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> alter system set cluster_database=true scope=spfile; System altered.
8 将数据库资源添加到CRS管理
在节点1用oracle用户运行:http://www.cndba.cn/dave/article/227
[oracle@rac1 dbs]$ srvctl add database -d dave -o /u01/oracle/12.1.0/db_1 -c RAC -y MANUAL -a DATA[oracle@rac1 dbs]$ srvctl add instance -d dave -i dave1 -n rac1[oracle@rac1 dbs]$ srvctl add instance -d dave -i dave2 -n rac2[oracle@rac1 dbs]$ srvctl config database -d dave Database unique name: dave Database name: Oracle home: /u01/oracle/12.1.0/db_1 Oracle user: oracle Spfile: Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: MANUAL Server pools: Disk Groups: DATA Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: oper Database instances: dave1,dave2 Configured nodes: rac1,rac2 Database is administrator managed[oracle@rac1 dbs]$
9 验证RAC
使用sqlplus 关闭所有节点上的cndba实例,然后用crs启动。
[grid@rac2 ~]$ srvctl start database -d dave[grid@rac2 ~]$ srvctl status database -d dave Instance dave1 is running on node rac1 Instance dave2 is running on node rac2[grid@rac1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA.dg ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.LISTENER.lsnr ONLINE OFFLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.OCR.dg ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.asm ONLINE ONLINE rac1 Started,STABLE ONLINE ONLINE rac2 Started,STABLE ora.net1.network ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE ora.ons ONLINE ONLINE rac1 STABLE ONLINE ONLINE rac2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE rac2 STABLE ora.MGMTLSNR 1 ONLINE ONLINE rac2 169.254.143.20 192.1 68.57.40,STABLE ora.cndba.cdb_taf.svc 1 OFFLINE OFFLINE STABLE ora.cndba.db 1 OFFLINE OFFLINE Instance Shutdown,ST ABLE 2 OFFLINE OFFLINE Instance Shutdown,ST ABLE ora.cndba.pdb_taf.svc 1 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE rac2 STABLE ora.dave.db 1 ONLINE ONLINE rac1 Open,STABLE 2 ONLINE ONLINE rac2 Open,STABLE ora.mgmtdb 1 ONLINE ONLINE rac2 Open,STABLE ora.oc4j 1 ONLINE ONLINE rac2 STABLE ora.rac1.vip 1 ONLINE INTERMEDIATE rac2 FAILED OVER,STABLE ora.rac2.vip 1 ONLINE ONLINE rac2 STABLE ora.scan1.vip 1 ONLINE ONLINE rac2 STABLE -------------------------------------------------------------------------------- SQL> col comp_name for a50 SQL> select comp_name,version,status from sys.dba_registry; COMP_NAME VERSION STATUS -------------------------------------------------- -------------------- -------------------- Oracle Real Application Clusters LOADING Oracle Database Packages and Types 12.1.0.2.0 LOADED Oracle Database Catalog Views 12.1.0.2.0 LOADED Oracle XML Database 12.1.0.2.0 LOADED Oracle Workspace Manager LOADING 4 rows selected.
这里组件状态不太正常,可以删除重建。 这里不操作了。http://www.cndba.cn/dave/article/227
SQL> select file_name from cdb_data_files; FILE_NAME ------------------------------------------------------------------- +DATA/DAVE/DATAFILE/system01.dbf +DATA/DAVE/DATAFILE/sysaux01.dbf +DATA/DAVE/DATAFILE/undotbs01.dbf +DATA/DAVE/DATAFILE/user01.dbf +DATA/DAVE/DATAFILE/undotbs2.315.894749615 SQL> select file_name from dba_temp_files; FILE_NAME ----------------------------------------------------------------- +DATA/DAVE/DATAFILE/temp01.dbf
10 创建PDB 验证
之前创建的PDB都是别名:http://www.cndba.cn/dave/article/227
ASMCMD> pwd +data/dave/datafile/pdbseed ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE NOV 02 22:00:00 N sysaux01.dbf => +DATA/DAVE/238E921BBEA53185E0531E38A8C0A5EF/DATAFILE/SYSAUX.306.894744295 DATAFILE UNPROT COARSE NOV 02 22:00:00 N system01.dbf => +DATA/DAVE/238E921BBEA53185E0531E38A8C0A5EF/DATAFILE/SYSTEM.300.894744257 TEMPFILE UNPROT COARSE NOV 02 22:00:00 N temp01.dbf => +DATA/DAVE/238E921BBEA53185E0531E38A8C0A5EF/TEMPFILE/TEMP.309.894744319 DATAFILE UNPROT COARSE NOV 02 22:00:00 N user01.dbf => +DATA/DAVE/238E921BBEA53185E0531E38A8C0A5EF/DATAFILE/USERS.311.894744347 ASMCMD> SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> create pluggable database cndba admin user cndba identified by cndba; Pluggable database created. SQL> alter pluggable database cndba open; Warning: PDB altered with errors.
打开报错,查看log 提示我们查看这个视图:
***************************************************************
WARNING: Pluggable Database CNDBA with pdb id - 3 is
altered with errors or warnings. Please look into
PDB_PLUG_IN_VIOLATIONS view for more details.
这个问题具体过程参考:
Oracle 12c Non CDB 数据库 切换成 CDB 操作示例
http://www.cndba.cn/dave/article/220
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CNDBA READ WRITE YES SQL> alter session set container=CNDBA; Session altered. SQL> select file_name from dba_data_files; FILE_NAME ---------------------------------------------------------------------------------------------------------------------------------- +DATA/DAVE/2390855AABD05BD1E0531E38A8C0BE7F/DATAFILE/system.321.894752617 +DATA/DAVE/2390855AABD05BD1E0531E38A8C0BE7F/DATAFILE/sysaux.320.894752615 +DATA/DAVE/2390855AABD05BD1E0531E38A8C0BE7F/DATAFILE/users.319.894752615 SQL> select comp_name,version,status from sys.dba_registry; COMP_NAME VERSION STATUS -------------------------------------------------- -------------------- -------------------- Oracle Workspace Manager 12.1.0.2.0 VALID Oracle XML Database 12.1.0.2.0 VALID Oracle Database Catalog Views 12.1.0.2.0 VALID Oracle Database Packages and Types 12.1.0.2.0 INVALID Oracle Real Application Clusters 12.1.0.2.0 VALID
注意,我们这里查询PDB的组件,除了倒数第二个,其他都正常。 需要重新运行前面的善后脚本,这个时间太长,不跑了。
至此12c RAC中手工建库手工。
版权声明:本文为博主原创文章,未经博主允许不得转载。