Saturday 31 March 2018

Oracle CREATE ASM DISKGROUP manually in 11gR2/12C

Create ASM DiskGroup ....!!!!

Node 1

#ls -ltr /dev/mapper/

lrwxrwxrwx 1 root root 8 Mar 29 10:01 ora_fra_0003p1 -> ../dm-29

lrwxrwxrwx 1 root root 8 Mar 29 10:01 ora_acfs_0001 -> ../dm-43

#oracleasm createdisk -v ORA_0001 /dev/mapper/ora_acfs_0001

# oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001 <<<<<< New disk

ls -alrt /dev/oracleasm/disks/ORA*

scan and list

# date ; hostname; oracleasm scandisks ; for d in `oracleasm listdisks | sort`; do oracleasm querydisk -p $d; done

Node2

#oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "ORA_0001"

Scanning system for ASM disks...

#oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001 <<<<<< New disk

Node3

#oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "ORA_0001"

Scanning system for ASM disks...

#oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001 <<<<<< New disk

-- for Candidate disk !!!

ASMCMD [+] > lsdsk --candidate -p

Group_Num Disk_Num Incarn Mount_Stat Header_Stat Mode_Stat State Path

set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout on trimspool on verify off

clear columns breaks computes

column disk_group_name format a12 head 'disk group name'

column disk_file_path format a45 head 'path'

column disk_file_name format a12 head 'file name'

column disk_file_fail_group format a12 head 'fail group'

column total_mb format 999,999,999 head 'file size (mb)'

column used_mb format 999,999,999 head 'used size (mb)'

column pct_used format 999.99 head 'pct. used'

break on report on disk_group_name skip 1

compute sum label "" of total_mb used_mb on disk_group_name

compute sum label "grand total: " of total_mb used_mb on report

SELECT

NVL(a.name, '[CANDIDATE]') disk_group_name

, b.path disk_file_path

, b.name disk_file_name

, b.failgroup disk_file_fail_group

, b.total_mb total_mb

, (b.total_mb - b.free_mb) used_mb

-- , ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used

FROM v$asm_diskgroup a ,v$asm_disk b

where a.group_number (+) =b.group_number

ORDER BY a.name;

Disk Group N Path File Name Fail Group File Size (MB) Used Size (MB)

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

************

[candidate] /dev/oracleasm/disks/ORA_0001 0 0

************ -------------- --------------

0 0

SQL> CREATE DISKGROUP ORAFS EXTERNAL REDUNDANCY disk '/dev/oracleasm/disks/ORA_0001';

Check the disk group ...

[grid]$ asmcmd -p

ASMCMD [+] > lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 51200 51150 0 51150 0 N ORAFS/

ASMCMD [+] > ls -l + <<<< for DiskGroup !!!

ASMCMD [+] > lsdsk -t -G DATA

Create_Date Mount_Date Repair_Timer Path

01-FEB-18 01-FEB-18 0 ORCL:DATA0

01-FEB-18 01-FEB-18 0 ORCL:DATA1

01-FEB-18 01-FEB-18 0 ORCL:DATA2

01-FEB-18 01-FEB-18 0 ORCL:DATA3

set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout on

set trimspool on verify off

clear columns breaks computes

column disk_group_name format a12 head 'disk group name'

column disk_file_path format a45 head 'path'

column disk_file_name format a12 head 'file name'

column disk_file_fail_group format a12 head 'fail group'

column total_mb format 999,999,999 head 'file size (mb)'

column used_mb format 999,999,999 head 'used size (mb)'

column pct_used format 999.99 head 'pct. used'

break on report on disk_group_name skip 1

compute sum label "" of total_mb used_mb on disk_group_name

compute sum label "grand total: " of total_mb used_mb on report

SELECT

NVL(a.name, '[CANDIDATE]') disk_group_name

, b.path disk_file_path

, b.name disk_file_name

, b.failgroup disk_file_fail_group

, b.total_mb total_mb

, (b.total_mb - b.free_mb) used_mb

, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used

FROM v$asm_diskgroup a ,v$asm_disk b

where a.group_number (+) =b.group_number

ORDER BY a.name;

*********************************************************************

Imp ......

On all the Nodes mount the DiskGroup !!!!!!!!!!!!!!!!!!!!!

SQL> alter diskgroup ORAFS mount ;

Diskgroup altered.

ASMCMD [+] > lsdg -g <<<<<<<< check for all node

*********************************************************************

If Required ..

sqlplus> alter diskgroup ORACFS set attribute 'compatible.asm'='11.2.0.0.0';

set linesize 200

col compatibility form a15

col database_compatibility form a25

col name form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

set linesize 200 pagesize 300

col value for a10

col name for a50

select group_number, name, value from v$asm_attribute

where 1=1

and group_number=1

;

GROUP_NUMBER NAME VALUE

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

1 idp.type dynamic

1 idp.boundary auto

1 disk_repair_time 3.6h

