Loading...

Follow Carlos Magno – EzDBA on Feedspot

Continue with Google
Continue with Facebook
or

Valid

– Be sure that the BUG Fix (18633374) is applied on the Source Database:

— The PSU 12.1.0.2.190416 or higher should be applied also on the source.

*** On-Premises:

1.) Check Status of the Source Database:
SQL> select * from gv$pdbs where con_id=13;

INST_ID     CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
     1         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ WRITE NO  06-MAY-19 02.03.32.098 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0
     2         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ WRITE NO  06-MAY-19 02.04.32.119 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0
2.) GLobal user for DB LInk:
create user c##_link identified by link001#;
grant dba to c##_link container=all;
grant create pluggable database to c##_link container=all;

3.) Close the database:

SQL> alter pluggable database pdbtest close instances=all;

Pluggable database altered.

SQL> select * from gv$pdbs where con_id=13;

INST_ID     CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
     1         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        MOUNTED        06-MAY-19 02.06.27.877 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0
     2         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        MOUNTED        06-MAY-19 02.06.28.166 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0

4.) open the Database in Just on Node in READ ONLY MODE:

SQL> alter pluggable database pdbtest open read only;

Pluggable database altered.

SQL> select * from gv$pdbs where con_id=13;

INST_ID     CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
     1         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ ONLY  NO  06-MAY-19 02.10.32.513 PM +02:00                                            8042252890  886046720       8192 ENABLED                       0
     2         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        MOUNTED        06-MAY-19 02.06.28.166 PM +02:00                                            8042252890  886046720       8192 ENABLED                       0

*** CLOUD:

5.) Create the TNS Entry on the TNSNAMES.ORA

PDBTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDBTEST)
)
)

[oracle@oci-racdb1 admin]

$ tnsping pdbtest

TNS Ping Utility for Linux: Version 12.2.0.1.0 – Production on 06-MAY-2019 12:06:29

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDBTEST)))
OK (40 msec)

[oracle@oci-racdb1 admin]

$

[oracle@oci-racdb1 admin]

$ sqlplus c##_link/link001#@pdbtest

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 12:07:16 2019

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

Last Successful login time: Tue Apr 02 2019 13:26:37 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>

conn / as sysdba

6.) Create a database link from CLOUD Database to ON-Premise:

create public database link lnk_PDBTEST connect to c##_link identified by link001# using ‘PDBTEST’;

SQL> select * from v$pdbs@lnk_PDBTEST;

CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
    13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ ONLY  NO  06-MAY-19 02.10.32.513 PM +02:00                                            8042252890  886046720       8192 ENABLED                       0

7.) Create the new PDB on the Oracle Cloud:

SQL> show parameter tables

NAME TYPE VALUE

encrypt_new_tablespaces string DDL

CREATE PLUGGABLE DATABASE PDBTEST FROM PDBTEST@lnk_PDBTEST;

SQL> SQL> SQL> SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 PDB1                           READ WRITE NO
     4 PDB2                           READ WRITE NO
     5 PDB3                           READ WRITE NO
     6 PDBTEST                        MOUNTED

SQL> alter pluggable database PDBTEST open;

Warning: PDB altered with errors.

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 PDB1                           READ WRITE NO
     4 PDB2                           READ WRITE NO
     5 PDB3                           READ WRITE NO
     6 PDBTEST                        MIGRATE    YES

8.) Check the violations

*** In case of errors with APEX ***
cd /u01/oracle/product/12.1.0/dbhome_1/apex
How to Uninstall Oracle HTML DB / Application Express from a 10G/11G Database (Doc ID 558340.1)
Deinstall the APEX from the PDB if is not in use.

*** Violations
set lin 1000
set pages 10000
select * from  PDB_PLUG_IN_VIOLATIONS where status='PENDING' and TYPE='ERROR';
1* select * from  PDB_PLUG_IN_VIOLATIONS where status='PENDING' and TYPE='ERROR'
SQL> /
TIME                           NAME            CAUSE                          TYPE      ERROR_NUMBER       LINE MESSAGE                                                                                                                  STATUS    ACTION                                                                               CON_ID
06-MAY-19 02.11.16.853559 PM   PDBTEST         VSN not match                  ERROR                0          1 PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's version 12.2.0.1.0.                          PENDING   Either upgrade the PDB or reload the components in the PDB.                               6

9.) Upgrade the PDB:

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -c 'PDBTEST' catupgrd.sql

sqlplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 PDB1                           READ WRITE NO
     4 PDB2                           READ WRITE NO
     5 PDB3                           READ WRITE NO
     6 PDBTEST                        MOUNTED

alter pluggable database pdbtest open instances=all;

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 PDB1                           READ WRITE NO
     4 PDB2                           READ WRITE NO
     5 PDB3                           READ WRITE NO
     6 PDBTEST                        READ WRITE YES

exit;

10.) Apply Datapatch
cd $ORACLE_HOME/OPatch
./datapatch -verbose -pdbs PDBTEST
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 7 14:09:42 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 PDB1                           READ WRITE NO
     4 PDB2                           READ WRITE NO
     5 PDB3                           READ WRITE NO
     6 PDBTEST                        READ WRITE YES
SQL> alter pluggable database PDBTEST close instances=all;
Pluggable database altered.
SQL> alter pluggable database PDBTEST open instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 PDB1                           READ WRITE NO
     4 PDB2                           READ WRITE NO
     5 PDB3                           READ WRITE NO
     6 PDBTEST                        READ WRITE NO
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Carlos Magno – EzDBA by Carlos Magno - 4M ago

— Table Compression

https://oracle-base.com/articles/11g/dbms_compression-11gr2
https://www.oracle.com/us/assets/lad-2015-ses16380-pedregal-2604876.pdf
http://www.bestremotedba.com/2017/01/25/how-to-advance-compression-12c/
https://www.morganslibrary.org/reference/compression.html

--- MOS Notes
- Advanced Compression Master Note(Doc ID 1223705.1)
– How to compress a table that is online(Doc ID 1353967.1)
– Advanced Compression critical patches(Doc ID 1061366.1)
– Redo Transport compression with Data Guard (Doc ID 729551.1)
– How to see if rows are compressed in a table(Doc ID 1477918.1)
--- Tested on

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

— Table: cmagno.testcomp

create tablespace ts_2011 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2011.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2012 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2012.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2013 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2013.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2014 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2014.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2015 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2015.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2016 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2016.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2017 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2017.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2018 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2018.dbf' size 10m autoextend on next 10m maxsize 1g;

