To move/rename current block change tracking file, one would need to restart the database. # Get current block change tracking file SQL> SELECT filename FROM v$block_change_tracking; FILENAME -------------------------------------------------------------------------------- /tmp/blocktracking.f # Shutdown database and start database in mount state SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; # Rename block tracking file and SQL> ALTER DATABASE RENAME FILE … Continue reading How to rename block change tracking file?
Tag: block
List blocked sessions in SQLServer
List sessions that are being blocked select spid , blocked being_blocked , db_name(dbid) , login_time , last_batch , program_name from master..sysprocesses where blocked <> 0 Output: spid being_blocked login_time last_batch program_name ------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- 53 52 test 2010-10-04 20:40:41.780 2010-10-04 20:45:50.357 Microsoft SQL Server Management Studio - Query
Blocking locks
http://www.orafaq.com/node/854 SQL from the link above URL to find blocking SQL select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session … Continue reading Blocking locks
How to find sessions that are blocked?
To find sessions that are blocking a session and blocked session, one can run the following SQL which works on SQLServer 2000 and SQL Server 2005. select spid, blocked, dbid, open_tran, status, hostname, loginame, waittime, waitresource, login_time from master..sysprocesses where spid in (select blocked from master..sysprocesses) or blocked != 0 Sample Ouptut: In this example … Continue reading How to find sessions that are blocked?
How to find and fix block corruption using RMAN?
One of the scenario we ran into when one of the data file reported there was block corruption as RMAN reported the following message in its logs. Note: This scenario was done on 9.2.0.1 running Linux. RMAN-03009: failure of backup command on ch1 channel at 07/02/2009 04:27:06 ORA-19566: exceeded limit of 0 corrupt blocks for … Continue reading How to find and fix block corruption using RMAN?
How to find blocking session?
In 10g, there is a column in V$session called blocking_session to find the session id that is blocking. Session 1) sid # 145 SQL> select userenv('sid') from dual; USERENV('SID') -------------- 145 SQL> create table t ( x number ); Table created. SQL> create unique index t_idx on t(x); Index created. SQL> insert into t values … Continue reading How to find blocking session?
How to enable/disable block tracking for RMAN?
Using block tracking one can improve performance of the backup time of incremental backup from hours to minutes depending on size of database as it no longer needs to scan blocks in datafiles to find blocks changed it can use the block tracking file to identify changed blocks for incremental backup. Enabling this starts a … Continue reading How to enable/disable block tracking for RMAN?
alexlima.com 