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

How to fix error >>> DBMS_AW_EXP: BIN$DdyrY during export

During export using data pump, you see the following:

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7.968 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
>>> DBMS_AW_EXP: BIN$DdyrY/cyAMLgU5/YLVaAbg==$0 not AW$

Solution: empty the recyclebin:

purge dba_recyclebin;

and restart the export.

How to use SUBSTR and INSTR to find the path used for the files in a tablespace

SELECT SUBSTR(FILE_NAME, 1, 
               INSTR(FILE_NAME, '/', -1, 1)  -- Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" -- starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS'

If you want to limit the output the first file found for a tablespace, add

AND ROWNUM < 2;

See also this post for how to use this piece of code in a script using input variables.

Source: "SQL Certified Expert Exam Guide" by Steve O'Hearn, page 220