create tablespace ts_2019 datafile '/opt/oracle/oradata/db01/DB01/datafile/ts_2019.dbf' size 10m autoextend on next 10m maxsize 1g;
CREATE TABLE CMAGNO.TESTCOMP
(
NUM NUMBER
, DATE_INS DATE
, TEXT VARCHAR2(200)
)
TABLESPACE "TS_TESTE"
PARTITION BY RANGE (DATE_INS) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','dd-MON-yyyy')) TABLESPACE "TS_2011"
, PARTITION P2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','dd-MON-yyyy')) TABLESPACE "TS_2012"
, PARTITION P2013 VALUES LESS THAN (TO_DATE('01-JAN-2014','dd-MON-yyyy')) TABLESPACE "TS_2013"
, PARTITION P2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy')) TABLESPACE "TS_2014"
, PARTITION P2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy')) TABLESPACE "TS_2015"
, PARTITION P2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')) TABLESPACE "TS_2016"
, PARTITION P2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy')) TABLESPACE "TS_2017"
, PARTITION P2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy')) TABLESPACE "TS_2018"
, PARTITION P2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','dd-MON-yyyy')) TABLESPACE "TS_2019"
);


— Loading data

declare
varDate date;
varNum number;
begin

varDate := to_date('01/01/2011','DD/MM/YYYY');

while varDate != trunc(sysdate) loop
select hsecs into varNUm from v$timer;
insert into cmagno.TESTCOMP values (varNum, varDate, 'Data for :' || to_char(varDate,'DD/MM/YYYY'));
varDate := vardate +(1/24);
end loop;
commit;
dbms_stats.gather_table_stats('CMAGNO','TESTCOMP');
end;
/
--- Execute some times the command below in order to have more data.
insert into cmagno.TESTCOMP select * from cmagno.TESTCOMP;
commit;
exec dbms_stats.gather_table_stats('CMAGNO','TESTCOMP');

— Checking the current status

col partition_name format a15;
col tablespace_name format a15;
select
a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block,
sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb
from dba_tab_partitions a, dba_segments b
where
a.table_name = 'TESTCOMP' and
a.partition_name = b.partition_name and
a.table_name = b.segment_name
group by a.partition_name, a.tablespace_name, a.compression, a.compress_for
order by 1;
PARTITION_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS MB
--------------- --------------- -------- ----------------- ------------ -------------------- --------------------
P2011 TS_2011 DISABLED 10465280 58054 456
P2012 TS_2012 DISABLED 10493952 58054 456
P2013 TS_2013 DISABLED 10465280 58054 456
P2014 TS_2014 DISABLED 10465280 58054 456
P2015 TS_2015 DISABLED 10465280 58054 456
P2016 TS_2016 DISABLED 10493952 58054 456
P2017 TS_2017 DISABLED 10465280 58054 456
P2018 TS_2018 DISABLED 10465280 58054 456
P2019 TS_2019 DISABLED 1462272 9094 72

— Checking the Compress ratio

SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.get_compression_ratio (
scratchtbsname => 'TS_2011',
ownname => 'CMAGNO',
objname => 'TESTCOMP',
subobjname => 'P2011',
comptype => DBMS_COMPRESSION.comp_advanced,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
objtype => DBMS_COMPRESSION.objtype_table
);

DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
END;
/
Number of blocks used (compressed) : 751
Number of blocks used (uncompressed) : 1577
Number of rows in a block (compressed) : 378
Number of rows in a block (uncompressed) : 179
Compression ratio : 2
Compression type : "Compress Advanced"

— Compression Type:
https://docs.oracle.com/database/121/ARPLS/d_compress.htm#ARPLS65600

— Compressing:

Basic Compression -> Read only tables and partitions in Data Warehouse environments or “inactive” data partitions in OLTP environments
Advanced Row Compression -> Active tables and partitions in OLTP and Data Warehouse environments
Advanced LOB Compression and Deduplication -> Non-relational data in OLTP and Data Warehouse environments

alter table cmagno.TESTCOMP compress partitionfor oltp;
-- All OPERATIONS
ALTER TABLE cmagno.TESTCOMP move PARTITION P2011 COMPRESS FOR ALL OPERATIONS;
-- OLTP
ALTER TABLE cmagno.TESTCOMP move PARTITION P2012 COMPRESS FOR OLTP;
-- Basic
ALTER TABLE cmagno.TESTCOMP move PARTITION P2013 COMPRESS ;
-- Compress Advanced
ALTER TABLE cmagno.TESTCOMP move PARTITION P2014 ROW STORE COMPRESS ADVANCED;

— Checking the Partitions

exec dbms_stats.gather_table_stats(‘CMAGNO’,’TESTCOMP’);

select
a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block,
sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb
from dba_tab_partitions a, dba_segments b
where
a.table_name = 'TESTCOMP' and
a.partition_name = b.partition_name and
a.table_name = b.segment_name
group by a.partition_name, a.tablespace_name, a.compression, a.compress_for
order by 1;
PARTITION_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR ROWS_PER_BLOCK NUM_ROWS BLOCKS MB
--------------- --------------- -------- -------------- -------------------- -------------------- -------------------- --------------------
P2011 TS_2011 ENABLED ADVANCED 376 10465280 27858 224
P2012 TS_2012 ENABLED ADVANCED 374 10493952 28081 224
P2013 TS_2013 ENABLED BASIC 416 10465280 25168 200
P2014 TS_2014 ENABLED ADVANCED 374 10465280 27983 224
P2015 TS_2015 DISABLED 180 10465280 58054 456
P2016 TS_2016 DISABLED 181 10493952 58054 456
P2017 TS_2017 DISABLED 180 10465280 58054 456
P2018 TS_2018 DISABLED 180 10465280 58054 456
P2019 TS_2019 DISABLED 161 1462272 9094 72

— Identify the partition Type

Checking for
*** P2011
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNQAACAAAOSSAAA COMP_ADVANCED
AAAbNQAACAAAOSSAAB COMP_ADVANCED
AAAbNQAACAAAOSSAAC COMP_ADVANCED

*** P2012
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNRAAIAAAOSSAAA COMP_ADVANCED
AAAbNRAAIAAAOSSAAB COMP_ADVANCED
AAAbNRAAIAAAOSSAAC COMP_ADVANCED

*** P2013
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNSAAJAAAOSSAAA COMP_BASIC
AAAbNSAAJAAAOSSAAB COMP_BASIC
AAAbNSAAJAAAOSSAAC COMP_BASIC
*** P2014
ROWID COMPRESSION_TYPE
—————— —————————
AAAbNTAAKAAAOSSAAA COMP_ADVANCED
AAAbNTAAKAAAOSSAAB COMP_ADVANCED
AAAbNTAAKAAAOSSAAC COMP_ADVANCED

