Configuring Enterprise Manager Database Express in Oracle 12c

In my setup I have a container database (CDB) named DB01 and one pluggable database (PDB) named PDB01. …

I want to set up EM Database Express for both my container database CDB and my pluggable database PDB. …

Setting up EM Database Express for my CDB.
The default URL’s for EM Database Express are http://localhost:5510/em and https://localhost:5500/em. …

The current EM DB Express Port configuration is checked with below SQL: …

sys@cdb$root:db01> select dbms_xdb_config.gethttpsport() from dual;


1 row selected.

A value of 0 means EM Database Express is not configured. To configure EM Database Express use DBMS_XDB_CONFIG.SETHTTP(S)PORT as shown below. …

sys@cdb$root:db01> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

PL/SQL procedure successfully completed.

sys@cdb$root:db01> exec DBMS_XDB_CONFIG.SETHTTPPORT(5510);

PL/SQL procedure successfully completed.
sys@cdb$root:db01> select
  2     dbms_xdb_config.gethttpport()
  3  ,  dbms_xdb_config.gethttpsport()
  4  from dual;

----------------------------- ------------------------------
                         5510                           5500

1 row selected.

No go to http://localhost:5510/em or https://localhost:5500/em to log on to EM Database Express. …

I get ‘error’ message “Alternate HTML content should be placed here. This content requires the Adobe Flash Player. Get Flash” …

Click the link to install …

Read more →

while true do heredoc golden gate info all

While monitoring a Golden Gate Extract ‘catching up’ I got tired of typing ‘info all’ and a tail -f on the report rpt file gives me more information than I want. I’m just interseted in the status of my Extract and my ‘Lag at checkpoint’. …

Shell snippet below echoes ggsci info all output to my screen every 10 (sleep 10) seconds. …

while true
  ./ggsci <<EOF
info all
  sleep 10

Use Ctrl^C to break out of the while loop …

GGSCI (********) 1>
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

EXTRACT     RUNNING     EL****      13:45:11      00:00:08
EXTRACT     RUNNING     EP****      47:42:56      00:00:05

GGSCI (********) 2>
Oracle GoldenGate Command Interpreter for Oracle
Version 17547423 OGGCORE_11.
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Oct 22 2013 09:35:16

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (********) 1>
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

EXTRACT     RUNNING     EL****      13:43:13      00:00:10
Read more →

awk remove file with filenumber less then

I’m restoring a large amount of oracle archived redo logs into one directory. …

The archived redolog filenames have the following names ‘1_116133_820730017.dbf’ – ‘1_{log_sequence_number}_820730017.dbf’. The log sequence number is reflected in the filename, it is the second number pair – between the first and the second underscore. …

So it is like: …


# log sequence number is 116133

Newest restored archived logfiles ( log seq number like 119,…) ; …

$ ls -lat *.dbf  | head -n2
-rw-r-----    1 oracle   oinstall   15843840 Dec 06 13:34 1_119570_820730017.dbf 
-rw-r-----    1 oracle   oinstall   10368512 Dec 06 13:34 1_119571_820730017.dbf

Oldest restored archived logfiles ( log seq number like 116,…) ; …

$ ls -lart *.dbf  | head -n2
-rw-r-----    1 oracle   oinstall   30214656 Dec 06 12:06 1_116134_820730017.dbf
-rw-r-----    1 oracle   oinstall   30930944 Dec 06 12:06 1_116133_820730017.dbf

Now I want to remove all restored archived logs that have a log sequence number less than 118133. I do not need them anymore. …

The awk snippet below does that;
It extracts the log sequence number into an awk variable ‘seq_no’, does a simple if then comparison and echos the rm command if seq_no is less then 118133. …

awk 'BEGIN { FS = "_"} { seq_no = $2; if ( seq_no < 118133 ) print "rm ./" $0; }'

Output of the ‘ls awk ‘: …

# oldest files that can me removed
Read more →

RMAN cheatsheet


archivelogRecordSpecifier …

# List archives by log sequence

# List archives with NEXT_TIME (see <a href="">v$archived_log.next_time</a>) is  greater then FROM TIME - last day (24 hours)

