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
 
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
ADRCI, How to create a package?

ADRCI, How to create a package?

The goal is to upload a generated zip file from Montreal to the My Oracle Support website. This package will includes all the trace files, instance alert file and other diagnostic information for the critical error .

It is called a IPS package (Incident Packaging Services).

$adrci

adrci> show home

if you have multiple ORACLE_HOME:
adrci> set homepath database_home

adrci> show problem

PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
——– ———- ————- ————
2 ORA 4030 146025 2014-09-17 22:16:08.625000 -04:00
1 ORA 445 144125 2014-09-18 09:24:12.998000 -04:00

adrci> show incident -p « problem_key=’ORA 4030′ »

ADR Home = /u01a/diag/rdbms/nhsmppr/NHsmPPr:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
—————- ————– —————————————-
145585 ORA 4030 2014-09-17 22:16:05.493000 -04:00
144217 ORA 4030 2014-09-17 22:16:05.523000 -04:00
145441 ORA 4030 2014-09-17 22:16:05.639000 -04:00
144577 ORA 4030 2014-09-17 22:16:05.782000 -04:00
145985 ORA 4030 2014-09-17 22:16:05.919000 -04:00
5 rows fetched

adrci> ips pack incident 145585 in /tmp
Generated package 1 in file /tmp/ORA4030_20140918102208_COM_1.zip, mode complete

If at this step the ADRCI utility complains that the incident was flood-controlled and that no package can be generated for it, then instead of choosing the most recent incident to be packaged, choose the first incident that occurred after an instance startup.

Vincent Fenoll – Oracle DBA in Montreal

2014/09/18Read More