Saturday, October 26, 2019

Oracle 19c New Features - DBCA

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=

Sunday, October 20, 2019

How to fix block device permission issue without asmlib or asmfd


[root@oracle19c ~]# cat /etc/redhat-release 
CentOS Linux release 7.7.1908 (Core)

[root@oracle19c ~]# cat /etc/udev/rules.d/55-oracle.rules 
KERNEL=="sdc1" OWNER="oracle", GROUP="oinstall", MODE="0660"
KERNEL=="sdd1" OWNER="oracle", GROUP="oinstall", MODE="0660"
KERNEL=="sde1" OWNER="oracle", GROUP="oinstall", MODE="0660"

[root@oracle19c ~]# udevadm trigger

[root@oracle19c ~]# ls -l /dev/sd[cde]1
brw-rw----. 1 oracle oinstall 8, 33 Oct 20 20:17 /dev/sdc1
brw-rw----. 1 oracle oinstall 8, 49 Oct 20 20:17 /dev/sdd1
brw-rw----. 1 oracle oinstall 8, 65 Oct 20 20:17 /dev/sde1

Friday, October 18, 2019

Oracle characterset, char semantics & storage length

[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)