DBA survival BLOG

DBA stuff and Oracle Data Guard

Oracle Database 12c: RMAN recover at table level

Posted on June 27, 2013 by Ludovico

Oracle Database 12c comes with a new feature named “RMAN table level recovery”.

After a quick try it’s easy to understand that we are talking about Tablespace Point-in-Time Recovery (TSPITR) with some automation to have it near-transparent.

How to launch it

The syntax is quite trivial. Suppose you’ve dropped a table ludovico.reco and then purged it (damn!) then you can’t flashback it to before drop and don’t want to flashback the entire database.

SQL> create table reco (field1 varchar2(50) primary key);

Table created.

SQL> insert into reco values ('test');

1 row created.

SQL> insert into reco values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from reco;

FIELD1
--------------------------------------------------
foo 
test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
803916

SQL> drop table reco;

Table dropped.

SQL> purge table reco;

Table purged.

SQL> flashback table reco to before drop;
flashback table reco to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL> create table reco (field1 varchar2(50) primary key);

Table created.

SQL> insert into reco values ('test');

1 row created.

SQL> insert into reco values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from reco;

FIELD1

--------------------------------------------------

foo

test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

803916

SQL> drop table reco;

Table dropped.

SQL> purge table reco;

Table purged.

SQL> flashback table reco to before drop;

flashback table reco to before drop

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

You can recover the table with:

RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '/tmp/recover';

1	RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '/tmp/recover';

You identify the schema.table:partition to restore, optionally you can pass the pluggable database containing the table to recover, the time definition as usual (scn, seq# or timestamp) and an auxiliary destination.

This Auxiliary destination is well-known to be mandatory for TSPITR. You can pass other options like table renaming or tablespace remapping.

Off course, the database must be open in read-write, in archivelog mode and at least one successful backup must be taken.

How it works

Oracle prepare an auxiliary instance by restoring the SYSTEM, UNDO and SYSAUX tablespaces.

Starting recover at 10-JUN-13
using channel ORA_DISK_1 
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM 
Tablespace UNDOTBS1

Creating automatic instance, with SID='kCoq'

initialization parameters used for automatic instance:
db_name=CLASSIC 
db_unique_name=kCoq_pitr_CLASSIC 
compatible=12.0.0.0.0 
db_block_size=8192 
db_files=200 
sga_target=1G 
processes=80 
diagnostic_dest=/u01/app/oracle 
db_create_file_dest=/tmp/recover 
log_archive_dest_1='location=/tmp/recover' 
#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 281020272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# restore the controlfile 
restore clone controlfile; 
# mount the controlfile 
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current'; 
} 
executing Memory Script

executing command: SET until clause

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 
output file name=/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl 
Finished restore at 10-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new; 
set newname for clone datafile 3 to new; 
set newname for clone datafile 2 to new; 
set newname for clone tempfile 1 to new; 
# switch all tempfiles 
switch clone tempfile all; 
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2; 
switch clone datafile all; 
} 
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/recover/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/recover/CLASSIC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56 
Finished restore at 10-JUN-13

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf
datafile 3 switched to datafile copy 
input datafile copy RECID=5 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf
datafile 2 switched to datafile copy 
input datafile copy RECID=6 STAMP=817751487 file name=/tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

Starting recover at 10-JUN-13

using channel ORA_DISK_1

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='kCoq'

initialization parameters used for automatic instance:

db_name=CLASSIC

db_unique_name=kCoq_pitr_CLASSIC

compatible=12.0.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/tmp/recover

log_archive_dest_1='location=/tmp/recover'

#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 281020272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 10-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl

Finished restore at 10-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/recover/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/recover/CLASSIC/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56

Finished restore at 10-JUN-13

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=817751487 file name=/tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf

Then it opens in READ-ONLY mode the partial database.

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
# recover and open database read only 
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only'; 
} 
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

Starting recover at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=54 
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=55 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54 
archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55 
media recovery complete, elapsed time: 00:00:10 
Finished recover at 10-JUN-13

sql statement: alter database open read only

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

Starting recover at 10-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=54

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=55

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54

archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55

media recovery complete, elapsed time: 00:00:10

Finished recover at 10-JUN-13

sql statement: alter database open read only

It uses then the read-only dictionary to take the tablespace that was containing the table before the data loss. This tablespace (users in my example) is restored and recovered, and the database is opened.

contents of Memory Script:

{ 
sql clone "create spfile from memory";
shutdown clone immediate; 
startup clone nomount; 
sql clone "alter system set control_files = 
''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment=
''RMAN set'' scope=spfile"; 
shutdown clone immediate; 
startup clone nomount; 
# mount database 
sql clone 'alter database mount clone database'; 
} 
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new; 
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4; 
switch clone datafile all; 
} 
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
Finished restore at 10-JUN-13

datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=817751583 file name=/tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# online the datafiles restored or switched
sql clone "alter database datafile 4 online";
# recover and open resetlogs 
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs; 
} 
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 4 online

Starting recover at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=54 
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=55 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54 
channel clone_default: deleting archived log(s) 
archived log file name=/tmp/recover/1_54_814717091.dbf RECID=4 STAMP=817751585 
archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55 
channel clone_default: deleting archived log(s) 
archived log file name=/tmp/recover/1_55_814717091.dbf RECID=5 STAMP=817751587 
media recovery complete, elapsed time: 00:00:01 
Finished recover at 10-JUN-13

database opened

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 285214576 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 285214576 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 4 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 4;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 10-JUN-13

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=817751583 file name=/tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# online the datafiles restored or switched

sql clone "alter database datafile 4 online";

# recover and open resetlogs

recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 4 online

Starting recover at 10-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=54

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=55

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54

channel clone_default: deleting archived log(s)

archived log file name=/tmp/recover/1_54_814717091.dbf RECID=4 STAMP=817751585

archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55

channel clone_default: deleting archived log(s)

archived log file name=/tmp/recover/1_55_814717091.dbf RECID=5 STAMP=817751587

media recovery complete, elapsed time: 00:00:01

Finished recover at 10-JUN-13

database opened

At this point, RMAN starts an export/import with datapump to move the table from the auxiliary database back to the target database:

contents of Memory Script:
{ 
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/recover''"; 
# create directory for datapump export 
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/recover''"; 
} 
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic": 
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB 
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE 
EXPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER 
EXPDP> . . exported "LUDOVICO"."RECO" 5.054 KB 2 rows
EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded 
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is: 
EXPDP> /tmp/recover/tspitr_kCoq_82218.dmp 
EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_kCoq_Fbti" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "LUDOVICO"."RECO" 5.054 KB 2 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07
Import completed

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/recover''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/recover''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 64 KB

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> . . exported "LUDOVICO"."RECO" 5.054 KB 2 rows

EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is:

EXPDP> /tmp/recover/tspitr_kCoq_82218.dmp

EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_kCoq_Fbti" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "LUDOVICO"."RECO" 5.054 KB 2 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07

Import completed

Finally, the auxiliary instance is cleaned:

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted
auxiliary instance file tspitr_kCoq_82218.dmp deleted
Finished recover at 10-JUN-13

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted

auxiliary instance file tspitr_kCoq_82218.dmp deleted

Finished recover at 10-JUN-13

We can check if our table is ok:

RMAN> select * from ludovico.reco;

FIELD1
--------------------------------------------------
foo
test

RMAN>

RMAN> select * from ludovico.reco;

FIELD1

--------------------------------------------------

foo

test

RMAN>

Oh, and yes, now we can select directly from RMAN! 🙂

My opinion

It still needs the amount of space needed to recover the auxiliary instance (system, sysaux, temp and the user tablespace containing the missing data), so it has all the defeats of the typical TSPITR, but it’s automatic so is an improvement for the real life.
Restoring the user tablespace separately from the system tablespaces can be an issue if you’re saving backupsets over tape: you can end up by reading twice the same backupset that could be read once instead.

Cheers

Ludovico

Oracle Database 12c: sequence.nextval as default and identity columns

Posted on June 27, 2013 by Ludovico

Finally! I can count how many times I’ve seen questions like this one.

The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:

SQL> create sequence foo_seq;

Sequence created.

SQL> create table foo (
2 id number default foo_seq.nextval,
3 bar varchar2(50) not null,
4 constraint foo_pk primary key (id)
5 );

Table created.

SQL> insert into foo (bar) values ('baz');

1 row created.

SQL> insert into foo (bar) values ('test');

1 row created.

SQL> insert into foo (bar) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

ID BAR
---------- --------------------------------------------------
1 baz
2 test
3 whoo

SQL> create sequence foo_seq;

Sequence created.

SQL> create table foo (

2 id number default foo_seq.nextval,

3 bar varchar2(50) not null,

4 constraint foo_pk primary key (id)

5 );

Table created.

SQL> insert into foo (bar) values ('baz');

1 row created.

SQL> insert into foo (bar) values ('test');

1 row created.

SQL> insert into foo (bar) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

ID BAR

---------- --------------------------------------------------

1 baz

2 test

3 whoo

But Oracle has fixed this twice, in the new release it’s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:

SQL> create table bar (
2 id number generated as identity,
3 foo varchar2(50) not null,
4 constraint bar_pk primary key (id)
5 );

Table created.

SQL> insert into bar (foo) values ('baz');

1 row created.

SQL> insert into bar (foo) values ('test');

1 row created.

SQL> insert into bar (foo) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bar;

ID FOO
---------- --------------------------------------------------
1 baz
2 test
3 whoo

SQL> create table bar (

2 id number generated as identity,

3 foo varchar2(50) not null,

4 constraint bar_pk primary key (id)

5 );

Table created.

SQL> insert into bar (foo) values ('baz');

1 row created.

SQL> insert into bar (foo) values ('test');

1 row created.

SQL> insert into bar (foo) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bar;

ID FOO

---------- --------------------------------------------------

1 baz

2 test

3 whoo

I’ve said “explicitly” because actually a sequence is created with a system-generated name, so you’ll still need to deal with sequences.

TABLE_NAME TABLE_TYPE
------------------------------- -----------
ISEQ$$_23657 SEQUENCE

TABLE_NAME TABLE_TYPE

------------------------------- -----------

ISEQ$$_23657 SEQUENCE

cheers

Ludo

Oracle Database 12c: move datafile online

Posted on June 26, 2013 by Ludovico

The new Oracle Database 12c allows to move datafiles ONLINE while they’re been used. This allows great availability when dealing with database moving, compared to the previous approach.

Pre 12c:

copy datafile with RMAN
offline datafile
switch datafile to copy
recover datafile
alter datafile online

From 12c:

move the datafile! 🙂

The actual command for moving the datafile is:

SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

1	SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

where the source can be specified using the file#, or the actual path.

The destination must be specified only if moving to a non-OMF file, otherwise it takes the db_create_file_dest parameter:

SQL> alter database move datafile 1;

1	SQL> alter database move datafile 1;

in the latter it will move the system datafile back to my +DATA diskgroup.

So, it’s a great enhancement to move database:

from a storage to another without using ASM
migrating online from an ASM DG to another
moving from FS to ASM and viceversa
(not tested) move datafiles on Windows from a logical disk to another!
etc.

Full example (including some information on the proper MV enqueue):

SQL> select file#, name from v$datafile;

FILE# NAME
---------- ------------------------------------------------------------------------------------------
1 +DATA/CLASSIC/DATAFILE/system.267.814717093
2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123
3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147
4 +DATA/CLASSIC/DATAFILE/users.258.814717205
5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN
---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
KSQSTEXPL KSQSTEVIDX 
------------------------------------------------------------------------------------------------------------------------ ---------- 
000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move 
Held during online datafile move operation or cleanup 157

SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

Database altered.

SQL>

SQL> select file#, name from v$datafile;

FILE# NAME
---------- ------------------------------------------------------------------------------------------
1 /data/CLASSIC/system01.dbf
2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123
3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147
4 +DATA/CLASSIC/DATAFILE/users.258.814717205
5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN
---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
KSQSTEXPL KSQSTEVIDX
------------------------------------------------------------------------------------------------------------------------ ----------
000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move
Held during online datafile move operation or cleanup 157

SQL> select file#, name from v$datafile;

FILE# NAME

---------- ------------------------------------------------------------------------------------------

1 +DATA/CLASSIC/DATAFILE/system.267.814717093

2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123

3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147

4 +DATA/CLASSIC/DATAFILE/users.258.814717205

5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN

---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------

KSQSTEXPL KSQSTEVIDX

------------------------------------------------------------------------------------------------------------------------ ----------

000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move

Held during online datafile move operation or cleanup 157

SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf';

Database altered.

SQL>

SQL> select file#, name from v$datafile;

FILE# NAME

---------- ------------------------------------------------------------------------------------------

1 /data/CLASSIC/system01.dbf

2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123

3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147

