ALTER DATABASE BEGIN/END BACKUP
To take backup manually or when cloning a database using the following SQL one can place the database in backup mode.
SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.
To find if database or any tablespace is in backup mode, the status in V$BACKUP is ACTIVE
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 11453595 31-OCT-10
2 ACTIVE 11453595 31-OCT-10
3 ACTIVE 11453595 31-OCT-10
4 ACTIVE 11453595 31-OCT-10
… perform database manual or cloning
SQL> ALTER DATABASE END BACKUP;
Database altered.
To check if database is in backup mode
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 11453595 31-OCT-10
2 NOT ACTIVE 11453595 31-OCT-10
3 NOT ACTIVE 11453595 31-OCT-10
4 NOT ACTIVE 11453595 31-OCT-10
If one tries to perform shutdown of database when database is in BACKUP mode, you will receive the following error “ORA-01149″
SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/oradata/TEST/system_01.dbf’
If one tries to set the database that is already in backup mode, one will receive the following error “ORA-01146″
SQL> alter database begin backup;
alter database begin backup
*
ERROR at line 1:
ORA-01146: cannot start online backup – file 1 is already in backup
ORA-01110: data file 1: ‘/u01/oradata/TEST/system_01.dbf’
In 9i to set database is backup mode, “ALTER DATABASE BEGIN/END BACKUP” doesn’t exists so it has to be done at individual table space level by executing “ALTER TABLESPACE <tablespace> BEGIN/END BACKUP”.
If one tries to perform incremental backup using RMAN it will skip backing up datafiles as datafiles are in backup
RMAN> backup incremental level 1 database;
Starting backup at 31-OCT-10
using channel ORA_DISK_1
RMAN-06554: WARNING: file 1 is in backup mode
RMAN-06554: WARNING: file 2 is in backup mode
RMAN-06554: WARNING: file 3 is in backup mode
RMAN-06554: WARNING: file 4 is in backup mode
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/u01/oradata/TEST/sysaux_01.dbf
skipping datafile 00003 because it has not changed
input datafile fno=00001 name=/u01/oradata/TEST/system_01.dbf
skipping datafile 00001 because it has not changed
input datafile fno=00002 name=/u01/oradata/TEST/undo_01.dbf
skipping datafile 00002 because it has not changed
input datafile fno=00004 name=/u01/oradata/TEST/test_01.dbf
skipping datafile 00004 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
How to loop through parameters in batch file?
The following example shows how to loop through parameters in batch file.
c:> TYPE script.bat
@ECHO OFF
:TOP
IF (%1) == () GOTO END
ECHO %1
SHIFT
GOTO TOP
:END
ECHO End
Sample Ouptut:
C:\>.\script.bat aaa bbb
aaa
bbb
End
How to fix RMAN-06059/ORA-19625?
If archive logs are missing RMAN will fail with the following error also in cases when the archive logs are moved to another folder due to archive log filesystem getting full.
RMAN-03002: failure of backup plus archivelog command at 10/31/2010 04:36:37
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/oradata/TEST/arch/1_1148_694732375.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
So running crosscheck on archivelog the missing files are marked as unavailable and one can run Oracle backup
RMAN> change archivelog all crosscheck;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=21 devtype=DISK
validation failed for archived log
archive log filename=/u01/oradata/TEST/arch/1_1148_694732375.dbf recid=359 stamp=716265182
validation failed for archived log
archive log filename=/u01/oradata/TEST/arch/1_1149_694732375.dbf recid=360 stamp=716265185
validation failed for archived log
…
In cases when the archive log filesystem is full and files are moved and you still them to be archived so they can be backed up in the new folder or can be moved back to the original archive log folder but one need to rerun the command “change archivelog all crosscheck;” to backup the archive logs.
“Step Error Description:Class not registered” error message when executing a job through SQLServer Agent?
After scheduling a task through SQLServer Agent it generated the error below when executing the DTS. The DTS package required Oracle OLE-DB drivers which was installed. The error message is a generic error message.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Class not registered
(Microsoft Data Transformation Services (DTS) Package (80040154): Class not registered) (Microsoft OLE DB Service Components (80040154): Class not registered)
Step Error code: 80040154
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Command to be executed: dtsrun.exe /S server /E /N “DTS Package name”
To narrow down the issue the following steps were performed, the command was executed on the command line which ran successfully, then tried executing the command using the service account that SQLServer Agent runs using and which also run successfully too. This narrowed the issue that it was related to when the command was being executed through SQLServer Agent so after restarting SQLServer Agent it fixed the issue.
How to monitor CPU of oracle processes on Windows?
Using QSlice one can monitor CPU usage for individual thread on a windows box. Qslice can be downloaded from the following url http://www.microsoft.com/downloads/en/details.aspx?familyid=6247BB76-13C5-4E0E-B800-53DC1B84A94C&displaylang=en
By clicking on the oracle.exe in Qslice it displays all threads and CPU usage for each thread, so using the following SQL you can match the corresponding SID.
SQL> select to_char(p.spid, 'xxxxxxxx') threadid, s.sid, s.username, s.program
from v$process p, v$session s
where p.addr=s.paddr;
Sample output:
THREADID SID USERNAME PROGRAM
——— ———- —————————— ————————-
d90 125 ORACLE.EXE (PMON)
1728 187 ORACLE.EXE (VKTM)
1660 1 ORACLE.EXE (GEN0)
d84 64 ORACLE.EXE (DIAG)
1794 126 ORACLE.EXE (DBRM)
144c 188 ORACLE.EXE (PSP0)
…
How to find tables that have a specific column name?
Using the following SQL one can find tables that contain a column name.
In SQLServer
SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name like ‘%PASSWORD%’
ORDER BY table_name;
In Oracle
SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE column_name like 'PASSWORD'
ORDER by table_name;
Example:
OWNER TABLE_NAME COLUMN_NAME
—————————— —————————— ——————————
SYS DBA_USERS PASSWORD
How to setup Database Mail on SQL Server 2005?
In SQLSever 2005 msdb.dbo.sp_send_dbmail is the new way of sending email and it replaces xp_sendmail. Below are the steps how to setup Database Mail to use msdb.dbo.sp_send_dbmail.
– To check if “Database Mail XPs” option is turned ON
use master
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs'
go
sp_configure 'show advanced options',0
go
reconfigure
go
– If you need to enable “Database Mail XPs”
use master
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go
– Create new database mail account to hold SMTP information
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'reply@oraclespin.com',
@replyto_address = 'reply@oraclespin.com',
@display_name = 'Database Administrator',
@mailserver_name = 'mailserver.com', — Mail server
@use_default_credentials = 1 — When this parameter is 1, Database Mail uses the credentials of the Database Engine
– Create new Database Mail profile
exec msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBMailProfile',
@description = 'Profile used for database mail'
– Adds the account to the profile, the sequence number determines the order
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBMailProfile',
@account_name = 'DBMailAccount',
@sequence_number = 1
– Grants permission for a database user or role to use a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBMailProfile',
@principal_name = 'public', — if public profile it allows all principals in the database access to the profile
@is_default = 0 — Specifies whether this profile is the default profile
– Test email
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile',
@recipients='ajaffer@oraclespin.com',
@subject = 'My Mail Test',
@body = 'Test'
Global Statistics vs. Histograms with DBMS_STATS Package
The following article explains global statistics versus histogram with DBMS_STATS
http://www.akadia.com/services/ora_gather_statistics.html
How to restore table statistics (STATS)?
When using DBMS_STATS to gather table statics Oracle backups up the statistics before overwriting it.
The following example shows how dbms_stats.gather_*_stats works and how to restore statistics
Example:
– stats was gathered on a table
SQL> exec dbms_Stats.gather_table_stats('SCOTT', 'TEST');
PL/SQL procedure successfully completed.
– verified stats by checking last_analyzed date
SQL> select to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') from user_tables where table_name = 'TEST';
TO_CHAR(LAST_ANALYZE
——————–
06-OCT-2010 00:08:05
– Some updates/inserts/deletes occurred on the table, gather table stats again
SQL> exec dbms_Stats.gather_table_stats('SCOTT', 'TEST');
– show last analyzed date
SQL> select to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') from user_tables where table_name = 'TEST';
TO_CHAR(LAST_ANALYZE
——————–
06-OCT-2010 00:08:48
– Check historical stats present for the table
SQL> select table_name, to_char(stats_update_time, 'DD-MON-YYYY HH24:MI:SS') from dba_tab_stats_history where owner = 'SCOTT';
TABLE_NAME TO_CHAR(STATS_UPDATE
—————————— ——————–
TEST 06-OCT-2010 00:08:05
TEST 06-OCT-2010 00:08:48
– restore stats to time it was before
SQL> exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCOTT', tabname=>'TEST', as_of_timestamp=>TO_DATE('06-OCT-2010 00:08:48', 'DD-MON-YYYY HH24:MI:SS'));
PL/SQL procedure successfully completed.
– verify stats was restored by checking date
SQL> select to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') from user_tables where table_name = 'TEST';
TO_CHAR(LAST_ANALYZE
——————–
06-OCT-2010 00:08:05
See the oldest STATs data available
How to change STATS retention
How to identify the job running through SQLServer Agent using SQL?
– List processes currently running through SQLServer Agent
select spid, program_name, blocked from master..sysprocesses where program_name like ‘SQLAgent%’
spid program_name blocked
—— ——————————————————————————— ——-
148 SQLAgent – TSQL JobStep (Job 0x6CDB1221B7584941B63E44C95E805E43 : Step 1) 0
– Using the Job from program_name in Step 1, find information on the job
select job_id, name, description from msdb..sysjobs where job_id = convert(uniqueidentifier, 0x6CDB1221B7584941B63E44C95E805E43)
job_id name description
———————————— ————————————- ————
2112DB6C-58B7-4149-B63E-44C95E805E43 purge transaction