SELECT rowid,
CASE DBMS_COMPRESSION.get_compression_type ('CMAGNO', 'TESTCOMP', rowid, 'P2014')
WHEN 1 THEN 'COMP_NOCOMPRESS'
WHEN 2 THEN 'COMP_ADVANCED'
WHEN 4 THEN 'COMP_QUERY_HIGH'
WHEN 8 THEN 'COMP_QUERY_LOW'
WHEN 16 THEN 'COMP_ARCHIVE_HIGH'
WHEN 32 THEN 'COMP_ARCHIVE_LOW'
WHEN 64 THEN 'COMP_BLOCK'
WHEN 128 THEN 'COMP_LOB_HIGH'
WHEN 256 THEN 'COMP_LOB_MEDIUM'
WHEN 512 THEN 'COMP_LOB_LOW'
WHEN 1024 THEN 'COMP_INDEX_ADVANCED_HIGH'
WHEN 2048 THEN 'COMP_INDEX_ADVANCED_LOW'
WHEN 1000 THEN 'COMP_RATIO_LOB_MINROWS'
WHEN 4096 THEN 'COMP_BASIC'
WHEN 5000 THEN 'COMP_RATIO_LOB_MAXROWS'
WHEN 8192 THEN 'COMP_INMEMORY_NOCOMPRESS'
WHEN 16384 THEN 'COMP_INMEMORY_DML'
WHEN 32768 THEN 'COMP_INMEMORY_QUERY_LOW'
WHEN 65536 THEN 'COMP_INMEMORY_QUERY_HIGH'
WHEN 32768 THEN 'COMP_INMEMORY_CAPACITY_LOW'
WHEN 65536 THEN 'COMP_INMEMORY_CAPACITY_HIGH'
END AS compression_type
FROM cmagno.TESTCOMP PARTITION (P2014)
WHERE rownum <= 3;

Conclusions:





Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

https://docs.oracle.com/en/engineered-systems/exadata-database-machine/sagug/exadata-storage-server-monitoring.html#GUID-56D5DEDC-6999-4BA5-A6B8-1816A8528405

select * from v$cell;
--------------------------------------------------------------------------------------------------------------------------------------
select * from V$BACKUP_DATAFILE;
--------------------------------------------------------------------------------------------------------------------------------------
SELECT name, value/1024/1024 MB FROM v$sysstat a WHERE
a.name = 'physical read total bytes' OR
a.name = 'physical write total bytes' OR
a.name = 'cell physical IO interconnect bytes' OR
a.name = 'cell physical IO bytes eligible for predicate offload' OR
a.name = 'cell physical IO bytes saved during optimized file creation' OR
a.name = 'cell physical IO bytes saved during optimized RMAN file restore'
OR
a.name = 'cell IO uncompressed bytes' OR
a.name = 'cell physical IO interconnect bytes returned by smart scan' OR
a.name = 'cell physical IO bytes saved by storage index';
--------------------------------------------------------------------------------------------------------------------------------------
--- This example shows a query that returns all objects that have 
--- performed more than 1000 optimized reads from a cell. 
--- A similar query can be used to determine which objects have performed 
-- very few optimized reads.

SELECT object_name, value FROM V$segment_statistics 
WHERE statistic_name='optimized physical reads' AND 
value>1000 ORDER BY value;

--------------------------------------------------------------------------------------------------------------------------------------
-- This example shows a query returning Oracle Exadata System Software offload processing performance data. 
-- A WHERE predicate was used to select only the V$SQL command of interest, which was the query that scanned the SALES table. 
-- The performance data stored in V$SQL for the query shows that of the approximately 5 GB of data in the SALES table, all of it was eligible for offload processing. 
-- Because of the effectiveness of Oracle Exadata System Software offload processing, only 417 MB of data was delivered over the network to the database host.

SELECT sql_text,
io_cell_offload_eligible_bytes/1024/1024 cell_offload_eligible_mb,
io_cell_uncompressed_bytes/1024/1024 io_uncompressed_mb,
io_interconnect_bytes/1024/1024 io_interconnect_mb,
io_cell_offload_returned_bytes/1024/1024 cell_return_bytes_mb,
(physical_read_bytes + physical_write_bytes)/1024/1024 io_disk_mb 
FROM v$sql WHERE
sql_text LIKE '%from sales%';

SQL_TEXT CELL_OFFLOAD_ELIGIBLE_MB IO_UNCOMPRESSED_MB IO_INTERCONNECT_MB CELL_RETURN_BYTES_MB IO_DISK_MB
---------------------------------------------------------------------------------------------------------------------------
select count(*) from sales 5283.06 5283.06 520.34 417.65 5385.75

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

SPOOL /tmp/cell_state_080619.log
SET PAGESIZE 10000
SET LONG 500000
SELECT statistics_type, XMLTYPE(statistics_value) FROM v$cell_state;
SPOOL OFF

--------------------------------------------------------------------------------------------------------------------------------------
PROMPT Querying V$CELL_THREAD_HISTORY ("ASH" for Storage Cells) ...

select count(*), cell_name, database_id, sql_id, wait_state
from V$CELL_THREAD_HISTORY
where sql_id is not null
group by cell_name, database_id, sql_id, wait_state
order by 1 desc;


SELECT * FROM (
SELECT
COUNT(*) seconds
, ROUND(COUNT(*) / LEAST((CAST(sysdate AS DATE)-CAST(sysdate-1 AS DATE))*86400, 600),1) avg_threads -- V$CELL_THREAD_HISTORY doesn't usually keep more than 10 minutes of history
, sql_id,job_type,wait_state,wait_object_name
, MIN(snapshot_time), MAX(snapshot_time)
FROM (
SELECT
substr(cell_name,1,20) cell_name 
, thread_id 
, job_type 
, wait_state 
, wait_object_name 
, sql_id 
, database_id 
, instance_id 
, session_id 
, session_serial_num 
, snapshot_time
FROM
v$cell_thread_history 
WHERE
snapshot_time BETWEEN sysdate-1 AND sysdate
AND sql_id='&SQL_ID'
AND wait_state NOT IN ( -- "idle" thread states
'waiting_for_SKGXP_receive'
, 'waiting_for_connect' 
, 'waiting_for_SKGXP_receive'
, 'looking_for_job' 
)
)
GROUP BY sql_id,job_type,wait_state,wait_object_name
ORDER BY COUNT(*) DESC
)
WHERE ROWNUM <= 20
/

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

select * from V$CELL_REQUEST_TOTALS;

---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
https://docs.oracle.com/en/engineered-systems/exadata-database-machine/sagug/exadata-storage-server-cellcli.html#GUID-6332ACC7-5183-4A62-9C96-C967221E4D0F

CELLCLI:

LIST CELL
LIST CELL ATTRIBUTES name, status, location, fanStatus, temperatureStatus, powerStatus
LIST CELL ATTRIBUTES emailFormat

LIST ACTIVEREQUEST
LIST ALERTDEFINITION
LIST ALERTHISTORY WHERE ageInMinutes < 15
LIST ALERTHISTORY 1671443714 DETAIL
LIST ALERTHISTORY WHERE begintime > 'Jan 1, 2019 00:00:00 AM CET'
LIST ALERTHISTORY WHERE endtime=null

LIST CELLDISK
LIST CELLDISK CD_01_cell05 ATTRIBUTES size
LIST CELLDISK WHERE status!=normal ATTRIBUTES name
LIST CELLDISK CD_01_cell05 DETAIL

LIST DATABASE
LIST DATABASE DETAIL
LIST DATABASE ATTRIBUTES NAME, PROFILE
LIST DATABASE ATTRIBUTES NAME, DATABASEID WHERE PROFILE = 'GOLD'

LIST DIAGPACK DETAIL

