Oracle Enterprise Manager 13c

Oracle announced Friday 18 the latest version of Oracle Enterprise Manager. This is the latest version of this powerful tool, It started monitoring the database and has been extended to the entire ecosystem we can find in any infrastructure, whether local (data center) or remote (cloud), the latter by now quite limited.

You can find more information in the next blog, from Oracle;

 
Oracle announces EM 13c

 
If you want to download it, I recommend for now just to test and probes before installing in production enviroment, use this link;

download Enterprise Manager 13c

 

HTH – Antonio NAVARRO

 

Get ORA600 kolrrdl:0rfc From PL/SQL Developer

Today I found out the next error in an alert.log;

21-DEC-2015 14:28:35.77:
Errors in file $1$DGA523:[SAP.][USER]HORUS_SAP_FG_ORACLE_701.TRC;:
ORA-00600: cdigo de error interno, argumentos: [kolrrdl:0rfc], [], [], [], [], [], [], []

 

Doing some mining in several log files and also making several phone calls, I discovered that the problem It produced by a developer who is using PL/SQL Developer (it is a high quality tool). The error gives when He try get a XMLTYPE. After some more research I have seen that is the Bug 4745114. for which there is patch although the recommendation is an upgrade, of course, this version is 10g and It is older. In my case the issue remains pending (until ….), since it is a critical database (24×7) and any intervention takes a lot of decisions from many managers.

 

HTH –  Antonio NAVARRO

How To Insert A TXT File Into Oracle

Many times need to upload files into database. In this post I proposed You a way using dbms_lob package.

Create the table to store the files. I have included a column of date type to store the insert time;

CREATE TABLE EXTERNAL_TXT_FILES
(
INS_TIME DATE,
TXT_FILE CLOB
)
/

Create from sqlplus a directory, which exist at operating system, where are the txt files;

CREATE OR REPLACE DIRECTORY DIRECTORY_PROBES
AS '/EXPORT/HOME/ORACLE/ANR/FILES';

Create a procedure

CREATE OR REPLACE
PROCEDURE LOAD_A_FILE(FILENAME_2_UPLOAD IN VARCHAR2 )
AS
COLUMN_CLOB CLOB;
PHYSYCAL_FILE BFILE;
BEGIN
INSERT INTO EXTERNAL_TXT_FILES VALUES ( SYSDATE, EMPTY_CLOB() ) RETURNING TXT_FILE INTO COLUMN_CLOB;
PHYSYCAL_FILE := BFILENAME( 'DIRECTORY_PROBES', FILENAME_2_UPLOAD );
DBMS_LOB.FILEOPEN( PHYSYCAL_FILE );
DBMS_LOB.LOADFROMFILE( COLUMN_CLOB, PHYSYCAL_FILE, DBMS_LOB.GETLENGTH( PHYSYCAL_FILE ) );
DBMS_LOB.FILECLOSE( PHYSYCAL_FILE );
END;
/

Execute the next command to upload a file, in this case MyFile.txt;

EXEC LOAD_A_FILE('MYFILE.TXT');

Now you can query the table with the next select;

SELECT * FROM EXTERNAL_TXT_FILES;

HTH – Antonio NAVARRO

How To Truncate Tables In A Schema With Referential Integrity

Often we need to clean a schema, keeping the tables, indexes, etc. it is necessary to truncate the tables, the problem comes when exists referential integrity between tables in the scheme, in this case we will not see referential integrity between different schemes. Used by the truncate is faster than the delete, not fire the trigger, the redo and undo is minimal. Of course, there are other options like making a export and import without rows, this is just one more option, depending on the case which can be adjusted more or less to our needs.

First of all, let the script to generate the disable and enable sentences for the constraints, we will use the following script;

BEGIN
-- GENERATE SCRIPT TO DISABLE CONSTRAINTS ----
FOR c IN (SELECT table_name, constraint_name FROM dba_constraints WHERE owner ='INTRANET' and constraint_type = 'R')
LOOP
DBMS_OUTPUT.PUT_LINE ('alter table publicidad.' || c.table_name || ' disable constraint ' || c.constraint_name || ';');
END LOOP;

-- GENERATE SCRIPT TO ENABLE CONSTRAINTS ----
FOR c IN (SELECT table_name, constraint_name FROM dba_constraints WHERE owner ='INTRANET' and constraint_type = 'R')
LOOP
DBMS_OUTPUT.PUT_LINE ('alter table publicidad.' || c.table_name || ' enable constraint ' || c.constraint_name || ';');
END LOOP;
END;
/

Once we have it, we have to disable all constraints that we have obtained with the execution of the PL/SQL anonymous above. Note that we run the script to disable the constraints several times, this is because they may not all matching disable, for example by having a circular referential integrity.

At this point we can execute truncate all table or subset of tables that interests us. If the truncate is a subset that we must remember the model and you might have problems to raise some constraints by violating integrity. We can use the following script to generate the truncated;

BEGIN
-- GENERATE SCRIPT TO TRUNCATE ALL TABLES ----
FOR c IN (SELECT table_name FROM dba_tables WHERE owner ='INTRANET')
LOOP
DBMS_OUTPUT.PUT_LINE ('truncate table ' || c.table_name || ';' );
END LOOP;

END;
/

By putting enable the constraints you may also need to run the script several times. It would also be good idea to review all the constraints associated with the schema and type ‘R’ are enable.

HTH – Antonio NAVARRO

The Transaction Log For Database ‘model’ Is Full

Today I have received the following error in a  SQL Server 2005 database;

 The transaction log for database ‘model’ is full. To find out why space in the log can not be reused, see the column in sys.databases log_reuse_wait_desc

This error gives when I try to start the service, so the database do not get start up. The problem seems to be in the transaction log of the model database, which will be in full recovery mode.

To start the database use the following command from a cmd, and we put a trace flag.

NET START MSSQLPXDSAP / T3609

Let us start the database but in a way very limited work.

The problem is that the transaction log can not grow because it limited its maximum size, an option is to remove the restriction, the other is to do a backup with truncate the log option, I chose the latter, although in the future may repeat the problem, so I put the model database in SIMPLE recovery mode. I stop and start the service again without trace flag;

NET STOP MSSQLPXDSAP

NET START MSSQLPXDSAP

HTH – Antonio NAVARRO