DBMS_REDACT

http://docs.oracle.com/database/121/ARPLS/d_redact.htm#ARPLS73800
Tested ON:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
1.) Create users:
create user red1 identified by red1;
 grant connect, resource , select any table to red1;
create user red2 identified by red2;
 grant connect, resource , select any table to red2;
grant unlimited tablespace to red1;
grant execute on sys.dbms_redact TO red1;
2.) Create table:
create sequence red1.seq_info_id minvalue 1 maxvalue 999999999999 nocache order increment by 1;
create table red1.info
 (info_id number,
 passport varchar2(10),
 dt_create date,
 account_no number)
 tablespace users;
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'AA0000', sysdate, '123456789');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'AA9999', sysdate, '123789456');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'BB0000', sysdate, '123456000');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'CC1234', sysdate, '123456888');
COMMIT;
SELECT * FROM red1.info;
 INFO_ID PASSPORT   DT_CREAT ACCOUNT_NO
 ---------- ---------- -------- ----------
 1 AA0000     29/02/16  123456789
 2 AA9999     29/02/16  123789456
 3 BB0000     29/02/16  123456000
 4 CC1234     29/02/16  123456888
3.) ADDING POLICY:
 conn red1/red1
Connected.
 RED1 @ > show user
 USER is "RED1"
BEGIN
 DBMS_REDACT.add_policy(
 object_schema => 'RED1',
 object_name   => 'INFO',
 column_name   => 'PASSPORT',
 policy_name   => 'REDACT_INFO',
 function_type => DBMS_REDACT.full,
 expression    => '1=1'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREAT ACCOUNT_NO
 ---------- ---------- -------- ----------
 1            29/02/16  123456789
 2            29/02/16  123789456
 3            29/02/16  123456000
 4            29/02/16  123456888
4.) Checking the POLICY (as sysdba) or need to GRANT SELECT to RED1.
select * from REDACTION_POLICIES
OBJECT_OWNER                   OBJECT_NAME                    POLICY_NAME                    EXPRESSION                               ENABLE  POLICY_DESCRIPTION
 ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ------- ------------------------------
 RED1                           INFO                           REDACT_INFO                    1=1                                      YES
5.) Changing the POLICY:
conn red1/red1
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.modify_column,
 column_name         => 'PASSPORT',
 function_type       => DBMS_REDACT.partial,
 function_parameters => 'VVVVVV,VVVVVV,#,1,4'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16  123456789
 2 ####99     29-FEB-16  123789456
 3 ####00     29-FEB-16  123456000
 4 ####34     29-FEB-16  123456888
6.) Adding Another Column
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.add_column,
 column_name         => 'ACCOUNT_NO',
 function_type       => DBMS_REDACT.partial,
 function_parameters => '0,1,9'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16          0
 2 ####99     29-FEB-16          0
 3 ####00     29-FEB-16          0
 4 ####34     29-FEB-16          0
