Quest_in_OracleDBA: 2015

Thursday, November 19, 2015

RMAN backup skip tablespace using EXCLUDE feature

To Skip the tablespace during backup database

export ORACLE_SID=PROD
sqlplus / as sysdba

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
———- —————————— — — — —
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
3 TEMP NO NO YES
4 USERS YES NO YES
5 PROD_DATA YES NO YES
6 PROD_INDX YES NO YES

10 rows selected.

sql exit

rman target /

rman>show exclude;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD are:
RMAN configuration has no stored or default parameters

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_DATA;

Tablespace PROD_DATA will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_INDX;
Tablespace PROD_INDX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_DATA’;
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_INDX’;

RMAN> BACKUP DATABASE;

Starting backup at 08-JUNE-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
file 5 is excluded from whole database backup
file 6 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/SYSTEM01.DBF
…….

We can override the exclude feature explicitly during database backup.

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_DATA’;
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_INDX’;

RMAN> BACKUP DATABASE NOEXCLUDE;

To Clear the EXCLUDE feature:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_DATA CLEAR;
Tablespace PROD_DATA will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_INDX CLEAR;
Tablespace PROD_INDX will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> show EXCLUDE;
RMAN configuration parameters for database with db_unique_name PROD are:
RMAN configuration has no stored or default parameters
Note:- This will only work while taking backups with RMAN. While cloning or while duplicating we can use :skip tablespace tba_name" to skip particular database.

Skipping Tablespaces in RMAN cloning

Skipping Tablespaces While Cloning RMAN Database

The example will show how to skip Read Write, Read Only and Offline tablespaces. Then how to clone the database to a past point in time will be covered.

Perform all above steps up to Step 5 and bring the auxiliary database to the nomount mode. Then create three tablespaces in the production database:

tbs_skip: This is a Read Write tablespace. This tablespace will be skipped during the clone process.
tbs_readonly: This is a Read Only tablespace and will be skipped automatically by the skip readonly command during the clone process.
tbs_offline: This is an offline tablespace and will be skipped automatically by RMAN during the clone process.

Then create a table and use it to show the result of the past point in time recovery. Look at this example in more details. Create three tablespaces:

sys@TEST>
create
tablespace tbs_skip datafile '/u02/oradata/test/tbs_skip.dbf' SIZE 1M;
Tablespace created.
sys@TEST>
create
tablespace tbs_readonly datafile '/u02/oradata/test/tbs_readonly.dbf'
SIZE 1M;
Tablespace created.
sys@TEST>
create
tablespace tbs_offline datafile '/u02/oradata/ /test/tbs_offline.dbf'
SIZE 1M;
Tablespace created.
sys@TEST>

Create two tables: tbs_test and tbs_readonly. The first table will be used in our past point in time recovery scenario. The second table will be used when the Read Only tablespace is recovered after the clone process. Create the tbs_readonly table and insert one row in it:

sys@TEST>
create
table tbl_test (id number);
Table created.
sys@TEST>
create
table tbl_readonly (col1 varchar2(15)) tablespace tbs_readonly;
Table created.
sys@TEST>
insert into
tbl_readonly values('Readonly table');
1 row created.
sys@TEST>
commit;
Commit complete;

Change the status of the tablespace tbs_readonly to Read Only, tbs_offline to Offline:

sys@TEST>
alter
Tablespace tbs_readonly Read Only;
Tablespace altered.
sys@TEST>
alter
tablespace tbs_offline Offline Normal;
Tablespace altered.
sys@TEST>

Connect to RMAN and take a full backup:

RMAN> backup database plus archivelog delete input;
Starting backup at 01-OCT-09
....................
....................
Finished backup at 01-OCT-09

Make changes to the tbl_test table and take a backup of the database and archivelogs. Insert a row to the tbl_test table and commit it. Then take the current scn value of the database that will be used during recovery of said database. A clone will be made of the database to this scn value.

Next, insert one more row since it will be assumed that this row is a mistake and will not be recovered during the clone process. Then switch the redo log file and take a backup of the archived redo log files:

