oracle19c dbca静默创建容器多租户数据库(名称长度限制)

dbca -silent -ignorePreReqs-ignorePrereqFailure-createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \-gdbname dw-sid dw \-createAsContainerDatabase TRUE \-numberOfPDBs 1 \-pdbName

好记忆不如烂笔头abc

1957人浏览 · 2020-11-13 12:31:54

好记忆不如烂笔头abc · 2020-11-13 12:31:54 发布

[oracle@rac1 ~]$ dbca -silent -ignorePreReqs -ignorePrereqFailure -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE -gdbname d1234567890145678901234567890123456789012345678901234567890d123456789012345678901234567890123456789012345678901234567890d123456789012345678901234567890123456789012345678901234567890 -sid d123456789012345678901234567890123456789012345678901234567890d123456789012345678901234567890123456789012345678901234567890d123456789012345678901234567890123456789012345678901234567890 -createAsContainerDatabase TRUE -numberOfPDBs 1 -pdbName dbcenter -pdbAdminPassword abcd1234 -sysPassword abcd1234 -systemPassword abcd1234 -dbsnmpPassword abcd1234 -datafileDestination '+DATA' -recoveryAreaDestination NONE -redoLogFileSize 200 -storageType ASM -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema false -initParams sga_target=3000MB,cluster_database=true,processes=500,pga_aggregate_target=1000MB,audit_trail=none -totalMemory 0 -databaseType MULTIPURPOSE -emConfiguration none -databaseConfigType RAC -nodelist rac1,rac2
[FATAL] [DBT-10315] Specified GDB Name cannot exceed (30) characters.
[FATAL] [DBT-10315] Specified Instance Name cannot exceed (12) characters.
[oracle@rac1 ~]$ oerr dbt 10315
10315, 0,"Specified %s cannot exceed (%s) characters."
// *Cause: n/a
// *Action: n/a
// *FQN: oracle.assistants.dbca.resource.DBCAErrorCode.DB_NAME_LENGTH_ERR

pdb用字母开头，不能数字开头，最长64个字符，最少一个字母
SQL> create pluggable database 123456789012345678901234567890 from cdrplatform;
create pluggable database 123456789012345678901234567890 from cdrplatform
*
ERROR at line 1:
ORA-65000: missing or invalid pluggable database name
SQL> !oerr ora 65000
65000, 00000, "missing or invalid pluggable database name"
// *Cause: A valid pluggable database name was not present where required.
// *Action: Reissue the statement with a valid pluggable database name.
//

SQL> create pluggable database a1234567890123456789012345678901234567890123456789012345678901234 from DBCENTER;
create pluggable database a1234567890123456789012345678901234567890123456789012345678901234 from DBCENTER
*
ERROR at line 1:
ORA-00972: identifier is too long

SQL> !oerr ora 972
00972, 00000, "identifier is too long"
// *Cause: An identifier with more than 128 bytes was specified,
// or a password identifier longer than 30 bytes was specified.
// *Action: Specify at most 128 bytes for identifiers,
// and at most 30 bytes for password identifiers.

SQL> create pluggable database a123456789012345678901234567890123456789012345678901234567890123 from DBCENTER;

Pluggable database created.

SQL> create pluggable database a from DBCENTER;

Pluggable database created.

dbca -silent -ignorePreReqs  -ignorePrereqFailure  -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname dw  -sid dw \
-createAsContainerDatabase TRUE \
-numberOfPDBs 1 \
-pdbName dbcenter \
-pdbAdminPassword pwd \
-sysPassword pwd -systemPassword pwd -dbsnmpPassword pwd \
-datafileDestination '+DATAC1' -recoveryAreaDestination '+RECOC1' \
-redoLogFileSize 500 \
-storageType ASM \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-sampleSchema false \
-totalMemory 10240 \
-databaseType MULTIPURPOSE \
-emConfiguration none \
-databaseConfigType RAC \
-nodelist rac1,rac2

删除实例：

dbca -silent -deleteDatabase -sourceDB dw -sysDBAPassword abcd1234

