External Tables In Oracle

Step 1) As a SYS user create a directory EXT and grant read, write permission to user Ashwani.

SQL> conn / as sysdba

SQL> create directory ext as 'G:\pump';

SQL> grant read,write on directory  ext to ashwani;

Step 2) Connect to user Ashwani and create table EMP

SQL> conn ashwani/ashwani

SQL>

CREATE TABLE EMP(

    emp_id INT,

    emp_name VARCHAR2(30)

)

ORGANIZATION EXTERNAL(

    TYPE oracle_loader

    DEFAULT DIRECTORY ext

    ACCESS PARAMETERS

    (FIELDS TERMINATED BY ',')

    LOCATION ('mytable.csv')

);

Step 3) Create CSV file 'mytable.csv' at location 'G:\pump' and insert few records.

G:\pump>dir mytable.csv

 Volume in drive G is New Volume

 Volume Serial Number is 867E-CA03

 Directory of G:\pump

27-Nov-19  03:05 PM                12 mytable.csv

               1 File(s)             12 bytes

               0 Dir(s)  26,743,087,104 bytes free

SQL> select * from ashwani.emp;

NOTE: You cannot apply the INSERT,DELETE and UPDATE to external table.

Oracle 18c XE installtion on AWS EC2 Server

Installation of Oracle 18c XE on  on AWS

                               (RPM based installation on CentOS)

Step 1) Download the rpm from oracle site on your local machine.

From url https://www.oracle.com/database/technologies/xe-downloads.html file name Oracle Database 18c Express Edition for Linux x64

For example I have downloaded the rpm from oracle site to my local drive G:\ Oracle_AWS

I am using Cygwin tool for connecting the EC2 machine. You can download the setup from https://www.cygwin.com/ .You can also use putty for same purpose.

Open the Cygwin terminal and use below command to SCP the downloaded rmp file to AWS EC2     machine. Below command will copy the rpm file (oracle-database-xe-18c-1.0-1.x86_64.rpm) from local location G:\ Oracle_AWS to to EC2 machine  /tmp location.

Note: ec2-13-233-153-8.ap-south-1.compute.amazonaws.com is an endpoint and it varies machine to machine please change according to your EC2 Operating Machine.

Step 2) SCP the rpm from local machine to EC2.

scp -i "xeserver.pem" /cygdrive/g/Oracle_AWS/oracle-database-xe-18c-1.0-1.x86_64.rpm centos@ec2-13-233-153-8.ap-south-1.compute.amazonaws.com:/tmp

Where, xeserver.pem is a key for connecting the EC2 machine. Keep this file at location G:\

Centos is a user Centos server. For REHL servers you have to use ec2-user instead of Centos.

[root@ip-172-31-15-9 tmp]# ls -lrth

total 2.4G

-rwxrwx---. 1 centos centos 2.4G Sep  6 13:49 oracle-database-xe-18c-1.0-1.x86_64.rpm

Step 3) Download the Oracle Database Preinstall RPM via. The process of doing so is using curl.

As a root user run below command:

[root@ip-172-31-15-9]#cd /tmp

[root@ip-172-31-15-9 tmp]# curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

Now you can see two files at /tmp location:

[root@ip-172-31-15-9 tmp]# ls -lrth

total 2.4G

-rwxrwx---. 1 centos centos 2.4G Sep  6 13:49 oracle-database-xe-18c-1.0-1.x86_64.rpm

-rw-r--r--. 1 root   root      18K Sep  6 14:07  oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

Step 4) So to enable Red Hat's/Centos Extras and Optional repos, you would then run the following:

[root@ip-172-31-15-9]# sudo su - oracle

yum-config-manager --enable rhui-REGION-rhel-server-extras rhui-REGION-rhel-server-optional

Step 5) Run the below command to install both the rpm in one go:

[root@ip-172-31-15-9 tmp]# yum -y localinstall oracle-database*18c*

The above command will check all the dependencies and download the all the stuff for you.

Step 6) Create database:

By now oracle home is installed now it’s time to create the database.

If we create the database directly using command /etc/sysconfig/oracle-xe-18c.conf

this will use all default location for database creation. Let’s modified few parameters before proceeding with database creation step.

[root@ip-172-31-15-9]# mkdir -p /u01/app/oracle/oradata

[root@ip-172-31-15-9]# chown -R oracle:oinstall /u01/app

