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 →

Why Oracle May Really Be Doomed This Time

http://pandodaily.com/2012/02/11/why-oracle-may-really-be-doomed-this-time/
I was laying awake early this morning thinking about Oracle. …

O my …

Typically I think big public companies are inept, but I would not count Oracle out. Ellison has long shown he viscerally gets where the stock market, the customer and the technology are going. He may be better at this than anyone leading a technology company today. He has pulled off stunning and dramatic turn arounds of Oracle in the past. He can force the company to shift out of sheer force of will, uncowed by the near term pain he may inflict on customers, employees or Wall Street in the process. That’s something that only Steve Jobs and Jeff Bezos can do well. …

But to win, Oracle will have to change its strategy as dramatically as it did in 2006 when Ellison famously announced that software innovation was dead and just started to buy everything. Buying once-hot companies like Taleo and RightNow isn’t going to cut it this time when there are better products in the market like Workday and Salesforce. …

Wole article here;
http://pandodaily.com/2012/02/11/why-oracle-may-really-be-doomed-this-time/ …

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 →

ORAENV_ASK

ORAENV_ASK=no …

export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;
export ORACLE_SID=sidje0;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORACLE_SID=sidje1;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORACLE_SID=sidje2;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORAENV_ASK=YES;
Read more →

monitor progress of long running job with dbms_application_info.set_session_longops

Investigating a major performance issue with a PL/SQL loop running for more then 12 hours. Added dbms_application_info.set_session_longops to have entry in v$session_longnops. …

DECLARE

	-- @what: display elapsed msecs
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2389164927483
	n_starttime NUMBER;
	n_endtime   NUMBER;
	n_elapsed   NUMBER;
	
	d_date			DATE   := SYSDATE;

	-- @what: dbms_application_info.set_session_longops
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1099233454171#18173358903352
	l_nohint 		NUMBER DEFAULT dbms_application_info.set_session_longops_nohint;
	l_rindex 		NUMBER DEFAULT l_nohint;

  n_slno      NUMBER;
  n_totalwork NUMBER := 847; -- init total work, number of records in loop recordset is 847
  n_sofar     NUMBER := 0;


