Oracle DBA Tips and Techniques

By Arjun Raja

Archive for August, 2009

TESTING FLASHBACK

Posted by Arjun Raja on August 25, 2009

SQL> select count(*) from test;

COUNT(*)
———-
50000

At 09:02

SQL> @loop
18 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

COUNT(*)
———-
100000

At 09:10

SQL> select name from v$database;

NAME
———
BOZO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 478150656 bytes
Fixed Size 2084944 bytes
Variable Size 436211632 bytes
Database Buffers 33554432 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp(‘2009-01-15 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> ALTER DATABASE OPEN NORESETLOGS;
ALTER DATABASE OPEN NORESETLOGS
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select count(*) from test;

COUNT(*)
———-
50000

SQL> exit

_______________________________________________________________________________ Unencrypted electronic mail is not secure and may not be authentic. If you have any doubts as to the contents please telephone to confirm.

This electronic transmission including any attachments is intended only for those to whom it is addressed. It may contain copyright material or information that is confidential, privileged or exempt from disclosure by law. Any claim to privilege is not waived or lost by reason of mistaken transmission of this information. If you are not the intended recipient you must not distribute or copy this transmission and should please notify the sender. Your costs for doing this will be reimbursed by the sender.

We do not accept liability in connection with computer virus, data corruption, delay, interruption, unauthorised access or unauthorised amendment. _______________________________________________________________________________

______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Posted in High Availability | Leave a Comment »

ORA-19809: limit exceeded for recovery files

Posted by Arjun Raja on August 24, 2009

ALERT LOG ERROR  – ORA-19809

If the archivelog desintation as suggested by the parameter db_recovery_file_dest has used all the capacity as indicated in the db_recovery_file_dest_size parameter , then Oracle will not archive the next log until space is added or freed up.

The following error will appear in the alert log if you try to shutdown a database or switch a log file –

ORACLE Instance flash – Archival Error
ORA-16038: log 1 sequence# 43 cannot be archived
ORA-19809: limit exceeded for recovery files

To overcome this error after you have issued the Shutdown Immediate command –

1.  Shutdown abort;

2.  Startup mount;

3.

SQL>

set lines 100

col name format a60

select  name,  (space_limit/1048576),(space_used/1048576)from  v$recovery_file_dest;

NAME                                                         (SPACE_LIMIT/1048576)
———————————————————— ———————
(SPACE_USED/1048576)
——————–
/u02/oradata/flash_recovery_area                                              2048
            2184.979

 SQL> alter system set db_recovery_file_dest_size=4096m scope=both;

SQL> alter database open;

Database altered.

SQL>

Now shutdown immediate;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Posted in Administration, High Availability | Tagged: , , | 1 Comment »

DBUPGDIAG script to check integrity of database before 11g upgrade

Posted by Arjun Raja on August 20, 2009

Script to check integrity of a 9i or 10g database before upgrade to 11g.

Run this script connected as sysdba in the 9i or 10g database.

NAME: DBUPGDIAG.SQL

This script can be downloaded from Oracle Metalink note 556610.1

— PURPOSE:
— This script is intended to provide a user friendly output to diagonise
— the status of the database before (or) after upgrade. The script will
— create a file called db_upg_diag__.log in your local
— working directory. This does not make any DDL / DML modifications.

— This script will work in both Windows and Unix platforms from database
— version 9.2 or higher.

col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,’fmMonth DD, YYYY’) TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,’dd-Mon-yyyy_hhmi’) timecol,’.log’ spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || ‘_’ output FROM v$parameter WHERE name = ‘db_name’;
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col ‘Initial DB Creation Info’ for a35
col ‘Total Invalid JAVA objects’ for a45
col ‘Role’ for a30
col ‘User Existence’ for a27
col “JAVAVM TESTING” for a15
Prompt
Prompt
set feedback off head off
select LPAD(‘*** Start of LogFile ***’,50) from dual;
select LPAD(‘Oracle Database Upgrade Diagnostic Utility’,44)||
LPAD(TO_CHAR(SYSDATE, ‘MM-DD-YYYY HH24:MI:SS’),26) from dual;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup Time”
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct(‘This is a ‘ || (length(addr)*4) || ‘-bit database’) “WordSize”
FROM v$process;
Prompt
Prompt ================
Prompt Software Verison
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT ‘Compatibility is set as ‘||value Compatible
FROM v$parameter WHERE name =’compatible’;
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN

SELECT version INTO p_version
FROM registry$ WHERE cid=’CATPROC’ ;

IF SUBSTR(p_version,1,5) = ‘9.2.0’ THEN

DBMS_OUTPUT.PUT_LINE(RPAD(‘Comp ID’, 8) ||RPAD(‘Component’,35)||
RPAD(‘Status’,10) ||RPAD(‘Version’, 15));