4 +DATA/CLASSIC/DATAFILE/users.258.814717205

5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305

SQL> select * from x$ksqst where indx=63;

ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN

---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------

KSQSTEXPL KSQSTEVIDX

------------------------------------------------------------------------------------------------------------------------ ----------

000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move

Held during online datafile move operation or cleanup 157

Controlfiles cannot be moved online yet. The other kind of files (temp and redo logs) off course can be moved easily by creating the new ones and deleting the old ones, as it was on pre-12c releases.

Cheers

Ludo

Oracle Database 12c: Multithreaded Execution (or how make processes decrease)

Posted on June 26, 2013 by Ludovico

Too many background processes

Oracle instances on Unix/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user process, but this is prehistory…). The background processes number has increased to support all the new features of Oracle, up to this new Oracle 12c release. On a simple database installation you’ll be surprised to have this output from a ps command (38 processes):

# ps -eaf | grep CLASSIC | grep -v grep

oracle 3582 1 0 21:59 ? 00:00:00 ora_pmon_CLASSIC 
oracle 3584 1 0 21:59 ? 00:00:00 ora_psp0_CLASSIC 
oracle 3590 1 4 21:59 ? 00:00:51 ora_vktm_CLASSIC 
oracle 3596 1 0 21:59 ? 00:00:00 ora_gen0_CLASSIC 
oracle 3599 1 0 21:59 ? 00:00:00 ora_mman_CLASSIC 
oracle 3608 1 0 21:59 ? 00:00:00 ora_diag_CLASSIC 
oracle 3612 1 0 21:59 ? 00:00:00 ora_dbrm_CLASSIC 
oracle 3616 1 0 21:59 ? 00:00:00 ora_dia0_CLASSIC 
oracle 3620 1 0 21:59 ? 00:00:00 ora_dbw0_CLASSIC 
oracle 3624 1 0 21:59 ? 00:00:04 ora_lgwr_CLASSIC 
oracle 3628 1 0 21:59 ? 00:00:00 ora_ckpt_CLASSIC 
oracle 3632 1 0 21:59 ? 00:00:00 ora_smon_CLASSIC 
oracle 3636 1 0 21:59 ? 00:00:00 ora_reco_CLASSIC 
oracle 3640 1 0 21:59 ? 00:00:00 ora_lreg_CLASSIC 
oracle 3644 1 0 21:59 ? 00:00:00 ora_rbal_CLASSIC 
oracle 3648 1 0 21:59 ? 00:00:00 ora_asmb_CLASSIC 
oracle 3652 1 0 21:59 ? 00:00:01 ora_mmon_CLASSIC 
oracle 3659 1 0 21:59 ? 00:00:00 ora_mmnl_CLASSIC 
oracle 3664 1 0 21:59 ? 00:00:00 ora_d000_CLASSIC 
oracle 3667 1 0 21:59 ? 00:00:00 ora_s000_CLASSIC 
oracle 3672 1 0 21:59 ? 00:00:00 ora_mark_CLASSIC 
oracle 3707 1 0 21:59 ? 00:00:01 ora_o000_CLASSIC 
oracle 3717 1 0 21:59 ? 00:00:01 ora_o001_CLASSIC 
oracle 3725 1 0 21:59 ? 00:00:00 ora_tmon_CLASSIC 
oracle 3729 1 0 21:59 ? 00:00:00 ora_tt00_CLASSIC 
oracle 3736 1 0 21:59 ? 00:00:00 ora_smco_CLASSIC 
oracle 3738 1 0 22:00 ? 00:00:00 ora_w000_CLASSIC 
oracle 3749 1 0 22:00 ? 00:00:00 ora_fbda_CLASSIC 
oracle 3751 1 0 22:00 ? 00:00:00 ora_aqpc_CLASSIC 
oracle 3757 1 0 22:00 ? 00:00:00 ora_qm02_CLASSIC 
oracle 3759 1 0 22:00 ? 00:00:00 ora_p000_CLASSIC 
oracle 3763 1 0 22:00 ? 00:00:00 ora_p001_CLASSIC 
oracle 3765 1 0 22:00 ? 00:00:00 ora_q002_CLASSIC 
oracle 3767 1 0 22:00 ? 00:00:00 ora_p002_CLASSIC 
oracle 3769 1 0 22:00 ? 00:00:00 ora_q003_CLASSIC 
oracle 3771 1 0 22:00 ? 00:00:00 ora_p003_CLASSIC 
oracle 3774 1 0 22:00 ? 00:00:00 ora_cjq0_CLASSIC 
oracle 3801 1 0 22:00 ? 00:00:02 ora_vkrm_CLASSIC

# ps -eaf | grep CLASSIC | grep -v grep

oracle 3582 1 0 21:59 ? 00:00:00 ora_pmon_CLASSIC

oracle 3584 1 0 21:59 ? 00:00:00 ora_psp0_CLASSIC

oracle 3590 1 4 21:59 ? 00:00:51 ora_vktm_CLASSIC

oracle 3596 1 0 21:59 ? 00:00:00 ora_gen0_CLASSIC

oracle 3599 1 0 21:59 ? 00:00:00 ora_mman_CLASSIC

oracle 3608 1 0 21:59 ? 00:00:00 ora_diag_CLASSIC

oracle 3612 1 0 21:59 ? 00:00:00 ora_dbrm_CLASSIC

oracle 3616 1 0 21:59 ? 00:00:00 ora_dia0_CLASSIC

oracle 3620 1 0 21:59 ? 00:00:00 ora_dbw0_CLASSIC

oracle 3624 1 0 21:59 ? 00:00:04 ora_lgwr_CLASSIC

oracle 3628 1 0 21:59 ? 00:00:00 ora_ckpt_CLASSIC

oracle 3632 1 0 21:59 ? 00:00:00 ora_smon_CLASSIC

oracle 3636 1 0 21:59 ? 00:00:00 ora_reco_CLASSIC

oracle 3640 1 0 21:59 ? 00:00:00 ora_lreg_CLASSIC

oracle 3644 1 0 21:59 ? 00:00:00 ora_rbal_CLASSIC

oracle 3648 1 0 21:59 ? 00:00:00 ora_asmb_CLASSIC

oracle 3652 1 0 21:59 ? 00:00:01 ora_mmon_CLASSIC

oracle 3659 1 0 21:59 ? 00:00:00 ora_mmnl_CLASSIC

oracle 3664 1 0 21:59 ? 00:00:00 ora_d000_CLASSIC