sys@TEST>
insert into
tbl_test values(1);
1 row created.
sys@TEST>
commit;
Commit complete.
sys@TEST>
select * from
tbl_test;

ID
----------
1
sys@TEST>
select
current_scn
from
v$database;
current_scn
-----------
471662
sys@TEST>
insert into
tbl_test values(100000);
1 row created.
sys@TEST>
commit;
Commit complete.
sys@TEST>
select * from
tbl_test;

ID
----------
1
100000

sys@TEST>
alter
system switch logfile;
System altered.
sys@TEST>
exit
$ rman target /
RMAN> backup archivelog all delete input;
Starting backup at 01-OCT-09
....................
....................
Finished backup at 01-OCT-09
RMAN>

The database is ready to be cloned. To clone the database, connect to both databases and run the following script:

$ rman target sys/test auxiliary sys/test@clone_db
connected to target database: test (DBID=782965739)
connected to auxiliary database: clone_db (not mounted)
RMAN> run {
set newname for tempfile 1 to '/u03/oracle/new_clone/temp01.dbf';
set newname for datafile 1 to '/u03/oracle/new_clone/system01.dbf';
set newname for datafile 2 to '/u03/oracle/new_clone/undotbs01.dbf';
set newname for datafile 3 to '/u03/oracle/new_clone/sysaux01.dbf';
set newname for datafile 4 to '/u03/oracle/new_clone/users01.dbf';
set newname for datafile 5 to '/u03/oracle/new_clone/skip.dbf';
set newname for datafile 6 to '/u03/oracle/new_clone/readonly.dbf';
set newname for datafile 7 to '/u03/oracle/new_clone/offline.dbf';
duplicate target database to clone_db
skip tablespace tbs_skip
skip readonly
until scn 471662
logfile
'/u03/oracle/new_clone/redo log01.log' SIZE 5M,
'/u03/oracle/new_clone/redo log02.log' SIZE 5M,
'/u03/oracle/new_clone/redo log03.log' SIZE 5M;
}

RMAN starts to clone and recovers the database until the specified scn value by skipping the tbl_skip tablespace, Read Only and Offline tablespaces. With the SKIP TABLESPACE clause, the tablespaces can be excluded from the duplicate database, but not the system tablespace or undo tablespace which contains rollback or undo segments. Below is the part of the result of the script:

contents of Memory Script:
{
set until scn 471662;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: set until clause
Starting recover at 01-OCT-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=disk
datafile 5 not processed because file is offline
datafile 6 not processed because file is read-only
datafile 7 not processed because file is offline
starting media recovery
.................
.................
Finished recover at 01-OCT-09
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
................
................
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace tbs_skip including contents cascade
constraints";
# drop offline and skipped tablespaces
sql clone "drop tablespace tbs_offline including contents cascade
constraints";
}
executing Memory Script

sql statement: drop tablespace tbs_skip including contents cascade
constraints

sql statement: drop tablespace tbs_offline including contents cascade
constraints
Finished Duplicate Db at 01-OCT-09

RMAN>

As can be seen, the database has been recovered to scn 471662 and all tablespaces have been skipped.

datafile 5 not processed because file is offline
datafile 6 not processed because file is read-only
datafile 7 not processed because file is offline

Datafile 5 was manually skipped because the skip tablespace tbs_skip command was used. Datafile 6 was skipped because it was a Read Only tablespace and all Read Only tablespaces were skipped by the skip readonly command. Datafile 7 was skipped automatically by RMAN because it is an offline tablespace. Then the database was opened with the resetlogs optionand the tbs_skip and tbs_offline tablespaces dropped by RMAN.

Now, query the tbl_test table:

sys@clone_db>
select * from
tbl_test;

ID
----------
1

The last time when this table was queried, there were two lines: however, as the database was recovered until a specific scn value, at that scn there was only one row.

Now, query the tbl_readonly table:

sys@clone_db>
select * from
tbl_readonly;
select * from
tbl_readonly
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6:
'/u01/oracle/product/10.2.0/db_1/dbs/MISSING00006'