LIST DISKMAP

LIST FLASHCACHE
LIST FLASHCACHE DETAIL

LIST FLASHCACHECONTENT
LIST FLASHCACHECONTENT DETAIL

---> Example:
CREATE TABLE parttabl (c1 number) PARTITION BY RANGE(c1) 
(
PARTITION partt1 VALUES LESS THAN (100), 
PARTITION partt2 VALUES LESS THAN (200)
);

SQL> SELECT SUBSTR(OBJECT_NAME, 0 , 10) OBJ_NAME, SUBOBJECT_NAME, DATA_OBJECT_ID 
FROM user_objects WHERE OBJECT_NAME LIKE ('PARTT%');

OBJ_NAME SUBOBJECT_NAME DATA_OBJECT_ID
---------- ------------------------------ --------------
PARTTABL
PARTTABL PARTT1 63197
PARTTABL PARTT2 63198

CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber=63197 DETAIL
cachedKeepSize: 0
cachedSize: 24576
dbID: 3722937438
dbUniqueName: VIEW6
hitCount: 0
missCount: 4
objectNumber: 63197
tableSpaceNumber: 0


CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber=63198 DETAIL
cachedKeepSize: 0
cachedSize: 16384
dbID: 3722937438
dbUniqueName: VIEW6
hitCount: 0
missCount: 2
objectNumber: 63198
tableSpaceNumber: 0

LIST FLASHLOG
LIST FLASHLOG DETAIL

LIST GRIDDISK
LIST GRIDDISK DETAIL
LIST GRIDDISK ATTRIBUTES name, asmDeactivationOutcome
LIST GRIDDISK ATTRIBUTES name, asmModeStatus

---> Example:

ellCLI> LIST GRIDDISK WHERE cellDisk = 'CD_01_cell05' -
ATTRIBUTES name, status

DATA_CD_01_cell05 active
RECO_CD_01_cell05 active

CellCLI> LIST GRIDDISK DATA_CD_01_cell05 DETAIL

name: DATA_CD_01_cell05
status: active
comment: 
id: 00000117-84d9-0096-0000-000000000000
creationTime: 2009-01-16T17:04:49-06:00
cellDisk: CD_01_cell05
offset: 0
availableTo: 
size: 10G
errorCount: 0
diskType: HardDisk
cachedBy: FD_01_FLASH, FD02_FLASH, FD03_FLASH
cachingPolicy: default

CellCLI> LIST GRIDDISK DATA_CD_01_cell05 ATTRIBUTES size

136.640625G

CellCLI> LIST GRIDDISK WHERE status!=active ATTRIBUTES name

data_CD_01_1_abcd2x3

CellCLI> LIST GRIDDISK data4_CD_09_sgsata1 DETAIL

name: data4_CD_09_sgsata1
availableTo: 
cellDisk: CD_09_sgsata1
comment: 
creationTime: 2009-07-26T17:09:46-07:00
diskType: HardDisk
errorCount: 0
id: 00000122-b98a-a47a-0000-000000000000
offset: 27.546875G
size: 75G
status: active

LIST IBPORT
LIST IBPORT DETAIL

LIST IORMPLAN ATTRIBUTES status
LIST IORMPROFILE

LIST LUN
LIST LUN 0_0 DETAIL

LIST METRICCURRENT WHERE objectType = 'CELLDISK'
LIST METRICCURRENT attributes name, metricObjectName, alertState, metricValue ORDER BY metricValue desc, metricObjectName asc, name desc LIMIT 3

LIST metricDefinition WHERE objectType=cell
LIST metricDefinition WHERE name=cl_swap_in_by_sec DETAIL

LIST PHYSICALDISK
LIST PHYSICALDISK 20:0 DETAIL

LIST SOFTWAREHISTORY
LIST SOFTWAREHISTORY WHERE name='12.2.1.2.0.170808.1' DETAIL
LIST SOFTWAREUPDATE DETAIL

LIST USER DETAIL

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

DCLI:

https://docs.oracle.com/en/engineered-systems/exadata-database-machine/sagug/exadata-storage-server-dcli.html#GUID-0DDD16D9-937E-4891-B639-E7824E4F6131

Sintaxe:
./dcli -g <CELL FILE> "cellcli -e list griddisk"

Example:
./dcli -r '.*active' -g mycells "cellcli -e list griddisk"
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Carlos Magno – EzDBA by Carlos Magno - 6M ago

--- Using OCI and Database JSON
https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1
https://stackoverflow.com/questions/44149124/load-a-json-file-from-filestsystem-to-oracle-database
https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973

On this example i am going to show , based on the oci cli output, 
how to insert and query a JSON Data on database.

The file is based on REGIONS list from Oracle Cloud.

--- Including JSON into a table:

Json File: based on OCI CLI - Regions

C:\Users\candrade>oci iam region list --output table
+-----+----------------+
| key | name |
+-----+----------------+
| FRA | eu-frankfurt-1 |
| IAD | us-ashburn-1 |
| LHR | uk-london-1 |
| PHX | us-phoenix-1 |
+-----+----------------+

C:\Users\candrade>oci iam region list
{
"data": [
{
"key": "FRA",
"name": "eu-frankfurt-1"
},
{
"key": "IAD",
"name": "us-ashburn-1"
},
{
"key": "LHR",
"name": "uk-london-1"
},
{
"key": "PHX",
"name": "us-phoenix-1"
}
]
}

DROP TABLE json_regions PURGE;

CREATE TABLE json_regions (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);