asmcmd lsattr -G DATA -l
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 12.1.0.0.0
compatible.rdbms 10.1.0.0.0
content.check FALSE
content.type data
disk_repair_time 3.6h
failgroup_repair_time 24.0h
idp.boundary auto
idp.type dynamic
phys_meta_replicated true
sector_size 512
thin_provisioned FALSE

[grid@rac01 ~]$ asmcmd -p

ASMCMD [+] > lsattr -G DATA -l
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 12.1.0.0.0
compatible.rdbms 10.1.0.0.0
content.check FALSE
content.type data
disk_repair_time 3.6h
failgroup_repair_time 24.0h
idp.boundary auto
idp.type dynamic
phys_meta_replicated true
sector_size 512

thin_provisioned FALSE

=====

set pagesize 0

select

'GROUP_NUMBER : '||GROUP_NUMBER ||chr(10)||

'NAME : '||NAME ||chr(10)||

'SECTOR_SIZE : '||SECTOR_SIZE ||chr(10)||

'BLOCK_SIZE : '||BLOCK_SIZE ||chr(10)||

'ALLOCATION_UNIT_SIZE : '||ALLOCATION_UNIT_SIZE ||chr(10)||

'STATE : '||STATE ||chr(10)||

'TYPE : '||TYPE ||chr(10)||

'TOTAL_MB : '||TOTAL_MB ||chr(10)||

'FREE_MB : '||FREE_MB ||chr(10)||

'HOT_USED_MB : '||HOT_USED_MB ||chr(10)||

'COLD_USED_MB : '||COLD_USED_MB ||chr(10)||

'REQUIRED_MIRROR_FREE_MB : '||REQUIRED_MIRROR_FREE_MB ||chr(10)||

'USABLE_FILE_MB : '||USABLE_FILE_MB ||chr(10)||

'OFFLINE_DISKS : '||OFFLINE_DISKS ||chr(10)||

'COMPATIBILITY : '||COMPATIBILITY ||chr(10)||

'DATABASE_COMPATIBILITY : '||DATABASE_COMPATIBILITY ||chr(10)||

'VOTING_FILES : '||VOTING_FILES

from v$asm_diskgroup

where 1=1

order by 1

;

set pagesize 100

Monday 26 March 2018

Oracle 12c: Transportable Tablespaces


Oracle 12c: Transportable Tablespaces


[oracle@vbgeneric ~]$ export TWO_TASK=

sqlplus / as sysdba



show pdbs

 CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 ORCL READ WRITE NO Move from tablespace>>>>>>>> 
 4 ORDS READ WRITE NO
 5 ANUJ READ WRITE NO <<<<< To Move tablespace
 7 ORCLDP READ WRITE NO




set linesize 300
col FILE_NAME for a90
select FILE_ID,FILE_NAME from cdb_data_files ;

 FILE_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------
 20 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_usertbs_dxk31j8j_.dbf
 19 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_users_dxk31j7b_.dbf
 18 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_sysaux_dxk31j9h_.dbf
 21 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anuj01.dbf
 17 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_system_dxk31j8o_.dbf
 23 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/tts01_.dbf <<< datafile 


******************

Demo ...


SQL> alter session set container=orcl;
Session altered.


set linesize 200 pagesize 100 serveroutput on
column "db details" format a100
select
 'db_name: ' ||sys_context('userenv', 'db_name') ||
 ' \cdb:-' ||(select cdb from v$database) ||
 ' \auth_id: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \user: ' ||sys_context('userenv', 'current_user') ||
 ' \container:' ||nvl(sys_context('userenv', 'con_name'), 'non-cdb') "db details"
 from dual ;
----------------------------------------------------------------------------------------------------
db_name: orcl12c \cdb:-YES \auth_id: oracle \user: SYS \container:ORCL



Create tablespace .. 

CREATE TABLESPACE tts datafile '/u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/tts01_.dbf' size 1024M autoextend on maxsize unlimited ;
Tablespace created.


SQL> create user ttsuser identified by tts ;

User created.

SQL> grant connect,resource to ttsuser ;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ttsuser;

Grant succeeded.

SQL> alter user ttsuser default tablespace tts ;

User altered.


Create table and row .. 

create table TTSUSER.table1_test ( EmpNO number(8), EmpDate date ) ;
create table TTSUSER.table2_test ( Emp_name varchar2(20), EmpDate date ) ;

declare
i number;
begin
for i in 1..1000 loop
insert into TTSUSER.table1_test values(i,sysdate);
insert into TTSUSER.table2_test values ('Number is LAST'||i, sysdate);
end loop;
commit;
end;


SQL> select count(*) from TTSUSER.table1_test;

 COUNT(*)
----------
 1000


SQL> select count(*) from TTSUSER.table2_test ;

 COUNT(*)
----------
 1000



SQL> alter tablespace tts read only ;

Tablespace altered.

Now copy datafile 

Copy datafile now from one pluggable database(orcl) to another pluggable database (anuj) dir .. 


