Donghua's Blog - DBAGlobe: October 2019

Create database using ResponseFile

Resonsefile example

oracle@oracle19c ~]$ grep -v '^#' orcl3.rsp |grep -v ^$|grep -v '=$'
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
gdbName=orcl3
sid=orcl3
databaseConfigType=SI
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=orcl3pdb
useLocalUndoForPDBs=true
pdbAdminPassword=password
templateName=General_Purpose.dbc
sysPassword=password
systemPassword=password
emExpressPort=5500
datafileDestination=/u01/oradata
recoveryAreaDestination=/u01/fra
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
databaseType=MULTIPURPOSE
totalMemory=3000

[oracle@oracle19c ~]$ $ORACLE_HOME/bin/dbca -silent -createDatabase -responseFile orcl3.rsp 
[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
Registering database with Oracle Restart
11% complete
Copying database files
33% complete
Creating and starting Oracle instance
35% complete
38% complete
42% complete
45% complete
48% complete
Completing Database Creation
53% complete
55% complete
56% complete
Creating Pluggable Databases
60% complete
78% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/orcl3.
Database Information:
Global Database Name:orcl3
System Identifier(SID):orcl3
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl3/orcl3.log" for further details.

[oracle@oracle19c ~]$ more /u01/app/oracle/cfgtoollogs/dbca/orcl3/orcl3.log
[ 2019-10-26 11:01:59.196 SGT ] [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
[ 2019-10-26 11:01:59.196 SGT ] [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
[ 2019-10-26 11:01:59.197 SGT ] [WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
[ 2019-10-26 11:02:01.287 SGT ] Prepare for db operation
DBCA_PROGRESS : 7%
[ 2019-10-26 11:02:01.660 SGT ] Registering database with Oracle Restart
DBCA_PROGRESS : 11%
[ 2019-10-26 11:02:02.725 SGT ] Copying database files
DBCA_PROGRESS : 33%
[ 2019-10-26 11:03:48.238 SGT ] Creating and starting Oracle instance
DBCA_PROGRESS : 35%
DBCA_PROGRESS : 38%
DBCA_PROGRESS : 42%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 48%
[ 2019-10-26 11:14:38.150 SGT ] Completing Database Creation
DBCA_PROGRESS : 53%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
[ 2019-10-26 11:21:00.143 SGT ] Creating Pluggable Databases
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 78%
[ 2019-10-26 11:21:28.105 SGT ] Executing Post Configuration Actions
DBCA_PROGRESS : 100%
[ 2019-10-26 11:21:28.106 SGT ] Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/orcl3.
Database Information:
Global Database Name:orcl3
System Identifier(SID):orcl3

Create database using command line parameter

[oracle@oracle19c ~]$ $ORACLE_HOME/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcl2 -sid orcl2 -createAsContainerDatabase true -numberOfPDBs 0 -useLocalUndoForPDBs true -responseFile NO_VALUE -characterSet AL32UTF8 -totalMemory 2300 -sysPassword password -systemPassword password -pdbAdminPassword password -enableArchive true -recoveryAreaDestination /u01/fra -recoveryAreaSize 15000 -datafileDestination /u01/oradata
[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.
Prepare for db operation
10% complete
Registering database with Oracle Restart
14% complete
Copying database files
43% complete
Creating and starting Oracle instance
45% complete
49% complete
54% complete
58% complete
62% complete
Completing Database Creation

Launch DBCA in silent mode to clone PDB1 from ORCL as PDB2 in ORCL2

-- created in source for demo purpose
create pluggable database pdb1 admin user donghua identified by password;
alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;

-- create in ORCL in order to support source to target clone, DBLink will be created in TARGET (ORCL2) automatically
CREATE USER c##remote_user IDENTIFIED BY password CONTAINER=ALL;
GRANT create session, create pluggable database TO c##remote_user CONTAINER=ALL;

[oracle@oracle19c db]$ dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName PDB1 -remoteDBConnString ORCL -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword password -sysDBAUserName sys -sysDBAPassword password -dbLinkUsername c##remote_user -dbLinkUserPassword password -sourceDB orcl2 -pdbName pdb2
[FATAL] [DBT-19408] Incompatible location type for PDB operation. Pluggable database operation cannot be performed.
   CAUSE: Remote CDB(ORCL) is configured with Oracle Managed Files (OMF) option. Local CDB(orcl2) is not configured with OMF option.

SQL> alter system set db_create_file_dest='/u01/oradata/ORCL2';

System altered.

[oracle@oracle19c db]$ dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName PDB1 -remoteDBConnString ORCL -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword password -sysDBAUserName sys -sysDBAPassword password -dbLinkUsername c##remote_user -dbLinkUserPassword password -sourceDB orcl2 -pdbName pdb2
Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "pdb2" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl2/pdb2/orcl2.log" for further details.

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 26 12:22:54 2019
Version 19.3.0.0.0

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


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

SQL> show pdbs;

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB2               READ WRITE NO

SQL> conn donghua/password@oracle19c:1521/pdb2
Connected.
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> -- clean up
SQL> conn / as sysdba
Connected.

SQL> select name from v$database;

NAME
---------
ORCL2

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> drop pluggable database pdb2 including datafiles;

Pluggable database dropped.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Relocate PDBs using DBCA in Silent Mode

-- Connect to ORCL as SYS.
CREATE USER c##remote_user IDENTIFIED BY password CONTAINER=ALL;
GRANT create session, create pluggable database, sysoper TO c##remote_user CONTAINER=ALL;

[oracle@oracle19c db]$ dbca -silent -relocatePDB -remotePDBName PDB1 -remoteDBConnString ORCL -sysDBAUserName SYSTEM -sysDBAPassword password  -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword password  -dbLinkUsername c##remote_user -dbLinkUserPassword  password  -sourceDB ORCL2 -pdbName PDB1_IN_ORCL2
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "PDB1_IN_ORCL2" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl2/PDB1_IN_ORCL2/orcl2.log" for further details.

SQL> -- login ORCL2
SQL> select name from v$database;

NAME
---------
ORCL2

SQL> show pdbs;

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB1_IN_ORCL2          READ WRITE NO

SQL> conn donghua/password@oracle19c:1521/PDB1_IN_ORCL2
Connected.
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

Drop the database

[oracle@oracle19c ~]$ $ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB orcl3 -sid orcl3 -sysPassword p_ssw0rd
[WARNING] [DBT-11503] The instance (orcl3) is not running on the local node. This may result in partial delete of Oracle database.
   CAUSE: A locally running instance is required for complete deletion of Oracle database instance and database files.
   ACTION: Specify a locally running database, or execute DBCA on a node where the database instance is running.
[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
35% complete
39% complete
42% complete
45% complete
48% complete
52% complete
65% complete
Updating network configuration files
68% complete
Deleting instance and datafiles
84% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl3/orcl30.log" for further details.

Full command line reference

[oracle@oracle19c ~]$ dbca -createDatabase -help

Complete response file parameter list

oracle@oracle19c ~]$ grep -v '^#' /u01/db/assistants/dbca/dbca.rsp|grep -v ^$
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
gdbName=
sid=
databaseConfigType=
RACOneNodeServiceName=
policyManaged=
createServerPool=
serverPoolName=
cardinality=
force=
pqPoolName=
pqCardinality=
createAsContainerDatabase=
numberOfPDBs=
pdbName=
useLocalUndoForPDBs=
pdbAdminPassword=
nodelist=
templateName=
sysPassword=
systemPassword= 
oracleHomeUserPassword=
emConfiguration=
emExpressPort=5500
runCVUChecks=
dbsnmpPassword=
omsHost=
omsPort=
emUser=
emPassword=
dvConfiguration=
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=
datafileJarLocation=
datafileDestination=
recoveryAreaDestination=
storageType=
diskGroupName=
asmsnmpPassword=
recoveryGroupName=
characterSet=
nationalCharacterSet=
registerWithDirService=
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=
variablesFile=
variables=
initParams=
sampleSchema=
memoryPercentage=
databaseType=
automaticMemoryManagement=
totalMemory=

[oracle@oracle19c admin]$ sql donghua@orclpdb

SQLcl: Release 19.1 Production on Fri Oct 18 23:03:04 2019

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

Password? (**********?) ********
Last Successful login time: Fri Oct 18 2019 23:03:08 +08:00

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

SQL> select /*ansiconsole*/ * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8

SQL> create table t1 (c1 varchar2(20 byte), c2 varchar2(20 char), c3 nvarchar2(20));

Table created.

SQL> desc t1;

Name Null? Type
-------------------- -------- ----------------------------
C1 VARCHAR2(20)
C2 VARCHAR2(20 CHAR)
C3 NVARCHAR2(20)

SQL> insert into t1 values ('a','a','a');

1 row created.

SQL> insert into t1 values ('中','中','中');

1 row created.

SQL> commit;

Commit complete.

SQL> select /*ansiconsole*/ * from t1;

C1 C2 C3
-------------------- -------------------- --------------------
a a a
中中中

SQL> select /*ansiconsole*/ length(c1),length(c2),length(c3) from t1;

LENGTH(C1) LENGTH(C2) LENGTH(C3)
---------- ---------- ----------
1 1 1
1 1 1

SQL> select /*ansiconsole*/ lengthb(c1),lengthb(c2),lengthb(c3) from t1;

LENGTHB(C1) LENGTHB(C2) LENGTHB(C3)
----------- ----------- -----------
1 1 2
3 3 2

SQL> update t1 set c1='中中中中中中';

2 rows updated.

SQL> update t1 set c1='中中中中中中中';

update t1 set c1='中中中中中中中'
*
ERROR at line 1:
ORA-12899: value too large for column "DONGHUA"."T1"."C1" (actual: 21, maximum: 20)

SQL> update t1 set c2='中中中中中中中中中中中中中中中中中中中中';

2 rows updated.

SQL> update t1 set c2='中中中中中中中中中中中中中中中中中中中中中';

update t1 set c2='中中中中中中中中中中中中中中中中中中中中中'
*
ERROR at line 1:
ORA-12899: value too large for column "DONGHUA"."T1"."C2" (actual: 21, maximum: 20)

SQL> update t1 set c3='中中中中中中中中中中中中中中中中中中中中';

2 rows updated.

SQL> update t1 set c3='中中中中中中中中中中中中中中中中中中中中中';

update t1 set c3='中中中中中中中中中中中中中中中中中中中中中'
*
ERROR at line 1:
ORA-12899: value too large for column "DONGHUA"."T1"."C3" (actual: 21, maximum: 20)

Donghua's Blog - DBAGlobe

Saturday, October 26, 2019

Oracle 19c New Features - DBCA

Create database using ResponseFile

Resonsefile example

Create database using command line parameter

Launch DBCA in silent mode to clone PDB1 from ORCL as PDB2 in ORCL2

Relocate PDBs using DBCA in Silent Mode

Drop the database

Full command line reference

Sunday, October 20, 2019

How to fix block device permission issue without asmlib or asmfd

Friday, October 18, 2019

Oracle characterset, char semantics & storage length

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me