insert into json_regions values (sys_guid(),
'
{
"data": [
{
"key": "FRA",
"name": "eu-frankfurt-1"
},
{
"key": "IAD",
"name": "us-ashburn-1"
},
{
"key": "LHR",
"name": "uk-london-1"
},
{
"key": "PHX",
"name": "us-phoenix-1"
}
]
}
');


SELECT jt.*
FROM json_regions,
JSON_TABLE(data, '$.data[*]'
COLUMNS (row_number FOR ORDINALITY,
key VARCHAR2(10) PATH '$.key',
name VARCHAR2(20) PATH '$.name'))
AS jt;

ROW_NUMBER KEY NAME
---------- ---------- --------------------
1 FRA eu-frankfurt-1
2 IAD us-ashburn-1
3 LHR uk-london-1
4 PHX us-phoenix-1

Execution Plan
----------------------------------------------------------
Plan hash value: 1080556958

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2008 | 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 2008 | 32 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | JSON_REGIONS | 1 | 2002 | 3 (0)| 00:00:01 |
| 3 | JSONTABLE EVALUATION | | | | | |
---------------------------------------------------------------------


---Loading from File:

SQL> create or replace directory json_dir as '/opt/oracle/scripts';

Directory created.

SQL> grant read, write on directory json_dir to public;

Grant succeeded.

-> file: json_regions.json

SELECT *
FROM JSON_TABLE(
BFILENAME('JSON_DIR','json_regions.json'),
'$'
COLUMNS(
NESTED PATH '$.data[*]'
columns (
row_number for ordinality,
key VARCHAR2(10) PATH '$.key',
name VARCHAR2(20) PATH '$.name'
)));

ROW_NUMBER KEY NAME
---------- ---------- --------------------
1 FRA eu-frankfurt-1
2 IAD us-ashburn-1
3 LHR uk-london-1
4 PHX us-phoenix-1

Execution Plan
----------------------------------------------------------
Plan hash value: 418783202

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 49008 | 29 (0)| 00:00:01 |
| 1 | JSONTABLE EVALUATION | | | | | |
------------------------------------------------------------


create table t1 as 
SELECT *
FROM JSON_TABLE(
BFILENAME('JSON_DIR','json_regions.json'),
'$'
COLUMNS(
NESTED PATH '$.data[*]'
columns (
row_number for ordinality,
key VARCHAR2(10) PATH '$.key',
name VARCHAR2(20) PATH '$.name'
)));

SQL> desc t1
Name Null? Type
----------------- -------- -------------
ROW_NUMBER NUMBER
KEY VARCHAR2(10)
NAME VARCHAR2(20)

SQL> select * from t1;

ROW_NUMBER KEY NAME
---------- ---------- --------------------
1 FRA eu-frankfurt-1
2 IAD us-ashburn-1
3 LHR uk-london-1
4 PHX us-phoenix-1

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hi All, this is the roadmap that just came out from Oracle Open World 2018, have a look into the Roadmap and stay tunned….

ATP_Overview_and_Roadmap_2018

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Steps to convert the Standard Database to Enterprise Edition… in a real world…

How to Convert Oracle Database 12c from Standard to Enterprise Edition ? (Doc ID 2046103.1)

1.) Take a FULL backup of the database, if possible OFFLINE;
2.) Install the new binary in the different ORACLE_HOME;
3.) Check the PATCHES, the new ORACLE_HOME should be at least on the same level of PATCHES. So , if needed apply the PSUs / One-OFFs …
4.) If you are using the GRID Infrastructure, the ORACLE_HOME must be changed with srvctl;
5.) create pfile from spfile in order to keep the current INIT of the database;
6.) Shut immediate the database;
7.) Copy the INIT created based on spfile to the NEW ORACLE HOME;
8.) Copy the Password File to the NEW ORACLE HOME;
9.) If needed do some changes into INIT file;
10.) Change the /etc/oratab to the new ORACLE_HOME
11.) . oraenv to the database, whith this all ENV variables will bet set accordingly;

12.) Check the current configuration;

[oracle@vddl-ocm-odg-01 dbs]$ srvctl config database -d db02
Database unique name: db02
Database name: db02
Oracle home: /opt/oracle/product/12.2.0.1/dbhome_2
Oracle user: oracle
Spfile: /opt/oracle/product/12.2.0.1/dbhome_2/dbs/spfiledb02.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:
OSDBA group:
OSOPER group:
Database instance: db02

13.) Create spfile from pfile, in order to create the new spfile on the new ORACLE_HOME;

sqlplus / as sysdba

create spfile from pfile;

14.) Change the HAS:
srvctl modify database -d db02 -oraclehome /opt/oracle/product/12.2.0.1/dbhome_1 -spfile /opt/oracle/product/12.2.0.1/dbhome_1/dbs/spfiledb02.ora

15.) Check: 
[oracle@vddl-ocm-odg-01 dbs]$ srvctl config database -d db02
Database unique name: db02
Database name: db02
Oracle home: /opt/oracle/product/12.2.0.1/dbhome_1
Oracle user: oracle
Spfile: /opt/oracle/product/12.2.0.1/dbhome_1/dbs/spfiledb02.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:
OSDBA group: dba
OSOPER group:
Database instance: db02

16.) Start the database:

srvctl start database -d db02

17.) Check HAS

[oracle@vddl-ocm-odg-01 dbs]$ crsctl stat res -t
---------------------------------------------------------------------------
Name Target State Server State details
---------------------------------------------------------------------------
Local Resources
---------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE vddl-ocm-odg-01 STABLE
ora.ons
OFFLINE OFFLINE vddl-ocm-odg-01 STABLE
---------------------------------------------------------------------------
Cluster Resources
---------------------------------------------------------------------------
ora.cssd
1 OFFLINE OFFLINE STABLE
ora.db02.db
1 ONLINE ONLINE vddl-ocm-odg-01 Open,HOME=/opt/oracl
e/product/12.2.0.1/d
bhome_1,STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE vddl-ocm-odg-01 STABLE
ora.orcl.db
1 ONLINE ONLINE vddl-ocm-odg-01 Open,HOME=/opt/oracl
e/product/12.2.0.1/d
bhome_1,STABLE
---------------------------------------------------------------------------

18.) Check DB

1* select * from v$version
SQL> /

BANNER CON_ID
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -64bit Production
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

19.) Check the components:

set lin 400
set pages 10000
col comp_name format a60
col status format a20

1* select comp_name, status, version, namespace, startup from dba_registry
SQL> /

COMP_NAME STATUS VERSION NAMESPACE STARTUP
-------------------------------------------------------------- --------
Oracle Database Catalog Views VALID 12.2.0.1.0 SERVER
Oracle Database Packages and Types VALID 12.2.0.1.0 SERVER
JServer JAVA Virtual Machine VALID 12.2.0.1.0 SERVER
Oracle XDK VALID 12.2.0.1.0 SERVER
Oracle Database Java Packages VALID 12.2.0.1.0 SERVER
OLAP Analytic Workspace OPTION OFF 12.2.0.1.0 SERVER
Oracle Real Application Clusters OPTION OFF 12.2.0.1.0 SERVER
Oracle XML Database VALID 12.2.0.1.0 SERVER
Oracle Workspace Manager VALID 12.2.0.1.0 SERVER
Oracle Text VALID 12.2.0.1.0 SERVER
Oracle Multimedia VALID 12.2.0.1.0 SERVER
Spatial OPTION OFF 12.2.0.1.0 SERVER
Oracle OLAP API OPTION OFF 12.2.0.1.0 SERVER
Oracle Label Security VALID 12.2.0.1.0 SERVER
Oracle Database Vault VALID 12.2.0.1.0 SERVER

20.) execute some tests to validate the database;

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

On the link below you will find the steps to apply patch over a DBSystem in Oracle Cloud. DBSystem is different comparing with just a Database in cloud. There you can find already runing the ASM Instance and CRS runing.

Enjoy….

PatchDBSystem

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

On this post i will show how to apply patch on Oracle Database inside of Oracle Cloud.

1.) Choose your database

2.) Checks the Avalilables Patches… On this case we have 1…

3.) Before apply the patch you should execute the PreCheck option, in order to see if there is some issue…

For the first time I faced this issue during the Pre-Check

Precheck failed due to incorrect Cloud tooling version and latest Cloud tooling update not available . “No applicable dbaastools rpms found” (Doc ID 2400143.1)

The Solution:

[opc@db02 ~]$ sudo su –
[root@db02 ~]#
[root@db02 ~]#

