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
do
  ./ggsci <<EOF
info all
exit
EOF
  sleep 10
done



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

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

MANAGER     RUNNING
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 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605
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

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

RMAN cheatsheet

Commands
SET
LIST
RESTORE …

LIST ARCHIVELOG
Objects
archivelogRecordSpecifier …

# List archives by log sequence
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 3398;

# List archives with NEXT_TIME (see <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1016.htm">v$archived_log.next_time</a>) is  greater then FROM TIME - last day (24 hours)
LIST BACKUP OF ARCHIVELOG FROM TIME "SYSDATE-1";

# List archives between two dates 
LIST BACKUP OF ARCHIVELOG 
  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')";

RESTORE ARCHIVELOG
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

MANAGER     RUNNING
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  /
   DEST_ID STATUS    DEST_NAME          DESTINATION
---------- --------- ------------------ --------------------------------------------------
         1 DISABLED  LOG_ARCHIVE_DEST_1
         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                                                \
    USERID=${_DB_IMPDP_USERNAME}/${_DB_IMPDP_PASSWORD}@${OPT_DB_TARGET}   \
    JOB_NAME=${_IMPDP_JOBNAME}                                            \
    NETWORK_LINK=${OPT_DB_SOURCE}                                         \
    SCHEMAS=${OPT_DB_SCHEMA}                                       
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
DBMS_REDEFINITION …

Here is a complete test on my local database; …

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

SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE ( 'REMI', 'DEMO', 'DEMO_INTERIM');

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 →

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL returns ORA-30992

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

SQL> BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  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  /
BEGIN
*
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

it’s not who you are underneath, it’s what you do that defines you. …

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 …

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

 
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
=== …

SQL> DECLARE
  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  /
DECLARE
*
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
USER is "SCOTT"
SQL>

“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’ – //docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012 …

-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

SQL>

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; …

/m001/oradata/oracle_sid/filename1.dbf
/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

SQL> 

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
  8     
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
Completed: ALTER DATABASE CLOSE NORMAL

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 →

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)
********_ora_17587.trc:2012-07-26 

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. -->
<INVENTORY>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
<VERSION_INFO>
   <SAVED_WITH>11.1.0.8.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
.
.
<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"/>
</HOME_LIST>
</INVENTORY>

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. -->
<INVENTORY>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
<VERSION_INFO>
   <SAVED_WITH>11.1.0.8.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
.
.
<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"/>
</HOME_LIST>
</INVENTORY>

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 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

SELECT name
,	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
ORDER BY name
/

kudos: http://www.shutdownabort.com/errors/ORA-19809.php …

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

SELECT name
, 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
ORDER BY name
/

SQL> SELECT name
  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” – http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm …

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. …

IE;
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 →

Fix ORA-00257: archiver error. Connect internal only, until freed – in Oracle 11G

When your database hangs with a ORA-00257 error while you notice there is plenty of space in the /recoveryarea mountpoint you’re likely to have reached the maximum size defined by the new ** initialization parameter ‘db_recovery_file_dest_size‘. Fast solution is to simply increase the value for db_recovery_file_dest_size (and after that of course start archiving…) …

** 2012-04-02T12:10:; I see now the init parameter db_recovery_file_dest_size is not so new in fact has been around since Oracle 10.1 … OK…. …

Mon Apr 02 06:01:12 2012
Errors in file /ora/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/$ORACLE_SID_arc0_25342.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
SQL> sho parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /recoveryarea
db_recovery_file_dest_size           big integer 10G
SQL>


Still plenty of space in the /recoveryarea mountpoint – namely 42G however in the database’s archive log …

Read more →

PL/SQL quickie; generate RMAN set newname script to different mountpoints

Script below generates RMAN newname file where database A is copied to database B AND database B has ‘conflicting’ mountpoints set up. …

Mountpoints setup on database A is like /m001/, /m002/, /m003/, /m004/ but mountpoints on database B side is just /m001/ and /m002. …

/m001 has only 800 G free space so I place everything in /m001 until i_num_bytes reaches 800G then all the rest goes to /m002. …

declare
 i_num_bytes number := 0;
 v_mount   varchar2(100);
 v_file   varchar2(100);

begin

 for i in (select file_id, file_name, bytes from dba_data_files order by file_id) 
 loop
  i_num_bytes := i_num_bytes + i.bytes ;
  -- 800g
  if i_num_bytes < 858993459200 then 
   v_mount := '/m001/oradata/********/' ;
  else 
   v_mount := '/m002/oradata/********/' ;
  end if;

  -- get base name of the file
  v_file := SUBSTR( i.file_name, INSTR( i.file_name ,'/', -1) +1);
  -- replace oracle_sid
  v_file := REPLACE( v_file, '********', '********');
  
  dbms_output.put_line( 'set newname for datafile ' || i.file_id || 
    ' to '''|| v_mount || v_file || ''';');
 
 end loop;

end;
/

Read more →

Generate scp datafiles script with regular expression

Need to transfer a bunch of databases with pattern {a…9} to {t…8}. …

Please copy databases below; …

adummy19 to tdummy18 – server070 (*)
adummy29 to tdummy28 – server526
adummy39 to tdummy38 – server526
adummy49 to tdummy48 – server526
adummy59 to tdummy58 – server526
adummy69 to tdummy68 – server526
adummy79 to tdummy78 – server526
adummy89 to tdummy88 – server526 …

Created PL/SQL script below to get me the scp commands; …

declare
 function replace_sid ( piv_string in varchar2)
  return varchar2
 as
  db_name_cur varchar2(64);
  db_name_new varchar2(64);
 begin
  -- current db_name
  select value into db_name_cur from v$parameter where name = 'db_name';
  
  -- new db_name 
  db_name_new := regexp_replace( db_name_cur, '(^a)', 't');
  db_name_new := regexp_replace( db_name_new, '(9$)', '8');
 	
  -- return replacement
  return replace( piv_string, db_name_cur, db_name_new);
 end;

begin
 for i in ( select file_name from dba_data_files ) 
 loop
  dbms_output.put_line ( 'scp ' || i.file_name || ' ahc526:' || replace_sid( i.file_name));
 end loop;
 
end;
/
Read more →

Segment Advisor dump (R 10.1)

Dump of running segment advisor in Oracle 10.1 …

@See:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_advis.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592
http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php / …

For tablespace …

create or replace procedure sa_tablespace
(
  piv_tablespace in varchar2,
  piv_task		   in varchar2 default null
)
  authid current_user

as

  obj_id number;
  task   varchar2(100);

begin

 	if piv_task is null then
 	  task := 'SA TABLESPACE ' || piv_tablespace ;
 	else
 	  task := piv_task;
 	end if;

	-- remove task
	begin
		dbms_advisor.delete_task ( task_name     => task);
	exception
		when others then null ;
	end;

	-- create task
  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_name     => task);

  dbms_advisor.create_object (
    task_name        => task,
    object_type      => 'TABLESPACE',
    attr1            => piv_tablespace,
    attr2            => NULL,
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);

  dbms_advisor.set_task_parameter(
    task_name        => task,
    parameter
Read more →

expdp / impdp

Some expdp impdp commands.
Quotes in ‘/as sysdba’ needs escape slashes …

/m001/oradata/EXPORT>expdp \'/ as sysdba\' schemas=owner1,owner2,owner3 directory=EXP_DIR2 dumpfile=<filename> logfile=<filename>
Read more →