[root@ip-172-31-15-9]# cd /etc/sysconfig

Make the changes to file /etc/sysconfig/oracle-xe-18c.conf as highlighted in yellow.

[root@ip-172-31-15-9 tmp]#vi /etc/sysconfig/oracle-xe-18c.conf

#This is a configuration file to setup the Oracle Database.

#It is used when running '/etc/init.d/oracle-xe-18c configure'.

# LISTENER PORT used Database listener, Leave empty for automatic port assignment

LISTENER_PORT=1521

# EM_EXPRESS_PORT Oracle EM Express URL port

EM_EXPRESS_PORT=5500

# Character set of the database

CHARSET=AL32UTF8

# Database file directory

# If not specified, database files are stored under Oracle base/oradata

DBFILE_DEST=/u01/app/oracle/oradata

# SKIP Validations, memory, space

SKIP_VALIDATIONS=false

Save the file by pressing Esc + Esc wq!

Execute the below command to start the database creation.

[root@ip-172-31-15-9 tmp]#/etc/init.d/oracle-xe-18c configure

Provide the password for SYS User:

[root@ip-172-31-15-9 tmp]# /etc/init.d/oracle-xe-18c configure

Specify a password to be used for database accounts. 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]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:

Confirm the password:

Configuring Oracle Listener.

Listener configuration succeeded.

Configuring Oracle Database XE.

Enter SYS user password:

********

Enter SYSTEM user password:

********

Enter PDBADMIN User Password:

**********

Prepare for db operation

7% complete

Copying database files

29% complete

Creating and starting Oracle instance

30% complete

31% complete

34% complete

38% complete

41% complete

43% complete

Completing Database Creation

47% complete

50% complete

Creating Pluggable Databases

54% complete

71% complete

Executing Post Configuration Actions

93% complete

Running Custom Scripts

100% complete

Database creation complete. For details check the logfiles at:

 /opt/oracle/cfgtoollogs/dbca/XE.

Database Information:

Global Database Name:XE

System Identifier(SID):XE

Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.

Connect to Oracle Database using one of the connect strings:

     Pluggable database: ip-172-31-15-9.ap-south-1.compute.internal/XEPDB1

     Multitenant container database: ip-172-31-15-9.ap-south-1.compute.internal

Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

If you don’t want to create database as container. Make changes to parameter export CREATE_AS_CDB=false under the file /etc/init.d/oracle-xe-18c

Now login to database:

-bash-4.2$ . oraenv

ORACLE_SID = [oracle] ? XE

The Oracle base has been set to /opt/oracle

-bash-4.2$

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Sep 9 13:08:47 2019

Version 18.4.0.0.0

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

Connected to:

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

XE        READ WRITE

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/XE/system01.dbf

/u01/app/oracle/oradata/XE/sysaux01.dbf

/u01/app/oracle/oradata/XE/undotbs01.dbf

/u01/app/oracle/oradata/XE/pdbseed/system01.dbf

/u01/app/oracle/oradata/XE/pdbseed/sysaux01.dbf

/u01/app/oracle/oradata/XE/users01.dbf

/u01/app/oracle/oradata/XE/pdbseed/undotbs01.dbf

/u01/app/oracle/oradata/XE/XEPDB1/system01.dbf

/u01/app/oracle/oradata/XE/XEPDB1/sysaux01.dbf

/u01/app/oracle/oradata/XE/XEPDB1/undotbs01.dbf

/u01/app/oracle/oradata/XE/XEPDB1/users01.dbf

11 rows selected.

SQL>

DATABASE TABLE REFRESH OVER NETWORK USING DATABASE LINK

TABLE REFRESH:

Advantages:

This is an alternate and fast approach for importing database from one database to another.

This process will help you to save time as we can skip following activities:

1.       Creating database directory for backup on source database.

2.       Create dump file on source database.

3.       Copy dump file at destination server.

4.       Creating database directory for backup restoration on Destination database.

5.       Expdp command to take database backup.

Suppose we have two databases DB11G and DB11GDEV. We need to restore user Metals table called ‘Products’ backup from db11g to user FINANCE db11gdev database.
In another words source and Target will be as below:

Souce: METSLS.PRODUCTS
TARGET:FINANCE.PRODUCT

Prerequisites:

1.       Source and Destination database must be connected through the network.

2.       Both databases must be in open mode.

3.       Source database (DB11G) tns detail must exist at destination (DB11GDEV).