[oracle@rac1 ~]$ dbca -silent -deleteDatabase -sourceDB dw
Enter SYS user password:

[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
39% complete
42% complete
45% complete
48% complete
52% complete
55% complete
58% complete
65% complete
Updating network configuration files
68% complete
Deleting instances and datafiles
77% complete
87% complete
97% complete
100% complete
Database deletion completed.
Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/dw/dw0.log" for further details.

20分钟完成创建

[oracle@rac1 ~]$ ll /oracle/app/oracle/cfgtoollogs/dbca/dw/dw0.log
-rw-r----- 1 oracle oinstall 1365 Nov 14 18:42 /oracle/app/oracle/cfgtoollogs/dbca/dw/dw0.log
[oracle@rac1 ~]$ head /oracle/app/oracle/cfgtoollogs/dbca/dw/dw0.log
[ 2020-11-14 18:23:56.159 CST ] [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
[ 2020-11-14 18:23:56.159 CST ] [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
[ 2020-11-14 18:23:56.159 CST ] [WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
[ 2020-11-14 18:23:58.561 CST ] Prepare for db operation
DBCA_PROGRESS : 7%
[ 2020-11-14 18:24:13.467 CST ] Copying database files
DBCA_PROGRESS : 27%
[ 2020-11-14 18:25:11.338 CST ] Creating and starting Oracle instance
DBCA_PROGRESS : 28%
DBCA_PROGRESS : 31%

[oracle@rac2 rdbms]$ dbca -slient -deleteDatabase -help
        -deleteDatabase - Command to Delete a database.
                -sourceDB <Database unique name for RAC database or SID for Single Instance database>
                [-useWalletForDBCredentials <true | false> Specify true to load database credentials from wallet]
                        -dbCredentialsWalletLocation <Path of the directory containing the wallet files>
                        [-dbCredentialsWalletPassword <Password to open wallet with auto login disabled>]
                [-unregisterWithDirService <true | false>]
                        -dirServiceUserName <User name for directory service>
                        [-dirServicePassword <Password for directory service>]
                        [-walletPassword <Password for database wallet>]
                [-deRegisterEMCloudControl ]
                        -emUser <EM Admin username to add or modify targets>
                        -omsHost <EM management server host name>
                        -omsPort <EM management server port number>
                        [-emPassword <EM Admin user password>]
                [-sysDBAUserName <User name  with SYSDBA privileges>]
                [-forceArchiveLogDeletion <To delete archive logs of a database>]
                [-sysDBAPassword <Password for sysDBAUserName user name>]
                [-sid <Database system identifier>]

创建实例：

dbca -silent -ignorePreReqs  -ignorePrereqFailure  -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
-gdbname dw  -sid dw \
-createAsContainerDatabase TRUE \
-numberOfPDBs 1 \
-pdbName dbcenter \
-pdbAdminPassword abcd1234 \
-sysPassword abcd1234 -systemPassword abcd1234 -dbsnmpPassword abcd1234 \
-datafileDestination '+DATA' -recoveryAreaDestination NONE \
-redoLogFileSize 200 \
-storageType ASM \
-characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
-sampleSchema false \
-initParams sga_target=4000MB,cluster_database=true,processes=500,pga_aggregate_target=1000MB,audit_trail=none \
-totalMemory 0 \
-databaseType MULTIPURPOSE \
-emConfiguration none \
-databaseConfigType RAC \
-nodelist rac1,rac2

[oracle@rac1 ~]$ dbca -silent -ignorePreReqs -ignorePrereqFailure -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
> -gdbname dw -sid dw \
> -createAsContainerDatabase TRUE \
> -numberOfPDBs 1 \
> -pdbName dbcenter \
> -pdbAdminPassword abcd1234 \
> -sysPassword abcd1234 -systemPassword abcd1234 -dbsnmpPassword abcd1234 \
> -datafileDestination '+DATA' -recoveryAreaDestination NONE \
> -redoLogFileSize 200 \
> -storageType ASM \
> -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
> -sampleSchema false \
> -initParams sga_target=4000MB,cluster_database=true,processes=500,pga_aggregate_target=1000MB,audit_trail=none \
> -totalMemory 0 \
> -databaseType MULTIPURPOSE \
> -emConfiguration none \
> -databaseConfigType RAC \
> -nodelist rac1,rac2
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
7% complete
Copying database files
27% complete
Creating and starting Oracle instance
28% complete
31% complete
35% complete
37% complete
40% complete
Creating cluster database views
41% complete
53% complete
Completing Database Creation
57% complete
59% complete
60% complete
Creating Pluggable Databases
64% complete
80% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/oracle/app/oracle/cfgtoollogs/dbca/dw.
Database Information:
Global Database Name:dw
System Identifier(SID) Prefix:dw
Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/dw/dw.log" for further details.

参数文件参考：

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=wjw
sid=wjw
databaseConfigType=RAC
RACOneNodeServiceName=
policyManaged=false
createServerPool=false
serverPoolName=
cardinality=
force=false
pqPoolName=
pqCardinality=
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=hmp
useLocalUndoForPDBs=true
pdbAdminPassword=
nodelist=rac1,rac2
templateName=/oracle/app/oracle/product/19c/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
sysPassword=
systemPassword= 
serviceUserPassword=
emConfiguration=
emExpressPort=5501
runCVUChecks=FALSE
dbsnmpPassword=
omsHost=
omsPort=0
emUser=
emPassword=
dvConfiguration=false
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=+DATA/{DB_UNIQUE_NAME}/
recoveryAreaDestination=
storageType=ASM
diskGroupName=+DATA/{DB_UNIQUE_NAME}/
asmsnmpPassword=
recoveryGroupName=
characterSet=ZHS16GBK
nationalCharacterSet=AL16UTF16
registerWithDirService=false
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=LISTENER
variablesFile=
variables=ORACLE_BASE_HOME=/oracle/app/oracle/product/19c/dbhome_1,DB_UNIQUE_NAME=wjw,ORACLE_BASE=/oracle/app/oracle,PDB_NAME=,DB_NAME=wjw,ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1,SID=wjw
initParams=wjw1.undo_tablespace=UNDOTBS1,wjw2.undo_tablespace=UNDOTBS2,sga_target=6000MB,db_block_size=8192BYTES,cluster_database=true,family:dw_helper.instance_mode=read-only,nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP) (SERVICE=wjwXDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=exclusive,db_create_file_dest=+DATA/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=1000,pga_aggregate_target=2000MB,wjw1.thread=1,wjw2.thread=2,nls_territory=AMERICA,local_listener=-oraagent-dummy-,open_cursors=300,compatible=19.0.0,db_name=wjw,wjw1.instance_number=1,wjw2.instance_number=2,audit_trail=none
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0

如何知道上面的这些参数怎么来呢？？？

可以先通过图形化dbca创建响应文件并保存，然后查看dbca.rsp内容即可：

cat dbca.rsp |grep -v "#"|grep -v "^$" > wjw.rsp

创建pdb参考：

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 14 14:44:53 2020
Version 19.8.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBCENTER READ WRITE NO
SQL> create pluggable database db2 from pdb$seed;
create pluggable database db2 from pdb$seed
*
ERROR at line 1:
ORA-65189: cannot clone a PDB from PDB$SEED as the source PDB

SQL> !oerr ora 65189
65189, 00000, "cannot clone a PDB from PDB$SEED as the source PDB"
// *Cause: An attempt was made to clone a pluggable database (PDB) from
// PDB$SEED as the source PDB.
// *Action: Consider using the "create_pdb_from_seed" clause in CREATE PLUGGABLE
// DATABASE statement or clone the PDB from a different source PDB.
//

SQL> create pluggable database db2 from DBCENTER;

Pluggable database created.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBCENTER READ WRITE NO
4 DB2 MOUNTED
SQL> alter pluggable database db2 open instances=all;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBCENTER READ WRITE NO
4 DB2 READ WRITE NO

SQL> alter pluggable database all save state instances=all;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBCENTER READ WRITE NO
4 DB2 READ WRITE NO
SQL>