[root@db02 ~]# wget https://storage.us2.oraclecloud.com/v1/dbcsswlibp-usoracle29538/dbaas_patch/18.2.3.1.0/180516/dbaastools.rpm

–2018-06-22 15:01:04–  https://storage.us2.oraclecloud.com/v1/dbcsswlibp-usoracle29538/dbaas_patch/18.2.3.1.0/180516/dbaastools.rpm

Resolving storage.us2.oraclecloud.com… 129.152.172.3, 129.152.172.4

Connecting to storage.us2.oraclecloud.com|129.152.172.3|:443… connected.

HTTP request sent, awaiting response… 200 OK

Length: 138209324 (132M) [application/octet-stream]

Saving to: “dbaastools.rpm”

100%[=========================================================================================================================================================================================>] 138,209,324 27.8M/s   in 6.1s

2018-06-22 15:01:11 (21.5 MB/s) – “dbaastools.rpm” saved [138209324/138209324]

[root@db02 ~]# ls

dbaastools.rpm  keylist

[root@db02 ~]# rpm -qpi ./dbaastools.rpm

Name        : dbaastools                   Relocations: (not relocatable)
Version     : 1.0                               Vendor: Oracle
Release     : 1+18.2.3.1.0_180516.0000      Build Date: Mon 14 May 2018 02:00:58 PM CEST
Install Date: (not installed)               Build Host: adc01bqu.us.oracle.com
Group       : Applications/Administrative   Source RPM: dbaastools-1.0-1+18.2.3.1.0_180516.0000.src.rpm

Size        : 143835544                        License: Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.

Signature   : (none)
Packager    : mmoteka
URL         : http://dbdev.us.oracle.com/
Summary     : DBAAS Tools
Description :
DBAAS Tools for Oracle Public Cloud

[root@db02 ~]# rpm -qa|grep -i dbaastools

dbaastools-1.0-1+18.2.3.0.0_180413.0807.x86_64

[root@db02 ~]# rpm -ev dbaastools-1.0-1+18.2.3.0.0_180413.0807.x86_64

[root@db02 ~]# rpm -ivh ./dbaastools.rpm

Preparing…                ########################################### [100%]   1:dbaastools             ########################################### [100%]

[root@db02 ~]# rpm -qa|grep -i dbaastools

dbaastools-1.0-1+18.2.3.1.0_180516.0000.x86_64

[root@db02 ~]# /var/opt/oracle/ocde/assistants/bkup/bkup

Starting BKUP

Logfile is /var/opt/oracle/log/bkup/bkup_2018-06-22_15:03:31.log
Config file is /var/opt/oracle/ocde/assistants/bkup/bkup.cfg
dbname: DB02
Dataguard configuration
Enabled: 0
Mode: PRIMARY
INFO : No backup strategy enabled.
Looking for previous configuration:
Directory : /home/oracle/bkup/DB02
-> obkup
-> Found: 1 files
-> Moving previous configuration to /home/oracle/bkup/DB02_20180622150342
No initial bkup of PFILE needed.
Common RMAN Config
Instantiating obkup
Instantiating dbcfg.spec
Updating Control File Record Keep Time
Enabling block change tracking
Updating RMAN defaults
INFO: Archivelog management enabled.
Adding entry to crontab
Accessing to your Database ID ..
The DBaaS instance database id is: 1726838203
Deleting unencrypted autobackups.

#### Completed Execution.

[root@db02 ~]#

Execute Again the Pre-Check:

The current Patch Level: Jan 2018

Let’s Apply the Patch

Force apply patch Option:

If you want errors to be ignored during the patching operation, select the Force apply patch option. Then, click Patch.

If the Force apply patch option is selected, patch conflicts or errors discovered during the precheck stage of the patching operation are ignored and the patch will be applied (space permitting). If the option is not selected and conflicts or errors are discovered, the patch will not be applied.

https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/apply-patch.html

Follow the patching with ALERT.LOG

[oracle@db02 trace]$ pwd

/u01/app/oracle/diag/rdbms/db02/DB02/trace

[oracle@db02 trace]$ tail -1000f alert_DB02.log

Check also the log :

/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/ opatch2018-06-22_16-11-16PM_1.log

Checking again the status of the applied Patches….

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

On this post i will show the steps in order to create an Oracle Database on Oracle Cloud and connect…

1.) Connect o Oracle Cloud, on this case a create a Trial Login, you will receive an amount to try create a lot of stuffs… it’s nice to know better the platform…

It’s easy when you use the Guided Journey

You choose the Integrate, Store and Analyze Data option…

Here you can choose the best option… I choose the Custom in order to explore more the options…

Here i am no setting up the backup strategy, in another post i will explore more about that…

Check also here the Time Zone appropriate for your environment…

For the SSH Key, I created my own key RSA and this will be used in “SSH Public Key” Field. I am using MobaXterm over Windows.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/javaservice/JCS/JCS_SSH/create_sshkey.html

The summary for your database …

You should receive an email also regarding the Creation of the Database…

After creation let’s connect…

opc is the default user for Oracle Cloud…

Connection using sqlplus:

TNS String:
DBCLOUD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.75.175)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.svcsubnetad1.svcvcn.oraclevcn.com)
)
)

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Quarterly Full Stack Download Patch For
Oracle Exadata (Apr 2018 – 18.0.0.0)

 

 

Version

Software

Notes See Note 1270094.1 for additional fixes that
address critical issues.

18.1.5.0.0

Patch
27503194
Storage server and InfiniBand switch software (18.1.5.0.0.180506)

Patch
27561908
– x86-64 Database server bare metal / domU ULN
exadata_dbserver_18.1.5.0.0_x86_64_base OL6 channel ISO image
(18.1.5.0.0.180506)

Patch
27561909

x86-64 Database server dom0 ULN
exadata_dbserver_dom0_18.1.5.0.0_x86_64_base OVM3 channel ISO image
(18.1.5.0.0.180506) 

Recommended for all Database releases to get the latest
functionality, bug fixes, and security fixes

Supplemental README Note 2362411.1

Install proper ACFS drivers before updating to this
release. See Supplemental README.

 

Note:

The patchmgr used for
updating Exadata storage servers is not the same as the patchmgr used for
applying the Exadata database server software update
.

Summary of Steps:

1.)
Grid Infra – Patch Below

[24 de maio de 2018 17:37:46 UTC+2] Carlos Magno Andrade
Junior (cmagno.andrade@gmail.com): 2.) InfiniBand

[24 de maio de 2018 17:38:00 UTC+2] Carlos Magno Andrade
Junior (cmagno.andrade@gmail.com): 3.) Cells

[24 de maio de 2018 17:38:07 UTC+2] Carlos Magno Andrade Junior
(cmagno.andrade@gmail.com): 4.) Db Node (DOM0 and DOMU)

 

Pre-Requisite:

The Patch below should be applied in each CRS for each VM
before start the Patching Process:

Release Oracle Database 12.2.0.1.180417 ACFSApr2018RU