STEP 1)  Create a public database link at DB11GDEV connecting to DB11G using system user.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

--------- ------------

DB11GDEV  READ WRITE

SQL> create public database link metals_link connect to system identified by oracle_4U using 'db11g';

Script for checking db links:

COLUMN owner FORMAT A30

COLUMN db_link FORMAT A50

COLUMN username FORMAT A30

COLUMN host FORMAT A30

SELECT owner,

       db_link,

       username,

       host

FROM   dba_db_links

ORDER BY owner, db_link

Crosscheck the db link is working.

SQL> select * from dual@metals_link ;

D

-

X

STEP 2)   Import using Impdp command at  DB11GDEV (Destination)

SET ORACLE_SID=db11gdev

impdp finance/finance@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance tables=metals.products table_exists_action=replace

C:\Users\ashwanik\Desktop\MY_SCRIPT>impdp finance/finance@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance tables=metals.products table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Fri Jul 19 16:10:54 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "FINANCE"."SYS_IMPORT_TABLE_01":  finance/********@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance tables=metals.products table_exists_action=replace

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 584 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . imported "FINANCE"."PRODUCTS"                       16664157 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-02270: no matching unique or primary key for this column-list

Failing sql is:

ALTER TABLE "FINANCE"."PRODUCTS" ADD CONSTRAINT "FK_PROD_MAT" FOREIGN KEY ("ILI_MATERIAL_NUMBER") REFERENCES "FINANCE"."MATERIAL_RECORDS" ("ILI_MATERIAL_NUMBER") ON DELETE CASCADE ENABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "FINANCE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri Jul 19 16:13:57 2019 elapsed 0 00:03:03

DATABASE SCHEMA REFRESH OVER NETWORK USING DATABASE LINK

SCHEMA REFRESH:

SET ORACLE_SID=db11gdev

impdp finance/finance@db11gdev directory=pump network_link=metals_link remap_schema=metals:finance table_exists_action=replace

OR

system/********@db11gdev directory=pump network_link=metals_link schemas=metals remap_schema=metals:finance table_exists_action=replace

Shrink Monitoring Script

col event form a30
col sql_text form a40
select
   b.sid
  ,a.event
  ,a.WAIT_TIME
  ,round(d.read_value/1024/1024/1024,2) read_gb
  ,round(d.write_value/1024/1024/1024,2) write_gb
  ,round(d.undo_value/1024/1024/1024,2) undo_gb
  ,e.current_undo
  ,c.SQL_TEXT
from v$session_wait a
join v$session b on a.sid = b.sid
join v$sqlarea c on b.SQL_ID = c.SQL_ID
join (select * from (select s.sid, t.name, s.value from v$sesstat s inner join v$statname t on s.statistic#=t.statistic#)
              pivot (sum(value) as value for name in (
                 'undo change vector size' UNDO
                ,'physical read total bytes' READ
                ,'physical write total bytes' WRITE
))) d on a.sid=d.sid
join (select b.sid, sum(a.USED_UBLK) current_undo from V$TRANSACTION a join v$session b on a.SES_ADDR=b.saddr group by b.sid) e on e.sid=a.sid
where upper(c.sql_text) like 'ALTER TABLE%SHRINK SPACE%'
and b.sid != (select sys_context('USERENV','SID') from dual)
;

ORA-01017: invalid username/password; logon denied

[oracle@ashwani]$ sqlplus sys/oracle_4U@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 9 17:28:37 2019

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

ERROR:
ORA-01017: invalid username/password; logon denied

Solution:
This might be the issue of permission of password file:

[oracle@ashwani]$ su -
Password:
-bash: i: command not found
[root@ashwani ~]#  chmod -R 777 /u01

[oracle@ashwani]$ sqlplus sys/oracle_4U@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 9 17:30:38 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ORCL      READ WRITE

Or
Create yhe local listener using netca utility.

RMAN-00571,RMAN-00569,RMAN-00571,RMAN-03002: failure of list command at 03/12/2019 19:26:16,RMAN-06403: could not obtain a fully authorized session,ORA-01034: ORACLE not available,ORA-27101: shared memory realm does not exist

C:\Users\ashwanik\Desktop\MY_SCRIPT>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 12 19:26:11 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> list backup
2> ;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 03/12/2019 19:26:16
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Solution: Restart the services will resolve the issue.