This shows that since the tbs_readonly tablespace was skipped with the skip readonly command, the table which resides on Datafile 6 cannot be queried. It was dropped after the database had been cloned and opened. Check the status of the datafile in the v$datafile view:

sys@clone_db>
set
linesize 1000
sys@clone_db>
select
status, enabled, name
from
v$datafile;

STATUS ENABLED NAME
------- ---------- -------------------------------------
SYSTEM READ WRITE /u03/oracle/new_clone/system01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/undotbs01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/sysaux01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/users01.dbf
OFFLINE READ ONLY /u01/oracle/product/10.2.0/db_1/dbs/MISSING00006

To recover this tablespace, copy it from the production databaseto the location where the auxiliary database's files are located and recover them by performing the following steps:

Copy it from the production database's file location to the auxiliary database's location:

sys@clone_db>host

$ cp /u01/oracle/test/tbs_readonly.dbf /u03/oracle/new_clone/

Note: Now is the time to use a cp copy of the datafile without taking a fresh backup because the tablespace is Read Only.

Then rename it and change the status to online in the clone_db database:

sys@clone_db>
alter tablespace
tbs_readonly rename datafile
'/u01/oracle/product/10.2.0/db_1/dbs/MISSING00006' TO
'/u03/oracle/new_clone/tbs_readonly.dbf';
Tablespace altered.
sys@clone_db>
alter tablespace
tbs_readonly online;
Tablespace altered.

Now view the status and datafile name of that file from the v$datafile view:

sys@clone_db>
select
status, enabled, name
from
v$datafile;

STATUS ENABLED NAME
------- ---------- ---------------------------------
SYSTEM READ WRITE /u03/oracle/new_clone/system01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/undotbs01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/sysaux01.dbf
ONLINE READ WRITE /u03/oracle/new_clone/users01.dbf
ONLINE READ ONLY /u03/oracle/new_clone/tbs_readonly.dbf

Now the tbl_readonly table can be queried:

sys@clone_db>
select * from
tbl_readonly;
COL1
---------------
Readonly table
sys@clone_db>

Saturday, November 7, 2015

Setting up Network ACLs in Oracle 11gr SMTP server

From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Fine-Grained Access to these packages by creating an Access Control List to use any external network resource through these packages. Before this any user who had an execute privilege on these packages was able to do anything to any network resource like web and local mail servers etc. But now a user needs a little more then just an execute privilege on the network packages.

Lets talk about using ACL in oracle 11g:

$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and   grantee = 'PUBLIC';

GRANTEE    TABLE_NAME     PRIVILEGE
---------- -------------- -------------
PUBLIC     UTL_HTTP       EXECUTE

-- By default access on UTL_HTTP is granted to PUBLIC.
-- Revoke from public and grant to specific user who needs it.

revoke execute on utl_http from public;
grant execute on utl_http to scott;

select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and   grantee in ('PUBLIC','SCOTT')

GRANTEE    TABLE_NAME     PRIVILEGE
---------- -------------- -------------
SCOTT      UTL_HTTP       EXECUTE

-- Now only SCOTT has execute rights on UTL_HTTP.

SQL> conn scott/tiger
Connected.

create or replace procedure getTitle(pUrl VARCHAR2)
is
  vResult CLOB;
begin
  vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
  vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
  dbms_output.put_line(vResult);
end;
/

SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
BEGIN getTitle('http://www.oracleflash.com'); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCOTT.GETTITLE", line 5
ORA-06512: at line 1

As you may see that even in the presence of EXECUTE privilege, SCOTT is not able to access the web page using UTL_HTTP and has encountered an error "network access denied by access control list (ACL)". This literally means that the user is being denied access by the Access Control List.


How to configure Access Control List



We need to configure an Access Control List (ACL) and grant "connect" privilege on that ACL to user SCOTT. Then we need to assign host "www.oracleflash.com" to this ACL and any other host to which user SCOTT needs access.



DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()
 creates a new Access Control List. Following are the parameters that it takes.




acl
 => Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.

Description
 => Description of the ACL. 

Principal
 => Name of the user or role (case sensitive) to whom the permissions are being granted or denied.

is_grant
 => TRUE or FALSE, whether to grant access or deny access.

privilege
 => connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.

start_date
 => Start date (optional) of the access to the user.

end_date
 => End date (optional) of the access to the user.


SQL> conn / as sysdba
Connected.
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'oracleflash.xml',
    description  => 'Permissions to access http://www.oracleflash.com',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


Add a privilege to Access Control List



First access to the ACL to any user is granted when the ACL is created with the CREATE_ACL procedure. If any other user or role needs permission on the ACL you may user the procedure ADD_PRIVILEGE.



DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE()
 Add access for more users or roles in an already existing ACL. It takes similar parameters as CREATE_ACL procedure except there is no description parameter and a new parameter position which is used in ADD_PRIVILEGE but not in CREATE_ACL.


The position parameter decides the precedence of the rights for multiple users. For example we grant access to a role ORACLEFLASH at position 1, grant this role to user HR and deny access to user HR at position 2 in ACL. The user HR will still be able to use the network resource because he is granted access via role ORACLEFLASH which takes precedence in the ACL. When granting access to multiple roles and user set the precedence appropriately.

create role oracleflash;

-- A role is created. Now we grant connect to this role on our ACL.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'oracleflash.xml',                
    principal    => 'ORACLEFLASH',
    is_grant     => TRUE, 
    privilege    => 'connect',
    position     => null);
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


So far we have created an ACL and have granted connect access to user SCOTT and role ORACLEFLASH on this ACL. Now is the time to assign network hosts that this ACL can be used to access. In our case the host is "www.oracleflash.com".



Assign a network host to Access Control List





DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()
 assigns a network host local or remote to an ACL. It takes the following parameters:




acl
 => Name of the Access Control List.

host
 => Name of the host. 

lower_port
 => Lower port (optional) from the range of ports allowed on this host.

upper_port
 => Upper port (optional) from the range of ports allowed on this host



Default for lower and upper port is null, which means all ports can be used on this host. And if you provide a port in lower_port and null in upper_port oracle assumes the upper_port=lower_port.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'oracleflash.xml',                
    host         => '*.oracleflash.com');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'oracleflash.xml',                
    host         => '*.oracle.com');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


NOTE: you may assign multiple hosts to one ACL, but you can't assign one host to multiple ACLs. If you do that then the previous assignment will be removed and new will become in effect.



Host Assignments:

Host assignment can be done in many ways. For example if you assign a host to an ACL like www.oracleflash.com, the users can only access www.oracleflash.com. But if you assign a host like *.oracleflash.com, the users can assign any sub-domain on the oracleflash.com. And *.com will grant access to the whole web using .com domains. You need to be careful with this as you may be granting access to more servers then you should.



DBMS_NETWORK_ACL_UTILITY.DOMAINS()
 package contains functions to help determine possible matching domains. The DOMAINS table function returns all possible references against a host, that may be specified in ASSIGN_ACL procedure, in order of precedence.


SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.oracleflash.com'));

COLUMN_VALUE
------------------------------------
www.oracleflash.com
*.oracleflash.com
*.com
*

SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('192.168.0.132'));

COLUMN_VALUE
------------------------------------
192.168.0.132
192.168.0.*
192.168.*
192.*
*


The precedence here means that if you have assigned all these hosts to the ACLs then which host entry will take precedence on others. The above query returns results in order of precedence.

We can verify the ACL's host assignment and privileges via two dictionary views, DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES.

column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com
/sys/acls/oracleflash.xml      *.oracle.com

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true
/sys/acls/oracleflash.xml      ORACLEFLASH          connect    true


Lets now see if the access is enabled or not.

SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
OracleFlash.com: Oracle Articles, Tutorials, Step by Step Install Guides, Scripts.

PL/SQL procedure successfully completed.

SQL> execute getTitle('http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm');
Configuring Privilege and Role Authorization

PL/SQL procedure successfully completed.


The user SCOTT is able to access both the oracleflash.com and oracle.com hosts. Lets see how the ACL grant to a role works.

SQL> conn / as sysdba
Connected.
SQL> grant execute on utl_http to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual;
select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual
              *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1


Even after granting the EXECUTE privilege on UTL_HTTP to user HR, it is not able to access the host www.oracleflash.com. This is because the user HR has no access on the ACL we created for oracleflash.com. Now we will grant the role ORACLEFLASH to user HR, which has access on the ACL for oracleflash.com and see what happens.

SQL> conn / as sysdba
Connected.
SQL> grant oracleflash to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,112) oracleflash
  2  from dual;

ORACLEFLASH
-----------------------------------------------------------------------------------------





This time the HR can access the web page as it has the role ORACLEFLASH, which has access on the ACL.



Cleaning Up the Access Control List



Remove a host from Access Control List

Following procedure can be used to remove a host from the ACL.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com
/sys/acls/oracleflash.xml      *.oracle.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'oracleflash.xml',
    host        => '*.oracle.com'); 
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com




Delete a privilege from Access Control List

Following procedure can be used to delete a privilege from the ACL.

SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true
/sys/acls/oracleflash.xml      ORACLEFLASH          connect    true

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege ( 
    acl         => 'oracleflash.xml', 
    principal   => 'ORACLEFLASH',
    is_grant    => TRUE, 
    privilege   => 'connect');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true




Drop an Access Control List

Following procedure can be used to drop the ACL.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL ( 
    acl         => 'oracleflash.xml');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

no rows selected


See also Managing Fine-Grained Access to External Network Services ORA-24248 XMLDB extensible security not installed

Creating ACL for SMTP server:



BEGIN

   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (

 acl        => 'mailserver_acl.xml',

 host       => 'CAINCCHX01.EG01.yahoo.net',

 lower_port => 25,

 upper_port => 25);

 END;

 /

   COMMIT;




   BEGIN

   DBMS_NETWORK_ACL_ADMIN.drop_acl('mailserver_acl.xml');

   END;

   /

   commit;



   BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl(

    acl           => 'mailserver_acl.xml',

    description   => 'ACL that lets me talk to the my email server',

    principal     => 'PPM_USER',

    is_grant      => TRUE,

    privilege     => 'connect'

    );

/



    DBMS_NETWORK_ACL_ADMIN.assign_acl(

    acl          => 'mailserver_acl.xml',

    HOST         => 'CAINCCHX01.EG01.Yahoo.net',

    lower_port   => 25,

    upper_port   => 25

    );



    COMMIT;

END;

/

References:
http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html
http://oracledbajourney.blogspot.com/2012/09/ora-24247-network-access-denied-by.html

Friday, October 23, 2015

Scripts to check Rollback Segments information

Rollback segment Information

SELECT segment_name, tablespace_name, status

FROM sys.dba_rollback_segs;

SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents

FROM sys.dba_segments

WHERE segment_type = 'ROLLBACK';

SELECT name, xacts "ACTIVE TRANSACTIONS" FROM v$rollname, v$rollstat WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;

SELECT segment_name, tablespace_name, owner

FROM sys.dba_rollback_segs;

SELECT segment_name, segment_type, tablespace_name

FROM sys.dba_segments

WHERE segment_type = 'DEFERRED ROLLBACK';

Shrinking Rollback segment command

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

select count(*) from dba_extents where tablespace_name='RBSTS';

Shrinking all rollback Segments

spool shrink_em.sql

select 'alter rollback segment '||segment_name||' shrink to 2;' from dba_rollback_segs where tablespace_name='RBSTS';

spool off

@shrink_em.sql

Number of rollback extents

select count(*) from dba_extents where tablespace_name='RBSTS';

Finding Rollback Segment Size

SQL> select segment_name,sum(bytes) from dba_segments where

> tablespace_name

> = 'RBS' and segment_name

> = 'RBS17' group by segment_name;

> SEGMENT_NAME SUM(BYTES)

> ------------------------ ----------

> RBS17 22364160

Finding Rollback Segment Optimal Size

> SQL> select rs.optsize, rs.extents

> 2 from dba_rollback_segs drs,

> 3 v$rollstat rs

> 4 where drs.segment_name = 'RBS17'

> 5 and drs.segment_id = rs.usn;

> OPTSIZE EXTENTS

> ---------- ----------

> 22020096 21

Shrinking Rollback Segment

> SQL> alter rollback segment RBS17 shrink to 10M;

> Rollback segment altered.

Script to shrink all rollback Segments

-- Script: shrink_rollback_segs.sql

-- Purpose: to shrink all online rollback segments back to optimal

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

@save_sqlplus_settings

set pagesize 0

set termout off

spool shrink_rollback_segs.tmp

select

'alter rollback segment ' || segment_name || ' shrink;'

from

sys.dba_rollback_segs

where

status = 'ONLINE'

spool off

@shrink_rollback_segs.tmp

host rm -f shrink_rollback_segs.tmp -- for Unix

host del shrink_rollback_segs.tmp -- for others

Finding Current Optimal and Suggested Optimal

column name format a30 heading "Rollback Segment"

column optsize format 99999999999 heading "Current Optimal"

column new_opt format 99999999999 heading "Suggested Optimal"

select

n.name,

s.optsize,

( ceil(s.extents * (s.optsize + s.aveshrink)/(s.rssize + p.value))

* (s.rssize + p.value)

/ s.extents

) - p.value new_opt

from

( select

optsize,

avg(rssize) rssize,

avg(extents) extents,

max(wraps) wraps,

max(shrinks) shrinks,

avg(aveshrink) aveshrink

from

sys.v_$rollstat

where

optsize is not null and

status = 'ONLINE'

group by

optsize

) s,

( select

kvisval value

from

sys.x_$kvis

where

kvistag = 'kcbbkl' ) p,

sys.v_$rollstat r,

sys.v_$rollname n

where

s.shrinks > 1 and

s.shrinks > s.wraps / ceil(s.optsize / ((s.rssize + p.value) / s.extents)) and

r.optsize = s.optsize and

r.status = 'ONLINE' and

n.usn = r.usn

Generating Shrink commands (Examples)

select b.segment_name,b.tablespace_name,a.extents,a.rssize,a.xacts,a.optsize,a.shrinks,a.wraps,a.status from v$rollstat a, dba_rollback_segs b where b.segment_id = a.usn;

select 'alter rollback segment ' || segment_name || ' shrink;' from sys.dba_rollback_segs where status = 'ONLINE';

SQL> select 'alter rollback segment ' || segment_name || ' shrink;' from sys.dba_rollback_segs where status = 'ONLINE';

'ALTERROLLBACKSEGMENT'||SEGMENT_NAME||'SHRINK;'

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

alter rollback segment SYSTEM shrink;

alter rollback segment R01 shrink;

alter rollback segment R02 shrink;

alter rollback segment R03 shrink;

alter rollback segment R04 shrink;

SQL> alter rollback segment R01 shrink;

Rollback segment altered.

SQL> alter rollback segment R02 shrink;

Rollback segment altered.

SQL> alter rollback segment R03 shrink;

Rollback segment altered.

SQL> alter rollback segment R04 shrink;

Rollback segment altered.

Enter value for tbs: RBS1

old 11: dba_data_files where tablespace_name in ('&tbs')) where

new 11: dba_data_files where tablespace_name in ('RBS1')) where

Enter value for tbs: RBS1

old 12: tablespace_name in ('&tbs')

new 12: tablespace_name in ('RBS1')

Used Space(MB) allocated size(MB) maximum allowable (MB) effectivefree(MB) % FREE

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

200 500 500 300 60

SQL> SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents

FROM sys.dba_segments

WHERE segment_type = 'ROLLBACK'; 2 3

SEGMENT_NAME TABLESPACE_NAME (BYTES)/1024/1024 BLOCKS EXTENTS

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

SYSTEM SYSTEM 1.328125 170 17