oracle 3667 1 0 21:59 ? 00:00:00 ora_s000_CLASSIC

oracle 3672 1 0 21:59 ? 00:00:00 ora_mark_CLASSIC

oracle 3707 1 0 21:59 ? 00:00:01 ora_o000_CLASSIC

oracle 3717 1 0 21:59 ? 00:00:01 ora_o001_CLASSIC

oracle 3725 1 0 21:59 ? 00:00:00 ora_tmon_CLASSIC

oracle 3729 1 0 21:59 ? 00:00:00 ora_tt00_CLASSIC

oracle 3736 1 0 21:59 ? 00:00:00 ora_smco_CLASSIC

oracle 3738 1 0 22:00 ? 00:00:00 ora_w000_CLASSIC

oracle 3749 1 0 22:00 ? 00:00:00 ora_fbda_CLASSIC

oracle 3751 1 0 22:00 ? 00:00:00 ora_aqpc_CLASSIC

oracle 3757 1 0 22:00 ? 00:00:00 ora_qm02_CLASSIC

oracle 3759 1 0 22:00 ? 00:00:00 ora_p000_CLASSIC

oracle 3763 1 0 22:00 ? 00:00:00 ora_p001_CLASSIC

oracle 3765 1 0 22:00 ? 00:00:00 ora_q002_CLASSIC

oracle 3767 1 0 22:00 ? 00:00:00 ora_p002_CLASSIC

oracle 3769 1 0 22:00 ? 00:00:00 ora_q003_CLASSIC

oracle 3771 1 0 22:00 ? 00:00:00 ora_p003_CLASSIC

oracle 3774 1 0 22:00 ? 00:00:00 ora_cjq0_CLASSIC

oracle 3801 1 0 22:00 ? 00:00:02 ora_vkrm_CLASSIC

If you have consolidated many databases without the pluggable database feature, you’ll end up to have several hundreds of processes even without users connected. But Oracle 12c now introduce the possibility to start an instance using multithreading instead of the traditional processes. This could lead to some optimizations due to the shared process memory, and reduced context switches overhead, I presume (need to test it).

Enabling the Multithreaded Execution

By default this feature is not enabled, so you have to set it explicitly:

SQL> alter system set threaded_execution=true scope=spfile;

System altered.

SQL>

SQL> alter system set threaded_execution=true scope=spfile;

System altered.

SQL>

And in parallel, you’ll need to add this line to the listener.ora:

DEDICATED_THROUGH_BROKER_listener=on

1	DEDICATED_THROUGH_BROKER_listener=on

After a restart, the instance will show only a bunch of processes:

# ps -eaf | grep CLASSIC | grep -v grep
oracle 4792 1 0 22:25 ? 00:00:00 ora_pmon_CLASSIC
oracle 4794 1 0 22:25 ? 00:00:00 ora_psp0_CLASSIC
oracle 4800 1 2 22:25 ? 00:00:01 ora_vktm_CLASSIC
oracle 4804 1 1 22:25 ? 00:00:00 ora_u004_CLASSIC
oracle 4810 1 7 22:25 ? 00:00:03 ora_u005_CLASSIC
oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC
oracle 4884 1 0 23:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

# ps -eaf | grep CLASSIC | grep -v grep

oracle 4792 1 0 22:25 ? 00:00:00 ora_pmon_CLASSIC

oracle 4794 1 0 22:25 ? 00:00:00 ora_psp0_CLASSIC

oracle 4800 1 2 22:25 ? 00:00:01 ora_vktm_CLASSIC

oracle 4804 1 1 22:25 ? 00:00:00 ora_u004_CLASSIC

oracle 4810 1 7 22:25 ? 00:00:03 ora_u005_CLASSIC

oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC

oracle 4884 1 0 23:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The remaining processes

So we have the Process Monitor (pmon), the Process Spawner (psp0), the Virtual Keeper of Time (vktm), the Database Writer (dbw0) and two new multithreaded processes (u004) and (u005). “U” can stand for User or Unified?

Where can I find the information on the other processes?

They still exist in the v$process view, thus leading to some confusion when talking about Oracle Processes with your sysadmins… The new EXECUTION_TYPE column show if the Oracle Process is executed as a thread or as an OS process, and the SPID let us know which process actually executes it.

  PID SPID      PNAME EXECUTION_
----- --------- ----- ----------
    2 4792      PMON  PROCESS   
    3 4794      PSP0  PROCESS   
    4 4800      VKTM  PROCESS   
    5 4804      GEN0  THREAD    
    6 4804      SCMN  THREAD    
   18 4804      LREG  THREAD    
   19 4804      RBAL  THREAD    
   20 4804      ASMB  THREAD    
   11 4804      DBRM  THREAD    
   14 4804      LGWR  THREAD    
   15 4804      CKPT  THREAD    
   16 4804      SMON  THREAD    
    7 4804      MMAN  THREAD    
   17 4810      RECO  THREAD    
   12 4810      DIA0  THREAD    
   10 4810      SCMN  THREAD    
    9 4810      DIAG  THREAD    
   25 4810      N000  THREAD    
   50 4810      Q002  THREAD    
   49 4810      W004  THREAD    
   21 4810      MMON  THREAD    
   22 4810      MMNL  THREAD    
   23 4810      D000  THREAD    
   24 4810      S000  THREAD    
   51 4810      Q003  THREAD    
   26 4810      MARK  THREAD    
   27 4810      W001  THREAD    
   28 4810            THREAD    
   29 4810            THREAD    
   30 4810      TMON  THREAD    
   31 4810      TT00  THREAD    
   32 4810      SMCO  THREAD    
   33 4810      FBDA  THREAD    
   34 4810      W000  THREAD    
   35 4810      AQPC  THREAD    
   36 4810      CJQ0  THREAD    
   37 4810      P000  THREAD    
   38 4810      P001  THREAD    
   39 4810      P002  THREAD    
   40 4810      P003  THREAD    
   41 4810      VKRM  THREAD    
   42 4810            THREAD    
   43 4810      O000  THREAD    
   45 4810      W002  THREAD    
   46 4810      QM02  THREAD    
   47 4810      W003  THREAD    
   13 4818      DBW0  PROCESS   
    8 4884            PROCESS   
    1                 NONE

PID SPID PNAME EXECUTION_

----- --------- ----- ----------

2 4792 PMON PROCESS

3 4794 PSP0 PROCESS

4 4800 VKTM PROCESS

5 4804 GEN0 THREAD

6 4804 SCMN THREAD

18 4804 LREG THREAD