7.) Filtering by CONTEXT - Column PASSPORT:
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.modify_expression,
 column_name         => 'PASSPORT',
 expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''RED1'''
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 AA0000     29-FEB-16  123456789
 2 AA9999     29-FEB-16  123789456
 3 BB0000     29-FEB-16  123456000
 4 CC1234     29-FEB-16  123456888
conn red2/red2
17:53:51 SQL> SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16          0
 2 ####99     29-FEB-16          0
 3 ####00     29-FEB-16          0
 4 ####34     29-FEB-16          0
8.) Droping POLICY:
conn red1/red1
BEGIN
 DBMS_REDACT.drop_policy (
 object_schema => 'RED1',
 object_name   => 'INFO',
 policy_name   => 'REDACT_INFO'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 AA0000     29-FEB-16  123456789
 2 AA9999     29-FEB-16  123789456
 3 BB0000     29-FEB-16  123456000
 4 CC1234     29-FEB-16  123456888

Creating several AWR Reports

Execute:
 ./mk_awr.sh 1 --> Number of days
Will generate files for AWRRPT:
 -rw-r--r-- 1 oracle oinstall 178628 Feb 25 16:17 AWR_clonetst_370_371.txt
 -rw-r--r-- 1 oracle oinstall 172747 Feb 25 16:17 AWR_clonetst_371_372.txt
 -rw-r--r-- 1 oracle oinstall 171340 Feb 25 16:17 AWR_clonetst_372_373.txt
 -rw-r--r-- 1 oracle oinstall 169183 Feb 25 16:17 AWR_clonetst_373_374.txt
 -rw-r--r-- 1 oracle oinstall 168289 Feb 25 16:17 AWR_clonetst_374_375.txt
Script Name: mk_awr.sh
 -------------------------------------------------------------
 snapINI=
 snapEND=
 nomeReport=
 reportMODE=text
>snaps.txt
# Number of Days to create
 Days=$1
sqlplus -s " / as sysdba" <<EOF
 set lin 100
 set pages 0
 set head off
 set term off
 set echo off
 set feed off
 col snap_id format 99999
 spool snaps.txt
SELECT snap_id  FROM DBA_HIST_SNAPSHOT WHERE
 TRUNC(BEGIN_INTERVAL_TIME) = TRUNC(SYSDATE)-$Days AND
 INSTANCE_NUMBER in (select instance_number from v\$instance)
 ORDER BY SNAP_ID;
spool off
 exit
 EOF
cat snaps.txt | awk '{print $1}' > snaps1.txt
 mv snaps1.txt snaps.txt
for x in `cat snaps.txt`
 do
snapINI=$x
 snapEND=`expr $x + 1`
 echo $snapINI
 echo $snapEND
 nomeReport='AWR_'$ORACLE_SID'_'$snapINI'_'$snapEND'.txt'
echo 'Creating...'$nomeReport
sqlplus " / as sysdba" @?/rdbms/admin/awrrpt.sql <<EOF
 $reportMODE
 $Days
 $snapINI
 $snapEND
 $nomeReport
 exit
 EOF
 done
-------------------------------------------------------------

Flashback Data Archive

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFFDCEH
1.) Grant
SQL> conn / as sysdba
 Connected.
 SQL>
 SQL>
 SQL> grant flashback archive administer to cmagno;
Grant succeeded.
SQL>
2.) tablespaces:
SQL> select tablespace_name, contents, status from dba_tablespaces;
TABLESPACE_NAME                CONTENTS  STATUS
 ------------------------------ --------- ---------
 SYSTEM                         PERMANENT ONLINE
 SYSAUX                         PERMANENT ONLINE
 UNDOTBS1                       UNDO      ONLINE
 TEMP                           TEMPORARY ONLINE
 USERS                          PERMANENT ONLINE
 EXAMPLE                        PERMANENT ONLINE
create tablespace ts_fda datafile 'D:\APP\CANDRADE\ORADATA\DB01\TS_FDA_01.DBF' size 10m autoextend on next 10m maxsize 8g;
3.) Flashback Data Archive
create flashback archive FDA_01 tablespace ts_fda retention 1 month;
1* SELECT * FROM DBA_FLASHBACK_ARCHIVE
 SQL> /
OWNER_NAME      FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                                                         LAST_PURGE_TIME                                                     STATUS
 --------------- ------------------------------ ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
 SYS             FDA_01                                          1                30 25-FEB-16 02.44.41.000000000 PM                                             25-FEB-16 02.44.41.000000000 PM
SQL> SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
 ------------------------------ ------------------ ------------------------------ ------------------------
 FDA_01                                          1 TS_FDA
4.) Table
create table cmagno.FDA_TESTE
 (num number,
 text varchar2(1000))
 tablespace users;
insert into cmagno.FDA_TESTE values (1,'TEST FDA 1');
 insert into cmagno.FDA_TESTE values (2,'TEST FDA 2');
 insert into cmagno.FDA_TESTE values (3,'TEST FDA 3');
 COMMIT;
5.) FDA to the table:
alter table cmagno.FDA_TESTE flashback archive FDA_01;
SQL> select * from dba_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME                                                                                                               OWNER_NAME      FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME                            STATUS
 -------------------------------------------------------------------------------------------------------------------------------- --------------- ------------------------------ ----------------------------------------------
 FDA_TESTE                                                                                                                CMAGNO          FDA_01                         SYS_FBA_HIST_93691                            ENABLED
1* select * from dba_FLASHBACK_ARCHIVE_TABLES
 SQL> /
TABLE_NAME           OWNER_NAME      FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME                            STATUS
 -------------------- --------------- ------------------------------ -----------------------------------------------------
 FDA_TESTE            CMAGNO          FDA_01                         SYS_FBA_HIST_93691                            ENABLED
6.) Test
SQL> select to_char(sysdate,'dd-mon-rr hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'D
 ------------------
 25-feb-16 15:09:26
delete cmagno.FDA_TESTE;
 commit;
SQL> select count(*) from cmagno.FDA_TESTE;
COUNT(*)
 ----------
 0
SQL> select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> show parameter undo
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -------------
 temp_undo_enabled                    boolean     FALSE
 undo_management                      string      AUTO
 undo_retention                       integer     900
 undo_tablespace                      string      UNDOTBS1
create undo tablespace undotbs2 datafile 'D:\APP\CANDRADE\ORADATA\DB01\UNDOTBS2_01.DBF' size 10m autoextend on next 10m maxsize 8g;
alter system set undo_tablespace=undotbs2 scope=both;
-- Just to show
SQL> shut immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.
Total System Global Area 5133828096 bytes
 Fixed Size                  3842712 bytes
 Variable Size            1107299688 bytes
 Database Buffers         4009754624 bytes
 Redo Buffers               12931072 bytes
 Database mounted.
 Database opened.
 SQL> show parameter undo
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- --------------------
 temp_undo_enabled                    boolean     FALSE
 undo_management                      string      AUTO
 undo_retention                       integer     900
 undo_tablespace                      string      UNDOTBS2
 SQL>
SQL> select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> truncate table cmagno.FDA_TESTE;
Table truncated.
SQL> select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> create view cmagno.vw_fda as select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
View created.
SQL> select * from cmagno.vw_fda;
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> select * from cmagno.FDA_TESTE;
no rows selected

VPD – Using Function

1.)
 create table cmagno.teste_vpd
 (num number,
 dt date,
 text varchar2(1000))
 tablespace users;
create sequence cmagno.seq_test 
minvalue 1 maxvalue 999999999999 
nocache order increment by 1;
2.)
declare
 i number;
 begin
for i in 1..1000 loop
 insert into cmagno.teste_vpd values
 (cmagno.seq_test.nextval, sysdate,'TEST :' || TO_CHAR(I));
 end loop;
 COMMIT;
 end;
 /
3.)
 SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 1000
1* select * from cmagno.teste_vpd where rownum<10
 SQL> /
NUM DT        TEXT
 ---------- --------- ----------------------------
 546 25-FEB-16 TEST :546
 547 25-FEB-16 TEST :547
 548 25-FEB-16 TEST :548
 549 25-FEB-16 TEST :549
 550 25-FEB-16 TEST :550
 551 25-FEB-16 TEST :551
 552 25-FEB-16 TEST :552
 553 25-FEB-16 TEST :553
 554 25-FEB-16 TEST :554
SQL>
 /
4.) Function
CREATE OR REPLACE FUNCTION CMAGNO.num_gt_500 
( v_schema IN VARCHAR2,  v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
 v_where VARCHAR2 (200);
BEGIN
 v_where := 'num>=500';
 RETURN (v_where);
 END num_gt_500;
 /
5.) Add Policy
 BEGIN
 DBMS_RLS.ADD_POLICY (
 object_schema     => 'CMAGNO',
 object_name       => 'TESTE_VPD',
 policy_name       => 'NUM_GT_500_PLCY',
 policy_function   => 'NUM_GT_500',
 sec_relevant_cols => 'NUM');
 END;
 /
6.) Just rows with NUM >= 500
SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 501
7.) Droping Policy:
 BEGIN
 DBMS_RLS.DROP_POLICY (object_schema     => 'CMAGNO',
 object_name       => 'TESTE_VPD',
 policy_name       => 'NUM_GT_500_PLCY');
 END;
 /
8.)
 SQL> SELECT COUNT(*) FROM cmagno.teste_vpd;
COUNT(*)
 ----------
 1000

ODA – Reconfig SSH user equivalence

In case of problem with SSH User Equivalence, there are some steps to rebuild the equivalence for Oracle, Grid and ROOT, bellow the steps :
Obs. : -> Mandatory reset default password to WELCOME1
  • Login as ROOT on Node 1;
  • Passwd root, change password to welcome1;
  • Passwd oracle, change password to welcome1;
  • Passwd grid, chance password to welcome1;
  • Login as ROOT on Node 2;
  • Passwd root, change password to welcome1;
  • Passwd oracle, change password to welcome1;
  • Passwd grid, chance password to welcome1;
  • On Node 1, execute
    1. cd /opt/oracle/oak/onecmd
    2. ./gridinst.pl -o -s  3 è for root
    3. ./gridinst.pl -o -s  12 è for oracle and grid
  • login as Oracle ;
  • execute a test with ssh between the nodes ;
    1. from Node 1 :
       ssh <node 2> date, the password prompt should not appears
    2. from Node 2 :
       ssh <node 1> date, the password prompt should not appears
  • login as GRID ;
  • execute a test with ssh between the nodes ;
    1. from Node 1 :
       ssh <node 2> date, the password prompt should not appears
    2. from Node 2 :
       ssh <node 1> date, the password prompt should not appears
  • login as ROOT ;
  • execute a test with ssh between the nodes ;
    1. from Node 1 :
       ssh <node 2> date, the password prompt should not appears
    2. from Node 2 :
       ssh <node 1> date, the password prompt should not appears

ODA – Change ROOT password ILOM

The procedure bellow describes how to change the password for Oracle and GRID users on ODA (Oracle Database Appliance).
  • Login as ROOT on ILOM;
  • -> set /SP/users/root password=<PASSWORD>
     set /SP/users/root password=<PASSWORD>
     Changing password for user /SP/users/root...
     Enter new password again: <PASSWORD>
     ********
     New password was successfully set for user /SP/users/root

Obs.: If any problem to connect directly to the ILOM, can connect on ODA_BASE and use the command bellow:
# ipmitool sunoem cli
 Connected. Use ^D to exit.

ODA – Change ROOT Password

The procedure bellow describes how to change the password for ROOT user on ODA (Oracle Database Appliance).
  • On BOTH NODES login as ROOT ;
    1. create a copy of the directory $HOME/.ssh;
  • execute a test with ssh between the nodes ;
  1. from Node 1 :
     ssh <node 2> date, the password prompt should not appears
  2. from Node 2 :
     ssh <node 1> date, the password prompt should not appears
  • passwd root, change password for root user for BOTH NODES ;
  • execute a test with ssh between the nodes ;
    1. from Node 1 :
       ssh <node 2> date, the password prompt should not appears
    2. from Node 2 :
       ssh <node 1> date, the password prompt should not appears