DBMS_OUTPUT.PUT_LINE(RPAD(‘ ‘,8,’-‘) ||RPAD(‘ ‘,35,’-‘)||
RPAD(‘ ‘,10,’-‘) ||RPAD(‘ ‘,15,’-‘));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name,
dr.status Status,SUBSTR(dr.version,1,15) version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
RPAD(SUBSTR(x.comp_name,1,35),35)||
RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != ‘9.2.0’ THEN

DBMS_OUTPUT.PUT_LINE(RPAD(‘Comp ID’, 8) ||RPAD(‘Component’,35)||
RPAD(‘Status’,10) ||RPAD(‘Version’, 15)||
RPAD(‘Org_Version’,15)||RPAD(‘Prv_Version’,15));

DBMS_OUTPUT.PUT_LINE(RPAD(‘ ‘,8,’-‘) ||RPAD(‘ ‘,35,’-‘)||
RPAD(‘ ‘,10,’-‘)||RPAD(‘ ‘,15,’-‘)||RPAD(‘ ‘,15,’-‘)||
RPAD(‘ ‘,15,’-‘));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
SUBSTR(dr.version,1,11) version,org_version,prv_version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN ‘There are no Invalid Objects’
ELSE ‘There are ‘||count(object_name)||’ Invalid objects’
END “Number of Invalid Objects”
FROM dba_objects
WHERE status=’INVALID’
AND owner in (‘SYS’,’SYSTEM’);
Prompt
DOC
################################################################

If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status=’INVALID’
AND owner in (‘SYS’,’SYSTEM’)
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN ‘There are no Invalid Objects’
ELSE ‘There are ‘||count(object_name)||’ Invalid objects’
END “Number of Invalid Objects”
FROM dba_objects
WHERE status=’INVALID’
AND owner not in (‘SYS’,’SYSTEM’);
Prompt
DOC
################################################################

If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status=’INVALID’
AND owner not in (‘SYS’,’SYSTEM’)
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################

Result referencing the string ‘B023’ ==> Database was created as 32-bit
Result referencing the string ‘B047’ ==> Database was created as 64-bit
When String results in ‘B023’ and when upgrading database to 10.2.0.3.0
(64-bit) , For known issue refer below articles

Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
Upgrading Or Patching Databases To 10.2.0.3
Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) “Metadata”,
CASE SUBSTR(metadata,109,4)
WHEN ‘B023’ THEN ‘Database was created as 32-bit’
WHEN ‘B047’ THEN ‘Database was created as 64-bit’
ELSE ‘Metadata not Matching’
END “Initial DB Creation Info”
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ….
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM’;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ….
Prompt
SELECT object_name, object_type
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = ‘SYS’)
AND owner = ‘SYSTEM’;
Prompt
DOC

################################################################################

If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

— If so, get the version of the JAVAM component
EXECUTE IMMEDIATE ‘SELECT version FROM registry$ WHERE cid=”JAVAVM”
AND status 99’ INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’ AND owner=’SYS’;

IF SUBSTR(p_version,1,5) = ‘8.1.7’ THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,5) = ‘9.0.1’ THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,5) = ‘9.2.0’ THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,6) = ‘10.1.0’ THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,6) = ‘10.2.0’ THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – NOT Installed. Below results can be ignored’);

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

There should not be any Java Based users for database version 9.0.1 and above.
If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN ‘No Java Based Users’
ELSE ‘There are ‘||count(*)||’ JAVA based users’
END “User Existence”
FROM dba_users WHERE username LIKE ‘%AURORA%’ AND username LIKE ‘%OSE%’;
Prompt
DOC

###############################################################

Healthy JVM Should contain Six Roles.
If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN ‘No JAVA related Roles’
ELSE ‘There are ‘||count(role)||’ JAVA related roles’
END “Role”
FROM dba_roles
WHERE role LIKE ‘%JAVA%’;
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE ‘%JAVA%’;
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
WHEN 0 THEN ‘There are no SYS owned invalid JAVA objects’
ELSE ‘There are ‘||count(*)||’ SYS owned invalid JAVA objects’
END “Total Invalid JAVA objects”
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
AND status=’INVALID’
AND owner=’SYS’;
Prompt
DOC

#################################################################

Check the status of the main JVM interface packages DBMS_JAVA
and INITJVMAUX and make sure it is VALID.
If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
AND status=’INVALID’
AND owner=’SYS’;
set feedback off
Prompt
Prompt INFO: Below query should succeed with ‘foo’ as result.
set heading on
select dbms_java.longname(‘foo’) “JAVAVM TESTING” from dual;
set heading off
Prompt