# List archives between two dates 
  FROM TIME "TO_DATE('09/05/2013 22:00:00', 'MM/DD/YYYY hh24:mi:ss')" 
  UNTIL TIME "TO_DATE('09/06/2013 04:00:00', 'MM/DD/YYYY hh24:mi:ss')";

Sometimes you need to autonomously restore a set of archives – in my case I need them to get an abended Golden Gate Extract process in business again. …

2013-09-06 10:24:19  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2013-09-06 10:24:19  INFO    OGG-01513  Positioning to Sequence 3389, RBA 475152, SCN 1390.3913669324.


2013-09-06 10:25:34  ERROR   OGG-00446  Could not find archived log for sequence 3389 thread 1 under alternative or default destinations. SQL <SELECT  name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO' AND name not like '+%' AND standby_dest = 'NO' >. Last alternative log tried /tmp/_temp.archives/<database_name>/archivelog/1_3389_819925872.dbf., error
retrieving redo file name for sequence 3389, archived = 1, use_alternate = 0Not able to establish initial position for sequence 3389, rba 475152.

2013-09-06 10:25:34  ERROR   OGG-01668  PROCESS ABENDING.

OK, apparently Golden Gate need the archives from log sequence number 3389 and ‘above’. …

Command below restores all archives from 3389 until ‘now’. …

Read more →

Add simple DDLERROR exception in Replicat to ingore certain ORA errors

Short braindump about adding a DDLERROR error exception in your Golden Gate REPLICAT configuration. …

In this scenario the goal is to prevent my replicat from abending over an ‘ORA-01432: public synonym to be dropped does not exist ‘ error. …

Check statuses for all OGG processes

GGSCI () 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

REPLICAT    ABENDED     REPL1       00:00:06      96:57:06

%^(&* My REPL1 Replicat has been down for more than four days… Luckily it is a test site. …

Look up the error
First thing is finding the error that caused the REPLICAT to abend in the report file of the REPL1 Replicat; …

GGSCI () 4> view report REPL1 
2013-08-28 17:48:28  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [1432], ORA-01432: public synonym to be dropped does not exist SQL drop public synonym <synonym_name>  /* GOLDENGATE_DDL_REPLICATION */], no error handler present.

OK, the error is obvious. A public synonym did exist on the Extract database but NOT in the Replicat database. So, Golden Gate has captured the ‘drop public synonym’ statement from the Extract database logs and it expects to successfully execute this statement on the Replicat database.
This failed because, apparently, this public synonym is not present in the Replicat database. The result …

Read more →

Script to get size of generated archived logfiles in certain timeframe

I’m testing a import datapump load procedure and I need to know the approximate size of generated archives that this load will generate to determine the size needed for our database recoveryarea. …

Script below queries the v$archived_log view for this information. …

Please note that I have tested this with ONE and only ONE active / valid log_archive_dest destination. I THINK that when there is more than one active log_archive_dest active these archived log entries will be written to v$archived_log as well and the query will not ‘work’ anymore. …

SQL> SELECT dest_id, status, dest_name, destination
  2    FROM v$archive_dest_status
  3   WHERE status <> 'INACTIVE'
  4  /
---------- --------- ------------------ --------------------------------------------------
         2 VALID     LOG_ARCHIVE_DEST_2 /ora/recoveryarea/overflow/ 
col archives_m for 999,999,999,999,999

select round ( sum ( blocks * block_size ) /1024 /1024 )  archives_m
  from v$archived_log
 where first_time > to_date( '2013-AUG-30-090000', 'YYYY-MON-DD-HH24MISS')
   and first_time < SYSDATE

SQL> select round ( sum ( blocks * block_size ) /1024 /1024 )  archives_m
  2    from v$archived_log
  3   where first_time > to_date( '2013-AUG-30-090000', 'YYYY-MON-DD-HH24MISS')
  4     and first_time < SYSDATE
  5  /
Read more →

Import / export datapump using FLASHBACK_TIME or FLASHBACK_SCN ( and NETWORK_LINK ) fails with ORA-31693, ORA-01031

