Oracle DBA

Manual Steps to Setup ACFS file system in ODA box with GI version 11.2

Posted on by Anil Shenoy

Below are the Manual Steps to Setup ACFS file system in ODA box with GI version 11.2

Terminology
———–
DATA – is the diskgroup from which we are taking space (-volumeDiskGroup parameter)
1024 GB – is the space that we are allocating (-volumeSizeGB parameter )
acfsbkpvol – is the ACFS volume name (-volumeName parameter)
/backupfs – is the file system that will be mounted on both the ODA nodes
GI_GOME = /u01/app/11.2.0.4/grid
+ASM = instance name

Steps

——

 1. Open a terminal window and log in as grid user (to the node 1)
 #su - grid
2. Setup the right environment variables to connect ASM instance
 $export ORACLE_HOME=/u01/app/11.2.0.4/grid
 $export ORACLE_SID=+ASM1
3. Create your ACFS volume executing the following command:
 /u01/app/11.2.0/grid/bin/asmca -silent -createVolume -volumeName RECO -volumeDiskGroup <volume_disk_group> -volumeSizeGB <volume_size> -sysAsmPassword <sysasmpassword>
 for example:-
 /u01/app/11.2.0.4/grid/bin/asmca -silent -createVolume -volumeName acfsbkpvol -volumeDiskGroup DATA -volumeSizeGB 1024 -sysAsmPassword <sysasmpassword>
4. Verify your volume creation
 /u01/app/11.2.0.4/grid/bin/asmcmd volinfo -G DATA acfsbkpvol | grep -oE '/dev/asm/.*'
 Volume Device: /dev/asm/<To_be_obtained>

for example:- Volume Device: /dev/asm/acfsbkpvol-107
5. Create the ACFS filesystem

/u01/app/11.2.0/grid/bin/asmca -silent -createACFS -acfsVolumeDevice <volume_device>

for example:-
 /u01/app/11.2.0.4/grid/bin/asmca -silent -createACFS -acfsbkpvolumeDevice <volume_device>
 <volume_device> is the one which is obtained in Step 4 output.
6. Create the mount point
 Open a terminal window and log in as root (on both nodes)
 mkdir /backupfs
 chown grid:asmdba /backupfs
7. Add the new ACFS as cluster resource
 Execute on node 1 as root user the following:

/u01/app/11.2.0/grid/bin/srvctl add filesystem -d <volume_device> -g RECO -v <volumename> -m <mount_point> -u grid

for example:-
 /u01/app/11.2.0.4/grid/bin/srvctl add filesystem -d <volume_device> -g DATA -v acfsbkpvol -m /backupfs -u grid
 <volume_device> is the one which is obtained in Step 4 output.
8. Mount the filesystem
 /u01/app/11.2.0/grid/bin/srvctl start filesystem -d <volume_device>

/u01/app/11.2.0.4/grid/bin/srvctl start filesystem -d <volume_device>
 <volume_device> is the one which is obtained in Step 4 output.
9. Display all ACFS registered file systems issuing the following command
 #acfsutil info fs

Monitor real time active session statistics by ORAPUB tool

Posted on by debasis maity

The tool was devised by Craig from ORAPUB which is excellent to monitor what happens in session real time.

1.First I need to down OSM tool kit from ORAPUB. First we need to run the following scripts to create some pre-requisite tables under sys/system schema.

a)osmprep.sql

-- ******************************************************
-- * Copyright Notice : (c)1998-2014,2015 OraPub, Inc.
-- * Filename : osmprep.sql 
-- * Author : Craig A. Shallahamer
-- * Original : 17-AUG-98
-- * Last Modified : 23-Dec-2014
-- * Description : OSM preperation script
-- * Usage : start osmprep.sql 
-- ******************************************************

prompt 
prompt OraPub System Monitor - Interactive (OSM-I) installation script.
prompt 
prompt (c)1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008 by OraPub, Inc.
prompt (c)2009,2010,2011,2012,2013,2014,2015 by OraPub, Inc.
prompt 
prompt There is absolutely no guarantee with this software. You may
prompt use this software at your own risk, not OraPub's risk. 
prompt No value is implied or stated.
prompt
prompt You may need to run $ORACLE_HOME/rdbms/admin/catblock.sql
prompt
prompt Connect as the user who will be using the OSM.
prompt
prompt Press ENTER to continue.
accept x

prompt
prompt Creating interim tables for delta calculations
prompt
-- Just for rtsysx8.sql
drop table o$sysstat;
create table o$sysstat as select * from v$sysstat where 1=0;
--
drop table system_event_snap;
create table system_event_snap as select * from v$system_event where 1=0;
drop table event_histogram_snap;
create table event_histogram_snap as select * from v$event_histogram where 1=0;
drop table op_sys_time_model_snap;
create table op_sys_time_model_snap as select * from v$sys_time_model where 1=0;
drop table op_os_cpu_snap;
create table op_os_cpu_snap as select stat_name,value from v$osstat where 1=0;

drop table o$system_event_snap;
create table o$system_event_snap as select * from v$system_event where 1=0;

drop table o$sys_time_model;
create table o$sys_time_model as select * from v$sys_time_model where 1=0;

drop table o$rtsysx_sql;
create table o$rtsysx_sql ( 
 sql_id varchar2(13),sql_address raw(8),cpu_time number, elapsed_time number,
 sorts number, executions number, parse_calls number, disk_reads number,
 buffer_gets number, rows_processed number, sql_text varchar2(1000)
);
create unique index o$rtsysx_sql on o$rtsysx_sql (sql_id);

-- rtsysx8.sql
drop table o$rtsysx_sql8;
create table o$rtsysx_sql8 ( 
 sql_address raw(8),cpu_time number, elapsed_time number,
 sorts number, executions number, parse_calls number, disk_reads number,
 buffer_gets number, rows_processed number, sql_text varchar2(1000)
);
create unique index o$rtsysx_sql8 on o$rtsysx_sql8 (sql_address);

-- rtcx.sql
drop table o$rtcx_snap;
create table o$rtcx_snap (category varchar2(100),time_cs number, waits number);

-- collection scripts (e.g., rss.sql, ash scripts,) need these two functions

create or replace function get_interval_s_fnc(i_intrvl interval day to second) return number is
begin
 return extract(day from i_intrvl) * 86400 +
 extract(hour from i_intrvl) * 3600 +
 extract(minute from i_intrvl) * 60 +
 extract(second from i_intrvl);
 exception
 when others then begin return null;
 end;
end;
/


prompt To categorize wait events for OSM reports, run:
prompt
prompt For pre-10g systems, run event_type_nc.sql
prompt For 10g and beyond, run event_type.sql

prompt
prompt Once you cateogrize the wait events, the installation is complete.
prompt
prompt Menu is osmi.sql 
prompt
prompt ENJOY!!
prompt

b)Then I will run another script

-- ********************************************************************
-- * Copyright Notice : (c)2001-2014 OraPub, Inc.
-- * Filename : event_type.sql - For 10g+ databases!!!!!
-- * Author : Craig A. Shallahamer
-- * Original : 11-may-01
-- * Last Update : 19-May-2015 (dealing with I/O events)
-- * Description : event_type.sql - Loads "event type" table
-- * which is used by other scripts.
-- * Usage : start event_type.sql
-- * This is usually run (manually) after osmprep.sql
-- ********************************************************************

