UNIX - More of searching for files

From the previous post....

In order to locate large directories, I extended the idea of the find statement and an executable script (ascr2)

In this example, I use the du -sm command to locate directories exceeding a certain size in MB.

File - ascr2 Parameter 1 in the directory name, parameter 2 is the minimum size we want to display.

if [ `du -sm $1 | cut -f1` -gt $2 ]; then
echo "$1 - `du -sm $1 | cut -f1`"
fi

The File Command. This will only display directories over 100Mb. Obviously the parent directories will be larger.

find /app/oracle -type d -exec ./ascr2 {} 100 \;


Happyjohn

UNIX - finding directories with more than x number of files

I had to scratch my head a little when I was supporting Oracle in a Unix/Linux/AIX environment. We would keep getting tickets for partition /app/oracle exceeds 90%.

So I'd log into the server and do a df -h /app/oracle or df -g /app/oracle (in AIX) and sure enough it exceed 90% full.
But which files should I delete or gzip?

So I'd do this...

find /app/oracle -type f -size +100000 -exec ls -l {} \;

to locate large files, and this to locate dump files.

find /app/oracle -name '*.dmp' -exec ls -l {} \;

but the biggest problem is with large numbers of small log files clogging up directories. Icouldn't find a suitable unix command so I wrote one.

File - ascr1 (this will display the count of files in directory $1 but only if it exceed the number in $2). Give it execute privs.

if [ `ls $1 | wc -l` -gt $2 ]; then
echo "$1 - `ls $1 | wc -l`"
fi

then use it in a find command. In this example it will only display directories exceeding 1000 files.

find /app/oracle -type d -exec ./ascr1 {} 1000 \;



Happyjohn.

How to demonstrate Oracle In-Doubt Transactions

An 'in doubt transaction' occurs in Oracle when you update both the local and a remote table via a db link but then the remote databases fails.

Here is the example.

Using databases TEST1 and TEST2, create a table on each. Also create a database link from TEST1 to TEST2.

conn user1/pwd1@TEST1
create table loc_tab (afld varchar2(10));
create database link alink connect to user2 identified by pwd2 using 'TEST2';

conn user2/pwd2@TEST2
create table rem_tab (afld varchar2(10));

Then connect to TEST1 and insert a row into both.

conn user1/pwd1@TEST1
insert into loc_tab values ('A');
insert into rem_tab@alink values ('B');

The connect another session to SYS as SYSDBA on TEST2 and abort the database.

shutdown abort

Back on TEST1, commit the transaction. You will get an error.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02054: transaction 6.21.2068 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from ALINK

Select * from dba_2pc_pending to see the in doubt transaction on TEST1.
Startup TEST2 and you'll see that the in doubt transaction remains.

The options for dealing with this are COMMIT FORCE or ROLLBACK FORCE. Because we are creating standby databases from TEST1 and don't want the users getting ORA-01591 errors, we will roll back the error.

SQL> select local_tran_id from dba_2pc_pending WHERE state not like 'forced%';
LOCAL_TRAN_ID
----------------------
6.21.2068

SQL> rollback force '6.21.2068';
Rollback complete.

** note the WHERE Clause to avoid seeing other in doubt transaction that have already been forced back recently.

If you choose to COMMIT FORCE '6.21.2068', then it will only commit the local part of the transaction.

I did find that when I restarted TEST2, before performing a 'commit force' on TEST1, the command hung? Not sure about that. I didn't hang on later tests.


Another thing you can do is pretend the failure on TEST2 occurred.
This is done by not aborting TEST2, but instead committing with a comment.

COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7';

Where the 7 can be a number between 1 and 10. The setting of this requires some thinking about. I've listed the values and what they mean at the end of this note.

The result however is the same. You still have to rollback force or commit force the in doubt transaction, but you'll get an error when you do.

Finally there are options to auto fix in doubt transactions based on some criteria.

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

Doc ID 100664.1 from Oracle will give you more detailed information on this matter, and it also has links to other related documents.


COMMIT COMMENT Options.

1 - Crash commit point after collect
2 - Crash non-commit-point site after collect
3 - Crash before prepare (non-commit-point site)
4 - Crash after prepare (non-commit-point site)
5 - Crash commit point site before commit
6 - Crash commit point site after commit
7 - Crash non-commit-point site before commit
8 - Crash non-commit-point site after commit
9 - Crash commit point site before forget
10 - Crash non-commit-point site before forget

Data Guard Transport Lag

I need to thank Emre Baransel's Oracle Blog for pulling me through a tight spot for something I should have already known.

OEM 12c reports the following :-

There are approximately 3,753 seconds of redo not yet available on this standby database.