Patch
27463879
:
TRACKING BUG FOR RECOMPILING USM DRIVERS WITH LATEST GCC

Note: As pre-requisite for the patch above the Grid
Infrastructure must be Patched with: 27468969

So, those 2 Patches must be present in each VM.

Reference: Oracle Exadata
Database Machine Patch Availability Document for CVE-2017-5715, CVE-2017-5753,
and CVE-2017-5754 (Doc ID 2356385.1)

 

Hints:

While executing the
UPGRADE Oracle does not recommend to use ILOM Console, there are some known
issues regarding halting the session. For this case I suggest use the NOHUP in
order to be safe regarding Network issues.

 

In some customer you
can have the SCREEN tool, this is also recommended to use.

 

Example:

[root@exa0021-dom0 dbserver_patch_5.180508]# vi
upgradeDOM0_exa0020-dom0.sh

 

Copy the command below:

./patchmgr -dbnodes /EXAVMIMAGES/procedure/patch_18_1_5/dbserver_patch_5.180508/dbnodes
-upgrade -iso_repo /EXAVMIMAGES/procedure/patch_18_1_5/p27561909_181000_Linux-x86-64.zip
-target_version 18.1.5.0.0.180506

 

[root@exa0021-dom0 dbserver_patch_5.180508]# chmod +x
upgradeDOM0_exa0020-dom0.sh

[root@exa0021-dom0 dbserver_patch_5.180508]# nohup
./upgradeDOM0_exa0020-dom0.sh &

 

è
Patch
27503194
– Storage server and InfiniBand switch software (18.1.5.0.0.180506)

 

Note:
The
patchmgr utility must be launched as the root user from a
database server in the rack that has root user SSH
equivalence set up to the root user on all cells that you want to
update.

 

Note:
The Files that will be create in order to execute commands like DCLI, must uses
always (admin hostnames), example: cell_group;

è
Procedure Patch Directory: /EXAVMIMAGES/procedure/patch_18_1_5/,
all files should be inside of this path.

è
Patchmgr comes inside of this patch: (Patch 27503194Storage server
and InfiniBand switch
software (18.1.5.0.0.180506)
) and will be
used to patch SWITCHES and CELLS.

 

Task

Description

Patching InfiniBand Switches

Create ibswitches.lst

Log
in as the root user to a database server on
Oracle Exadata Database Machine that has root user SSH access to the switches. The database server must be on the same InfiniBand network as the switches.

 

Unzip
the Patch 27503194

Change
to the patch_release.date directory.

 

#
ENTER SWITCH INFO TO A FILE as ibswitches.lst ON THE FIRST DB NODE

[root@exadbadm01:~]$
cat ibswitches.lst

exasw-iba01

exasw-ibb01

 

Check Current Version

connect ib
switches with root user and run “version” command

Checking Subnet Manager

Connect in IB
Switches and execute :

getmaster -l

Checking SSH Connection

ssh <ib
switch> (Should not ask password)

 

If still asking
password follow the procedure below:

1.)
Check
if already exist: ~/.ssh/id_rsa.pub

2.)
If
YES, copy to IB Switch: ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host

3.)  Test the SSH connection: ssh
<ib switch> date, should not ask password;

 

If the rsa key
doesn’t exist, create a new one in order to copy:

 

Example:

 

jsmith@local-host$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/jsmith/.ssh/id_rsa):[Enter key]
Enter passphrase (empty for no passphrase): [Press enter key]
Enter same passphrase again: [Pess enter key]
Your identification has been saved in /home/jsmith/.ssh/id_rsa.
Your public key has been saved in /home/jsmith/.ssh/id_rsa.pub.
The key fingerprint is:
33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9 jsmith@local-host

  

Check Pre-Requisites

./patchmgr -ibswitches ibswitches.lst -upgrade -ibswitch_precheck

Apply Patch

./patchmgr -ibswitches ibswitches.lst -upgrade

Check Current Version

connect ib
switches with root user and run “version” command

Patching Storage Cells

EXACHK

Oracle
recommends running the Exachk utility before and after performing planned
maintenance. Running the utility allows you to review and cross reference
collected data against supported release levels and recommended Oracle
Exadata best practices. See My Oracle Support note 1070954.1, “Oracle Exadata
Database Machine Exachk or HealthCheck”
.

https://docs.oracle.com/cd/E75572_01/OEXUG/toc.htm

 

User Connection

Run
all steps as the root user.

cell_group

Prepare
a file named cell_group that has one cell host name or
IP address per line for each cell that you want to update.

root SSH
equivalence

Check for existing root SSH equivalence. The following
command should require no password prompts and no interaction. It should
return the list of host names in the cell_group file.

dcli -g cell_group
-l root ‘hostname -i’

 

SSH root equivalence if not already done

Set up SSH root equivalence if not already done so from the launch server(DN NODE
0). Do not do this step if you already have root SSH equivalence.

Generate root SSH keys as follows:

ssh-keygen -t rsa

Accept the defaults so the SSH keys are created for the
root user.

Push the SSH keys to set up SSH equivalence using the
following command. Enter the root
password when prompted.

dcli -g cell_group
-l root -k

 

note 2362411.1

Oracle
recommends reviewing My Oracle Support note 2362411.1, check the issues and
workarounds listed in the note just before applying the update.

performing a
non-rolling update

 

All Oracle
Clusterware components must be offline. If you are performing a non-rolling
update in a configuration running Oracle VM, then you must check the Oracle
Clusterware state in all Oracle VM clusters

 

STOP CRS in all
VMs:

crsctl stop cluster -all
crsctl stop crs 
Note: Here is possible use dcli also to execute the commands. 
But check if you ROOT profile Inside of the VM is set to CRS Environment Variables.
 Also you need that SSH Equivalence already OK for all VMs.
 dcli -g VMs.lst -l root "crsctl stop crs" 

Stop Cell Services

Shut down all cell
services on all cells to be updated. This may be done by the root user on each cell by running cellcli -e 'alter cell shutdown services all'
or by the following dcli
command to do all cells at the same time:
dcli -g cell_group -l root "cellcli -e alter cell shutdown services all"
dcli -g cell_group -l root "cellcli -e list cell detail"

 

Check the software
release and the installed Oracle Linux package

If the system meets the following conditions, then
follow the instructions in My Oracle Support note 1589868.1 before applying this update to
storage servers and database servers.

  • Oracle Exadata Database Machine hardware uses Sun
    servers.
  • The installed Oracle Exadata Storage Server
    Software release is earlier than release 11.2.2.2.0.
  • Installed Linux ofa package is earlier than 1.5.1-4.0.28.

If the system meets all of the preceding conditions,
then Exadata Storage Servers and database servers running Oracle Linux may
encounter a file system corruption that results in the root file system
mounted as read-only after reboot.

Applying
the Update to Exadata Cells

 

  • Do not use the serial console, or the ILOM
    web-based console to start the patchmgr utility.

There is a known issue of a
system halt on the serial console when a write is attempted to stderr or stdout. If an update is started from
the serial console, then it may halt.

