Wednesday, March 11, 2020

Oracle 19c silent installation



[oracle@db19c stage]$ ./wget.sh
SSO User Name: user@domain.com <-- enter="" font="" oracle="" sso="" username="">
<-- enter="" font="" oracle="" password="" sso="">

[oracle@db19c stage]$ unzip V982063-01.zip -d /u01/db19/dbhome_1


[opc@db19c stage]$ sudo /bin/chown -HRf oracle:oinstall /u01/db19/dbhome_1


[oracle@db19c bin]$ cp $ORACLE_HOME/install/response/db_install.rsp /u01/stage/db_install.rsp

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/db19/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


# Install Oracle software binary
[oracle@db19c bin]$ /u01/db19/dbhome_1/runInstaller -silent -responseFile /u01/stage/db_install.rsp

# Run scripts which need root privileges
[opc@db19c stage]$ sudo /u01/app/oraInventory/orainstRoot.sh
[opc@db19c stage]$ sudo /u01/db19/dbhome_1/root.sh

# Create listener
[oracle@db19c bin]$ export ORACLE_BASE=/u01/app/oracle; export ORACLE_HOME=/u01/db19/dbhome_1; /u01/db19/dbhome_1/bin/netca /orahome /u01/db19/dbhome_1 /instype typical /inscomp client,oraclenet,javavm,server,ano /insprtcl tcp /cfg local /authadp NO_VALUE /responseFile /u01/db19/dbhome_1/network/install/netca_typ.rsp /lisport 1521 /silent /orahnam OraDB19Home1

# Create database (CDB+PDB)
[oracle@db19c bin]$ export ORACLE_HOME=/u01/db19/dbhome_1; export ORACLE_BASE=/u01/app/oracle; /u01/db19/dbhome_1/bin/dbca -silent -createDatabase  -emConfiguration NONE  -templateName 'General_Purpose.dbc' -storageType FS -datafileDestination '/u01/app/oracle/oradata' -datafileJarLocation '/u01/db19/dbhome_1/assistants/dbca/templates' -sampleSchema false -oratabLocation /etc/oratab  -runCVUChecks false -continueOnNonFatalErrors true -createAsContainerDatabase true -numberOfPDBs 1 -pdbName orclpdb1 -gdbName 'ORCL' -sid 'ORCL' -initParams filesystemio_options=setall -ignorePrereqs                                          
Enter SYS user password:
<-- enter="" font="" password="">
Enter SYSTEM user password:
<-- enter="" font="" password="">
Enter PDBADMIN User Password:
<-- enter="" font="" password="">
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/ORCL.
Database Information:
Global Database Name:ORCL
System Identifier(SID):ORCL
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL.log" for further details.


# Below software installation command only work if ORACLE_HOME is /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@db19c stage]$ /u01/db19/dbhome_1/oui/bin/runInstaller -silent -ignoreSysPrereqs -waitforcompletion -attachHome INVENTORY_LOCATION='/u01/app/oraInventory' ORACLE_HOME='/u01/db19/dbhome_1' ORACLE_HOME_NAME='OraDB19Home1' ORACLE_BASE='/u01/app/oracle'   -local

Saturday, November 9, 2019

Install SQL Server 2019 using Docker image


[root@devops ~]# docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
2019-GA-ubuntu-16.04: Pulling from mssql/server
59ab41dd721a: Pull complete 
57da90bec92c: Pull complete 
06fe57530625: Pull complete 
5a6315cba1ff: Pull complete 
739f58768b3f: Pull complete 
fd449e8d7345: Pull complete 
51d0933375e5: Pull complete 
64f21ba81504: Pull complete 
55b6919c0cc6: Pull complete 
Digest: sha256:c8fa22553ce421b0482febcafa712b29cbb933b0d97a8671686797b31cf157a9
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
[root@devops ~]# docker images
REPOSITORY                       TAG                    IMAGE ID            CREATED             SIZE
mcr.microsoft.com/mssql/server   2019-GA-ubuntu-16.04   76c7c66bff02        4 weeks ago         1.57GB
centos                           latest                 0f3e07c0138f        5 weeks ago         220MB
oraclelinux                      7-slim                 874477adb545        3 months ago        118MB
[root@devops ~]# 

[root@devops ~]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Strong!Passw0' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
ddf33243ed48c608af08f47340452bbff23a11e22ac6546675b3f4511d9bf9b4


[root@devops ~]# docker ps
CONTAINER ID        IMAGE                                                 COMMAND                  CREATED             STATUS              PORTS                    NAMES
ddf33243ed48        mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04   "/opt/mssql/bin/perm…"   14 seconds ago      Up 13 seconds       0.0.0.0:1433->1433/tcp   quirky_moore

[root@devops ~]# docker exec -it ddf33243ed48 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Strong!Passw0'
1> select @@version;
2> go
--------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 
Sep 24 2019 13:48:23 
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)                                                                                                                        
(1 rows affected)
1> exit


[root@devops ~]# docker version
Client: Docker Engine - Community
 Version:           19.03.4
 API version:       1.40
 Go version:        go1.12.10
 Git commit:        9013bf583a
 Built:             Fri Oct 18 15:52:22 2019
 OS/Arch:           linux/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          19.03.4
  API version:      1.40 (minimum version 1.12)
  Go version:       go1.12.10
  Git commit:       9013bf583a
  Built:            Fri Oct 18 15:50:54 2019
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.2.6
  GitCommit:        894b81a4b802e4eb2a91d1ce216b8817763c29fb
 runc:
  Version:          1.0.0-rc8
  GitCommit:        425e105d5a03fabd737a126ad93d62a9eeede87f
 docker-init:
  Version:          0.18.0
  GitCommit:        fec3683

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)




Friday, August 16, 2019

How to limit NiFi resource usage

File to modify: /opt/nifi-1.9.2/bin/nifi.sh

    if [ "$1" = "start" ]; then
        ( eval "cd ${NIFI_HOME} && ${run_nifi_cmd}" & )> /dev/null 1>&-
        sleep 10
        taskset -pc 0 `cat /opt/nifi-1.9.2/run/nifi.pid`
    else


To verify:

root@devops bin]# ./nifi.sh start
nifi.sh: JAVA_HOME not set; results may vary

Java home:
NiFi home: /opt/nifi-1.9.2

Bootstrap Config File: /opt/nifi-1.9.2/conf/bootstrap.conf

pid 4427's current affinity list: 0-2
pid 4427's new affinity list: 0

[root@devops bin]# taskset  -p `cat /opt/nifi-1.9.2/run/nifi.pid`
pid 4427's current affinity mask: 1



Tuesday, August 6, 2019

Tips to identify yarn applications from resource manager for Hive Tez sessions

How To:

Use "hive.query.tag" at the connection string or set using "set hive.query.tag=usertag"

beeline -n hive -u jdbc:hive2://hdp:10000/default?hive.query.tag=donghuatag

Output:

[root@hdp ~]# yarn application -list -appTags donghuatag
19/08/06 21:52:30 INFO client.RMProxy: Connecting to ResourceManager at hdp.dbaglobe.com/192.168.31.22:8050
Total number of applications (application-types: [], states: [SUBMITTED, ACCEPTED, RUNNING] and tags: [donghuatag]):1
                Application-Id      Application-Name      Application-Type        User       Queue               State         Final-State         Progress                        Tracking-URL
application_1565096253064_0004  HIVE-7c5ec7e6-6944-483d-bf2d-b7a2d18ad7c1                  TEZ        hive     default             RUNNING           UNDEFINED               0%                http://hdp:41592/ui/