prompt
prompt file: event_type.sql for Oracle 10g and beyond...
prompt
prompt About to categorize wait events for OSM reports.
prompt
prompt Press ENTER to re-create the o$event_type table.
accept x

-- Event TYPES are defined as follows:

-- ior - IO read related wait
-- iow - IO write related wait
-- other - "real" but not explicitly categorized
-- idle/bogus - idle events, usually not useful

drop table o$event_type
/
create table o$event_type (
event varchar2(64),
type varchar2(64)
)
/

insert into o$event_type
 select name,'other'
 from v$event_name
 where wait_class in ('Administrative','Application','Cluster','Concurrency',
 'Configuration','Other','Scheduler','Queuing','Scheduler')
/
insert into o$event_type
 select name,'bogus'
 from v$event_name
 where wait_class in ('Idle','Network')
/
insert into o$event_type
 select name,'ior'
 from v$event_name
 where wait_class in ('Commit','System I/O','User I/O')
 and name like '%read%'
/
insert into o$event_type
 select name,'iow'
 from v$event_name
 where wait_class in ('Commit','System I/O','User I/O')
 and name like '%write%'
/
insert into o$event_type
select name, 'other' from v$event_name
minus
select event, 'other' from o$event_type
/

-----------------------------------------------------------------------------------------
----- DO NOT REMOVE THE BELOW LINES as they make adjustments to the broad inserts above.
-----------------------------------------------------------------------------------------

