View Sidebar

Welcome Oracle DBA!

This site is intended for helping Oracle DBA’s to Administer Databases and make them running faster. We will try our best to publish useful scripts ans methods. The views expressed on this blog are ours and do not reflect the views of Oracle Corporation. The views and opinions expressed by visitors on this blog are theirs, not ours. These programs/scripts are for informational purposes only and we disclaim liability for any damages caused by such use of the Programs.  
Vincent FENOLL Cyrille MODIANO Yann COUSIN
View Vincent Fenoll's profile on LinkedIn View Cyrille Modiano's profile on LinkedIn View Yann Cousin's profile on LinkedIn
 
listener.log too big

listener.log too big

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

2016/10/31Read More
Create a pluggable database

Create a pluggable database

dba oracle montreal create pdb oracle12c
 

How to create a pluggable database?

One of my clients in Montreal wants to create an empty pluggable database in a multitenant Oracle database. How can I do that?

In this example, I will not use the plug/unplug/clone features.

tnsnames.ora:

# PDB:
MY-PDB-NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my-hostname.ca)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = my-pdb-name.ca)
(SERVER = DEDICATED)
)
)

create pluggable database MY-PDB-NAME admin user PDBA identified by xxxxx
file_name_convert = ('/u02/oradata/CDB-NAME/seed','/u02/oradata/CDB-NAME/MY-PDB-NAME');

alter pluggable database MY-PDB-NAME open;

alter session set container=MY-PDB-NAME;

alter system set listener_networks = '((NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my-hostname.ca)(PORT=1525)))))';

-- Restart the listener on 1525 port 


-- Create the user and the index tablespace

CREATE BIGFILE TABLESPACE MY-PDB-NAME_DATA DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_data.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE BIGFILE TABLESPACE MY-PDB-NAME_INDEX DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_index.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


-- Create the application user
CREATE USER MY-PDB-NAME
 IDENTIFIED BY xxxxx
 DEFAULT TABLESPACE MY-PDB-NAME_DATA
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 
 -- 2 Roles for MY-PDB-NAME
 GRANT CONNECT TO MY-PDB-NAME;
 GRANT RESOURCE TO MY-PDB-NAME;
 GRANT SELECT_CATALOG_ROLE TO MY-PDB-NAME;
 ALTER USER MY-PDB-NAME DEFAULT ROLE ALL;
 
 -- 1 System Privilege for MY-PDB-NAME
 GRANT UNLIMITED TABLESPACE TO MY-PDB-NAME;
 
 -- 2 Tablespace Quotas for MY-PDB-NAME
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_DATA;
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_INDEX;

GRANT CREATE ANY VIEW TO MY-PDB-NAME;


-- Test the connection to the pluggable database:
sqlplus MY-PDB-NAME/xxxxx@MY-PDB-NAME
Connected.
SQL>REM THE END

Author: Vincent Fenoll
Compatible: Oracle 12c with the multitenant option.

2016/04/14Read More
Resize online redo log in a dataguard configuration

Resize online redo log in a dataguard configuration

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

2015/09/25Read More
Log switch number

Log switch number

The Oracle DBA wants to know how many log switch the database made a particular month (for example 2015 of august)


SELECT to_char(first_time, 'yyyy-MM') as "Year-Month",
count(*) as "Month log switch count"
FROM V$log_history
GROUP BY to_char(first_time, 'yyyy-MM')
having to_char(first_time, 'yyyy-MM')='2015-08'

To obtain all the months, just remove the HAVING clause:

SELECT to_char(first_time, 'yyyy-MM') as "Year-Month",
count(*) as "Month log switch count"
FROM V$log_history
GROUP BY to_char(first_time, 'yyyy-MM')
ORDER BY 1;

If we want to know the Oracle log switch number day by day and hour by hour, ths script does the job:

set lines 130; 
set pages 999; 
SELECT 
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
GROUP BY TO_CHAR (first_time, 'YYYY-MON-DD')
ORDER BY 1;

Author: Vincent Fenoll (Montreal)
Compatible: Oracle 7 to 12c

2015/09/16Read More
In-Database Archiving Oracle 12c

In-Database Archiving Oracle 12c

Montreal In Database Archiving Temporal Validity
 

What is « In Database Archiving »

How can we deal with historic and non-active data and keeping most of your data online within the database itself?

With In Database Archiving, you can keep both operational and historical data together in the same database, but limit your applications so they access only the operational active data.

A new row property helps separate active and non-active data in the table and lets you
archive rows by marking them inactive. The data remains in the table and you can compress it, but to the application this part of the inactive data remains hidden.

You can also use a second capability, called Temporal Validity, to distinguish active rows from inactive rows in the same table. It uses the Oracle 11g Flashback Data Archive (FDA) that has been renamed Temporal with a 12c new feature.
Temporal Validity adds two « date » columns in the table.

The In-Database Archiving and Temporal Validity capabilities let you control the validity and visibility of data.
 

Hybrid Columnar Compression

Oracle’s Hybrid Columnar Compression (HCC) is designed for in-database archiving.

It supports 2 types of compression:
Warehouse Compression can compress data up to 10x, it will improve query by reducing the size of the data queried (active data)
Archive Compression can achieve a 15x to 50x compression ratio. Instead of compressing the repeating values found within rows of data to save storage space, HCC stores data by column.
 

How to enable/disable/view « In Database Archiving »?

&nbsp
– In order to enable In-Database Archiving in a table, you have to enable row archival by adding the hidden column ORA_ARCHIVE_STATE (0-1) during table creation.
CREATE TABLE ORDER_HISTORY (ORDER_ID NUMBER, […], ) row archival;

The row inactivity is managed by the temporal validity, when a row is rarely updated or accessed it is marked as inactive and then archived
&nbsp
To view active/non active state of rows in a table, you have to explicitly specify the column:
SELECT ora_archive_state, order_id FROM ORDER_HISTORY;
0: Active 1: non active
 
To update the value of the ORA_ARCHIVE_STATE:
update ORDER_HISTORY set ora_archive_state = dbms_ilm.archivestatename(1) where […];
 
To disable In Database Archiving:
ALTER TABLE ORDER_HISTORY NO ROW ARCHIVAL;
 
By default when you query a table where the In Database Archiving feature is enabled, Oracle returns only the active rows, to see all rows:
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Vincent Fenoll – Oracle DBA Montreal

2014/11/12Read More