You are using the serial console
if the output from the following command is serial.

echo $consoletype

  • Use the ILOM web-based console to monitor the cell
    during the update. The ILOM web-based console is the only way to
    interact with the cell if there is a problem after a failed firmware
    update or if there is a file system problem that has to be dealt with.
    The ILOM web-based console is a requirement during the update process.

To obtain ILOM and serial
console access for the cells, use SSH to the ILOM host name or IP address
as the
root user. Do the following to start the
serial console:

start /SP/console

To stop it press the Escape key
(ESC) followed by ( and then stop /SP/console.

  • Use a fresh log in session for each update or
    rollback procedure. Do not run the rollback procedure from the same
    login session where the update was applied. Do not re-run an update from
    a login session where the rollback procedure was run.
  • Do not interrupt the update process once it is
    initiated on a cell.
  • If you must use a cell as the patchmgr utility
    launch system, then do not use /opt/oracle as the staging area for the update. Using /opt/oracle as the staging area causes
    the update to fail and corrupt the cell. Use the /tmp directory as the staging
    area, that is, unzip the files for the update in /tmp.
  • Cells automatically reboot, as needed, during the
    update process. Do not reboot or power cycle cells while applying the
    update.
  • Do not edit any log file or open log files in writable
    mode. You may use any of the following to view a log: view, less, more or tail. You may cause the update
    process to be interrupted by editing the log files during the update.
  • At the end of the patchmgr session, the patchmgr.stdout log file is divided into
    individual cell log files with names in the format of cell_name.log. In addition, the /var/log/cellos content from the inactive
    cell partition is copied to the /var/log/cellos/inactive_partition directory. To locate the
    inactive partition, use the following command:

imageinfo -inactive -sys

 

Monitoring
Update Activity

 

Monitor update activity
using less
-rf patchmgr.stdout from another terminal session or window to see raw log details
from the patchmgr utility.

You may monitor
activity of the cell, by logging in to the serial console or web-based ILOM
console of individual cells being updated 5 minutes after the patchmgr
utility has started. Waiting 5 minutes allows the patchmgr utility time to
reset the ILOM on Sun hardware. Reset of the ILOM disconnects you from the
ILOM web console and serial console. You can reconnect once the ILOM has been
reset. By waiting 5 minutes, you avoid having to reconnect.

You lose the connection
during any ILOM update, and need to reconnect. The ILOM does not show any update
actions.

It is helpful to
monitor the activities of the normal cell boot, reboot, and other activities
to ensure that the process is proceeding correctly.

 

Using
the patchmgr Utility

 

1.)
Log in to a
system that has SSH equivalence set up for the root user to all cells that are to
be updated. ( DBNODE);

 

2.)
Unzip the
file p27503194_181000_Linux-x86-64.zip (If not Already done in “Patch IB
Switch”);

 

3.)
Change to
the patch_18.1.5.0.0.180506 directory

 

4.)
You use the
patchmgr update utility for updating Oracle Exadata storage servers. For
Exadata storage server updates the utility is packaged (and shipped) with the
update itself and is available for download from My Oracle Support as storage
server update.

https://docs.oracle.com/cd/E80920_01/DBMMN/updating-exadata-software.htm#DBMMN-GUID-B0A6A060-1C69-43D1-8780-B59B5E585C10

 

5.)  Patchmgr is inside:
../p27503194_181000_Linux-x86-64/patch_18.1.5.0.0.180506

patchmgr

 

6.)
Verify SSH
access to cells that was configured earlier with the following command:

dcli -g cell_group -l root 'hostname -i'

7.)
(Recommended)
Reset the patchmgr state to a known state using the following command:

./patchmgr -cells
cell_group -reset_force

 

Note:

This
step is only done the first time the cells are updated to this release. It
is not necessary to use the command for subsequent cell updates, even after
rolling back the update.

 

8.)
Clean up any
previous patchmgr utility runs using the following command:

 

./patchmgr -cells
cell_group -cleanup

 

Note: Always use the -cleanup option before retrying a failed or halted run of the
patchmgr utility.

 

9.)
Apply

 

Note: Recommended use NOHUP or
SCREEN Linux tool to execute, by this way avoiding any network issue.

 

./patchmgr
-cells cell_group -patch_check_prereq

./patchmgr
-cells cell_group -patch

 

Folow
the process, in another session:

[root@exadbadm01 patch_<your patch number>]$ tail -f patchmgr.stdout

Note:

The
ILOM restarts approximately 5 minutes after starting the patchmgr utility.
When cell update starts, a countdown runs on the session console where the
patchmgr utility was run from. Connections to monitor the console through
ILOM disconnect when ILOM restarts, and must be reconnected to continue to
monitor the console.

 

10.)
 Check image
status and history using the imageinfo and imagehistory commands on each cell:

 

[root@exadbadm01:~]$ dcli -g cell_group -l root 'imageinfo'
[root@exadbadm01:~]$ dcli -g cell_group -l root 'imagehistory'
[root@exadbadm01:~]$ dcli -g cell_group -l root 'cellcli -e list alerthistory'
[root@exadbadm01:~]$ dcli -g cell_group -l root "grep -i fail /var/log/cellos/validations.log"
[root@exadbadm01:~]$ ibhosts
 

Patching Database Nodes (DOM0 and DOMU)

patch
21634633

Before
starting go this note and download the suitable version of dbserver.patch.
Download dbserver.patch.zip as p21634633_12*_Linux-x86-64.zip, which contains
dbnodeupdate.zip and patchmgr for dbnodeupdate orchestration 

 

Patch
21634633: DBSERVER.PATCH.ZIP ORCHESTRATOR PLUS DBNU – ARU PLACEHOLDER

 

Last Updated

10-May-2018 17:58 (16 days ago)

Product

Oracle Exadata Storage Server Software

(More…)

Release

Oracle Exadata Storage Server 18.1.5.0.0

Platform

Linux x86-64

PATCHING DOM0

Unzip p21634633_181500_Linux-x86-64.zip

Unzip File: p21634633_181500_Linux-x86-64.zip,
inside all DOM-0s (/EXAVMIMAGES/procedure/patch_18_1_5) , in order to be used
as base for Patching Dom0 and DomU.

 

Sequence:

exa0020-dom0
Patches exa0021-dom0

exa0021-dom0
Patches exa0020-dom0

 

dbnodes file

è  exa0020-dom0:

/EXAVMIMAGES/procedure/patch_18_1_5/dbserver_patch_5.180508

 

[root@exa0020-dom0
dbserver_patch_5.180508]# cat dbnodes

exa0021-dom0

 

è  exa0021-dom0:

 

/EXAVMIMAGES/procedure/patch_18_1_5/dbserver_patch_5.180508

 

[root@exa0021-dom0
dbserver_patch_5.180508]# cat dbnodes

exa0020-dom0

 

SSH Equivalence

All DBnodes as
root must not ask for password during SSH connection between then

 

Check:

dcli -g dbnodes -l
root ‘hostname -i’

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview