What does the DEFAULT DEGREE on tables actually mean?

The DOP (degree of parallelism) settings on a table has to do with the number of threads per instance for scanning the table (an integer), or DEFAULT.

When a tables DEGREE setting is set to DEFAULT, it means that the DOP) is calculated via the following formula:

For a single instance:

DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT

For an Oracle RAC configuration:

DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

The DOP for a table can be viewed in either of the *_TABLES views.

SELECT DEGREE
FROM   DBA_TABLES
WHERE  TABLE_NAME = 'EMP'
AND    OWNER = 'SCOTT';


DEGREE
------------------
         1

How to use cpio on AIX

I recently received a patch from Oracle support, and when unpacking with unzip, the resulting file was in the cpio format. Unpacking this file, in turn, is done like this:

mkdir install
cd install
cpio -idcmv < 9205_aix5l64_release.cpio

How to create and how to extract a .tar file

Create a tar file of all the files in the current directory:

tar cvf myfile.tar *

or put it in a different directory:

 tar cvf $HOME/myfile.tar *

To extract myfile.tar to the current directory

tar xvf myfile.tar 

To a specific directory:

tar xvf myfile.tar -C mydir

How to solve ORA-17628: Oracle error 19505 returned by remote Oracle server during clone from active database

After some time, my RMAN "duplicate from active database" script threw the error below:

RMAN-03009: failure of backup command on c1 channel at 07/21/2015 12:13:15
ORA-17627: ORA-12577: Message 12577 not found;  product=RDBMS; facility=ORA
RMAN-12019: continuing other job steps, job failed will not be re-run

When I checked the alert log, it was clear

IBM AIX RISC System/6000 Error: 28: No space left on device

Solution: obvious.

How to solve ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified during clone from active database

During an attempt to clone from active database, the following error was thrown:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/21/2015 10:06:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on c1 channel at 07/21/2015 10:06:15
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

This seems pretty obvious, since the error stack states that there is a connection problem, but it's easy to overlook if you run your command from the destination server, and like in my case, checked that all connections work, that the passwords are identical etc etc.

The solution is simply to add the source and the destination connect descriptor in the $TNS_ADMIN/tnsnames.ora files on both the source and auxiliary server.


See my other article on active database cloning for a more detailed description of the required setup.

How to enable and disable the automatic SQL tuning advisor in Oracle 11g

To enable automatic SQL tuning, use the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/

To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor', 
    operation => NULL, 
    window_name => NULL);
END;
/

How to create an ACL

-- step 1: Create the Access Control List and its Privilege Definitions
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'my_acl.xml',
  description  => 'Limit the use of package utl_http til webservice',
  principal    => 'SCOTT',
  is_grant     => TRUE,
  privilege    => 'connect');
END;
/

-- Step 2: Assign the Access Control List to One or More Network Hosts
-- After you create the access control list, then you are ready to assign it to one or more network host computers.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'my_acl.xml',
  host        => '*');
END;
/

In the code above, there is no restrictions on which ports to use. If desirable, use the lower_port and upper_port directives in the ASSIGN_ACL procedure. You can also assign the ACL to apply to one specific host, or group of hosts.

For example

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'my_acl.xml',
  host        => 'appserver1.mycompany.com',
  lower_port => 80,
  upper_port => 3999);
END;
/

You can find information about the currently set up ACLs in the following views:

DBA_NETWORK_ACLS

DBA_NETWORK_ACL_PRIVILEGES

DBA_WALLET_ACLS

USER_NETWORK_ACL_PRIVILEGES

Sources: Oracle Documentation

http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG121 - creating an ACL
http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99984 - finding information about ACL
http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG106 - Specifying a Group of Network Host