I know this means that there is a delay or a gap in the transport of REDO logs to a Data Guard Standby Database, but having the commands to hand so you can make a quick diagnosis of the problem, was briefly out of my grasp. So this is what you do.

Locate your Primary and Standby databases and get a sqlplus session running as sysdba on each.

On the primary

SELECT a.thread#, 
                b. last_seq,
                a.applied_seq,
                a. last_app_timestamp,
                b.last_seq - a.applied_seq   arc_diff
FROM (SELECT   thread#,
                                MAX(sequence#) applied_seq,
                                MAX(next_time) last_app_timestamp
                FROM gv$archived_log
                WHERE applied = 'YES'
                GROUP BY thread#) a,
             (SELECT   thread#,
                                MAX (sequence#) last_seq
                FROM gv$archived_log
                GROUP BY thread#) b
WHERE a.thread# = b.thread#;

Sadly at this point I find that the 'arc_diff' is now zero and the problem has gone from OEM too.
However ,the principle still applies, and when this happens again, I'll put the figures in.

  THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
---------- ---------- ----------- -------------------- ----------
        1     568343      568343 03-NOV-2014 12:02:23          0

Now  looking on the Standby.

SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

  THREAD# MAX(SEQUENCE#)
---------- --------------
        1         568343

And

select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1     568343      12289       1833
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1     568342       8193       2034
ARCH      CONNECTED             0          0          0          0
MRP0      APPLYING_LOG          1     568344       7008     204800
RFS       IDLE                  1     568344       7010        116
RFS       IDLE                  0          0          0          0
RFS       IDLE    ;               0          0          0          0

8 rows selected.

•Standby database process status: You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.

And lastly..... Apply/transport lags: v$dataguard_stats view will show the general synchronization status of standby database.

 

set lines 200

col name format a40

col value format a20

select * from v$dataguard_stats;

 

NAME                                     VALUE                UNIT                           TIME_COMPUTED

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

apply finish time                        +00 00:00:00.0       day(2) to second(1) interval   03-NOV-2014 12:58:01

apply lag                                +00 00:00:06         day(2) to second(0) interval   03-NOV-2014 12:58:01

estimated startup time                   15                   second                         03-NOV-2014 12:58:01

standby has been open                    N                                                   03-NOV-2014 12:58:01

transport lag                            +00 00:00:00         day(2) to second(0) interval   03-NOV-2014 12:58:01

 

SQL>

 

And ..... Apply rate: To find out the speed of media recovery in a standby database, you can use this query

 

set lines 200

col type format a30

col ITEM format a20

col comments format a20

select * from v$recovery_progress;

 

START_TIME           TYPE                           ITEM                 UNITS                                 SOFAR      TOTAL TIMESTAMP

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

03-NOV-2014 08:41:01 Media Recovery                 Log Files            Files                                     4          0

03-NOV-2014 08:41:01 Media Recovery                 Active Apply Rate    KB/sec                                  241          0

03-NOV-2014 08:41:01 Media Recovery                 Average Apply Rate   KB/sec                                    3          0

03-NOV-2014 08:41:01 Media Recovery                 Redo Applied         Megabytes                                48          0

03-NOV-2014 08:41:01 Media Recovery                 Last Applied Redo    SCN+Time                         2147483647          0 03-NOV-2014 12:50:07

03-NOV-2014 08:41:01 Media Recovery                 Active Time          Seconds                                  13          0

03-NOV-2014 08:41:01 Media Recovery                 Apply Time per Log   Seconds                                   2          0

03-NOV-2014 08:41:01 Media Recovery                 Checkpoint Time per  Seconds                                   0          0

                                                    Log

 

03-NOV-2014 08:41:01 Media Recovery                 Elapsed Time         Seconds                               15598          0

 

9 rows selected.

 

 

 

Happyjohn

 

Is my Materialized View Log being used?

We have a large number of production databases that are being used as the source for Materialized Views.

Often the developers just add an MV Log to a new table on the chance that it might be used. As we all know MV Logs or MLogs are used to record changed rows within a table, so that the MV based upon that source table, can perform a fast refresh.

I needed a way to locate all MLOGs and work out if they are still or have even been used. The solution to this is quite simple.

The object SYS.MLOG$ contains various details of the MLOGs usage. The information is better at 11g but ok at 10g.

The following query lists all tables who's name starts with MLOG$, and then added the SYS.MLOG$ details. I've used an outer join to still show tables that have never been used as MV Logs. The v$database is only there because I was doing a lot.

select db.name, tabs.owner, tabs.table_name, mlog.master, mlog.log, mlog.youngest
from sys.mlog$ mlog, v$database db, dba_tables tabs
where tabs.owner = mlog.mowner(+) and tabs.table_name = mlog.log(+)
and tabs.table_name like 'MLOG$%' and tabs.table_name not in ('MLOG$','MLOG_REF$')
order by 2,3;

Let me know what you think of my solution.

Happyjohn

Resumable Timeouts caused by ORA-01652 but Tablespace is not full

* when you have a resumable timeout set so that SQL waiting for tablespace resources can pause until that space becomes available.

I had this interesting problem the other day, and while the solution is reasonably obvious, it didn't occur to me immediately at the time.

You can detect resumable pauses with this statement...

select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;

This then shows one or more sessions where the resumable wait has cleared or an error is display showing a wait in progress. At my company we monitor for these waits extending to long and then report them to call-out.

In this case, as always, this is the error message shown. There were 2 sessions waiting on a large data warehouse.

ORA-01652: unable to extend temp segment by string in tablespace cma_ts1

Earlier in the day, I had got the same message and when I checked, the tablespace was indeed almost completely full. It was on ASM and the single file in the tablespace was 1.5Tb. Because it's a production database I didn't hang around and increased the datafile to 2Tb.

ALTER DATABASE DATAFILE '+DATA_DG/dgb_tsm1/datafile/cma_ts1.1145.851597989' AUTOEXTEND ON MAXSIZE 2000G
database datafile '+DATA_DG/DGB_TSM1/DATAFILE/CMA_TS1.1145.851597989' altered.

So with an additional 500Gb, I figured we'd be ok for some time. However 3 hours later I was called out again. Same error.

However now, only 91% of the data file was being used. This equates to 180Gb still free.

Of course what I didn't immediately spot was that just checking the size of the free space isn't enough. You must also consider the amount of space being requested by running processes.

This statement gives you the tablespace usage details.

select file_name, file_id, round(user_bytes/1024/1024,2) "Used(Mb)", round(user_bytes/1024/1024/1024,2) "Used(Gb)", status, autoextensible
from dba_data_files c
where tablespace_name = 'CMA_TS1' order by file_id desc;

And this the percent used. (There are other ways).

select tablespace_name, used_percent, tablespace_size, used_space, (tablespace_size - used_space) free
from dba_tablespace_usage_metrics
where tablespace_name = 'CMA_TS1';

Having obtained the space used figures, take a look at the amount of space currently being requested by the running processes.

select segment_name, bytes/1048576/1024 from dba_segments where tablespace_name='CMA_TS1'
order by 2 desc;

If the segment name is a number rather than an object name, then this represents those temporary segments we're looking for. In my case there were 2 (as expected), and these were sized at 337Gb and 240Gb. Clearly neither of these next extent requirements would fit into the free space available.

I could have added more diskspace, but the prudent thing to do was to locate the session owners and have a quiet word with them. Having done this and explained the realities of initial and next extents, we killed the processes and the scripts were rewritten.

HappyJohn

ORA-30036 and no free UNDO space

We have a database with a large amount (10Gb) of redo that has never need to be increased since the database was built. One morning we find lots of sessions suspended because someone has taken all of the undo.

Firstly, I identified the session that was causing the problem.

SELECT a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC;

The result of this query showed one huge amount of undo held by a single session. In my case it was SID 177. So next I display details about the session.

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and sid = 177;

I've not only displayed the sid and serial# I need to kill the session but also the SPID so I can kill it from unix if necessary.

I also located the SQL for that session and found it was a massive DELETE statement with no commits.

Having got agreement from the user that I could end the session, I killed it.

alter system kill session '177,13989' immediate;


Once the session was killed, I increased the size of the tablespace to allow some sessions to run while the roll back occurred.

This statement shows how much undo is still being used.

select status,
round(sum_bytes / (1024*1024), 0) as MB,
round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
select status, sum(bytes) sum_bytes
from dba_undo_extents
group by status
),
(
select sum(a.bytes) undo_size
from dba_tablespaces c
join v$tablespace b on b.name = c.tablespace_name
join v$datafile a on a.ts# = b.ts#
where c.contents = 'UNDO'
and c.status = 'ONLINE'
);

STATUS MB PERC
--------- ---------- ----------
ACTIVE 10276 96
EXPIRED 1 0
UNEXPIRED 396 4

I continued to monitor the recovery of the database thus.

select * from v$fast_start_transactions;

Note: UNDOBLOCKSDONE increases up to a fixed batch which is then removed from UNDOBLOCKSTOTAL.

Once UNDOBLOCKSTOTAL reached 0, the available bocks in undo went back to normal.

Once the recovery has completed, the above query returns this result.

STATUS MB PERC
--------- ---------- ----------
ACTIVE 81 1
EXPIRED 2 0
UNEXPIRED 10590 99

The quantity of unexpired block is now ok.




HappyJohn.