R0 2 256 2

R01 RBS1 50 6400 10

R02 50 6400 10

R03 50 6400 10

R04 50 6400 10

6 rows selected.

SQL> SELECT segment_name, tablespace_name, status

FROM sys.dba_rollback_segs; 2

SEGMENT_NAME TABLESPACE_NAME STATUS

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

SYSTEM SYSTEM ONLINE

R0 OFFLINE

R01 RBS1 ONLINE

R02 ONLINE

R03 ONLINE

R04 ONLINE

SQL> SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents

FROM sys.dba_segments

WHERE segment_type = 'ROLLBACK'; 2 3

SEGMENT_NAME TABLESPACE_NAME (BYTES)/1024/1024 BLOCKS EXTENTS

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

SYSTEM SYSTEM 1.328125 170 17

R0 2 256 2

R01 RBS1 50 6400 10

R02 345 44160 69

R03 50 6400 10

R04 50 6400 10

6 rows selected.

SQL> SELECT name, xacts "ACTIVE TRANSACTIONS" FROM v$rollname, v$rollstat WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;

no rows selected

SQL> SELECT segment_name, segment_type, tablespace_name

FROM sys.dba_segments

WHERE segment_type = 'DEFERRED ROLLBACK'; 2 3

no rows selected

SQL> select b.segment_name,b.tablespace_name,a.extents,a.rssize,a.xacts,a.optsize,a.shrinks,a.wraps,a.status from v$rollstat a, dba_rollback_segs b where b.segment_id = a.usn;

SEGMENT_NAME TABLESPACE_NAME EXTENTS RSSIZE XACTS Current Optimal SHRINKS WRAPS STATUS

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

SYSTEM SYSTEM 17 1384448 0 0 0 ONLINE

R01 RBS1 10 52420608 0 0 360 ONLINE

R02 69 361750528 0 0 703 ONLINE

R03 10 52420608 0 0 710 ONLINE

R04 10 52420608 0 0 356 ONLINE

Quest_in_OracleDBA

Thursday, November 19, 2015

RMAN backup skip tablespace using EXCLUDE feature

RMAN backup skip tablespace using EXCLUDE feature

Skipping Tablespaces in RMAN cloning

Skipping Tablespaces in RMAN cloning

Saturday, November 7, 2015

Setting up Network ACLs in Oracle 11gr SMTP server

Setting up Network ACLs in Oracle 11gr SMTP server

How to configure Access Control List

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()

acl

Description

Principal

is_grant

privilege

start_date

end_date

Add a privilege to Access Control List

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE()

Assign a network host to Access Control List

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()

acl

host

lower_port

upper_port

Host Assignments:

DBMS_NETWORK_ACL_UTILITY.DOMAINS()

Cleaning Up the Access Control List

Remove a host from Access Control List

Delete a privilege from Access Control List

Drop an Access Control List

Friday, October 23, 2015

Scripts to check Rollback Segments information

Scripts to check Rollback Segments information

Total Pageviews

Labels

Blog Archive

Contact Form

Followers

Thursday, November 19, 2015

RMAN backup skip tablespace using EXCLUDE feature

RMAN backup skip tablespace using EXCLUDE feature

Skipping Tablespaces in RMAN cloning

Skipping Tablespaces in RMAN cloning

Saturday, November 7, 2015

Setting up Network ACLs in Oracle 11gr SMTP server

Setting up Network ACLs in Oracle 11gr SMTP server

How to configure Access Control List

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()

acl

Description

Principal

is_grant

privilege

start_date

end_date

Add a privilege to Access Control List

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE()

Assign a network host to Access Control List

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()

acl

host

lower_port

upper_port

Host Assignments:

DBMS_NETWORK_ACL_UTILITY.DOMAINS()

Cleaning Up the Access Control List

Remove a host from Access Control List

Delete a privilege from Access Control List

Drop an Access Control List

Friday, October 23, 2015

Scripts to check Rollback Segments information

Scripts to check Rollback Segments information

Subscribe To

Contact Form

Followers