19 4804 RBAL THREAD

20 4804 ASMB THREAD

11 4804 DBRM THREAD

14 4804 LGWR THREAD

15 4804 CKPT THREAD

16 4804 SMON THREAD

7 4804 MMAN THREAD

17 4810 RECO THREAD

12 4810 DIA0 THREAD

10 4810 SCMN THREAD

9 4810 DIAG THREAD

25 4810 N000 THREAD

50 4810 Q002 THREAD

49 4810 W004 THREAD

21 4810 MMON THREAD

22 4810 MMNL THREAD

23 4810 D000 THREAD

24 4810 S000 THREAD

51 4810 Q003 THREAD

26 4810 MARK THREAD

27 4810 W001 THREAD

28 4810 THREAD

29 4810 THREAD

30 4810 TMON THREAD

31 4810 TT00 THREAD

32 4810 SMCO THREAD

33 4810 FBDA THREAD

34 4810 W000 THREAD

35 4810 AQPC THREAD

36 4810 CJQ0 THREAD

37 4810 P000 THREAD

38 4810 P001 THREAD

39 4810 P002 THREAD

40 4810 P003 THREAD

41 4810 VKRM THREAD

42 4810 THREAD

43 4810 O000 THREAD

45 4810 W002 THREAD

46 4810 QM02 THREAD

47 4810 W003 THREAD

13 4818 DBW0 PROCESS

8 4884 PROCESS

1 NONE

What about the User processes?

Well, I’ve spawned 200 user processes with sqlplus, and got 200 threads:

SQL> select BACKGROUND, EXECUTION_TYPE, count(*)
2> from v$process group by background, EXECUTION_TYPE;

B EXECUTION_   COUNT(*)
- ---------- ----------
1 PROCESS             4
1 THREAD             34
  PROCESS             1
  NONE                1
  THREAD            200

SQL> select BACKGROUND, EXECUTION_TYPE, count(*)

2> from v$process group by background, EXECUTION_TYPE;

B EXECUTION_ COUNT(*)

- ---------- ----------

1 PROCESS 4

1 THREAD 34

PROCESS 1

NONE 1

THREAD 200

On the OS side, I’ve registered an additional process to distribute the load of the new user processes. Damn, I start to being confusional using the term “process” o_O

[oracle@luc12c01 ~]$ ps -eaf | grep CLASSIC | grep -v grep

oracle 4792 1 0 22:25 ? 00:00:01 ora_pmon_CLASSIC
oracle 4794 1 0 22:25 ? 00:00:01 ora_psp0_CLASSIC
oracle 4800 1 2 22:25 ? 00:03:28 ora_vktm_CLASSIC
oracle 4804 1 0 22:25 ? 00:00:08 ora_u004_CLASSIC
oracle 4810 1 0 22:25 ? 00:01:09 ora_u005_CLASSIC
oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC
oracle 4884 1 0 22:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 8083 1 1 23:50 ? 00:00:03 ora_u010_CLASSIC

[oracle@luc12c01 ~]$ ps -eaf | grep CLASSIC | grep -v grep

oracle 4792 1 0 22:25 ? 00:00:01 ora_pmon_CLASSIC

oracle 4794 1 0 22:25 ? 00:00:01 ora_psp0_CLASSIC

oracle 4800 1 2 22:25 ? 00:03:28 ora_vktm_CLASSIC

oracle 4804 1 0 22:25 ? 00:00:08 ora_u004_CLASSIC

oracle 4810 1 0 22:25 ? 00:01:09 ora_u005_CLASSIC

oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC

oracle 4884 1 0 22:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle 8083 1 1 23:50 ? 00:00:03 ora_u010_CLASSIC

On the session side however, all the user processes are DEDICATED.

SQL&gt; select server, count(*) from v$session group by server;

SERVER COUNT(*)
--------- ----------
DEDICATED 232

SQL> select server, count(*) from v$session group by server;

SERVER COUNT(*)

--------- ----------

DEDICATED 232

A huge side effect

By using the multithreaded execution, the operating system authentication doesn’t work.