-- select 'update o$event_type set type=''iow'' where event = '''||name||''';' from v$event_name where name like '%I/O%';

-- The below is not perfect cleary, but probably good enough... you still must think...

update o$event_type set type='iow' where event = 'Parameter File I/O';
update o$event_type set type='iow' where event = 'Disk file operations I/O';
update o$event_type set type='iow' where event = 'Disk file I/O Calibration';
update o$event_type set type='iow' where event = 'Datapump dump file I/O';
update o$event_type set type='iow' where event = 'dbms_file_transfer I/O';
update o$event_type set type='iow' where event = 'DG Broker configuration file I/O';
update o$event_type set type='iow' where event = 'Log archive I/O';
--update o$event_type set type='iow' where event = 'RMAN backup & recovery I/O';
update o$event_type set type='iow' where event like 'RMAN backup % recovery I/O';
update o$event_type set type='iow' where event = 'Standby redo I/O';
update o$event_type set type='iow' where event = 'RMAN Disk slave I/O';
update o$event_type set type='iow' where event = 'RMAN Tape slave I/O';
update o$event_type set type='iow' where event = 'DBWR slave I/O';
update o$event_type set type='iow' where event = 'LGWR slave I/O';
update o$event_type set type='iow' where event = 'Archiver slave I/O';
update o$event_type set type='iow' where event = 'db file async I/O submit';
update o$event_type set type='iow' where event = 'concurrent I/O completion';
update o$event_type set type='iow' where event = 'Archive Manager file transfer I/O';
--update o$event_type set type='iow' where event = 'resmgr:large I/O queued';
--update o$event_type set type='iow' where event = 'resmgr:small I/O queued';
update o$event_type set type='iow' where event = 'utl_file I/O';
update o$event_type set type='iow' where event = 'TEXT: File System I/O';
update o$event_type set type='iow' where event = 'ASM Fixed Package I/O';
update o$event_type set type='iow' where event = 'ASM Staleness File I/O';
update o$event_type set type='iow' where event = 'Data Guard: RFS disk I/O';


update o$event_type set type='iow' where event = 'free buffer waits';
update o$event_type set type='other' where event like 'latch%';
update o$event_type set type='other' where event like 'enq%';
update o$event_type set type='other' where event like 'cursor%';
update o$event_type set type='iow' where event like 'log%sync%';
update o$event_type set type='iow' where event like 'log%write%';
update o$event_type set type='iow' where event like 'db%write%';
update o$event_type set type='iow' where event like 'log%file%switch%';
update o$event_type set type='iow' where event like 'LGWR%';
update o$event_type set type='ior' where event like 'log%read%';
update o$event_type set type='ior' where event like 'db%read%';
update o$event_type set type='iow' where event like 'i/o%slave%wait';
update o$event_type set type='iow' where event = 'write complete waits';
update o$event_type set type='bogus' where event = 'wait for unread message on broadcast channel';
update o$event_type set type='bogus' where event = 'wait for unread message on multiple broadcast channels';
--update o$event_type set type='bogus' where event like 'db%file%async%submit%';
-- update o$event_type set type='bogus' where event like 'DLM%cross%inst%call%completion';
update o$event_type set type='bogus' where event like 'rdbms ipc reply';

-- for 12c
update o$event_type set type='bogus' where event like 'LGWR worker group idle';

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


commit;

col name format a50 trunc
col wait_class format a20
col type format a20

prompt
prompt OraPub Categorization Summary
prompt ------------------------------------------------

select distinct type,
count(*)
from o$event_type
group by type
order by type
/
select count(*) from o$event_type
/

prompt
prompt Oracle Categorization Summary
prompt ------------------------------------------------

select distinct wait_class, count(*) from v$event_name group by wait_class order by wait_class
/
select count(*) from v$event_name
/

2.Now the following script can be saved under /home/oracle/rss.sql

-- ******************************************************
-- * Copyright Notice : (c)2014 OraPub, Inc.
-- * Filename : rss.sql - Realtime Session Sampler
-- * Author : Craig A. Shallahamer
-- * Original : 07-Jun-2014
-- * Last Modified : 15-Jul-2014
-- * Description : Collect and display session activity in realtime
-- * Usage : @rss sid_low sid_high serial#_low serial#_high cpu|wait|% event%|% delay_s
-- * @rss 0 999999 0 999999 % % 1
-- * @rss 0 999999 0 999999 cpu % 1
-- * @rss 0 999999 0 999999 wait % 1
-- * @rss 0 999999 0 999999 wait db%file%seq% 1
-- * @rss 328 328 1 1 % % 0.25
-- ******************************************************

set echo off verify off heading off
--set echo on verify on heading on

def sidLow=&1
def sidHigh=&2
def serLow=&3
def serHigh=&4
def state=&5
def wePartial=&6
def delay=&7

prompt OraPub Realtime Session Sampler - collection and display
prompt
prompt Every &delay second(s), session ID from &sidLow to &sidHigh and serial# from &serLow to &serHigh
prompt in a &state state will be sampled and displayed.
prompt When waiting, only display when wait event is like &wePartial
prompt 
prompt Output will be written to the /tmp/rss_sql.txt file.
prompt To stream output in realtime, in another window do: tail -f /tmp/rss_sql.txt
prompt
accept x prompt "To begin sampling press ENTER. Otherwise break out now."

create or replace directory temp_dir as '/tmp';

prompt To stop sampling, break out (e.g., CNTRL-C)
prompt
prompt Sampling started...

set serveroutput on

begin
declare
 type cv_typ is ref cursor;
 cv cv_typ;
 sql_to_run_v varchar2(999);

fHandle UTL_FILE.FILE_TYPE;

cntr_v number;
 next_sample_time_ts_var timestamp;

current_timestamp_v timestamp;
 sid_v number;
 serial#_v number;
 username_v varchar2(30);
 state_v varchar2(19);
 event_v varchar2(64);
 wecat_v varchar2(64);
 sql_id_v varchar2(13);
 p1_v number;
 p2_v number;
 p3_v number;

begin
 dbms_session.set_identifier('osm:i rss.sql');

fHandle := UTL_FILE.FOPEN('TEMP_DIR', 'rss_sql.txt', 'W');

UTL_FILE.PUT_LINE(fHandle, 'Starting sampling...');
 utl_file.fflush(fHandle);

cntr_v := 0;

while 1 = 1
 loop

cntr_v := cntr_v + 1;

select current_timestamp + interval '0 00:00:&delay' day to second
 into next_sample_time_ts_var
 from dual;

begin

open cv for
 select
 current_timestamp,
 sid,
 serial#,
 decode(username,'','-',username),
 decode(state,'WAITING','WAIT','CPU '),
 nvl(sql_id,'-'),
 decode(state,'WAITING',decode(o.type,'bogus','idle',o.type),'-'),
 decode(state,'WAITING',vs.event,'-') ,
 decode(state,'WAITING',p1,''),
 decode(state,'WAITING',p2,''),
 decode(state,'WAITING',p3,'')
 from v$session vs,
 o$event_type o
 where sid between &sidLow and &sidHigh
 and serial# between &serLow and &serHigh
 and decode(state,'WAITING','WAIT','CPU') like upper('&state%')
 and vs.event like '&wePartial%'
 and vs.event = o.event(+)
 and o.type != 'bogus';

loop
 fetch cv into
 current_timestamp_v, 
 sid_v,
 serial#_v,
 username_v,
 state_v,
 sql_id_v,
 wecat_v,
 event_v,
 p1_v, p2_v, p3_v
 ;
 exit when cv%NOTFOUND;

UTL_FILE.PUT_LINE(fHandle, lpad(trim(cntr_v),3,' ')||' '|| to_char(current_timestamp_v,'HH24:MI:SS:FF3')||' '||lpad(trim( sid_v ),5,' ')||lpad(trim(serial#_v),6,' ')||' '||lpad(trim(username_v),10,' ')||' '||state_v||' '||rpad(trim(sql_id_v),20,' ')||' '||rpad(trim(wecat_v),5,' ')||' '||rpad(trim(event_v),30,' ')||' ['||p1_v||','||p2_v||','||p3_v||']');
 utl_file.fflush(fHandle);

end loop;

EXCEPTION when others then
 cntr_v := cntr_v + 1;
 end;

dbms_lock.sleep( greatest( get_interval_s_fnc( next_sample_time_ts_var - current_timestamp ),0));

end loop;
 UTL_FILE.PUT_LINE(fHandle, 'Ending sampling...');
 UTL_FILE.FCLOSE(fHandle);
end;
end;
/

3.Now execute the script to take sames of either individual user or many users

Situation: I want to sample a single session (sid:10 serial:50) once every five seconds.

SQL>@rss.sql 10 10 50 50 % % 5.0

Situation: I want to essentially stream a single session's (sid:10 serial:50) activity.

SQL>@rss.sql 10 10 50 50 % % 0.125

Situation: I want to see what sessions are waiting for an row level lock while sampling once every second.

SQL>@rss.sql 0 99999 0 99999 wait enq%tx%row% 1.0

Situation: I want to see which sessions are consuming CPU, while sampling once every half second.

SQL>@rss.sql 0 99999 0 99999 cpu % 0.50

Situation:I want to same all sessions of my database

SQL> @rss.sql 0 99999 0 99999 % % 0.125

Reference

http://blog.orapub.com/20140811/watch-oracle-db-session-activity-with-the-real-time-session-sampler.html

Oracle DBaaS 1z0-160 part 33 :-Patching in Oracle cloud RAC database

Posted on by debasis maity

Step 1:-Please check available patch and run precheck

Step 2:-Pre-check results failed with below message

Step 3:-Update RDK using below command to update cloud tool

[opc@pocracdemo1 ~]$ raccli update rdk -tag latest

{

 “jobId” : “41”,

 “requestStatus” : “SUCCESS”

}

Step 4:-Please apply patch now

From command line

Apply a patch to the Oracle Database home using the apply patch subcommand with the -db option:

$ raccli apply patch -db -tag tag-name

Where tag-name is the name of the patch. To find out the tag name for the latest available patch, see What’s New for Oracle Database Cloud Service.

Oracle DBaaS 1z0-160 part 32 :-On-premise database migration to oracle cloud using data pump conventional Export/Import

Posted on by debasis maity

On-premise database migration to oracle cloud using data pump conventional Export/Import

This is simplest migration if you prefer to migrate one schema to oracle cloud.

Source:-On-premise database(Hostname:-gnssrv01 )

Target:-Oracle cloud database (Hostname:-myclone)

Step 1:-On the on-premises database host, create an operating system directory to use for the on-premises database export files.

$ mkdir /u01/app/oracle/admin/orcl/dpdump/for_cloud

Step 2:-Create a directory object in the on-premises database to reference the operating system directory.

[oracle@gnssrv01 admin]$ sqlplus test/test@orclpdb
SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 26 07:38:24 2018

SQL> CREATE DIRECTORY dp_for_cloud AS '/u01/app/oracle/admin/orcl/dpdump/for_cloud';
Directory created.

Step 3:-Please take export dump of the schema.On the on-premises database host, invoke Data Pump Export as the SYSTEM user or another user with the DATAPUMP_EXP_FULL_DATABASE role and export the on-premises schemas. Provide the password for the user when prompted.

oracle@gnssrv01 admin]$ expdp system/XXX@orclpdb SCHEMAS=test DIRECTORY=dp_for_cloud
Export: Release 12.2.0.1.0 - Production on Thu Apr 26 07:40:38 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orclpdb SCHEMAS=test DIRECTORY=dp_for_cloud
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST"."MYTAB"                              9.613 MB   72620 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl/dpdump/for_cloud/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Apr 26 07:42:07 2018 elapsed 0 00:01:18

Step 4:-On the Database Cloud Service compute node, create a directory for the dump file.

$ mkdir /u01/app/oracle/admin/ORCL/dpdump/from_onprem

Step 5:-On the on-premises database host, use the SCP utility to transfer the dump file to the Database Cloud Service compute node.

Please follow below note on how to use private key to transfer using scp to remote host.

[oracle@gnssrv01 ~]$ scp  -i my_priv_key /u01/app/oracle/admin/orcl/ dpdump/for_cloud/expdat.dmp oracle@144.21.83.108:/u01/app/ oracle/admin/ORCL/dpdump/from_onprem/expdat.dmp


Step 6:-On the Database Cloud Service compute node, invoke SQL*Plus and log in to the database as the SYSTEM user.

[oracle@myclone admin]$ sqlplus system/Bppimt#123@orclpdb
SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 27 03:32:50 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Apr 27 2018 03:32:24 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE DIRECTORY dp_from_onprem AS '/u01/app/oracle/admin/ORCL/dpdump/from_onprem';
Directory created.

Step 7:-Now import the schema in oracle cloud database

[oracle@myclone admin]$ impdp system/Bppimt#123@orclpdb SCHEMAS=test DIRECTORY=dp_from_onprem

Import: Release 12.2.0.1.0 – Production on Fri Apr 27 03:34:00 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/********@orclpdb SCHEMAS=test DIRECTORY=dp_from_onprem
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “TEST”.”MYTAB” 9.613 MB 72620 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Fri Apr 27 03:34:38 2018 elapsed 0 00:00:29

 

 

Cache buffer chain CBC latch simple query to investigate

Posted on by debasis maity

For deep understanding on CBC,please refer to below links:-

http://www.proligence.com/pres/ioug14/2014_431_Nanda_ppr.pdf

and

Video tutorial from ORAPUB.

I am just going to mention some useful queries to investigate or identify CBC latch

Step 1:-You as user fire a sql query from client.

select * from cbctest;

Step 2:-Now oracle will search metadata and determine what is your file_id and block_id to locate the block to be fetched  from buffer cache or physical disk.

select
col,
dbms_rowid.rowid_relative_fno(rowid) rfile#,
dbms_rowid.rowid_block_number(rowid) block#
from cbc;




Step 3:-Let me for example choose file_id = 6 and block_id=2288143.Now oracle will determine DBA value from it.


SQL> select dbms_utility.make_data_block_address(6,2288143) from dual;




 


Step 4:-If there are 3 chains,oracle will use module function  that returns the reminder from an input after dividing it by 3:


SQL> select mod(27453967,3) from dual;


MOD(27340036,3)

—————

1


So this block will be put into chain #1.


Simulation of CBC test:-


From one session,run following:-


declare
 aa varchar2(1000);
 begin
 for i in 1..1000000 loop
 select count(1) into aa from cbc ;
 end loop;
 end;
 /


From another session ,run following:-


declare
 begin
 for i in 1..1000000 loop
 update CBC set c1='MAITY';
 end loop;
 commit;
 end;
 /


From oratop,I can see “Latch: cache buffers chains” now.




Mechanism:-


1.Oracle once determine file_id and block_id,create a hash value first.


2.After determining hash value,oracle will acquire latch to hash into hash bucket and move across to chain to ask block header whether the block is already in memory or it needs to be fetched from disk.


When the block comes to the buffer cache, Oracle applies a hash function to determine the buffer chain number and places the block in a buffer in that chain alone. Similarly, while looking up a specific buffer, Oracle applies the same hash function to the DBA, instantly knows the chain the buffer will be found and walks that specific buffer only.This makes accessing a buffer much easier compared to searching the entire cache.


 




Why CBC cache buffer chain occurs


Problem:-


a)When different  session try to acquire latch (Your buffer is very popular) chain #1.It needs to spin and sleep several times.


b)When different session try to acquire latch chain #1 and chain #2.It needs to spin and sleep several times.


Remember,latch is exclusive and if one session is trying to acquire latch,other session will be in queue.


No problem:-


When different session try to acquire latch chain #1 and chain #4.There will be no contention as they are in different latch .


Now how we can identify CBC problem


Step 5:-Let us find the latch number of “cache buffers chains”


SQL> select latch# from v$latch where name = 'cache buffers chains';




LATCH#

———-

228


If you check the values of the two hidden parameters explained earlier, you will see:

_db_block_hash_buckets 524288

_db_block_hash_latches 16384


Step 7:-Now check longest sleep and gets by executing following query 1 minutes gap and check whether sleep and gets are increasing.


select * from v$latch_children where latch#=228 order by sleeps desc;




Step 8:-You can also identify the buffer causing cache buffer chain wait for session you are experiencing CBC problem.Let us say session# 208 is experiencing CBC contention.


select p1, p1raw, p1text
 from v$session where sid = 208;


Here please take a note of p1raw which is hash address.


Step 8:-Please verify gets,misses and sleeps from v$latch_children


select gets, misses, sleeps, name
 from v$latch where addr = '000000014AFC7A70';


Step 9:-Also determine touch count of the corresponding block .


select dbarfil, dbablk, tch
 from x$bh
 where hladdr = '000000014AFC7A70';


Identify overall problem on CBC 


Step 10:-You can also check from v$session_wait or v$active_session_history 


select p1raw,count(*) from v$session_wait where event like '%cache%buffers%' group by event, p1raw order by 2 desc


or


select p1, count(*)
 from v$active_session_history
 where sample_time < sysdate – 1/24
 and event = 'latch: cache buffers chain'
 group by event, p1
 order by 3 desc


Step 11:-The easiest way is to dump the block and get the object ID from the dump file. Here is how you dump the above mentioned block. 


alter system dump datafile 6 block min 220 block max 220;


or we can get block_id of corresponding address.


select hladdr, file#, dbablk, decode(state,1,'cur ',3,'CR',state) ST, tch
 from x$bh where hladdr in
 (select addr from (select addr from v$latch_children where addr='000000B9CA3336A0'
 order by sleeps, misses,immediate_misses desc )where rownum <2)


Step 12:-Now we can identify the segment_name which may be table or index using below query.


select segment_name
 from dba_extents
 where file_id = 48
 and 94182 between block_id and block_id + blocks - 1
 and rownum = 1


Solving problem:-


a)CBC latch waits are caused by popularity of the blocks by different processes. If you reduce the popularity, you reduce the chances that two processes will wait for the same

buffer. Note: you can’t completely eliminate the waits; you can only reduce it. To reduce is, reduce logical I/O. For instance,Nested Loops revisit the same object several times causing the buffers to be accessed multiple times. If you rewrite the query to avoid NLs, you will significantly reduce the chance that one process will wait for the CBC latch.


b)You can minimize less no of rows per block.The less the number of rows in a block, the less popular the block will be. You can reduce the number of rows in a block by increasing PCTFREE or using ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK.


c) If that does not help, you can partition a table. That forces the data block address to be recomputed for each partition, making it more likely that the buffers will end up in different buffer chains and hence the competition for the same chain will be less.

 
		

		 
	

	 

	
	


	

		
SSH connection from on-premise Linux or Unix host to Oracle cloud


		 
		

			Posted on  by debasis maity 
		

		 
	


	
	 
	

		
Problem


1.First I copied the private key generated by puttygen (The public key was provided during DB creation in oracle cloud).After that I tried to connect cloud database using below command:-


cd /home/oracle


chown oracle:oinstall course_priv.ppk


chmod 0600 course_priv.ppk


[oracle@gnssrv01 ~]$ ssh -i course_priv.ppk opc@144.21.83.108

Enter passphrase for key ‘course_priv.ppk’:

Enter passphrase for key ‘course_priv.ppk’:

Enter passphrase for key ‘course_priv.ppk’:

Permission denied (publickey,gssapi-keyex,gssapi-with-mic).


It was asking for passphrase which I selected NULL during creation of private key using puttygen. I was not able to login .


Solution:-


1.Let me create the private key in openssh format from import/export option of puttygen




2.Now I need to import the private key generated earlier using puttygen.




3.Now this will show your public key.




4.Please export the key using “Export OpenSSH key” option




Save this key as my_priv_key and transfer to on-premise host.


5.Now you will be able to login 


[root@gnssrv01 oracle]# chown oracle:oinstall my_priv_key

[root@gnssrv01 oracle]# chmod 0600 my_priv_key

[root@gnssrv01 oracle]# su – oracle

[oracle@gnssrv01 ~]$ ssh -v -i my_priv_key oracle@144.21.83.108   (The -v option is for debug.You can omit it)

 
		

		 
	

	 

	
	


	

		
TDE encryption setup for migrating on-premise backup to  Oracle cloud using RMAN


		 
		

			Posted on  by debasis maity 
		

		 
	


	
	 
	

		
This note describe how we can configure TDE encryption for RMAN backup in on-premise database to migrate to Oracle cloud.


1.First I need to update sqlnet.ora with proper location where my wallet will reside to enable TDE encryption.


# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.


NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
 ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
 (METHOD=FILE)
 (METHOD_DATA=
 (DIRECTORY=/var/opt/oracle/hdg/tde_wallet)
 )
 )

SQLNET.ENCRYPTION_SERVER = requested

SQLNET.ENCRYPTION_TYPES_SERVER = (RC4_256, AES256)

SQLNET.ENCRYPTION_CLIENT = requested

SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256, AES256)


2.Now I need to create the directory and restart the listener.


mkdir -p /var/opt/oracle/hdg/tde_wallet


3.Connect to the root container and create the keystore. Please restart database if you find nothing in v$encryption_wallet.


SQL> administer key management create keystore '/var/opt/oracle/hdg/tde_wallet' identified by manager123;

keystore altered.

SQL> administer key management set keystore open identified by manager123;

keystore altered.

SQL> administer key management set key identified by manager123 with backup using 'TDE';

keystore altered.


If you want to enable autologin,you can do below


SQL> administer KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM keystore '/u01/app/oracle/tde_wallet' identified by manager123;

keystore altered.


For special case,where you deleted the old keystore , you need to do following step


QL> administer key management create keystore '/u01/app/oracle/local/wallet_orcl' identified by manager123;

keystore altered.

SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;

no rows selected

SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=TRUE SCOPE=MEMORY;

System altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY manager123;

keystore altered.


4.If you have PDB,you need to connect to PDB and check whether auto login is enabled from PDB


SQL>alter session set container=PDB1;


SQL> select * from v$encryption_wallet;


FILE

OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED

4


You can see OPEN_NO_MASTER_KEY which will create problem during backup after setting encryption.


SQL> alter tablespace system ENCRYPTION online using 'AES256' encrypt FILE_NAME_CONVERT = ('system01.dbf', 'system01_encrypt.dbf');
 alter tablespace system ENCRYPTION online using 'AES256' encrypt FILE_NAME_CONVERT = ('system01.dbf', 'system01_encrypt.dbf')
 *
 ERROR at line 1:
 ORA-28374: typed master key not found in wallet


Now please follow below step to resolve .This step is valid for 12.2:-


SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY manager123 with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

FILE 
OPEN AUTOLOGIN SINGLE NO
 4


5.Convert non-encrypted tablespace to encrypted online in 12.2 to support TDE encryption.


SQL> alter tablespace system ENCRYPTION online using 'AES256' encrypt FILE_NAME_CONVERT = ('system01.dbf', 'system01_encrypt.dbf');

Tablespace altered.

 
		

		 
	

	 

	
	


	

		
Backup on-premise database and restore to Oracle cloud database


		 
		

			Posted on  by debasis maity 
		

		 
	


	
	 
	

		
My on-premise database hostname:-gnssrv01


Database name:-orcl12c


Cloud database hostname:-myclone


A.Backup of on-premise database to cloud container


Please create the empty container for holding backup of on-premise database using following link


https://clouddba.co/oracle-dbaas-1z0-160-part-24-backup-in-single-instance-oracle-cloud/


1.First I need to install backup module for cloud in on-premise database


You need to download opc.cert and opc_install.jar mentioned in above metalink note (Doc ID 2360941.1)


[oracle@myclone ~]$ java -jar opc_install.jar -host https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle-data-storage6-1 
-opcId 'sadhuarun1980@gmail.com' -opcPass 'xxx' -walletDir /home/oracle/my_wallet -libDir $ORACLE_HOME/lib -libPlatform linux64 -debug -trustedCerts /home/oracle/opc.cert

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
Debug: os.name = Linux
Debug: os.arch = amd64
Debug: os.version = 4.1.12-112.14.10.el6uek.x86_64
Debug: file.separator = /
Debug: Platform = PLATFORM_LINUX64
Debug: OPC Account Verification: <?xml version="1.0" encoding="UTF-8"?><container name="oracle-data-storage6-1"><object><name>file_chunk/804514966/ORCL12C/backuppiece/2018-04-23/18t11tsh_1_1/KTVs8w9V0wwD/0000000001</name><hash>5752a8f0131e0f3d0c728f15a7ea71d6</hash><bytes>75235328</bytes><content_type>Application/Octet-Stream</content_type><last_modified>2018-04-23T03:09:28.687920</last_modified></object></container>
 Error: The specified OPC host name should not include container or object in the URL path.

In that case we will try not to provide the storage container name and try.Oracle will create a default container.

[oracle@myclone ~]$ java -jar opc_install.jar -host https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun 
-opcId 'sadhuarun1980@gmail.com' -opcPass 'Start123456$' -walletDir /home/oracle/my_wallet 
-libDir $ORACLE_HOME/lib -libPlatform linux64 -debug -trustedCerts /home/oracle/opc.cert

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
Debug: os.name = Linux
Debug: os.arch = amd64
Debug: os.version = 4.1.12-112.14.10.el6uek.x86_64
Debug: file.separator = /
Debug: Platform = PLATFORM_LINUX64
Debug: OPC Account Verification: <?xml version="1.0" encoding="UTF-8" standalone="yes"?><account name="Storage-62b84aec8de8478ba17a6e1f84c3475c"><container><name>oracle-data-storage6-1</name><count>1</count><bytes>20640</bytes><accountId><id>31714</id></accountId><deleteTimestamp>0.0</deleteTimestamp><containerId><id>7665965</id></containerId></container></account>
Oracle Database Cloud Backup Module credentials are valid.
Debug: Certificate Success: file = /home/oracle/opc.cert
Debug: Certificate Success:
 Subject : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
 Validity : Fri Nov 10 00:00:00 UTC 2006 - Mon Nov 10 00:00:00 UTC 2031
 Issuer : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
Oracle Database Cloud Backup Module wallet created in directory /home/oracle/my_wallet.
Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora created.
Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.
Debug: Temp zip file = /tmp/opc_linux646549103754721599993.zip
Debug: Downloaded 27314069 bytes in 14 seconds.
Debug: Transfer rate was 1951004 bytes/second.
Download complete.
Debug: Delete RC = true




Please note your default container is oracle-data-storage6-1.


2.Please encrypt all tablespace (Including PDB tablespaces)  using below note.


http://clouddba.co/tde-encryption-setup-for-migrating-on-premise-backup-to-oracle-cloud-using-rman/


3. Taking backup from on-premises to oracle cloud now.Please note that backup configuration file was created as a part of step 1.


[oracle@gnssrv01 dbs]$ cat $ORACLE_HOME/dbs/opcorcl12c.ora

OPC_HOST=https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun

OPC_WALLET=’LOCATION=file:/home/oracle/bkp_wallet CREDENTIAL_ALIAS=alias_opc’


RMAN script to execute:-


run{
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure default device type to sbt;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
backup database ;
backup archivelog all;
backup current controlfile;
}


[oracle@gnssrv01 dbs]$ rman target /


Recovery Manager: Release 12.2.0.1.0 – Production on Mon Apr 23 08:25:49 2018


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


connected to target database: ORCL12C (DBID=804514966)


RMAN> set encryption on;


executing command: SET encryption

using target database control file instead of recovery catalog


RMAN> run{

2> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS=’SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;

CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

configure default device type to sbt;

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;

backup database plus archivelog;

backup current controlfile;

}3> 4> 5> 6> 7> 8> 9>


old RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;

new RMAN configuration parameters are successfully stored


old RMAN configuration parameters:

CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;

new RMAN configuration parameters are successfully stored


old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored


old RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

new RMAN configuration parameters are successfully stored


old RMAN configuration parameters:

CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters are successfully stored


Starting backup at 23-APR-18

current log archived

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=276 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_SBT_TAPE_2

channel ORA_SBT_TAPE_2: SID=250 device type=SBT_TAPE

channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_SBT_TAPE_3

channel ORA_SBT_TAPE_3: SID=41 device type=SBT_TAPE

channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_SBT_TAPE_4

channel ORA_SBT_TAPE_4: SID=270 device type=SBT_TAPE

channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_SBT_TAPE_5

channel ORA_SBT_TAPE_5: SID=30 device type=SBT_TAPE

channel ORA_SBT_TAPE_5: Oracle Database Backup Service Library VER=12.2.0.1

channel ORA_SBT_TAPE_1: starting compressed archived log backup set

channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=16 RECID=15 STAMP=974188502

channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_2: starting compressed archived log backup set

channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set

input archived log thread=1 sequence=18 RECID=17 STAMP=974190408

channel ORA_SBT_TAPE_2: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_3: starting compressed archived log backup set

channel ORA_SBT_TAPE_3: specifying archived log(s) in backup set

input archived log thread=1 sequence=17 RECID=16 STAMP=974188571

channel ORA_SBT_TAPE_3: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_3: finished piece 1 at 23-APR-18

piece handle=1at11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:00:25

channel ORA_SBT_TAPE_2: finished piece 1 at 23-APR-18

piece handle=19t11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35

channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18

piece handle=18t11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:05:05

Finished backup at 23-APR-18


Starting backup at 23-APR-18

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_SBT_TAPE_3

using channel ORA_SBT_TAPE_4

using channel ORA_SBT_TAPE_5

channel ORA_SBT_TAPE_1: starting compressed full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01_encrypt.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_2: starting compressed full datafile backup set

channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01_encrypt.dbf

channel ORA_SBT_TAPE_2: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_3: starting compressed full datafile backup set

channel ORA_SBT_TAPE_3: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/orcl12c/orclpdb/sysaux01_encrypt.dbf

channel ORA_SBT_TAPE_3: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_4: starting compressed full datafile backup set

channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

channel ORA_SBT_TAPE_4: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_5: starting compressed full datafile backup set

channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set

input datafile file number=00009 name=/u01/app/oracle/oradata/orcl12c/orclpdb/system01_encrypt.dbf

channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18

piece handle=1ft11u65_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:05:15

channel ORA_SBT_TAPE_5: starting compressed full datafile backup set

channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_4: finished piece 1 at 23-APR-18

piece handle=1et11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:09:40

channel ORA_SBT_TAPE_4: starting compressed full datafile backup set

channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl12c/undotbs01.dbf_encrypt.dbf

channel ORA_SBT_TAPE_4: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18

piece handle=1gt11ug0_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:04:26

channel ORA_SBT_TAPE_5: starting compressed full datafile backup set

channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01_encrypt.dbf

channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18

piece handle=1it11uoa_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:01:25

channel ORA_SBT_TAPE_5: starting compressed full datafile backup set

channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set

input datafile file number=00011 name=/u01/app/oracle/oradata/orcl12c/orclpdb/users01_encrypt.dbf

channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_4: finished piece 1 at 23-APR-18

piece handle=1ht11uo9_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:01:26

channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18

piece handle=1jt11uqv_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:00:25

channel ORA_SBT_TAPE_2: finished piece 1 at 23-APR-18

piece handle=1ct11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:12:13

channel ORA_SBT_TAPE_3: finished piece 1 at 23-APR-18

piece handle=1dt11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:14:33

channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18

piece handle=1bt11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:38:04

Finished backup at 23-APR-18


Starting backup at 23-APR-18

current log archived

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_SBT_TAPE_3

using channel ORA_SBT_TAPE_4

using channel ORA_SBT_TAPE_5

channel ORA_SBT_TAPE_1: starting compressed archived log backup set

channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=19 RECID=18 STAMP=974193071

channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18

piece handle=1kt120dg_1_1 tag=TAG20180423T091112 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35

Finished backup at 23-APR-18


Starting backup at 23-APR-18

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_SBT_TAPE_3

using channel ORA_SBT_TAPE_4

using channel ORA_SBT_TAPE_5

channel ORA_SBT_TAPE_1: starting compressed full datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18

channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18

piece handle=1lt120ej_1_1 tag=TAG20180423T091147 comment=API Version 2.0,MMS Version 12.2.0.1

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

Finished backup at 23-APR-18


Starting Control File and SPFILE Autobackup at 23-APR-18

piece handle=c-804514966-20180423-00 comment=API Version 2.0,MMS Version 12.2.0.1

Finished Control File and SPFILE Autobackup at 23-APR-18


4.Please note control file backup taken now which will be required to restore in cloud database.


RMAN> list backup of controlfile;


List of Backup Sets

===================


BS Key Type LV Size Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

45 Full 512.00K SBT_TAPE 00:00:08 23-APR-18

BP Key: 45 Status: AVAILABLE Compressed: YES Tag: TAG20180423T091147

Handle: 1lt120ej_1_1 Media: sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle

Control File Included: Ckp SCN: 2008170 Ckp time: 23-APR-18


BS Key Type LV Size Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

46 Full 18.00M SBT_TAPE 00:00:09 23-APR-18

BP Key: 46 Status: AVAILABLE Compressed: NO Tag: TAG20180423T091214

Handle: c-804514966-20180423-00 Media: sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle

Control File Included: Ckp SCN: 2008184 Ckp time: 23-APR-18


5.Please note list of archive logs in backup.These archive logs needs to be restored later .


RMAN> list archivelog all;


List of Archived Log Copies for database with db_unique_name ORCL12C

=====================================================================


Key Thrd Seq S Low Time

——- —- ——- – ———

15 1 16 A 23-APR-18

Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_16_fftjzrkr_.arc


16 1 17 A 23-APR-18

Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_17_fftk238x_.arc


17 1 18 A 23-APR-18

Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_18_fftlvjqy_.arc


18 1 19 A 23-APR-18

Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_19_fftogq8q_.arc


19 1 20 A 23-APR-18

Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_ffxojbfp_.arc


20 1 21 A 24-APR-18

Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_24/o1_mf_1_21_ffxpg2fn_.arc


B.Restoration of on-premise database backup to cloud Machine


1.Please copy the TDE wallet generated in on-premise in previous step to oracle cloud TDE wallet (/u01/app/oracle/admin/orcl12c/tde_wallet).This database will be cloned from existing on-premise backup stored in cloud container.


[oracle@myclone admin]$ cat sqlnet.ora

SQLNET.ENCRYPTION_SERVER = required


SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)


SQLNET.CRYPTO_CHECKSUM_SERVER = required


ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl12c/tde_wallet)))


SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)


NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)


SQLNET.WALLET_OVERRIDE = FALSE


SQLNET.EXPIRE_TIME = 10


SSL_VERSION = 1.2


WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl12c/db_wallet)))


Please restart the database and check wallet status


SQL> select * from v$encryption_wallet;


WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS WALLET_TYPE WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

CON_ID

———-

FILE

/u01/app/oracle/admin/orcl12c/tde_wallet/

OPEN AUTOLOGIN SINGLE NO

1


2.Please  copy opc wallet from on-premise database which we created using step 1 procedure (earlier part of this note) of this note.You can also create a new OPC file as well using step 1 procedure.


3.Please make a note or modify the opc configuration file.Please note I have explicitly mentioned OPC_CONTAINER if it is not able to identify from previous process.


[oracle@myclone dbs]$ cat opcorcl12c.ora

OPC_HOST=https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun

OPC_WALLET=’LOCATION=file:/home/oracle/my_wallet CREDENTIAL_ALIAS=alias_opc’ OPC_CONTAINER=’oracle-data-storage6-1′


4.Please restore the controlfile now.Please use the control file tag we identified during backup.


[oracle@myclone orcl12c]$ sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 06:48:01 2018


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


Connected to:

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


SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.


Total System Global Area 2768240640 bytes

Fixed Size 8796624 bytes

Variable Size 704644656 bytes

Database Buffers 1979711488 bytes

Redo Buffers 75087872 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production


[oracle@myclone orcl12c]$ rman target /


Recovery Manager: Release 12.2.0.1.0 – Production on Tue Apr 24 06:49:43 2018


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


connected to target database: ORCL12C (not mounted)


RMAN>set decryption identified by ‘manager123’; <—(If you do not use autologin TDE wallet,you need to execute this command for password based TDE wallet)


RMAN> set DBID=804514966;<---(This DBID is on-premise database DBID)
run{

executing command: SET DBID
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore controlfile from 'c-804514966-20180423-00';
}


RMAN> 2> 3> 4>

using target database control file instead of recovery catalog

allocated channel: t1

channel t1: SID=237 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=12.2.0.1


Starting restore at 24-APR-18


channel t1: restoring control file

channel t1: restore complete, elapsed time: 00:00:03

output file name=/u02/app/oracle/oradata/orcl12c/control01.ctl

output file name=/u03/app/oracle/fast_recovery_area/orcl12c/control02.ctl

Finished restore at 24-APR-18

released channel: t1


RMAN>alter database mount;


5.Now we will restore the database.


run{
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore database;
}


RMAN> 2> 3> 4>


allocated channel: t1

channel t1: SID=22 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=12.2.0.1


Starting restore at 24-APR-18

Starting implicit crosscheck backup at 24-APR-18

Finished implicit crosscheck backup at 24-APR-18


Starting implicit crosscheck copy at 24-APR-18

Finished implicit crosscheck copy at 24-APR-18


searching for all files in the recovery area

cataloging files…

cataloging done


List of Cataloged Files

=======================

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_8_ffxnqmvs_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_7_ffxlzxkz_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_22/o1_mf_1_6_ffrz86dj_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_1_ffpn8cd7_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_3_ffpn9jpr_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_2_ffpn969s_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_4_ffpnbm66_.arc

File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_5_ffpnc7g3_.arc


channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl12c/orclpdb/system01_encrypt.dbf

channel t1: reading from backup piece 1ft11u65_1_1

channel t1: piece handle=1ft11u65_1_1 tag=TAG20180423T083306

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:15

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf


…….


channel t1: restore complete, elapsed time: 00:00:15

Finished restore at 24-APR-18

released channel: t1


6.Please restore the archive logs  from backup


[oracle@myclone orcl12c]$ rman target /


Recovery Manager: Release 12.2.0.1.0 – Production on Tue Apr 24 07:32:18 2018


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


connected to target database: ORCL12C (DBID=804514966, not open)


RMAN> run{
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore archivelog from sequence 16 until sequence 21;
}


7.Recover the database now


[oracle@myclone orcl12c]$ sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 07:33:55 2018


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


Connected to:

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


SQL> recover database using backup controlfile until cancel;


ORA-00279: change 2007164 generated at 04/23/2018 08:33:07 needed for thread 1

ORA-00289: suggestion :

/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_19_ffxq

gvp1_.arc

ORA-00280: change 2007164 for thread 1 is in sequence #19


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


ORA-00279: change 2008148 generated at 04/23/2018 09:11:11 needed for thread 1

ORA-00289: suggestion :

/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_%u_.

arc

ORA-00280: change 2008148 for thread 1 is in sequence #20

ORA-00278: log file

‘/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_19_ffx

qgvp1_.arc’ no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


ORA-00308: cannot open archived log

‘/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_%u_

.arc’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7


7. Please open the database in resetlogs


SQL> alter database open resetlogs;


Database altered.


 

 
		

		 
	

	 

	
	


	

		
Oracle DBaaS 1z0-160 part 31 :- Oracle cloud database security


		 
		

			Posted on  by debasis maity 
		

		 
	


	
	 
	

		
Data security


In Oracle Database Cloud Service databases, data security is provided for data in transit and data at  rest.


Security of data in transit is achieved through network encryption. Data at rest security is achieved through encryption of data stored in


database data files and backups.Data in Oracle Database files, including backups, is secured by the use of encryption implemented through a key management framework. Security of data across the network is provided by native Oracle Net encryption and integrity capabilities.


Security of data at rest


Oracle Database Cloud Service uses Oracle Transparent Data Encryption to encrypt data in the database data files and in backups. Encrypted data is also protected in temporary tablespaces, undo segments, redo logs and during internal database operations such as JOIN and SORT.


Oracle Transparent Data Encryption (TDE) includes a keystore (referred to as a wallet in Oracle Database 11g and previous releases) to securely store master encryption keys, and a management framework to securely and efficiently manage the keystore and perform key maintenance operations.


TDE is the underlying mechanism used for default tablespace encryption and encrypted backups. It uses a two-tiered, key-based architecture to transparently encrypt and decrypt data. The master encryption key is stored in the software keystore. For tablespace encryption, this master encryption key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace. By default in Oracle Database Cloud Service When a database deployment is created on Oracle Database Cloud Service, a local auto-login software keystore is created. The keystore is local to the compute node and is protected by a system-generated password. The auto-login software keystore is automatically opened when accessed.


The keystore location is specified in the ENCRYPTION_WALLET_LOCATION parameter in


the $ORACLE_HOME/network/admin/sqlnet.ora file.


The Oracle keystore stores a history of retired TDE master encryption keys, which enables you to change them and still be able to decrypt data that was encrypted under an earlier TDE master encryption key.


By default, backups to Cloud Storage for Enterprise Edition databases are encrypted. Recovery Manager (RMAN) performs transparent encryption using the auto-login software keystore.


Please refer below note for more examples:-


https://clouddba.co/tde-encryption-setup-for-migrating-on-premise-backup-to-oracle-cloud-using-rman/


Tablespace Encryption


By default, all new tablespaces that you create in a Database Cloud Service database are encrypted.


However, not all of the tablespaces created when you create a database deployment are encrypted:


    

  • In an Oracle Database 11g database, none of the tablespaces created when you create a database deployment are encrypted.
    • 

  • In an Oracle Database 12c Release 1 database, none of the tablespaces created when you create a database deployment are encrypted. This includes the tablespaces in the root (CDB$ROOT), the seed (PDB$SEED), and the PDB created when
    • 



you create a database deployment.


    

  • In an Oracle Database 12c Release 2 or later database, only the USERS tablespaces created when you create a database deployment are encrypted. None of the other tablespaces are encrypted. This includes the tablespaces in the root (CDB$ROOT), the seed (PDB$SEED), and the PDB created when you create a database deployment.
    • 



User-created tablespaces are encrypted by default.


By default, any new tablespaces created by using the SQL CREATE TABLESPACE command are encrypted with the AES128 encryption algorithm. You do not need to include the USING ‘encrypt_algorithm’ clause to use the default encryption.


You can specify another supported algorithm by including the USING ‘encrypt_algorithm’ clause in the CREATE TABLESPACE command. Supported algorithms for Oracle Database 11g and later are AES256, AES192, AES128, and 3DES168.


Managing Tablespace Encryption


You can manage the software keystore (known as an Oracle wallet in Oracle Database 11g), the master encryption key, and control whether encryption is enabled by default.


Managing the Software Keystore and Master Encryption Key Tablespace encryption uses a two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The master encryption key is stored in an external security module (software keystore). This master encryption key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.


When the database deployment is created on Database Cloud Service, a local autologin software keystore is created. The keystore is local to the compute node and is protected by a system-generated password. The auto-login software keystore is automatically opened when accessed.


You can change (rotate) the master encryption key by using the tde rotate masterkey subcommand of the dbaascli utility. When you execute this subcommand you will be prompted for the keystore password. Enter the password specified during the database deployment creation process. For example:


DBAAS>tde rotate masterkey


Controlling Default Tablespace Encryption


The ENCRYPT_NEW_TABLESPACES initialization parameter controls default encryption of new tablespaces. In Database Cloud Service databases, this parameter is set to CLOUD_ONLY by default. See Viewing and Modifying Initialization Parameters for additional information.With Oracle Database 12c Release 2 (12.2) or later releases on Database Cloud Service, you can no longer create a new unencrypted tablespace. An error message is returned if you set ENCRYPT_NEW_TABLESPACES to DDL and issue a CREATE TABLESPACE command without specifying an ENCRYPTION clause.






 


Value

Description




Always
During creation, tablespaces are transparently encrypted with the AES128 algorithm unless a different algorithm is specified in the ENCRYPTION clause.




CLOUD_ONLY
Tablespaces created in a Database Cloud Service database are transparently encrypted with the AES128 algorithm unless a different algorithm is specified in the ENCRYPTION clause. For noncloud databases, tablespaces are only encrypted if the ENCRYPTION clause is specified. This is the default value.




DDL
During creation, tablespaces are not transparently encrypted by default, and are only encrypted if the ENCRYPTION clause is specified.






 


Security of Data in Transit


To secure connections to your Oracle Database Cloud Service databases, you can use native Oracle Net encryption and integrity capabilities.Encryption of network data provides data privacy so that unauthorized parties are not able to view data as it passes over the network. In addition, integrity algorithms protect against data modification and illegitimate replay.


Oracle Database provides the Advanced Encryption Standard (AES), DES, 3DES, and RC4 symmetric cryptosystems for protecting the confidentiality of Oracle Net traffic. It also provides a keyed, sequenced implementation of the Message Digest 5 (MD5) algorithm or the Secure Hash Algorithm (SHA-1 and SHA-2) to protect against integrity attacks.


By default, database deployments on Database Cloud Service are configured to enable native Oracle Net encryption and integrity. Also, by default, Oracle Net clients are configured to enable native encryption and integrity when they connect to an appropriately configured server. If your Oracle Net client is configured to explicitly reject the use of native encryption and integrity then connection attempts will fail.


Checking your Database Cloud Service environment


The following procedure outlines the basic steps required to confirm that native Oracle Net encryption and integrity are enabled in your Database Cloud Service environment.


    

  1. In a command shell, connect to the compute node as the oracle user.
    2. 

  2. Change directories to the location of the sqlnet.ora Oracle Net configuration file. For example:
    3. 



$ cd $ORACLE_HOME/network/admin


$ ls sqlnet.ora


sqlnet.ora


    

  1. View the sqlnet.ora file and confirm that it contains the following parameter settings:
    2. 



SQLNET.ENCRYPTION_SERVER = required


SQLNET.CRYPTO_CHECKSUM_SERVER = required


The required setting enables the encryption or integrity service and disallows the connection if the client side is not enabled for the security service. This is the default setting for database deployments on Database Cloud Service.


Checking your Oracle Net Client Configuration


The following procedure outlines the basic steps required to confirm that native encryption and integrity are enabled in your Oracle Net client configuration.


    

  1. In a command shell, connect to the Oracle Net client.
    2. 

  2. Change directories to the location of the Oracle Net configuration files
    3. 



tnsnames.ora and sqlnet.ora, for example:


$ cd $ORACLE_HOME/network/admin


sqlnet.ora tnsnames.ora


    

  1. View the sqlnet.ora file and confirm that it does not contain the following
    2. 



parameter settings:


SQLNET.ENCRYPTION_CLIENT = rejected


SQLNET.CRYPTO_CHECKSUM_CLIENT = rejected


The rejected setting explicitly disables the encryption or integrity service, even if the server requires it. When a client with an encryption or integrity service setting of rejected connects to a server with the required setting, the connection fails with the following error: ORA-12660: Encryption or crypto-checksumming parameters incompatible.


Because native Oracle Net encryption and integrity are enabled in your Database Cloud Service environment by default, any parameter setting other than rejected,or no setting at all, would result in the use of native encryption and integrity.


Verifying the use of Native Encryption and Integrity


You can verify the use of native Oracle Net encryption and integrity by connecting to your Oracle database and examining the network service banner entries associated with each connection. This information is contained in the NETWORK_SERVICE_BANNER column of the V$SESSION_CONNECT_INFO view. The following example shows the SQL command used to display the network service banner entries associated with current connection:


SQL> select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat);


The following example output shows banner information for the available encryption service and the crypto-checksumming (integrity) service, including the algorithms in use:


NETWORK_SERVICE_BANNER


————————————————————————————-


TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 – Production


Encryption service for Linux: Version 12.1.0.2.0 – Production


AES256 Encryption service adapter for Linux: Version 12.1.0.2.0 – Production


Crypto-checksumming service for Linux: Version 12.1.0.2.0 – Production


SHA1 Crypto-checksumming service adapter for Linux: Version 12.1.0.2.0 – Production




Verifying the use of Native Encryption and Integrity


If native Oracle Net encryption and integrity was not in use, the banner entries would still include entries for the available security services; that is, the services linked into the Oracle Database software. However, there would be no entries indicating the specific algorithms in use for the connection. The following output shows an example:


NETWORK_SERVICE_BANNER


————————————————————————————-


TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 – Production


Encryption service for Linux: Version 12.1.0.2.0 – Production


Crypto-checksumming service for Linux: Version 12.1.0.2.0 – Production