set feedback off head off
select LPAD(‘*** End of LogFile ***’,50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select ‘Upload db_upg_diag_&&dbname&&timestamp&&suffix from “&log_path” directory’
from dual;
set heading on
set feedback on
Prompt
— – – – – – – – – – – – – – – – Script ends here – – – – – – – – – – – – – –

Posted in Scripts | Tagged: , | Leave a Comment »

Flashback database – with and without resetlogs.

Posted by Arjun Raja on August 11, 2009

FLASHBACK DATABASE – IMPACT OF RESETLOGS-

A flashback database can be completed and the database can either be opened for READ ONLY or opened with a RESETLOGS option.

As long as the RESETLOGS option is not used, a flashback can be rolled back .

Example –

Create table test – insert some records –

select count (*) from test ;

COUNT(*)
———-
100000

Select current_scn from v$database;

653026

Switch a couple of logfiles –

alter system switch logfile;

Insert another 50000 records into table test.

select count (*) from test ;

COUNT(*)
———-
150000

select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from V$LOG_HISTORY ORDER BY 1 ;

FIRST_CHANGE# TO_CHAR(FIRST_TIME,’ SEQUENCE#
————- ——————– ———-
538113 11-AUG-2009 10:58:17 1
576251 11-AUG-2009 11:03:35 2
653024 11-AUG-2009 11:18:11 3
653026 11-AUG-2009 11:18:12 4
653029 11-AUG-2009 11:18:17 5

Now shutdown immediate and flashback database to SCN 653026 – Before the latest insert to table test – at which point the count(*) of test is 100,000.

shutdown immediate;

startup mount ;

Now flashback database to SCN 653026.

SQL> flashback database to scn 653026;

Now open the database READ ONLY –

alter database read only;
select count(*) from test;

COUNT(*)
———-
100000

If you are satisfied with the results, the database can be opened with the RESETLOGS option –

Shutdown immediate;
startup mount;
alter database open resetlogs;

If not satisfied and you want to redo the flashback or get the database back to it’s current state-

Shutdown immediate;
startup mount;
recover database;

Media recovery complete.

alter database open;

Database altered.

select count(*) from test;

COUNT(*)
———-
150000

Therefore as long as a RESETLOGS is not mentioned, the flashback of a database can be reversed.

Ex – if you use RESETLOGS –

Shutdown immediate;

Startup mount;

flashback database to scn 653026;

Flashback complete.

alter database open resetlogs;

Database altered.

select count(*) from test;

COUNT(*)
———-
100000

shutdown immediate;

startup mount;

recover database;

SQL> ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

alter database open;

Database altered.

select count(*) from test;

COUNT(*)
———-
100000

Therefore the RESETLOGS option does not allow you to reverse the FLASHBACK of the database.

Posted in Administration | Tagged: , | Leave a Comment »

How to check the UTL_FILE_DIR parameter?

Posted by Arjun Raja on August 7, 2009

CREATE OR REPLACE procedure utl_file_test_write1 (
path in varchar2,
filename in varchar2,
firstline in varchar2,
secondline in varchar2)
is
output_file utl_file.file_type;
begin
output_file := utl_file.fopen (path,filename, ‘W’);

utl_file.put_line (output_file, firstline);
utl_file.put_line (output_file, secondline);
utl_file.fclose(output_file);

–exception
— when others then null;
end;
/

begin
utl_file_test_write1 (
‘/u03/users/ofsa/ofsatest/oracle_utl_data’,
‘utl_file_test’,
‘first line’,
‘second line’
);
end;
/

This works too –

SET SERVEROUTPUT ON
DECLARE
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(32767);
PROCEDURE recNgo (str IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (‘UTL_FILE error ‘ || str);

UTL_FILE.FCLOSE (fid);
END;
BEGIN
/* Change the directory name to one to which you at least
|| THINK you have read/write access.
*/
fid := UTL_FILE.FOPEN (‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test’, ‘R’);
UTL_FILE.GET_LINE (fid, v);
dbms_output.put_line (v);
UTL_FILE.FCLOSE (fid);

fid := UTL_FILE.FOPEN (‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test_10’, ‘W’);
UTL_FILE.PUT_LINE (fid, v);
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN recNgo (‘invalid_path’);
WHEN UTL_FILE.INVALID_MODE
THEN recNgo (‘invalid_mode’);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN recNgo (‘invalid_filehandle’);
WHEN UTL_FILE.INVALID_OPERATION
THEN recNgo (‘invalid_operation’);
WHEN UTL_FILE.READ_ERROR
THEN recNgo (‘read_error’);
WHEN UTL_FILE.WRITE_ERROR
THEN recNgo (‘write_error’);
WHEN UTL_FILE.INTERNAL_ERROR
THEN recNgo (‘internal_error’);
END;
/

message for success below.

first line

PL/SQL procedure successfully completed.

SQL> exit

Posted in Administration | Tagged: , | Leave a Comment »

Troubleshooting Agent Startup and Upload

Posted by Arjun Raja on August 7, 2009

If the agent in GRID control in not uploading to the OMS –

cd $AGENT_HOME/bin

oracle(DATABASE)@XXXXX:./emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ………. started.

oracle(DATABASE)@XXXXX:./emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
—————————————————————
Agent Version : 10.2.0.4.0
OMS Version : 10.2.0.4.0
Protocol Version : 10.2.0.4.0
Agent Home : /u01/oracle/agent10g
Agent binaries : /u01/oracle/agent10g
Agent Process ID : 856310
Parent Process ID : 897132
Agent URL : https://XXXXXX.com:3872/emd/main/
Repository URL : https://XXXXXX.com:1159/em/upload
Started at : 2009-08-07 12:27:29
Started by user : oracle
Last Reload : 2009-08-07 12:27:29
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0.00
Number of XML files pending upload : 5004
Size of XML files pending upload(MB) : 20.53
Available disk space on upload filesystem : 69.35%
Collection Status : Disabled by Upload Manager
Last successful heartbeat to OMS : 2009-08-07 12:27:39
—————————————————————
Agent is Running and Ready

Steps to fix this issue –

cd $AGENT_HOME/sysman/emd

rm lastupld.xml agntstmp.txt

cd $AGENT_HOME/sysman/emd/upload

rm *.*

cd $AGENT_HOME/sysman/emd/state

rm *.*

No need to remove storage directory under $AGENT_HOME/sysman/emd/state

cd $AGENT_HOME/bin

./emctl start agent

./emctl status agent

If the Last successful heartbeat to OMS is not shown as successful, then secure the agent .

cd $AGENT_HOME/bin

./emctl secure agent

Make sure the password prompted for matches the OMS password – usually the sysman password for the OMS URL –

Once agent is secured –

oracle(DATABASE)@XXXXX:./emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
—————————————————————
Agent is Not Running

oracle(DATABASE)@XXXXX:./emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ……… started.

oracle(DATABASE)@XXXXXXX:./emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
—————————————————————
Agent Version : 10.2.0.4.0
OMS Version : 10.2.0.4.0
Protocol Version : 10.2.0.4.0
Agent Home : /u01/oracle/agent10g
Agent binaries : /u01/oracle/agent10g
Agent Process ID : 217220
Parent Process ID : 864280
Agent URL : https://XXXXXXXXX.com:3872/emd/main/
Repository URL : https://XXXXXXXXX:1159/em/upload
Started at : 2009-08-07 12:55:43
Started by user : oracle
Last Reload : 2009-08-07 12:55:43
Last successful upload : 2009-08-07 12:56:00
Total Megabytes of XML files uploaded so far : 2.00
Number of XML files pending upload : 6
Size of XML files pending upload(MB) : 3.96
Available disk space on upload filesystem : 69.31%
Last successful heartbeat to OMS : 2009-08-07 12:55:52—————————————————————
Agent is Running and Ready
oracle(DATABASE)@XXXXXX:

Posted in EM Grid Control | Tagged: , , , | 1 Comment »

FLASHBACK DATABASE TO SCN

Posted by Arjun Raja on August 4, 2009

FLASHBACK DATABASE TO SCN

Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July.

Get oldest flashback time –

SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$FLASHBACK_DATABASE_LOG;

If there are logs up until 06:30 on 31 Jul, you can flashback.

Get the SCN number to flashback to –

col first_change# format 99999999999
select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from V$LOG_HISTORY ORDER BY 1 ;

23856899540 31-JUL-2009 04:30:09 119695
23856965501 31-JUL-2009 06:30:10 119696
23857031498 31-JUL-2009 08:30:09 119697
23859233947 31-JUL-2009 10:00:29 119704
23859242647 31-JUL-2009 10:31:34 119705

Note down the SCN at the time you want to flashback to which is the FIRST_CHANGE# in the V$LOG_HISTORY view –

Example here the SCN is 23856965501 at 06:30 – the time you want to flashback to.

Restore a few archivelogs prior to and after the flashback time – the log sequence is in the last column above….

Ex – to restore archivelogs from 119695 to 119702

rman target / catalog rman/rman@rman

RMAN > restore archivelog from sequence 119695 until sequence 119705;

Once the logs are restored make sure no automatic RMAN backup job of archivelogs run which delete old archivelogs from disk –

Next flashback the database –

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 23856965501;

Once FLASHBACK is complete-

Verify the data before opening with a resetlogs –

ALTER DATABASE OPEN READ ONLY;

If satisfied with the data –

Shutdown immediate;
Startup mount ;
Alter database open resetlogs;

Posted in High Availability | Tagged: | Leave a Comment »