[oracle@luc12c01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 10 01:14:17 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

[oracle@luc12c01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 10 01:14:17 2013

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

Unless Oracle will review it’s authentication mechanism in a future patchset, you’ll need to rely on the password file and use the password to connect to the instance as sysdba, even locally.

What about performance?

In theory, threads should be faster and with a lower footprint:

The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a boundthread creation.

http://www.princeton.edu/~unix/Solaris/troubleshoot/process.html

and

In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a thread from different process (because it requires more complicated process context switch).
http://en.wikipedia.org/wiki/Thread_switching_latency

In practice, I’ll do some tests and let you know! 🙂

What about the good old OS kill command to terminate processes?

Good question! Currently I have not found any references to an orakill command (that exists on Windows). Hope it will arrive soon!

Cheers

—

Ludo

Oracle Database 12c finally out!! First impressions

Posted on June 25, 2013 by Ludovico

After a long, long wait, Oracle finally announced the availability of his new generation database. And looking at the new features, I think it will take several months before I’ll learn them all. The impressive number of changes brings me back to the release 10gR1, and I’m not surprised that Oracle has waited so long, I still bet that we’ll find a huge amount of bugs in the first release. We need for sure to wait a first Patchset, as always, before going production.

Does ‘c’ stand for cloud?

While Oracle has developed this release with the cloud in mind, the first word that comes out of my mind is “consolidation”. The new claimed feature Pluggable Database (aka Oracle Multitenancy) will be the dream of every datacenter manager along with CloneDB (well, it was somehow already available on 11.2.0.2) and ASM Thin_provisioned diskgroups.

But yes, it’s definitely the best for clouds

Other features like Flex ASM, Flex Cluster, several new security features, crossplatform backups… let imagine how deeply we can work to make private, multi-tenant clouds.

First steps, what changes with a typical installation

The process for a traditional standalone DB+ASM installation is the same as the old 11gR2: You’ll need to install the Grid Infrastructure first (and then take advantage of the Oracle Restart feature) and subsequently the Database installation.

The installation documentation is complete as always and is getting quite huge as the Grid Infrastructure capabilities increment.

To meet most installation prerequisites, Oracle has prepared again an RPM that does the dirty work:

oracle-rdbms-server-12cR1-preinstall-1.0-3.el6.x86_64.rpm

Oracle suggests to use Ksplice and also explicitly recommends to use the deadline I/O scheduler (it has been longtime a best practice but I can’t remember it was documented officially).

The splash screen has become more “red” giving a colorful experience on the installation process. 😉

Once the GI is installed, the Database installation asks for many new OS groups: OSBACKUPDBA, OSDGDBA, OSKMDBA. This give you more possibilities to split administration duties, not specifying them will lead to the “old behavior”.

new_OSGROUPS

You can decide to use an ACFS filesystem for both the installation AND the database files (with some exceptions, e.g. Windows servers). So, you can take advantage of the snapshot features of ACFS for your data, provided that the performance is acceptable (I’ll try to test and blog more about this). You can use the feature Copy-On-Write to provide writable snapshot copies, directly embedding a special syntax inside the “create pluggable database” command. Unfortunately, Oracle has decided to deliver pluggable databases as an extra-cost option. :-/

The database creation with DBCA is even easier, you have an option for a very default installation, you can guess it uses templates with all options installed by default.

But the Hot topic is that you can create it as a “Container Database”. This is done by appending the keywords “enable pluggable database;” at the end of the create database command. The process will then put all the required bricks (creation of the pdb$seed database and so on), I’ll cover the topic in separate posts cause it’s the really biggest new feature.

You can still use advanced mode to have the “old style” database creation, where you can customize your database.

If you try to create only the scripts and run them manually (that’s my habit), you’ll notice that SQL scripts are not run directly within the opened SQL*Plus session, but they’re run from a perl script that basically suppresses all the output to terminal, giving the impression of a cleaner installation. IMO it could be better only if everything runs fine.

host perl /u01/app/oracle/product/12.1.0/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/CDBTEST/scripts -b catalog /u01/app/oracle/product/12.1.0/rdbms/admin/catalog.sql;

1	host perl /u01/app/oracle/product/12.1.0/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/CDBTEST/scripts -b catalog /u01/app/oracle/product/12.1.0/rdbms/admin/catalog.sql;

Finally, I’ll get something familiar, but with a brand new release number! 🙂

[oracle@luc12c01 ~]$ sqlplus sys/*****@classic as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu May 9 22:36:27 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@luc12c01 ~]$ sqlplus sys/*****@classic as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu May 9 22:36:27 2013

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

Stay tuned, I’ll write soon about some really interesting features of the new Oracle Database 12c!

Cheers

—

Ludo

Generating graphs massively from Windows Performance Counters logs

Posted on April 23, 2013 by Ludovico

Windows Performance Monitor is an invaluable tool when you don’t have external enterprise monitoring tools and you need to face performance problems, whether you have a web/application server, a mail server or a database server.

But what I don’t personally like of it is what you get in terms of graphing. If you schedule and collect a big amount of performance metrics you will likely get lost in adding/removing such metrics from the graphical interface.

What I’ve done long time ago (and I’ve done again recently after my old laptop has been stolen 🙁 ) is to prepare a PHP script that parse the resulting CSV file and generate automatically one graph for each metric that could be found.

Unfortunately, most of Windows Sysadmin between you will disagree that I’ve done this using a Linux Box. But I guess you can use my script if you install php inside cygwin. The other tool you need, is rrdtool, again I use it massively to resolve my graphing needs.

How to collect your data

Basically you need to create any Data Collector within the Performance Monitor that generates a log file. You can specify directly a CSV file (Log format: Comma separated) or generate a BLG file and convert it later (Log format: Binary). System dumps are not used, so if you use the standard Performace template, you can delete it from your collection.

Remember that the more counters you take, the more the graph generation will take. The script does not run in parallel, so it will use only one core. Generally:

(Time to complete) = (Num Counters) * (Num Samples) * (Speed factor)

1	(Time to complete) = (Num Counters) * (Num Samples) * (Speed factor)

Where (Speed factor) is depending on both the CPU speed and the disk speed because of the huge number of syncs required to update several thousands of files. I’ve tried to reduce the number of rrdupdates by queuing several update values in a single command line and I’ve noticed an important increase of performances, but I know it’s not enough.

Converting a BLG (binary) log into a CSV log

Just use the relog tool:

C:\PerfLogs\Admin\Perftest\LUDO_20130423-000002> <strong>relog "Performance Counter.blg" -f csv -o "Performance Counter.csv"</strong>

Input
----------------
File(s):
     Performance Counter.blg (Binary)

Begin:    23.4.2013 14:56:02
End:      23.4.2013 15:33:37
Samples:  452

100.00%

Output
----------------
File:     Performance Counter.csv

Begin:    23.4.2013 14:56:02
End:      23.4.2013 15:33:37
Samples:  452

The command completed successfully.

C:\PerfLogs\Admin\Perftest\LUDO_20130423-000002> <strong>relog "Performance Counter.blg" -f csv -o "Performance Counter.csv"</strong>

Input

----------------

File(s):

Performance Counter.blg (Binary)

Begin: 23.4.2013 14:56:02

End: 23.4.2013 15:33:37

Samples: 452

100.00%

Output

----------------

File: Performance Counter.csv

Begin: 23.4.2013 14:56:02

End: 23.4.2013 15:33:37

Samples: 452

The command completed successfully.

Generating the graphs

Transfer the CSV on the box where you have the php and rrdtool configured, then run:

[root@lucrac01 temp]# php process_l.php PerformanceCounter.csv

453--Creating rrd /root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.rrd

Creating rrd /root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.rrd
Creating rrd /root/temp/LUDO/IPv4/Fragmented_Datagrams_sec.rrd
Creating rrd /root/temp/LUDO/IPv4/Datagrams_sec.rrd

...

Creating rrd /root/temp/LUDO/Memory/Pages_Input_sec.rrd
Creating rrd /root/temp/LUDO/Memory/Pool_Paged_Resident_Bytes.rrd
Creating rrd /root/temp/LUDO/Memory/Write_Copies_sec.rrd

...

Creating rrd /root/temp/LUDO/PhysicalDisk_2_E__/Avg._Disk_sec_Transfer.rrd
Creating rrd /root/temp/LUDO/PhysicalDisk_1_D__/Avg._Disk_sec_Transfer.rrd
Creating rrd /root/temp/LUDO/PhysicalDisk_0_C__/Avg._Disk_sec_Transfer.rrd
----......

1.Generating Graph: /root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.png
rrdtool graph /root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.png --start "1366721762" --end "1366724017" --width 453 DEF:ds0=/root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.rrd:value:LAST:step=5 LINE1:ds0#0000FF:"IPv4\Datagrams Received Delivered/sec" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:" " COMMENT:" Maximum " GPRINT:ds0max:"%6.2lf" COMMENT:" Average " GPRINT:ds0avg:"%6.2lf" COMMENT:" Minimum " GPRINT:ds0min:"%6.2lf"
534x177
2.Generating Graph: /root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.png
rrdtool graph /root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.png --start "1366721762" --end "1366724017" --width 453 DEF:ds0=/root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.rrd:value:LAST:step=5 LINE1:ds0#0000FF:"IPv4\Datagrams Received Unknown Protocol" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:" " COMMENT:" Maximum " GPRINT:ds0max:"%6.2lf" COMMENT:" Average " GPRINT:ds0avg:"%6.2lf" COMMENT:" Minimum " GPRINT:ds0min:"%6.2lf"
534x177
...

[root@lucrac01 temp]# php process_l.php PerformanceCounter.csv

453--Creating rrd /root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.rrd

Creating rrd /root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.rrd

Creating rrd /root/temp/LUDO/IPv4/Fragmented_Datagrams_sec.rrd

Creating rrd /root/temp/LUDO/IPv4/Datagrams_sec.rrd

...

Creating rrd /root/temp/LUDO/Memory/Pages_Input_sec.rrd

Creating rrd /root/temp/LUDO/Memory/Pool_Paged_Resident_Bytes.rrd

Creating rrd /root/temp/LUDO/Memory/Write_Copies_sec.rrd

...

Creating rrd /root/temp/LUDO/PhysicalDisk_2_E__/Avg._Disk_sec_Transfer.rrd

Creating rrd /root/temp/LUDO/PhysicalDisk_1_D__/Avg._Disk_sec_Transfer.rrd

Creating rrd /root/temp/LUDO/PhysicalDisk_0_C__/Avg._Disk_sec_Transfer.rrd

----......

1.Generating Graph: /root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.png

rrdtool graph /root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.png --start "1366721762" --end "1366724017" --width 453 DEF:ds0=/root/temp/LUDO/IPv4/Datagrams_Received_Delivered_sec.rrd:value:LAST:step=5 LINE1:ds0#0000FF:"IPv4\Datagrams Received Delivered/sec" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:" " COMMENT:" Maximum " GPRINT:ds0max:"%6.2lf" COMMENT:" Average " GPRINT:ds0avg:"%6.2lf" COMMENT:" Minimum " GPRINT:ds0min:"%6.2lf"

534x177

2.Generating Graph: /root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.png

rrdtool graph /root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.png --start "1366721762" --end "1366724017" --width 453 DEF:ds0=/root/temp/LUDO/IPv4/Datagrams_Received_Unknown_Protocol.rrd:value:LAST:step=5 LINE1:ds0#0000FF:"IPv4\Datagrams Received Unknown Protocol" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:" " COMMENT:" Maximum " GPRINT:ds0max:"%6.2lf" COMMENT:" Average " GPRINT:ds0avg:"%6.2lf" COMMENT:" Minimum " GPRINT:ds0min:"%6.2lf"

534x177

...

Now it’s done!

The script generate a folder with the name of the server (LUDO in my example) and a subfolder for each class of counters (as you see in Performance Monitor).

Inside each folder you will have a PNG (and an rrd) for each metric.

Important: The RRD are generated with a single round-robin archive with a size equal to the number of samples. If you want to have the rrd to store your historical data you’ll need to modify the script. Also, the size of the graph will be the same as the number of samples (for best reading), but limited to 1000 to avoid huge images.

Future Improvements

Would be nice to have a prepared set of graphs for standard graphs with multiple metrics (e.g. CPU user, system and idle together) and additional lines like regressions…

Download the script: process_l_php.txt and rename it with a .php extension.

Hope you’ll find it useful!

Cheers

Ludo

ORA-00600 and user identified by values ”

Posted on April 5, 2012 by Ludovico

With rel. 10.2.0.5 was possibile to do this:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
...

SQL> create user foo identified by values '';

User created.

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

...

SQL> create user foo identified by values '';

User created.

With 11.2.0.3 an ORA-00600 is raised.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
...

sipes1 SQL> create user foo identified by values '';

create user foo identified by values ''
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzsviver:1], [], [], [], [], [], [], [], [], [], [], []

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

...

sipes1 SQL> create user foo identified by values '';

create user foo identified by values ''

ERROR at line 1:

ORA-00600: internal error code, arguments: [kzsviver:1], [], [], [], [], [], [], [], [], [], [], []

Mass datafile resizing

Posted on March 10, 2012 by Ludovico

Recently I needed to extend many datafiles on a database with more than 500 tablespaces because a lot of tablespaces were reaching the critical threshold.
Autoextend was not an option due to a bug I encountered on 10gR2 RAC on ASM and AIX.

The solution was the following script: it generates statements to autoextend datafiles with usage over a defined threshold (the “80” in the where clause) to low down the percentage below another defined threshold (the “75” in the select clause).

SELECT 'alter database datafile '''||f.file_name||
''' resize '||round(ceil(bytes/1024/1024/75*t.pct_used+50),-2)||'M;'
 FROM
   dba_data_files f, (
   SELECT  a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,
          round((a.total-nvl(b.free,0))*100/a.total) pct_used
  FROM    (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree
          FROM dba_free_space GROUP BY tablespace_name) b,
          (SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total
          FROM dba_data_files GROUP BY tablespace_name) a
  WHERE a.tablespace_name = b.tablespace_name (+)) t  WHERE t.tablespace_name=f.tablespace_name
  AND t.pct_used>80;

SELECT 'alter database datafile '''||f.file_name||

''' resize '||round(ceil(bytes/1024/1024/75*t.pct_used+50),-2)||'M;'

FROM

dba_data_files f, (

SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,

round((a.total-nvl(b.free,0))*100/a.total) pct_used

FROM (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree

FROM dba_free_space GROUP BY tablespace_name) b,

(SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total

FROM dba_data_files GROUP BY tablespace_name) a

WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name

AND t.pct_used>80;

Prior to extend it’s possible to show how much space is required to do this mass resizing:

 SELECT sum(mb_new-mb_old) FROM (
  SELECT t.tablespace_name, f.file_name, bytes/1024/1024 mb_old, round(ceil(bytes/1024/1024/75*t.pct_used+50),-2) mb_new FROM
 dba_data_files f, (
 SELECT  a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,
        round((a.total-nvl(b.free,0))*100/a.total) pct_used
FROM    (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree
        FROM dba_free_space GROUP BY tablespace_name) b,
        (SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total
        FROM dba_data_files GROUP BY tablespace_name) a
WHERE a.tablespace_name = b.tablespace_name (+)) t  WHERE t.tablespace_name=f.tablespace_name
AND t.pct_used>80);

SELECT sum(mb_new-mb_old) FROM (

SELECT t.tablespace_name, f.file_name, bytes/1024/1024 mb_old, round(ceil(bytes/1024/1024/75*t.pct_used+50),-2) mb_new FROM

dba_data_files f, (

SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,

round((a.total-nvl(b.free,0))*100/a.total) pct_used

FROM (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree

FROM dba_free_space GROUP BY tablespace_name) b,

(SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total

FROM dba_data_files GROUP BY tablespace_name) a

WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name

AND t.pct_used>80);

Dog eat Dog… Oracle deletes itself by mistake!

Posted on July 21, 2011 by Ludovico

While implementing the backup on a new DB inherited from a customer, I scheduled our standard backup “type disk” procedure through rman, on Windows.
The morning after I saw that the “delete obsolete” tried to delete ALL CURRENT DATAFILES!!

i criteri di conservazione RMAN verranno applicati al comando i criteri di conservazione RMAN sono impostati su una ridondanza 1 canale allocato: ORA_DISK_1 canale ORA_DISK_1: sid=29 devtype=DISK Eliminazione dei seguenti backup e copie obsoleti: Tipo Chiave Ora fine Nome file/Handle -------------------- ------ ------------------ -------------------- Set di backup 917 28-GIU-11 ... Set di backup 927 29-GIU-11 Backup piece 1005 29-GIU-11 H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629 Copia file di dati 14 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF Copia file di dati 16 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF Copia file di dati 17 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF Copia file di dati 18 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF Copia file di dati 19 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF Copia file di dati 20 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF Copia file di dati 21 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF Copia file di dati 22 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF Copia file di dati 23 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA Copia file di dati 24 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA Copia file di dati 25 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\OEM_REPOSITORY1.ORA Copia file di dati 26 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF Copia file di dati 27 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF backup piece eliminata ... backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110628-02 recid=990 stamp=755031582 backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110629-00 recid=1002 stamp=755130872 backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\CTL_20110629 recid=1004 stamp=755130883 backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629 recid=1005 stamp=755130885 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of delete command on ORA_DISK_1 channel at 06/29/2011 22:34:55 ORA-19584: file E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF già in usoRecovery Manager ha terminato.

That’s because all current datafiles were registered into recovery catalog as backup copy. With a recovery redundancy of 1, all datafiles were set as obsolete! But since it’s windows, a delete command doesn’t delete datafiles if they are already in use. What it was on unix? We had just luck!

Then we had to uncatalog all copies.

RMAN> list copy;


la specifica non corrisponde a nessuno dei log di archivio del Recovery Catalog

Lista di copie del file di dati Chiave SCN Ckp file S Ora di completamento Nome Ora ckp ------- ---- - -------------------- ---------- -------------------- ---- 26 1 X 29-NOV-10 18535127593 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF 27 2 X 29-NOV-10 18535127762 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF 14 3 X 29-NOV-10 18535122625 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF 16 4 X 29-NOV-10 18535123721 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF 17 5 X 29-NOV-10 18535124423 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF 18 6 X 29-NOV-10 18535124439 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF 19 7 X 29-NOV-10 18535124453 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF 20 8 X 29-NOV-10 18535124554 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF 21 9 X 29-NOV-10 18535125790 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF 22 10 X 29-NOV-10 18535125874 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF 23 11 X 29-NOV-10 18535125887 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA 24 12 X 29-NOV-10 18535126750 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA 25 13 X 29-NOV-10 18535127211 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\OEM_REPOSITORY1.ORA

RMAN> change copy of datafile 1..N uncatalog;

copia non catalogata del file di dati filename di copia del file di dati=E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF recid=14 stamp=736336991 Oggetti 1 non catalogati ...

until no “obsolete” current datafile were reported!

RMAN> report obsolete;

i criteri di conservazione RMAN verranno applicati al comando i criteri di conservazione RMAN sono impostati su una ridondanza 1 non sono stati trovati backup obsoleti

Lesson learned: never schedule delete obsolete without actually checking what could be deleted!

10gR2 RAC hangs and “KSV master wait”

Posted on June 3, 2011 by Ludovico

We recently migrated a customer’s 10gR2 RAC on AIX6.1 from GPFS+HACMP to a “basic” Clusterware with datafiles over ASM.
After (many) problems related to various installation bugs (the list of requirements for AIX is very long, incomplete and requires many one-off patches to complete), we had a problem during an import of a new schema: the import hung with no apparent wait events. We found that the event it was waiting for was classified as ‘Idle’:

SQL&gt; select sid, username, status, event, wait_class, program from gv$session;

 SID USERNAME   STATUS   EVENT                WAIT_CLASS PROGRAM
---- ---------- -------- -------------------- ---------- ----------------------------------------
...
 135 SYS        ACTIVE   KSV master wait      Idle       imp@trndcsaixdb1 (TNS V1-V3)
...

SQL> select sid, username, status, event, wait_class, program from gv$session;

SID USERNAME STATUS EVENT WAIT_CLASS PROGRAM

---- ---------- -------- -------------------- ---------- ----------------------------------------

...

135 SYS ACTIVE KSV master wait Idle imp@trndcsaixdb1 (TNS V1-V3)

...

The on ASM instance:

SQL&gt; @wait10g

SID USERNAME   MACHINE         PROGRAM              EVENT                  SEQ#           P1  S_IN_WAIT STATE               STATUS
---- ---------- --------------- -------------------- -------------------- ------ ------------ ---------- ------------------- --------
201 SYS        trndcsaixdb1    oracle@trndcsaixdb1  enq: FA - access file      6   1178664965        744 WAITING             ACTIVE

SQL> @wait10g

SID USERNAME MACHINE PROGRAM EVENT SEQ# P1 S_IN_WAIT STATE STATUS

---- ---------- --------------- -------------------- -------------------- ------ ------------ ---------- ------------------- --------

201 SYS trndcsaixdb1 oracle@trndcsaixdb1 enq: FA - access file 6 1178664965 744 WAITING ACTIVE

The problem was related to datafile resize (we use autoextend) and according to MOS, we were encountering a bug:

Bug 11712836: RESIZING DATAFILE HUNG WAITING FOR KSV MASTER WAIT IN RAC

Shutting down one instance solved the problem. Now we have to avoid autoextend……. We never encountered this bug in many 10.2.0.4 rac installations.