[oracle@vbgeneric ~]$ ls -ltr /u01/app/oracle/oradata/orcl12c/ANUJ/
total 2078520
-rw-r----- 1 oracle oinstall 20979712 May 1 2017 pdbseed_temp012016-06-02_07-10-28-AM.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 25 11:45 users01.dbf
-rw-r----- 1 oracle oinstall 272637952 Mar 25 14:58 system01.dbf
-rw-r----- 1 oracle oinstall 775954432 Mar 25 14:58 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Mar 25 15:00 tts01_.dbf >>>>>> 


cp /u01/app/oracle/oradata/orcl12c/orcl/tts01_.dbf /u01/app/oracle/oradata/orcl12c/ANUJ/


ls -ltr /u01/app/oracle/oradata/orcl12c/ANUJ/
total 2078580
-rw-r----- 1 oracle oinstall 20979712 Mar 25 15:06 pdbseed_temp012016-06-02_07-10-28-AM.dbf
-rw-r----- 1 oracle oinstall 1073750016 Mar 25 15:07 tts01_.dbf <<<<<<
-rw-r--r-- 1 oracle oinstall 1014 Mar 25 15:07 import.log
-rw-r----- 1 oracle oinstall 5251072 Mar 26 02:22 users01.dbf
-rw-r----- 1 oracle oinstall 775954432 Mar 26 03:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 272637952 Mar 26 03:24 system01.dbf



Now On diffrent pluggable database i.e. anuj 

SQL> alter session set container=anuj;

Session altered.


create directory TMP as '/u01/app/oracle/oradata/orcl12c/ANUJ';


create database link tts connect to system identified by sys using '//localhost/orcl';

Database link created.



SQL> create user ttsuser identified by tts ;

User created.

SQL> grant connect,resource to ttsuser ;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ttsuser;

Grant succeeded.

SQL> alter user ttsuser default tablespace tts ;

User altered.



impdp '"sys/vihaan@//localhost/anuj as sysdba"' network_link=tts directory=TMP TRANSPORT_TABLESPACES=TTS transport_datafiles=/u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf

Import: Release 12.1.0.2.0 - Production on Sun Mar 25 15:04:45 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/********@//localhost/anuj AS SYSDBA" network_link=tts directory=TMP TRANSPORT_TABLESPACES=TTS transport_datafiles=/u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf
Source time zone is +00:00 and target time zone is -07:00.
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Mar 25 15:07:55 2018 elapsed 0 00:03:01




SQL> alter session set container=anuj;

Session altered.

SQL> select count(*) from TTSUSER.table2_test;

 COUNT(*)
----------
 1000

oracle whoami.sql

oracle whoami.sql?

whoami.sql

oracle 12c 
set linesize 200 pagesize 100 serveroutput on
column "db details" format a100
select
 'db_name: ' ||sys_context('userenv', 'db_name') ||
 ' \cdb:-' ||(select cdb from v$database) ||
 ' \auth_id: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \user: ' ||sys_context('userenv', 'current_user') ||
 ' \container:' ||nvl(sys_context('userenv', 'con_name'), 'non-cdb') "db details"
 from dual ;

-----


set linesize 200 pagesize 0 serveroutput on
column "db details" format a200
select
'user:\'||sys_context('userenv','session_user')||' session_id:\ '||'current_schema:\ '||sys_context('userenv','current_schema')||' instance_name:\ '||sys_context('userenv','instance_name')||
' database role: '||sys_context('userenv','database_role')||' os user:\ '||sys_context('userenv','os_user')||' client ip address:\ '||sys_context('userenv','ip_address')||' server hostname:\ '||sys_context('userenv','server_host')||' client hostname:\ '||sys_context('userenv','host') "db details"
from dual ;



-- From Web ...

set serveroutput on
begin
dbms_output.put_line('USER: ' ||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID: ' ||sys_context('userenv','sid'));
dbms_output.put_line('CURRENT_SCHEMA: ' ||sys_context('userenv','current_schema'));
dbms_output.put_line('INSTANCE NAME: ' ||sys_context('userenv','instance_name'));
dbms_output.put_line('DATABASE ROLE: ' ||sys_context('userenv','database_role'));
dbms_output.put_line('OS USER: ' ||sys_context('userenv','os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv','ip_address'));
dbms_output.put_line('SERVER HOSTNAME: ' ||sys_context('userenv','server_host'));
dbms_output.put_line('CLIENT HOSTNAME: ' ||sys_context('userenv','host'));
end;
/

Saturday 10 March 2018

Linux - Sum using awk

UNIX Sum using awk

unix sum for file

ls -ltr|grep -i 'Mar 10' | awk '{ SUM += $5} END { print SUM/1024/1024/1024 }' --- in GB

ls -l database_full_*_2859*_1.bk | awk '{ SUM += $5} END { print SUM/1024/1024 }' --- In Mb

find /u01/app/oracle/diag/rdbms/anuj/ANUJ/trace -name '*.trm' -exec ls -l {} \; | awk '{ Total += $5} END { print Total/1024/1024 }'

Tuesday 6 March 2018

Oracle feature usage ....

col currently_used for a15

col name format a60

col detected_usages format 999999999999

select u1.name, u1.version ,u1.currently_used , u1.detected_usages from dba_feature_usage_statistics u1

where u1.version = (select max(u2.version) from dba_feature_usage_statistics u2

where u2.name = u1.name)

and u1.detected_usages > 0

and u1.dbid = (select dbid from v$database)

order by name;

force to update above view default update time 1 week

alter session set nls_date_format='DD-MM-YYYY HH24:mi' ;

SELECT MAX(last_usage_date) last_update_time FROM dba_feature_usage_statistics;

LAST_UPDATE_TIME

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

04-03-2018 05:26

SQL> EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

PL/SQL procedure successfully completed.

alter session set nls_date_format='DD-MM-YYYY HH24:mi' ;

SELECT sysdate, MAX(last_usage_date) last_update_time FROM dba_feature_usage_statistics;

SYSDATE LAST_UPDATE_TIME

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

06-03-2018 03:36 06-03-2018 03:34

Database Licensing Information User Manual

https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109

https://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116

http://anuj-singh.blogspot.co.uk/2011_11_21_archive.html

Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2, 12c and 12cR2 (Doc ID 1317265.1)

select product
, decode(usage, 'NO_USAGE','NO', usage ) "Used"
, last_sample_date
, first_usage_date
, last_usage_date
------- following sql is based on options_packs_usage_statistics.sql --> MOS Note 1317265.1
from (
with
MAP as (
-- mapping between features tracked by DBA_FUS and their corresponding database products (options or packs)
select '' PRODUCT, '' feature, '' MVERSION, '' CONDITION from dual union all
SELECT 'Active Data Guard' , 'Active Data Guard - Real-Time Query on Physical Standby' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Active Data Guard' , 'Global Data Services' , '^12\.' , ' ' from dual union all
SELECT 'Advanced Analytics' , 'Data Mining' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'ADVANCED Index Compression' , '^12\.' , 'BUG' from dual union all
SELECT 'Advanced Compression' , 'Advanced Index Compression' , '^12\.' , 'BUG' from dual union all
SELECT 'Advanced Compression' , 'Backup HIGH Compression' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Backup LOW Compression' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Backup MEDIUM Compression' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Backup ZLIB Compression' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Data Guard' , '^11\.2|^12\.' , 'C001' from dual union all
SELECT 'Advanced Compression' , 'Flashback Data Archive' , '^11\.2\.0\.[1-3]\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Flashback Data Archive' , '^(11\.2\.0\.[4-9]\.|12\.)' , 'INVALID' from dual union all -- licensing required by Optimization for Flashback Data Archive
SELECT 'Advanced Compression' , 'HeapCompression' , '^11\.2|^12\.1' , 'BUG' from dual union all
SELECT 'Advanced Compression' , 'HeapCompression' , '^12\.[2-9]' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Heat Map' , '^12\.1' , 'BUG' from dual union all
SELECT 'Advanced Compression' , 'Heat Map' , '^12\.[2-9]' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Hybrid Columnar Compression Row Level Locking' , '^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Information Lifecycle Management' , '^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Oracle Advanced Network Compression Service' , '^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'Oracle Utility Datapump (Export)' , '^11\.2|^12\.' , 'C001' from dual union all
SELECT 'Advanced Compression' , 'Oracle Utility Datapump (Import)' , '^11\.2|^12\.' , 'C001' from dual union all
SELECT 'Advanced Compression' , 'SecureFile Compression (user)' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Compression' , 'SecureFile Deduplication (user)' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Security' , 'ASO native encryption and checksumming' , '^11\.2|^12\.' , 'INVALID' from dual union all -- no longer part of Advanced Security
SELECT 'Advanced Security' , 'Backup Encryption' , '^11\.2' , ' ' from dual union all
SELECT 'Advanced Security' , 'Backup Encryption' , '^12\.' , 'INVALID' from dual union all -- licensing required only by encryption to disk
SELECT 'Advanced Security' , 'Data Redaction' , '^12\.' , ' ' from dual union all
SELECT 'Advanced Security' , 'Encrypted Tablespaces' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Security' , 'Oracle Utility Datapump (Export)' , '^11\.2|^12\.' , 'C002' from dual union all
SELECT 'Advanced Security' , 'Oracle Utility Datapump (Import)' , '^11\.2|^12\.' , 'C002' from dual union all
SELECT 'Advanced Security' , 'SecureFile Encryption (user)' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Advanced Security' , 'Transparent Data Encryption' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Change Management Pack' , 'Change Management Pack' , '^11\.2' , ' ' from dual union all
SELECT 'Configuration Management Pack for Oracle Database' , 'EM Config Management Pack' , '^11\.2' , ' ' from dual union all
SELECT 'Data Masking Pack' , 'Data Masking Pack' , '^11\.2' , ' ' from dual union all
SELECT '.Database Gateway' , 'Gateways' , '^12\.' , ' ' from dual union all
SELECT '.Database Gateway' , 'Transparent Gateway' , '^12\.' , ' ' from dual union all
SELECT 'Database In-Memory' , 'In-Memory Aggregation' , '^12\.' , ' ' from dual union all
SELECT 'Database In-Memory' , 'In-Memory Column Store' , '^12\.1\.0\.2\.0' , 'BUG' from dual union all
SELECT 'Database In-Memory' , 'In-Memory Column Store' , '^12\.1\.0\.2\.[^0]|^12\.2' , ' ' from dual union all
SELECT 'Database Vault' , 'Oracle Database Vault' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Database Vault' , 'Privilege Capture' , '^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'ADDM' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'AWR Baseline' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'AWR Baseline Template' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'AWR Report' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'Automatic Workload Repository' , '^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'Baseline Adaptive Thresholds' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'Baseline Static Computations' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'Diagnostic Pack' , '^11\.2' , ' ' from dual union all
SELECT 'Diagnostics Pack' , 'EM Performance Page' , '^12\.' , ' ' from dual union all
SELECT '.Exadata' , 'Exadata' , '^11\.2|^12\.' , ' ' from dual union all
SELECT '.GoldenGate' , 'GoldenGate' , '^12\.' , ' ' from dual union all
SELECT '.HW' , 'Hybrid Columnar Compression' , '^12\.1' , 'BUG' from dual union all
SELECT '.HW' , 'Hybrid Columnar Compression' , '^12\.[2-9]' , ' ' from dual union all
SELECT '.HW' , 'Hybrid Columnar Compression Row Level Locking' , '^12\.' , ' ' from dual union all
SELECT '.HW' , 'Sun ZFS with EHCC' , '^12\.' , ' ' from dual union all
SELECT '.HW' , 'ZFS Storage' , '^12\.' , ' ' from dual union all
SELECT '.HW' , 'Zone maps' , '^12\.' , ' ' from dual union all
SELECT 'Label Security' , 'Label Security' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Multitenant' , 'Oracle Multitenant' , '^12\.' , 'C003' from dual union all -- licensing required only when more than one PDB containers are created
SELECT 'Multitenant' , 'Oracle Pluggable Databases' , '^12\.' , 'C003' from dual union all -- licensing required only when more than one PDB containers are created
SELECT 'OLAP' , 'OLAP - Analytic Workspaces' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'OLAP' , 'OLAP - Cubes' , '^12\.' , ' ' from dual union all
SELECT 'Partitioning' , 'Partitioning (user)' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Partitioning' , 'Zone maps' , '^12\.' , ' ' from dual union all
SELECT '.Pillar Storage' , 'Pillar Storage' , '^12\.' , ' ' from dual union all
SELECT '.Pillar Storage' , 'Pillar Storage with EHCC' , '^12\.' , ' ' from dual union all
SELECT '.Provisioning and Patch Automation Pack' , 'EM Standalone Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all
SELECT 'Provisioning and Patch Automation Pack for Database' , 'EM Database Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all
SELECT 'RAC or RAC One Node' , 'Quality of Service Management' , '^12\.' , ' ' from dual union all
SELECT 'Real Application Clusters' , 'Real Application Clusters (RAC)' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Real Application Clusters One Node' , 'Real Application Cluster One Node' , '^12\.' , ' ' from dual union all
SELECT 'Real Application Testing' , 'Database Replay: Workload Capture' , '^11\.2|^12\.' , 'C004' from dual union all
SELECT 'Real Application Testing' , 'Database Replay: Workload Replay' , '^11\.2|^12\.' , 'C004' from dual union all
SELECT 'Real Application Testing' , 'SQL Performance Analyzer' , '^11\.2|^12\.' , 'C004' from dual union all
SELECT '.Secure Backup' , 'Oracle Secure Backup' , '^12\.' , 'INVALID' from dual union all -- does not differentiate usage of Oracle Secure Backup Express, which is free
SELECT 'Spatial and Graph' , 'Spatial' , '^11\.2' , 'INVALID' from dual union all -- does not differentiate usage of Locator, which is free
SELECT 'Spatial and Graph' , 'Spatial' , '^12\.' , ' ' from dual union all
SELECT 'Tuning Pack' , 'Automatic Maintenance - SQL Tuning Advisor' , '^12\.' , 'INVALID' from dual union all -- system usage in the maintenance window
SELECT 'Tuning Pack' , 'Automatic SQL Tuning Advisor' , '^11\.2|^12\.' , 'INVALID' from dual union all -- system usage in the maintenance window
SELECT 'Tuning Pack' , 'Real-Time SQL Monitoring' , '^11\.2' , ' ' from dual union all
SELECT 'Tuning Pack' , 'Real-Time SQL Monitoring' , '^12\.' , 'INVALID' from dual union all -- default
SELECT 'Tuning Pack' , 'SQL Access Advisor' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Tuning Pack' , 'SQL Monitoring and Tuning pages' , '^12\.' , ' ' from dual union all
SELECT 'Tuning Pack' , 'SQL Profile' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Tuning Pack' , 'SQL Tuning Advisor' , '^11\.2|^12\.' , ' ' from dual union all
SELECT 'Tuning Pack' , 'SQL Tuning Set (user)' , '^12\.' , 'INVALID' from dual union all -- no longer part of Tuning Pack
SELECT 'Tuning Pack' , 'Tuning Pack' , '^11\.2' , ' ' from dual union all
SELECT '.WebLogic Server Management Pack Enterprise Edition' , 'EM AS Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all
select '' PRODUCT, '' FEATURE, '' MVERSION, '' CONDITION from dual
),
FUS as (
-- the current data set to be used: DBA_FEATURE_USAGE_STATISTICS or CDB_FEATURE_USAGE_STATISTICS for Container Databases(CDBs)
select
0 as CON_ID,
NULL as CON_NAME,
-- Detect and mark with Y the current DBA_FUS data set = Most Recent Sample based on LAST_SAMPLE_DATE
case when DBID || '#' || VERSION || '#' || to_char(LAST_SAMPLE_DATE, 'YYYYMMDDHH24MISS') =
first_value (DBID ) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) || '#' ||
first_value (VERSION ) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) || '#' ||
first_value (to_char(LAST_SAMPLE_DATE, 'YYYYMMDDHH24MISS'))
over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc)
then 'Y'
else 'N'
end as CURRENT_ENTRY,
NAME ,
LAST_SAMPLE_DATE,
DBID ,
VERSION ,
DETECTED_USAGES ,
TOTAL_SAMPLES ,
CURRENTLY_USED ,
FIRST_USAGE_DATE,
LAST_USAGE_DATE ,
AUX_COUNT ,
FEATURE_INFO
from DBA_FEATURE_USAGE_STATISTICS xy
),
PFUS as (
-- Product-Feature Usage Statitsics = DBA_FUS entries mapped to their corresponding database products
select
CON_ID,
CON_NAME,
PRODUCT,
NAME as FEATURE_BEING_USED,
case when CONDITION = 'BUG'
--suppressed due to exceptions/defects
then '3.SUPPRESSED_DUE_TO_BUG'
when detected_usages > 0 -- some usage detection - current or past
and CURRENTLY_USED = 'TRUE' -- usage at LAST_SAMPLE_DATE
and CURRENT_ENTRY = 'Y' -- current record set
and ( trim(CONDITION) is null -- no extra conditions
or CONDITION_MET = 'TRUE' -- extra condition is met
and CONDITION_COUNTER = 'FALSE' ) -- extra condition is not based on counter
then '6.CURRENT_USAGE'
when detected_usages > 0 -- some usage detection - current or past
and CURRENTLY_USED = 'TRUE' -- usage at LAST_SAMPLE_DATE
and CURRENT_ENTRY = 'Y' -- current record set
and ( CONDITION_MET = 'TRUE' -- extra condition is met
and CONDITION_COUNTER = 'TRUE' ) -- extra condition is based on counter
then '5.PAST_OR_CURRENT_USAGE' -- FEATURE_INFO counters indicate current or past usage
when detected_usages > 0 -- some usage detection - current or past
and ( trim(CONDITION) is null -- no extra conditions
or CONDITION_MET = 'TRUE' ) -- extra condition is met
then '4.PAST_USAGE'
when CURRENT_ENTRY = 'Y'
then '2.NO_CURRENT_USAGE' -- detectable feature shows no current usage
else '1.NO_PAST_USAGE'
end as USAGE,
LAST_SAMPLE_DATE,
DBID ,
VERSION ,
DETECTED_USAGES ,
TOTAL_SAMPLES ,
CURRENTLY_USED ,
case when CONDITION like 'C___' and CONDITION_MET = 'FALSE'
then to_date('')
else FIRST_USAGE_DATE
end as FIRST_USAGE_DATE,
case when CONDITION like 'C___' and CONDITION_MET = 'FALSE'
then to_date('')
else LAST_USAGE_DATE
end as LAST_USAGE_DATE,
EXTRA_FEATURE_INFO
from (
select m.PRODUCT, m.CONDITION, m.MVERSION,
-- if extra conditions (coded on the MAP.CONDITION column) are required, check if entries satisfy the condition
case
when CONDITION = 'C001' and ( regexp_like(to_char(FEATURE_INFO), 'compression used:[ 0-9]*[1-9][ 0-9]*time', 'i')
or regexp_like(to_char(FEATURE_INFO), 'compression used: *TRUE', 'i') )
then 'TRUE' -- compression has been used
when CONDITION = 'C002' and ( regexp_like(to_char(FEATURE_INFO), 'encryption used:[ 0-9]*[1-9][ 0-9]*time', 'i')
or regexp_like(to_char(FEATURE_INFO), 'encryption used: *TRUE', 'i') )
then 'TRUE' -- encryption has been used
when CONDITION = 'C003' and CON_ID=1 and AUX_COUNT > 1
then 'TRUE' -- more than one PDB are created
when CONDITION = 'C004' and 'N'= 'N'
then 'TRUE' -- not in oracle cloud
else 'FALSE'
end as CONDITION_MET,
-- check if the extra conditions are based on FEATURE_INFO counters. They indicate current or past usage.
case
when CONDITION = 'C001' and regexp_like(to_char(FEATURE_INFO), 'compression used:[ 0-9]*[1-9][ 0-9]*time', 'i')
then 'TRUE' -- compression counter > 0
when CONDITION = 'C002' and regexp_like(to_char(FEATURE_INFO), 'encryption used:[ 0-9]*[1-9][ 0-9]*time', 'i')
then 'TRUE' -- encryption counter > 0
else 'FALSE'
end as CONDITION_COUNTER,
case when CONDITION = 'C001'
then regexp_substr(to_char(FEATURE_INFO), 'compression used:(.*?)(times|TRUE|FALSE)', 1, 1, 'i')
when CONDITION = 'C002'
then regexp_substr(to_char(FEATURE_INFO), 'encryption used:(.*?)(times|TRUE|FALSE)', 1, 1, 'i')
when CONDITION = 'C003'
then 'AUX_COUNT=' || AUX_COUNT
when CONDITION = 'C004' and 'N'= 'Y'
then 'feature included in Oracle Cloud Services Package'
else ''
end as EXTRA_FEATURE_INFO,
f.CON_ID ,
f.CON_NAME ,
f.CURRENT_ENTRY ,
f.NAME ,
f.LAST_SAMPLE_DATE,
f.DBID ,
f.VERSION ,
f.DETECTED_USAGES ,
f.TOTAL_SAMPLES ,
f.CURRENTLY_USED ,
f.FIRST_USAGE_DATE,
f.LAST_USAGE_DATE ,
f.AUX_COUNT ,
f.FEATURE_INFO
from MAP m
join FUS f on m.FEATURE = f.NAME and regexp_like(f.VERSION, m.MVERSION)
where nvl(f.TOTAL_SAMPLES, 0) > 0 -- ignore features that have never been sampled
)
where nvl(CONDITION, '-') != 'INVALID' -- ignore features for which licensing is not required without further conditions
and not (CONDITION = 'C003' and CON_ID not in (0, 1)) -- multiple PDBs are visible only in CDB$ROOT; PDB level view is not relevant
)
select
grouping_id(CON_ID) as gid,
CON_ID ,
decode(grouping_id(CON_ID), 1, '--ALL--', max(CON_NAME)) as CON_NAME,
PRODUCT ,
decode(max(USAGE),
'1.NO_PAST_USAGE' , 'NO_USAGE' ,
'2.NO_CURRENT_USAGE' , 'NO_USAGE' ,
'3.SUPPRESSED_DUE_TO_BUG', 'SUPPRESSED_DUE_TO_BUG',
'4.PAST_USAGE' , 'PAST_USAGE' ,
'5.PAST_OR_CURRENT_USAGE', 'PAST_OR_CURRENT_USAGE',
'6.CURRENT_USAGE' , 'CURRENT_USAGE' ,
'UNKNOWN') as USAGE,
max(LAST_SAMPLE_DATE) as LAST_SAMPLE_DATE,
min(FIRST_USAGE_DATE) as FIRST_USAGE_DATE,
max(LAST_USAGE_DATE) as LAST_USAGE_DATE
from PFUS
where USAGE in ('2.NO_CURRENT_USAGE', '4.PAST_USAGE', '5.PAST_OR_CURRENT_USAGE', '6.CURRENT_USAGE') -- ignore '1.NO_PAST_USAGE', '3.SUPPRESSED_DUE_TO_BUG'
group by rollup(CON_ID), PRODUCT
having not (max(CON_ID) in (-1, 0) and grouping_id(CON_ID) = 1) -- aggregation not needed for non-container databases
order by GID desc, CON_ID, decode(substr(PRODUCT, 1, 1), '.', 2, 1), PRODUCT );

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

SET LINESIZE 300
SET PAGESIZE 1000
SET FEEDBACK OFF
COL "Host Name" FORMAT A50
COL "Option/Management Pack" FORMAT A60
COL "Used" FORMAT A5
with features as(
select a OPTIONS, b NAME from
(
select 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual
union all
select 'Advanced Compression', 'HeapCompression' from dual
union all
select 'Advanced Compression', 'Backup BZIP2 Compression' from dual
union all
select 'Advanced Compression', 'Backup DEFAULT Compression' from dual
union all
select 'Advanced Compression', 'Backup HIGH Compression' from dual
union all
select 'Advanced Compression', 'Backup LOW Compression' from dual
union all
select 'Advanced Compression', 'Backup MEDIUM Compression' from dual
union all
select 'Advanced Compression', 'Backup ZLIB, Compression' from dual
union all
select 'Advanced Compression', 'SecureFile Compression (user)' from dual
union all
select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual
union all
select 'Advanced Compression', 'Data Guard' from dual
union all
select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual
union all
select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual
union all
select 'Advanced Security', 'ASO native encryption and checksumming' from dual
union all
select 'Advanced Security', 'Transparent Data Encryption' from dual
union all
select 'Advanced Security', 'Encrypted Tablespaces' from dual
union all
select 'Advanced Security', 'Backup Encryption' from dual
union all
select 'Advanced Security', 'SecureFile Encryption (user)' from dual
union all
select 'Change Management Pack', 'Change Management Pack (GC)' from dual
union all
select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual
union all
select 'Data Mining', 'Data Mining' from dual
union all
select 'Diagnostic Pack', 'Diagnostic Pack' from dual
union all
select 'Diagnostic Pack', 'ADDM' from dual
union all
select 'Diagnostic Pack', 'AWR Baseline' from dual
union all
select 'Diagnostic Pack', 'AWR Baseline Template' from dual
union all
select 'Diagnostic Pack', 'AWR Report' from dual
union all
select 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual
union all
select 'Diagnostic Pack', 'Baseline Static Computations' from dual
union all
select 'Tuning Pack', 'Tuning Pack' from dual
union all
select 'Tuning Pack', 'Real-Time SQL Monitoring' from dual
union all
select 'Tuning Pack', 'SQL Tuning Advisor' from dual
union all
select 'Tuning Pack', 'SQL Access Advisor' from dual
union all
select 'Tuning Pack', 'SQL Profile' from dual
union all
select 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual
union all
select 'Database Vault', 'Oracle Database Vault' from dual
union all
select 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual
union all
select 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual
union all
select 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual
union all
select 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual
union all
select 'Exadata', 'Exadata' from dual
union all
select 'Label Security', 'Label Security' from dual
union all
select 'OLAP', 'OLAP - Analytic Workspaces' from dual
union all
select 'Partitioning', 'Partitioning (user)' from dual
union all
select 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual
union all
select 'Real Application Testing', 'Database Replay: Workload Capture' from dual
union all
select 'Real Application Testing', 'Database Replay: Workload Replay' from dual
union all
select 'Real Application Testing', 'SQL Performance Analyzer' from dual
union all
select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual
union all
select 'Total Recall', 'Flashback Data Archive' from dual
)
)
select t.o "Option/Management Pack",
t.u "Used",
d.DBID "DBID",
d.name "DB Name",
i.version "DB Version",
i.host_name "Host Name",
to_char(sysdate, 'dd-mm-YYYY HH24:MI:SS') "ReportGen Time"
from
(select OPTIONS o, DECODE(sum(num),0,'NO','YES') u
from
(
select f.OPTIONS OPTIONS, case
when f_stat.name is null then 0
when ( ( f_stat.currently_used = 'TRUE' and
f_stat.detected_usages > 0 and
(sysdate - f_stat.last_usage_date) < 366 and
f_stat.total_samples > 0
)
or
(f_stat.detected_usages > 0 and
(sysdate - f_stat.last_usage_date) < 366 and
f_stat.total_samples > 0)
) and
( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')
or
(f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and
f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0')
)
then 1
else 0
end num
from features f,
sys.dba_feature_usage_statistics f_stat
where f.name = f_stat.name(+)
) group by options) t,
v$instance i,
v$database d
order by 2 desc,1
;

======

Set linesize 200 pagesize 300

Col name format a75 heading "Feature"

Col version format a10 heading "Version"

Col detected_usages format 999,990 heading "Detected|usages"

Col currently_used format a06 heading "Curr.|used?"

Col first_usage_date format a10 heading "First use"

Col last_usage_date format a10 heading "Last use"

Col nop noprint

Break on nop skip 1 on name

Select decode(detected_usages,0,2,1) nop,

name, version, detected_usages, currently_used,

to_char(first_usage_date,'DD/MM/YYYY') first_usage_date,

to_char(last_usage_date,'DD/MM/YYYY') last_usage_date

from dba_feature_usage_statistics

order by nop, 1, 2

Detected Curr.

Feature Version usages used? First use Last use

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

ACFS 12.2.0.1.0 209 TRUE 02/06/2017 10/07/2021

ADDM 12.2.0.1.0 12 FALSE 02/09/2017 03/07/2021

AWR Report 12.2.0.1.0 13 TRUE 26/08/2017 10/07/2021

Adaptive Plans 12.2.0.1.0 209 TRUE 02/06/2017 10/07/2021

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Saturday 31 March 2018

Oracle CREATE ASM DISKGROUP manually in 11gR2/12C

Oracle CREATE ASM DISKGROUP manually in 11gR2/12C

Monday 26 March 2018

Oracle 12c: Transportable Tablespaces

Oracle 12c: Transportable Tablespaces

oracle whoami.sql

oracle whoami.sql?

Saturday 10 March 2018

Linux - Sum using awk

UNIX Sum using awk

Tuesday 6 March 2018

Oracle feature usage ....

Oracle feature usage ....

Oracle DBA

anuj blog Archive