I’m writing a shellscript that loads a remote schema into my database using impdp network_link – all goes well. Only once I add the FLASHBACK_TIME I got error below; …

ORA-31693: Table data object "<TABLE_OWNER>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-02063: preceding line from <DATABASE_NAME>

Here is my impdp command; …

  ${ORACLE_HOME}/bin/impdp                                                \
    JOB_NAME=${_IMPDP_JOBNAME}                                            \
    NETWORK_LINK=${OPT_DB_SOURCE}                                         \
Read more →

Hello Oracle Golden Gate – Super simple Golden Gate monitor shellscript

We are setting up a test site for oracle golden gate. I missed a extract process that was abended so I wrote this little script to send me an email once one ore more process in Golden Gate show status of anbended – or better when ‘grep ABENDED’ returns anything. …

Nothing shocking really – it’s just grep. …


# constants
# -------------------------------------------------------------------------

# no more editting pas this point
# -------------------------------------------------------------------------

# for some utter reason you need to cd to Golden Gate home or else i got
# Could not load program /ora/product/goldengate/112101/ggsci:
#         Dependent module libgglog.a could not be loaded.
# Could not load module libgglog.a.
# System error: No such file or directory


# start golden gate with eof and execute 'info all' and 
golden_gate_infoall=`${GOLDENGATE_HOME}/ggsci << EOF
info all

# grep for ABENDED string
golden_gate_infoall=`echo $golden_gate_infoall | grep ABENDED`

if [[ "" = ${golden_gate_infoall} ]] ;
  exit 0
  # found 'ABENDED' - send email
  echo $golden_gate_infoall | mailx -s "One or more Golden Gate process abended at $(hostname)" ${MAILTO}
  exit 2
# set permission
$ chmod u+x

# execute the script

# query the exit code
$ echo $?

Example email

Read more →

Removing unused columns online using DBMS_REDEFINITION

We need to drop a number of unused columns on a fairly large (and important) table in our database. …

Example below uses DBMS_REDEFINITION in combination with a ‘Create table as select’ ( CTAS ) to achieve this.
I will create the interin table using a CTAS copy – Oracle will not copy the unused columns in a CTAS create table statement. After that we copy all the table dependents (indexes, grants, constraints) etc to the interim table.
Finally we start and finish the redefinition using DBMS_REDEFINITION.START_REDEF_TABLE and DBMS_REDEFINITION.FINISH_REDEF_TABLE. …

Redefining Tables Online

Here is a complete test on my local database; …

Remove old DBMS_REDEFINITION task (I ran my test a couple of times ) …


PL/SQL procedure successfully completed.

Create test table demo with three columns and populate it with some data …

SQL> create table demo ( k int primary key, l int, m int)
  2  /

Table created.

SQL> insert into demo select object_id, object_id, object_id from all_objects
  2  /

71058 rows created.

Now let’s add some ‘dependant’ objects on demo table like an index, grant, and a trigger – I want to see how/if dbms_redefinition.copy_table_dependents will work ok. …

SQL> create index idx_demo on demo (m)
  2  /

Index created.

SQL> grant select on demo to system
  2  /

Grant succeeded.

SQL> create trigger trg_demo
  2        BEFORE INSERT OR UPDATE on demo
  3        FOR EACH ROW
  4  begin
Read more →


The problem – issueing CREATE_ACL for a new ACL list results in ORA-30992. …

  3      acl          => 'acl_********.xml', 
  4      description  => 'Email ACL (*.*.nl + *.*.nl)',
  5      principal    => 'SYSTEM',
  6      is_grant     => TRUE, 
  7      privilege    => 'connect',
  8      start_date   => SYSTIMESTAMP,
  9      end_date     => NULL);
 10    COMMIT;
 11  end;
 12  /
ERROR at line 1:
ORA-30992: error occurred at Xpath /acl/ace[1][@start_date]
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 252
ORA-06512: at line 2

The solution:
run the same code on the database server directly – no time to investigate as to why I get this error on remote (dba) connection… …

Read more →

init parameter plsql_code_type braindump

Some code for plsql_code_type below; …

Current setting

select *
from v$parameter 
where name = 'plsql_code_type'

breakdown per plsql_code_type

col owner for a18
col plsql_code_type for a18

select owner, plsql_code_type, count(*)
from dba_plsql_object_settings 
group by owner, plsql_code_type
order by owner, plsql_code_type

compile schema(s)
After changing init.ora paramter …

  for i in ( select * from dba_objects where owner like 'REMITJE' )
  execute immediate 'begin dbms_utility.compile_schema( :1); end;'
     using i.owner;
  end loop;

Read more →

Using Email (utl_smtp) in Oracle 11g (results in ORA-24247) – Network Access Control List (ACL).

Using UTL_SMTP to send email from your oracle database has changed in Oracle 11g. Or – to be more precise – accessing the remote network has changed. …

Initializing a remote connection using UTL_SMTP will typically result in error below if you did not configure your database network access lists (ACL) for the user involved. …

-1. the error
=== …

  2    v_mailsever_host VARCHAR2(30) := 'mail.<domain>.nl';
  3    v_mailsever_port PLS_INTEGER  := 25;
  4    l_mail_conn  UTL_SMTP.CONNECTION;
  5  BEGIN
  6    l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
  7  END;
  8  /
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at line 6

SQL> sho user

“wat is dit nu weer !!!”

Allright – in Oracle 11g – you have to configure (grant) each and every network access point using so called Access Control Lists (ACL’s). Obviously user SCOTT is not configured to access the network point it’s trying to access here. …

The docs are here in ‘Managing Fine-Grained Access to External Network Services’ – // …

-0. check for current existing ACL’s
Let’s check out all currently presentAccess Control Lists in the database;
=== …

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

no rows selected


Allright – none present – let’s create a new network access list using DBMS_NETWORK_ACL_ADMIN …

— privilege – Use ‘connect’ for UTL_TCP, UTL_SMTP, UTL_MAIL and …

Read more →

Script to find duplicate datafile names

Whenever you want to duplicate (copy/scp) a database and place all datafiles into one single directory you always have to check if there are no duplicate filenames. …

Eg like; …

/m002/oradata/oracle_sid/filename1.dbf …

scp /m*/oradata/oracle_sid/*dbf remote_host:/m001/oradata/dup_oracle_sid/. …

SQL> select substr ( file_name, instr( file_name, '/', -1)) file_name, count(*)
  2  from dba_data_files
  3  group by substr ( file_name,instr( file_name, '/', -1)) 
  4  having count(*) > 1
  5  /

FILE_NAME                               COUNT(*)
------------------------------------- ----------
/****p1i005ds10.dbf                            2
/****p1i005ds11.dbf                            2


And to get the full paths – rows from dba_data_files: …

SQL> select *
  2  from dba_data_files
  3  where substr ( file_name, instr( file_name, '/', -1)) in (
  4     select file_name from (
  5        select substr ( file_name, instr( file_name, '/', -1)) file_name, 
  6        count(*)
  7        from dba_data_files
Read more →

shutdown immediate taking ages (two days) to complete – or – ‘No, I will NOT enlarge the UNDO tablespace’ – or – patience …

Third party asked for a restart of one of our testing databases that was having issues. Although I’m always a bit skeptical on simple restart requests I think in this case it actually turned out to shed some light; one huge transaction of a ‘runaway process’ turned out to virtually block all other operations … …

The shutdown immediate took almost two days to complete. I was able to perfectly monitor the progress of the shutdown operation; in the alertlog there was a redo log file application every 50 minutes.
This way I could see that the shutdown immediate was not hanging – oracle was actually pretty busy in rolling back this large transaction … …

I ruled out the known issue that can sometimes arise with active client (‘LOCAL=no’) processes that sometimes block a shutdown immediate to progress. …

I’m glad I had advised the project team against simply enlarging the UNDO tablespace (larger than it’s production equivalent) prior to these issues in this UAT acceptance database … …

All, …

This morning (Saturday 22 September 2012) at 8:35 AM the shutdown immediate command for the ******** database ******** (********) completed. (see alertlog below). …

The shutdown took almost 48 hours to complete.

Thu Sep 20 12:33:24 2012
Shutting down instance: further logons disabled
Sat Sep 22 08:35:30 2012

I noticed some Logwriter tracefiles this morning at around the same time of the ‘close normal’ and also the database (instance) seemed in a unstable state – I was not able to spawn an internal sysdba connection …

Read more →

Trace you own session using dbms_system / set events

For debugging … Make sure the session has a direct execute grant on dbms_system package. …

Level 12 makes sure to include the waits and bind data. dbms_system …

for i in (select sid, serial# from v$session where audsid=SYS_CONTEXT('USERENV','SESSIONID'))
	sys.dbms_system.set_ev ( si => i.sid, se => i.serial#, ev => 10046, LE => 12, nm => '');
end loop;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Read more →

ORA-01114: IO error writing block to file – returns wrong file number – can’t find file

I was notifed by developer team that they get a whole lot of ORA-01114 errors. …

********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:2012-07-26 <!--more-->19:02:32.484306*:9C82F5B9:sql_mon_query:keswx.c@3633:keswxWriteEndInfoToStream(): done writing error info: code=1114 fac=ORA msg=ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)

Of course the first thing to do is find the file `data | temp _file` involved but oddly I get ‘no rows returned’ when querying dba_data_files & dba_temp_files ; …

SQL> select file_id, file_name from dba_data_files where file_id=201
  2  union 
  3  select file_id, file_name from dba_temp_files where file_id=201
  4  /

no rows selected

¿ qué ? …

What’s that – no file with file_id 201 ??? Then why is Oracle error referring to file 201? …

Luckily I found this excellent article which in my own words says that: …

Whenever the file mentioned in errors like ‘ORA-01114’ is a tempfile the file id is `file_id` + `the value of the DB_FILES init parameter`. …

Or; …

IF file_id > DB_FILE THEN
    file_type := 'tempfile';
    file_id := DB_FILES - file_id ;

In our database, with DB_FILES set to 200, I need to substract 200 from the value in the ORA error message to get the correct file_id…
Makes no sense to me. …

Read more →

Overcome “ERROR: ‘${ORACLE_HOME}/agent11g’ subdirectory already exists in Oracle Inventory. Specify another home location.” when doing a silent install in a non-empty Oracle home.

While testing our silent Oracle Grid agents installs I ran into error below when (re-)installing into a non-empty Oracle home directory. …

$ ./runInstaller -silent -responseFile /home/oracle/additional_agent.rsp -invPtrLoc /var/opt/oracle/oraInst.loc
ERROR: Error:"${ORACLE_HOME}/agent11g" subdirectory already exists in Oracle Inventory. Specify another home location.

This error can easily be overcome by manually editting the ‘inventory.xml file in your ${ORACLE_INVENTOY}/ContentsXML direcotry. …

Find the directory of your Oracle inventory – locatino is defined in the pointerfile oraInst.loc (which on most os-es resides in /var/opt/oracle/oraInst.loc) …

Go to the Oracle inventory root directory and cd to the ‘ContentsXML’ directory, …

Backup your existing inventoy xml file, …

Remove the entry <HOME></HOME> entry in inventory.xml for the Oracle home where you want to re-install. …

Before edit: …

$ cat inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2010, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<HOME NAME="OraDb10g_home1" LOC="********************" TYPE="O" IDX="5"/>
<HOME NAME="agent11g1" LOC="${ORACLE_HOME}/agent11g" TYPE="O" IDX="6"/>
<HOME NAME="OUIPlaceHolderDummyHome1" LOC="/ora/OraPlaceHolderDummyHome_1" TYPE="O" IDX="3" REMOVED="T"/>

After edit: …

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2010, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<HOME NAME="OraDb10g_home1" LOC="/ora/product/db/10204" TYPE="O" IDX="5"/>
<HOME NAME="OUIPlaceHolderDummyHome1" LOC="/ora/OraPlaceHolderDummyHome_1" TYPE="O" IDX="3" REMOVED="T"/>

And off you go – reinstalling in your existing Oracle Home is not a problem anymore – because – Oracle deos not know this is an Oracle Home anymore … …

This was carried out on a test system – seems totally harmless to me in this scenario but still use at your own risk. …

Read more →

SQL query to show tables vs indexes in your tablespaces

SQL query below displays a tables / indexes breakdown of your tablespaces. …

SELECT tablespaces.tablespace_name, 
			NVL( tables, 0) as tables, 
			NVL( indexes, 0) as indexes
FROM 	dba_tablespaces tablespaces
				SELECT COUNT(*) tables, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'TABLE' 
				GROUP BY tablespace_name ) tables
		ON tablespaces.tablespace_name = tables.tablespace_name
				SELECT COUNT(*) indexes, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'INDEX' 
				GROUP BY tablespace_name ) indexes
		ON tablespaces.tablespace_name = indexes.tablespace_name

Sample output; …

SQL> /
------------------------------ ---------- ----------
***************                         5          3
*******                                11         28
************                           56        118
************                           64        120
Read more →

SQL script to check available space in your recoveryarea (db_recovery_file_dest_size)

We’re hitting a lot of ORA-19815, ORA-19809, ORA-00257, ORA-16038 these days on some of our development databases – see here. …

Scroll down for an updated version. The space_reclaimable should be substracted from space_used.

Script below displays percentage used in in your recoveryarea(s) as defined in init parameter ‘db_recovery_file_dest_size’. …

col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999

,	ceil( space_limit / 1024 / 1024) SIZE_M
,	ceil( space_used  / 1024 / 1024) USED_M
,	decode( nvl( space_used, 0),
	0, 0
	, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest

kudos: …

Update – SUBSTRACT replaimable space from space_used. …

col name for a32
col size_m for 999,999,999
col reclaimable_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999

, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used  / 1024 / 1024) USED_M
, ceil( space_reclaimable  / 1024 / 1024) RECLAIMABLE_M
, decode( nvl( space_used, 0),
 0, 0
 , ceil ( ( ( space_used - space_reclaimable ) / space_limit) * 100) ) PCT_USED
 FROM v$recovery_file_dest

  2  , ceil( space_limit / 1024 / 1024) SIZE_M
  3  , ceil( space_used  / 1024 / 1024) USED_M
  4  , ceil( space_reclaimable  / 1024 / 1024) RECLAIMABLE_M
  5  , decode( nvl( space_used, 0),
  6   0, 0
  7   , ceil ( ( ( space_used - space_reclaimable ) / space_limit) * 100) ) PCT_USED
  8   FROM v$recovery_file_dest
  9  ORDER
Read more →

Transporting an Oracle database to another os platform the fastest way

You can use RMAN to transport tablespaces across platforms with different endian formats. You can also use RMAN to transport an entire database to a different platform so long as the two platforms have the same endian format. …

— From “27 Transporting Data Across Platforms” – …

So, in my own words – datafiles (tablespaces) are interchangeable with any operating system having the same endian format. (I don’t know what ‘endian format’ is nor do I care but it’s exmplained here.) …

This is great and enables for exceptionally simple and fast moving of an entire database to another os platform. …

Unfortunately however the oracle documentation does not discuss the scenario that is in the Oracle Metalink document ‘Cross-Platform Database Migration (across same endian) using RMAN Transportable Database’ [ID 1401921.1] where you move your entire database to another platform and performing only a partial (the bare neccesary) rman convert. …

No need for ‘messing around’ with datapump tablespaces export / import since we want to whole database. …

— hat tip for the Metalink Note is from my esteemed colleague Jeroen Pouwiel – thanks amie. …

Most important statement that I found in this metalink note is; …

It’s default behavior is to perform datafile conversion on all datafiles in the database. However, only datafiles that contain undo data require conversion including all datafiles beloging to SYSTEM tablespace and all UNDO tablespaces.

Below is a complete walktrough of a move (copy) from a database running on a Sun solaris server to a server running AIX doing only a `convert datafile` on the datafiles …

Read more →