oracle exadata tfactl commands

 

tfactl diagcollect -all -from "APR/30/2022 08:00:00" -to "APR/30/2022 14:00:00" 

tfactl status

tfactl status

tfactl print config

to set redaction to none:

change with the following on each node

# <TFA_HOME>/bin/tfactl set redact=none

and you can check with

# tfactl print config |grep redact

Oracle Materialized Views

 

Create MV:

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

CREATE MATERIALIZED VIEW <MV_NAME> REFRESH WITH ROWID AS SELECT * FROM <MV_NAME> where <CONDITION>;

CREATE MATERIALIZED VIEW <MV_NAME> REFRESH WITH ROWID AS SELECT * FROM <MV_NAME>;

TO SCHEDULE AND REFRESH mv DAILY:

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

EXEC DBMS_MVIEW.REFRESH('<MV_NAME>');

to Drop MV: 

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

DROP MATERIALIZED VIEW <MV_NAME>;

GRANT access to any user:

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

GRANT SELECT ON <MV_NAME> TO <USERNAME>;

oracle db links useful queries

 

sqlplus / as sysdba

set pagesize 300

set linesize 300

col OWNER format a20;

col DB_LINK format a20;

col USERNAME format a20;

col HOST format a20;

select * from dba_db_links;

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

DROP PUBLIC DATABASE LINK <DBLINKNAME>;

CREATE PUBLIC DATABASE LINK <DBLINKNAME> CONNECT TO <USERNAME> IDENTIFIED BY <PASSWORD> using '<DBNAME>';

IssueS and fix:

1) Create DB link 

Following Note id 2592265.1, Setup the DBLink From DBCS to Autonomous Database (Serverless)

2) Validate Created db link 

SQL> select * from dual@adbdblink;
select * from dual@adbdblink
*
ERROR at line 1:
ORA-12696: Double Encryption Turned On, login disallowed

 3) Add the following parameter in the sqlnet.ora file of the Source(OCI DB system) db server to allow Native+ SSL (authentication to work together)

cd $OACLE_HOME/network/admin
cp -p sqlnet.ora sqlnet.ora_bkp
-- Add below line

sqlnet.IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE

 4) Another validation failed with: 

SQL> select * from dual@ADBDBLINK;
select * from dual@ADBDBLINK
*
ERROR at line 1:
ORA-02085: database link ADBDBLINK.SUB<<>>.ORACLEVCN.COM
connects to <ADB PDBNAME>

 5) Update Global name in source database This is due to the ADB-S global_name has no domain

sqlplus '/as sysdba'
show parameter global_names

If GLOBAL_NAMES is set TRUE, the local domain is automatically assigned if  there is no domain in the DBLINK name.
So ORA-2085 will occur when accessing DBLINK.

**  For test validation, Update at session level and later at the system level.

alter session set global_names=FALSE;
SQL> select sysdate from dual@ADBDBLINK;

SYSDATE
---------
06-SEP-21

## Update at the system level :

alter system set global_names=FALSE;

SQL> select sysdate from dual@ADBDBLINK;

SYSDATE
---------
06-SEP-21

 

Goal

Db link from OCI DB system to ADB
 

Symptoms

 

 ORA-12696: Double Encryption Turned On, login disallowed
 ORA-02085: database link ADBDBLINK connects to ADB

to check temp tablespace growth and monitor

 

Useful queries to find temp tablespace usage and monitor:

SELECT * FROM dba_temp_free_space;

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

select a.tablespace_name tablespace,d.TEMP_TOTAL_MB,

sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,

d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB

from v$sort_segment a,

(

select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB

from v$tablespace b, v$tempfile c

where b.ts#= c.ts#

group by b.name, c.block_size

) d

where a.tablespace_name = d.name

group by a.tablespace_name, d.TEMP_TOTAL_MB;

Output:

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

select sum(maxbytes/1024/1024/1024) from dba_temp_files where tablespace_name='PSTEMP'

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

set pages 999

set lines 400

col FILE_NAME format a75

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB

from dba_temp_files d,

 v$tempfile v

where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

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