Category Archives: Procedures

Procedures

Change primary key value in Oracle

Leave a comment

I want to modify the value of a primary key (PK) but there are foreign keys (FK) with data.

You will need to:
– Disable the foreign key constraints
– Change the values of the foreign keys
– Change the primary key values
– Re-enable the foreign key constraints.

The difficulty here is to quickly find all the foreign keys and disable them.

Here is how to disable the foreign key for all the dependant tables? Here is a script to automatically generate the SQL statements. Just change “my_table” and “my_column”

select ‘alter table ‘ || table_name || ‘ disable constraint ‘ || constraint_name || CHR(13) || ‘– my comment’ || CHR(13) || ‘;’ from user_constraints
where table_name in (select
src_cc.table_name as src_table
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = ‘R’
and dest_cc.owner = ‘DI’
and dest_cc.table_name = ‘my_table’
and dest_cc.column_name = ‘my_column’)
and constraint_name in (select
c.constraint_name
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = ‘R’
and dest_cc.owner = ‘DI’
and dest_cc.table_name = ‘my_table’
and dest_cc.column_name = ‘my_column’);

To re-enable the contraints replace “disable” with “enable”.

 

Author: Vincent Fenoll – Oracle DBA in Montreal (Canada)

 

Procedures

listener.log too big

Leave a comment

My big Oracle listener log

My listener.log file is growing and now it is very big; I have difficulties to open it.
What can I do when the listener.log is growing? Indeed, if you delete the LISTENER.LOG, the file will be recreated only after stopping and starting the listener …

I want to archive it without boucing the listener (online operation).

 

For Oracle < 11g:

# START LSNRCTL UTILITY
$lsnrctl # SET MY CURRENT LISTENER IF IT'S DIFFERENT FROM THE DEFAULT LSNRCTL>
set current_listener LISTENER_APP

# CHANGE LISTENER LOG LOCATION TO ANOTHER FILE
LSNRCTL> set log_file my_temp_listener.log
--> This command creates a new log file.
LSNRCTL> exit

# RENAME MY BIG LISTENER
# Unix:
$ mv listener.log listener.log.mydate
# Windows: copy ... or copy/paste :)
IN LSNRCTL UTILITY
### RENAME my_temp_listener.log BACK TO listener.log
LSNRCTL> set log_file listener
LSNRCTL> exit

 

For Oracle >= 11g

Oracle version 11g introduced the ADR or Automatic Diagnostic Repository feature. listener.log is written to xml file.

When I try to change the log file location using LSNRCTL utility, it will fail with:
TNS-01251: Cannot set trace/log directory under ADR.

The same error will get thrown when trying to change the trc_directory.

If the parameter DIAG_ADR_ENABLED_mylistener is set to ON in the listener.ora file, the parameter log_directory is ignored. The trace and log files are created in the location defined by ADR_BASE_mylistener.
– You can use the listener.ora ADR_BASE_mylistener parameter to specify the base directory into which tracing and logging incidents are stored when ADR is enabled.
– You can also disable ADR by setting in listener.ora :

DIAG_ADR_ENABLED_listener_name=OFF
LOG_DIRECTORY_listener_name =

 
Author: Vincent Fenoll
Compatible: Oracle 10g, 11g, 12c

Dataguard, Procedures

Resize online redo log in a dataguard configuration

Leave a comment

One of my clients in Montreal wants to resize the online redologs in a dataguard configuration.

The technique used by the Oracle DBA comprises:
– On the primary database: drop and re-create the online redologs and the standby online redologs
– On the standby database: after stopping the replication, drop and re-create the online redologs and the standby online redologs. Then reactive replication.

Here is the anonymous PL*SQL block. Change the desired SIZE for redologs in the DECLARE section, here it’s 200M 

SET SERVEROUTPUT ON
SET LINES 100
DECLARE
	v_rl_size_MB number DEFAULT 200;  
	v_count number DEFAULT 0;

