RMAN-06136 ORA-17627 ORA-01017 ORA-17629 Errors When Duplicating DB

Last morning I was improving a duplicate script, from production environment to development, when I get the next error;Last morning I was improving a duplicate script, from production environment to development, when I get the next error;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/31/2017 08:41:54
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

After many probes I have discovered that password file is wrong. I performed a scp from prod to dev. When I send the file again it works fine.

 
HTH – Antonio NAVARRO

Advertisements

ORA-12720, RMAN-06136 And RMAN-05501 Errors When Duplicating Database

Today I was performa a duplicate database when I got the next error.

 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/30/2017 10:32:22
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

Okay, it is my fault, I forgot set the cluster_databsae to FALSE. It came from a RAC and have value of TRUE. It is so easy as change this parameter.

HTH – Antonio NAVARRO

Better To Move Than To Copy

Yesterday a person was copying a file, in order to change location, in this case it is a unix system, but in windows it happens the same. In this example we are going to see copy times (cp command) versus move (mv command), to see the difference. This post is designed for people who start and do not know very well the difference between copy and move. For example I have used the compressed oracle engine, but for large files you can go the time from minutes to hours.

Copying the file to another path, we see that in this case it takes almost 18 seconds;

oracle12@mortages-1:~/soft_12c_sparc$ time cp solaris.sparc64_12102_database_1of2.zip ./soft_12c_sparc

real    0m17.82s
user    0m0.00s
sys     0m4.67s

Move the same file to the same path, we see that the time is 0 seconds;

oracle12@mortages-1:~/soft_12c_sparc$ time mv solaris.sparc64_12102_database_1of2.zip ./soft_12c_sparc

real    0m0.00s
user    0m0.00s
sys     0m0.00s

The explanation is simple. The cp moves the file physically (from the first byte to the last) to the new location. With mv what we do is leave the file where it is and change in index of directories (at OS level) the logical direction that we see.

HTH – Antonio NAVARRO

How To Know If Email Server For DBMS_SCHEDULER Is Configured

Yesterday I had a question at work and it was if the dbms_scheduler had configured the mail server, as I did not remember how it looked I had to reviewed to documenting, is a very simple command but I leave it here, partly if it suits someone, and partly because as I said in other times I use the blog as a database of knowledge of problems and doubts that appear in my daily life.

 

 
declare 
 IsConfiguredServer   VARCHAR2(64); 
 IsConfiguredSender   VARCHAR2(64); 
 BEGIN 
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', IsConfiguredServer); 
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', IsConfiguredSender); 
 DBMS_OUTPUT.PUT_LINE('server: ' || IsConfiguredServer); 
 DBMS_OUTPUT.PUT_LINE('sender: ' || IsConfiguredSender); 
END; 
/ 

 

HTH – Antonio NAVARRO

 

Example Of Execute SQL Advisor

I like show a basic example (valid only for a single sql_id) of how to execute the SQL ADVISOR from command like. If you prefer a GUI interface you can use EM Console or SQL Developer.

 

i) Create the task but before explain;

time_limit: maximum duration in seconds for the tuning session

The scope:

LIMITED

SQL Tuning Advisor produces recommendations based on statistical checks, access path analysis, and SQL structure analysis. SQL profile recommendations are not generated.

COMPREHENSIVE

SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL profiling.

 

DECLARE
  Output  VARCHAR2(100);
BEGIN
  Output := DBMS_SQLTUNE.create_tuning_task (
            sql_id      => '9v64r4s0n7znc',
            scope       => DBMS_SQLTUNE.scope_comprehensive,
            time_limit  => 500,
            task_name   => 'TAREA ANR 0x0000F2',
            description => 'Anr task for 9v64r4s0n7znc');
  DBMS_OUTPUT.put_line('Task created: ' || Output);
END;
/

 

ii) Execute the task;

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME => 'TAREA ANR 0x0000F2'); 

iii) Show the report;

SET LINESIZE 100
SET LONG 5000 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('TAREA ANR 0x0000F2') FROM DUAL; 

For more information, please refer to the doc at the next link;

SQL ADVISOR Documentation

HTH – Antonio NAVARRO

 

How To Trace A DBlink Connection (Local And Remote)

In many occasions we need to trace a session, possibly the most used in Oracle is 10046, this is a powerfull tool to see the execution times, but we usually use it on a single server.

Today I want to show a little trick to trace when we use a db link between two servers and generate this trace (can be used with others) in the two servers, the local and the remote. This can be usefull for example for snapshots between different databases.

In the remote server we create the following procedure within the user schema with which we are going to connect through the database link

 

REM *************************************
REM  You need create procedure privilege 
REM *************************************
CREATE OR REPLACE PROCEDURE set_on_trace
AS
BEGIN 

   EXECUTE IMMEDIATE 'alter session set TRACEFILE_IDENTIFIER = ''MY_TRACE_ANR'' '; 
   EXECUTE IMMEDIATE 'alter session set MAX_DUMP_FILE_SIZE=unlimited'; 
   EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12'' '; 

END; 
/

On the local server, where we are going to execute the call to the database link

 
alter session set STATISTICS_LEVEL=ALL; 
alter session set events '10046 trace name context forever, level 12';

alter session set TRACEFILE_IDENTIFIER = 'MY_TRACE_ANR'; 
exec set_on_trace@my_database_link
select count (*) from siebel.orders@my_database_link;

 

HTH – Antonio NAVARRO

An Easy Way To View System Users With A Default Password

Today I return to work after a few days of vacation. I am currently installing a couple of new engines and creating their corresponding databases and I would like to remember that we should not leave the keys by default, for this we have the view DBA_USERS_WITH_DEFPWD, which tells us that users have the default pass. This is for Oracle’s own users. For the rest of users there are other ways to verify it although we should have key policies, such as a minimum number of alphanumeric, capital letters, etc. Aside from having the keys expire from time to time is a good idea.

Here I leave an example of a newly created database and in which only the pass for the user SYS and SYSTEM has been changed.

 
MODB> column product format a50
MODB> column username format a25   
MODB> select * from dba_users_with_defpwd

USERNAME                  PRODUCT
_________________________ __________________________________________________
DIP                       ø
GSMADMIN_INTERNAL         ø
ORACLE_OCM                ø
APPQOSSYS                 ø
MDDATA                    ø
GSMCATUSER                ø
SPATIAL_WFS_ADMIN_USR     ø
SYSDG                     ø
WMSYS                     ø
SYSKM                     ø
SI_INFORMTN_SCHEMA        ø
SYSMAN                    ø
TSMSYS                    ø
OUTLN                     ø
ORDPLUGINS                ø
OLAPSYS                   ø
ORDSYS                    ø
MDSYS                     ø
SYSBACKUP                 ø
GSMUSER                   ø
SPATIAL_CSW_ADMIN_USR     ø
OJVMSYS                   ø
ORDDATA                   ø

23 rows selected.

HTH – Antonio NAVARRO