BEGIN

  FOR i IN
  (
          SELECT  *********.DBKey  FloorDBKey,
                  *********.Value3 StoreVersionID
          FROM    *********,
                  *********,
                  *********
          WHERE   *********.DBParentStoreKey = *********.DBKey
          AND     *********.DBKey = *********.DBParentFloorplanKey
          AND     *********.DBStatus = 1
          AND     *********.DBStatus = 1
  --        
Read more →

a winner, flashback query; AS OF TIMESTAMP

Recovering data from a malicious SQL update carried out on a production database. Instead of restoring the whole database to recover some lost data I gave flashback query a try and even thougg the ‘undo_retention’ parameter was set to only 5 minutes oracle recovered the data from the table from about 18 hours before. …

SQL Statements below recover the data from a timestamp just before the offending SQL update was carried out. I saved the data to a _BAK table. …

From: Visser, Remi
Sent: Wednesday, September 22, 2010 3:37 PM
To: ********
Subject: recoverd *********** table using oracle flashback query …

Importance: High …

Hi **** …

I was able to recover the table using flashback query. See below. …

Please check the data in the table ********_BAK it contains the data in ******** as of 2010-09-22 09:00:00. The offensing update was carried out at 9:10 hours. …

When the data is OK I can merge the data from the _BAK table to the original production table. …

 
SQL> create table ********_BAK
  2  as
  3  SELECT * FROM BAGR.********    AS OF TIMESTAMP 
  4     TO_TIMESTAMP('2010-09-22 09:00:00', 'YYYY-MM-DD HH:MI:SS')
  5  ;
 
Table created.
 

SQL> create public synonym ********_BAK for ********.********_BAK;
 
Synonym created.
 

SQL> grant select on ********.********_BAK to public;
 
Grant succeeded.
 
 

Dutch people, notice the schema owner name ‘BAGR.********’, Hello my name is ‘BAGR’, how nice… …

Read more →

Purging SYSAUX tablespace (Purging AWR reports)

Dump below is of a session clearing the sysaux tablespace. It turned out that something went wrong with the automatic AWR gathering/purging and the sysaux tablespace became very large. …

Thanks to this blog I found script below wich displays the various sysaux components and their sizes. …

SQL> col "Space (M)" for 999,999.99  
SQL> 
SQL> SELECT 
  2    occupant_name,  
  3    round( space_usage_kbytes/1024) "Space (M)",  
  4    schema_name, 
  5    move_procedure
  6  FROM 
  7    v$sysaux_occupants  
  8  ORDER BY 
  9    1  
 10  /
OCCUPANT_NAME          Space (M) SCHEMA_NAME          MOVE_PROCEDURE
-------------------- ----------- -------------------- ----------------------------------------
AO                          1.00 SYS                  DBMS_AW.MOVE_AWMETA
EM                           .00 SYSMAN               emd_maintenance.move_em_tblspc
JOB_SCHEDULER               1.00 SYS
LOGMNR  
Read more →

Restore SQL Server database with TSQL

Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking connections. …

I decided the try to restore the database with T-SQL, with T-SQL the restore completed successfully! Another advantage of using T-SQL is that T-SQL gave more logging. This logging shows me that the BAK file (delivered from a third party) is not a SQL Server 2005 backup but presumably a SQL Server 2000 database backup … …

DECLARE
	@BackupFile varchar(8000),
	@sql varchar(8000)
	SET @BackupFile = 'E:\DB2000.BAK'
	SET @sql = 'RESTORE DATABASE ADB01 FROM DISK = ''' + @backupfile + ''' WITH FILE = 1,  
		MOVE N''DB2000_dat'' TO N''E:\mssql\data\ADB01\ADB01_01.mdf'',  
		MOVE N''DB2000_log'' TO N''F:\mssql\tlog\ADB01\ADB01_01.ldf'',  
		NOUNLOAD,  REPLACE,  STATS = 10, RECOVERY'
	exec (@sql)
go



10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 2792 pages for database 'ADB01', file 'DB2000_dat' on file 1.
Processed 1 pages for database 'ADB01', file 'DB2000_log' on file 1.
Converting database 'ADB01' from version 539 to the current version 611.
Database 'ADB01' running the upgrade step from version 539 to version 551.
Database 'ADB01' running the upgrade step from version 551 to version 552.
Database 'ADB01' running the upgrade step from version 552 to version 553.
Database 'ADB01' running the upgrade step from version 553 to version 554.
Database 'ADB01' running the upgrade step from version 554 to version 589.
Database 'ADB01' running the upgrade step
Read more →

freaking hilarious incident…

**** production DB issue
User ID :****
Phone :+****
Location :****
PC nr :**** …

Software/hardware :****
Problem & TS :
A job hangs in ****,
Job name: ****

User would like to be called back on the phone number provided if more information is needed.
User said he cannot explain it to me because its too difficult to understand and i wouldnt understand anything anyways.
**** production DB issue

copy from call ticket 600-01-12184003 with incident ticket 600-02-6637739 …

hey team,
talked with **** **** about this problem and she assigned it to your team.
Can you please take a look at it?
Thank you …

Read more →

Manually triggering Metric Collection (clearing -tablespace- Alerts)

Manually triggering metric collection in Oracle Enterprise Manager (GRID control) …

Here’s the scenario: …

You have an Alert that your tablespace is ‘full’,
You enlarge the tablespace,
The ‘Tablespace Full Alert’ is still in your dashbord for the next 24 (x) hours since you’ve configured this metric to be collected once every 24 (x) hours,
You want however to ‘remove’ (clear) this Alert. In order to achive this you will have to to re-evaluate this metric collection … …

Here is how you do it (for anything storage related on your database server): …

$ emctl control agent runCollection <hostname>:host host_storage

See the Oracle docs, 2.7.6 Reevaluating Metric Collections. It shows you how to retrieve the correct collection name for any metric. …

You will find yourself grep-ing through ‘$ORACLE_HOME/sysman/admin/metadata/*xml’ and ‘$ORACLE_HOME/sysman/admin/default_collection/*xml’ … …

Read more →

osql switches

Some osql switches. …

Specify non default tcp ports
C:\>osql -Stcp:<hostname>,<portname>[\<instance_name>] -E
1>

See http://support.microsoft.com/kb/313295 …

Read more →

Increase idle time for OMS frontend sessions

Some additional configuration directives. …

To increase the idle time for the OMS frontend sessions edit the ‘oracle.sysman.eml.maxInactiveTime’ configuration parameter your Oracle Management Server’s emoms.properties file. The parameter value unit is in minutes and the default is 15 minutes which is a bit short I suppose. I have set it to 4 hours as you can see in the example below. …

$ cat $ORACLE_HOME/sysman/config/emoms.properties | grep maxInactiveTime
oracle.sysman.eml.maxInactiveTime=240
$

For more info see the ‘Enterprise Manager Advanced Configuration‘ book …

Read more →

Remove SQL Server database from single-user mode

Braindump of a session where I had to remove a SQL Server database from single-user mode. …

execute sp_dboption
1> exec sp_dboption 'testdb01', 'single user', 'FALSE';
2> go
Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1
Changes to the state or options of database 'testdb01' cannot be made at this
time. The database is in single-user mode, and a user is currently connected to
it.
Msg 5069, Level 16, State 1, Server REMIDIAN01, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Now I can use TSQL below to find all the database sesions connected to this database. …

TSQL script to retrieve client sessions per database.
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'testdb01'
go

Kill the particular session(s) with the ;kill’ command. …

1> kill 51
2> go
1>

Now I can ‘remove’ the database from Sinlge user mode. …

1> exec sp_dboption 'testdb01', 'single user', 'FALSE'
2> go
1>
Read more →

Batch SQL statements in parallel (multiple) sessions

So it was decided ‘this batch’ had to be done ‘this weekend’. Thousands of SQL statements some of them would take minutes others hours… And MAKE SURE it’s finished monday. …

I hate babysitting multiple sessions having to create multiple SQL Scripts to spread the workload (it’s on a 20+ CPUS’s database server) So I wrote this PL/SQL ‘application’ in a hurry to enable starting multiple databases sessions in nohup, each session picks up the next SQL statement ‘available’ (WHERE endtime IS NULL). It will save me some sleep. …

Setup The table holding my SQL statements
CREATE TABLE batchsql
 ( batchid              VARCHAR2(64)    NOT NULL
 , sqlid                INTEGER         NOT NULL UNIQUE
 , sqltext              varchar2(4000)  NOT NULL
 , starttime            DATE
 , endtime              DATE
 , sqlcode              INTEGER
 , sqlerrm              VARCHAR2(256)
 , error_stack          VARCHAR2(4000)
 , error_backtrace      VARCHAR2(4000)
 );

would be nicer …

Read more →

Really delete (‘corrupt’) Agent from OMS repository

Here’s how to overcome ORA-20206 in Oracle GRID environment, agent status unclear… …

Scenario

Removed (Windows) Agent from Grid. (With SQL Server Management plugin)
Tried to restart this SAME agent, errors below occur. …

# $ORACLE_HOME/sysman/log/emagent.log contains

2008-01-17 14:13:52 Thread-4032 EMAgent started successfully (00702)
2008-01-17 14:14:21 Thread-2720 <Upload Manager> Unsuccessful Upload attempts for XML file exceeds specified limit=3, 
      Agent will shutdown (00851)
2008-01-17 14:14:21 Thread-4032 EMAgent abnormal terminating (00704)
# $ORACLE_HOME/sysman/log/emdctl.trc contains

2008-01-17 14:13:32 Thread-752 WARN  http: snmehl_connect: connect failed to (<agent_hostname>:3872): 
      No connection could be made because the target machine actively refused it.
 (error = 10061)
2008-01-17 14:13:32 Thread-752 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1
2008-01-17 14:14:23 Thread-172 WARN  http: snmehl_connect: connect failed to 
      (<agent_hostname>:3872): 
      No connection could be made because the target machine actively refused it. (error = 10061)
2008-01-17 14:14:23 Thread-172 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1

# $ORACLE_HOME/sysman/log/emagent.trc contains

2008-01-17 14:14:18 Thread-192 WARN  TargetManager: Regenerating all Metadata
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: In getTypeAndNameForHostTarget
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: getTypeAndNameForHostTarget found host, <agent_hostname>
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: In getTypeAndNameForHostTarget
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: getTypeAndNameForHostTarget found host, <agent_hostname>
2008-01-17 14:14:18 Thread-192 WARN  collector: Regenerating all DefaultColls
2008-01-17 14:14:18 Thread-192 ERROR upload: Failed to upload file A0000001.xml, ret = -6
2008-01-17 14:14:19 Thread-192 WARN  upload: FxferSend: received fatal error in
Read more →

Clearing Alerts in Oracle GRID Control

Here’s how to clear alerts from the Oracle GRID manually. …

For some obscure reason Oracle is very reluctant in providing the information that is described here below. Search Metalink and you will find no entries… The information below is from dbasupport.com and helps me to clear alerts that the Agent does NOT clear for me. …

sysman.em_severity
[REMI@DB01.REMIDIAN.COM]
SQL>  @desc sysman.em_severity
.
.
PROCEDURE DELETE_CURRENT_SEVERITY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID                  RAW                     IN
 P_KEY_VALUE                    VARCHAR2                IN
.
.

Generate SQL to remove alerts from specific targets
select t.target_name
,      t.target_type
,      collection_timestamp
,      message
,
Read more →