BEGIN
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE('-- ==================================================');
	DBMS_OUTPUT.PUT_LINE('--                 PRIMARY DATABASE');
	DBMS_OUTPUT.PUT_LINE('-- ==================================================');
	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- Primary Database: DROP AND RESIZE ONLINE REDOLOGS');
	DBMS_OUTPUT.PUT_LINE('-- Useful statements:');
	DBMS_OUTPUT.PUT_LINE(' select group#,status from v$log;');
	DBMS_OUTPUT.PUT_LINE('alter system switch logfile;');
	DBMS_OUTPUT.PUT_LINE('alter system checkpoint;');
	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');

	FOR i IN (select group# 
		from v$log order by group#)
	LOOP
		v_count := 1;
		DBMS_OUTPUT.PUT_LINE(CHR(10));
		DBMS_OUTPUT.PUT_LINE('-- GROUP: ' || i.group#);
		DBMS_OUTPUT.PUT_LINE('alter database drop logfile group ' || i.group# || ';');
		FOR j IN (select member
			FROM v$logfile
			WHERE group#=i.group#)
		LOOP
			IF v_count = 1 THEN
				DBMS_OUTPUT.PUT_LINE('ALTER DATABASE ADD LOGFILE GROUP ' || i.group# || '(''' || j.member || ''') SIZE ' || v_rl_size_MB || 'M REUSE;');
				v_COUNT := v_COUNT + 1;
			ELSE
				DBMS_OUTPUT.PUT_LINE('ALTER DATABASE ADD LOGFILE MEMBER ''' || j.member || ''' REUSE to group ' || i.group# || ';');
			END IF;	
		END LOOP;
	END LOOP;

	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- Verify the new size of redologs');
	DBMS_OUTPUT.PUT_LINE('select group#, BYTES/1024/1024 "SIZE-MB", status from  v$log;');


	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- Primary Database: DROP AND RESIZE STANDBY REDOLOGS:');
	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');

	FOR i IN (select group# 
		from v$standby_log)
	LOOP
		v_count := 1;
		DBMS_OUTPUT.PUT_LINE(CHR(10));
		DBMS_OUTPUT.PUT_LINE('-- GROUP: ' || i.group#);
		DBMS_OUTPUT.PUT_LINE('alter database drop standby logfile group ' || i.group# || ';');
		FOR j IN (select member 
			FROM v$logfile
			WHERE group#=i.group#)
		LOOP
			DBMS_OUTPUT.PUT_LINE('ALTER DATABASE ADD STANDBY LOGFILE GROUP ' || i.group# || '(''' || j.member || ''') SIZE ' || v_rl_size_MB || 'M REUSE;');
		END LOOP;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- Verify the new size of Standby redologs');
	DBMS_OUTPUT.PUT_LINE(' select group#, BYTES/1024/1024 "SIZE-MB", status from  v$standby_log;');

	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));



	DBMS_OUTPUT.PUT_LINE('-- ==================================================');
	DBMS_OUTPUT.PUT_LINE('--                 STANDBY DATABASE');
	DBMS_OUTPUT.PUT_LINE('-- ==================================================');
	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- Standby Database: DROP AND RESIZE ONLINE REDOLOGS');
	DBMS_OUTPUT.PUT_LINE('-- Useful statements:');
	DBMS_OUTPUT.PUT_LINE(' select group#,status from v$log;');
	DBMS_OUTPUT.PUT_LINE('alter database clear logfile group n;');
	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');


	DBMS_OUTPUT.PUT_LINE('-- Prerequisites for the standby db if DG_BROKER_START = FALSE:');
	DBMS_OUTPUT.PUT_LINE('-- Update system parameter standby_file_management to manual:');
	DBMS_OUTPUT.PUT_LINE('alter system set standby_file_management=manual;');
	DBMS_OUTPUT.PUT_LINE('-- Cancel application of redologs on the standby:');
	DBMS_OUTPUT.PUT_LINE('alter database recover managed standby database cancel;');
	DBMS_OUTPUT.PUT_LINE(CHR(10));

	FOR i IN (select group# 
		from v$log order by group#)
	LOOP
		v_count := 1;
		DBMS_OUTPUT.PUT_LINE(CHR(10));
		DBMS_OUTPUT.PUT_LINE('-- GROUP: ' || i.group#);
		DBMS_OUTPUT.PUT_LINE('alter database clear logfile group ' || i.group# || ';');
		DBMS_OUTPUT.PUT_LINE('alter database drop logfile group ' || i.group# || ';');
		FOR j IN (select member
			FROM v$logfile
			WHERE group#=i.group#)
		LOOP
			IF v_count = 1 THEN
				DBMS_OUTPUT.PUT_LINE('ALTER DATABASE ADD LOGFILE GROUP ' || i.group# || '(''' || j.member || ''') SIZE ' || v_rl_size_MB || 'M REUSE;');
				v_COUNT := v_COUNT + 1;
			ELSE
				DBMS_OUTPUT.PUT_LINE('ALTER DATABASE ADD LOGFILE MEMBER ''' || j.member || ''' REUSE to group ' || i.group# || ';');
			END IF;	
		END LOOP;
	END LOOP;

	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- Verify the new size of redologs');
	DBMS_OUTPUT.PUT_LINE('select group#, BYTES/1024/1024 "SIZE-MB", status from  v$log;');

	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- Standby Database: DROP AND RESIZE STANDBY REDOLOGS:');
	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------');

	FOR i IN (select group# 
		from v$standby_log)
	LOOP
		v_count := 1;
		DBMS_OUTPUT.PUT_LINE(CHR(10));
		DBMS_OUTPUT.PUT_LINE('-- GROUP: ' || i.group#);
		DBMS_OUTPUT.PUT_LINE('alter database drop standby logfile group ' || i.group# || ';');
		FOR j IN (select member 
			FROM v$logfile
			WHERE group#=i.group#)
		LOOP
			DBMS_OUTPUT.PUT_LINE('ALTER DATABASE ADD STANDBY LOGFILE GROUP ' || i.group# || '(''' || j.member || ''') SIZE ' || v_rl_size_MB || 'M REUSE;');
		END LOOP;
	END LOOP;

	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- Post-requisites for the standby db if DG_BROKER_START = FALSE:');
	DBMS_OUTPUT.PUT_LINE('-- --------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- Update system parameter standby_file_management to auto:');
	DBMS_OUTPUT.PUT_LINE('alter system set standby_file_management=auto;');
	DBMS_OUTPUT.PUT_LINE('-- Start applying redologs on the standby:');
	DBMS_OUTPUT.PUT_LINE('alter database recover managed standby database disconnect from session using current logfile;');
	DBMS_OUTPUT.PUT_LINE('-- Verify the status and MRP process');
	DBMS_OUTPUT.PUT_LINE('select process,status,sequence# from v$managed_standby;');
	DBMS_OUTPUT.PUT_LINE('-- Verify the new size of Standby redologs');
	DBMS_OUTPUT.PUT_LINE(' select group#, BYTES/1024/1024 "SIZE-MB", status from  v$standby_log;');
	DBMS_OUTPUT.PUT_LINE('-- Verify the path of new Standby redologs');
	DBMS_OUTPUT.PUT_LINE(' select member from  v$logfile;');

	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));

	DBMS_OUTPUT.PUT_LINE('-- Unix commands to drop RL and Standby redologs ');
	DBMS_OUTPUT.PUT_LINE('-- -----------------------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE('-- IF MIGRATION ONLY, here is the Unix commands to drop RL and Standby redologs:');
	DBMS_OUTPUT.PUT_LINE('-- -----------------------------------------------------------------------------');
	FOR j IN (select member 
		FROM v$logfile)
	LOOP
		DBMS_OUTPUT.PUT_LINE('rm ' || j.member);
	END LOOP;
	
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	DBMS_OUTPUT.PUT_LINE(CHR(10));
	
	DBMS_OUTPUT.PUT_LINE('-- ============================================================');
	DBMS_OUTPUT.PUT_LINE('--                               THE END');
	DBMS_OUTPUT.PUT_LINE('-- ============================================================');
END;
/

Author: Vincent Fenoll
Compatible: Oracle 7 to 12c

Procedures

remove_agent_gridcontrol

Leave a comment 
-- ################################################
-- # Creator: Grégory RECHIGNAC (DBA Oracle)
-- # Created: 2012/03/01
-- # Name: remove_agent_gridcontrol.sql
--  ################################################
-- #
-- # Compatible: Oracle Grid Control 10g 11g 12c
-- #
-- ################################################
-- #
-- # Remove agent and it s target from Grid Control 
-- #
-- ################################################

SELECT 'exec mgmt_admin.cleanup_agent(''' || target_name || ''');'
  FROM mgmt_targets
 WHERE target_type = 'oracle_emd' AND UPPER (target_name) LIKE 'NOM_AGENT';

-- FYI: oracle_emd = agent
Procedures

External table Use

Leave a comment

How the DBA Oracle can use external table. Here is a small example

-- ################################################
-- # Creator: Vincent Fenoll (DBA Oracle)
-- # Created: 2011/11/08
-- # Name: External table Use
-- ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # Insert data into a table with my data in a
-- # csv (coma separated) file: Use of external table
-- #
-- ################################################
 

alter session set current_schema=my_schema;

select * from dba_directories;
--> ORA_DUMP  pour /oracle/export/ORCL


CREATE TABLE my_table_ext
(
	ID	NUMBER(7),
	LIBELLE	VARCHAR2(255)
)
	ORGANIZATION EXTERNAL
	(
	  TYPE ORACLE_LOADER
	  DEFAULT DIRECTORY	ORA_DUMP
	  ACCESS PARAMETERS
	  (
		records delimited by newline
		badfile	 ORA_DUMP:'my_table_ext.bad'
		logfile ORA_DUMP:'my_table_ext.log'
		fields terminated by ','
		missing field values are null
		(ID, LIBELLE)
	  ) 
	  LOCATION ('my_table.txt')
	)
	PARALLEL
	REJECT LIMIT UNLIMITED ;

SELECT * FROM my_table_ext ;


insert into my_table select * from my_table_ext;
commit;
exit