Advertisements

Oracle Activating the Standby Database – Switchover Method

Activating the Standby Database – Switchover Method

The main advantage of a graceful switchover is that it avoids the resetlogs operation. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.

1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In PRIMARY database check the database role. (ABCD02 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

4. In STANDBY database check the database role. (ABCD03 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

5. Shutdown the PRIMARY database. (ABCD02 server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the PRIMARY database in RESTRICTED mode. (ABCD02 server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the PRIMARY database. (ABCD02 server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (ABCD02 & ABCD03)

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.
9. Initiate the switchover on the PRIMARY database. (ABCD02)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
10. Once the step above has completed, log on to the STANDBY database and issues the following command. (ABCD03)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
11. Immediately return to the FORMER PRIMARY database and issue a shutdown and mount the NEW STANDBY database. (ABCD02 server)
SQL> shutdown immediate
SQL> startup mount;
12. On the NEW PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (ABCD03 server)
SQL> alter database open;
Database opened.

13. Verify the new STANDBY’S status. (ABCD02 server)
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY
14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 0

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
15. Put the NEW STANDBY/FORMER PRIMARY database into managed recovery mode. (ABCD02)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16. Test the communications for archive by performing a log switch. (ABCD03 server)
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Now check whether these logs are applying in the NEW STANDBY and also check the listener status.
17. On the NEW PRIMARY database instance, take a backup if possible. (ABCD03 server)
Switch back from SZB location (ABCD03) to KJ location orginal primary sever (ABCD02)
Switch back to Orginal Primary Database – Switchover Method
1. Prerequisites :-

There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 10

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

3. In New PRIMARY database check the database role. (ABCD03 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

4. In New STANDBY database check the database role. (ABCD02 server)

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE
——— —————- ——- ——————– ———– ———- ——————–
AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

5. Shutdown the New PRIMARY database. (ABCD03 server)

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

6. Open the New PRIMARY database in RESTRICTED mode. (ABCD03 server)

SQL> startup restrict

ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

7. Archive the current log on the New PRIMARY database. (ABCD03 server)

SQL> alter system archive log current;

System altered.

8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (ABCD02 & ABCD03)

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 1934

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.
9. Initiate the switchover on the New PRIMARY database. (ABCD03)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
10. Once the step above has completed, log on to the New STANDBY database/Orginal Primary and issues the following command. (ABCD02)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
11. Immediately return to the FORMER PRIMARY database/Orginal STANDBY and issue a shutdown and mount the Orginal STANDBY database. (ABCD03 server)
SQL> shutdown immediate
SQL> startup mount;
12. On the Orginal PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (ABCD02 server)
SQL> alter database open;
Database opened.

13. Verify the orginal STANDBY’S status. (ABCD03 server)
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
——— —————-
PHYSICAL STANDBY
14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (ABCD02 & ABCD03)

SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

NAME TYPE VALUE
———————————— ———– ————-
job_queue_processes integer 0

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
15. Put the Orginal STANDBY/FORMER PRIMARY database into managed recovery mode. (ABCD03)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16. Test the communications for archive by performing a log switch. (ABCD02 server)
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Now check whether these logs are applying in the Orginal STANDBY and also check the listener status.
17. On the Orginal PRIMARY database instance, take a backup if possible. (ABCD02 server)

Advertisements

Oracle Database Health Check Script

SPOOL OFF
CLEAR SCREEN
SPOOL /tmp/quickaudit.lst

–SELECT SYSDATE FROM DUAL;
–SHOW USER

PROMPT
PROMPT ———————————————————————–|
PROMPT

SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK ON

PROMPT
PROMPT Checking database name and archive mode, dbid
PROMPT

column NAME format A9
column LOG_MODE format A12

SELECT NAME,CREATED, LOG_MODE, DBID FROM V$DATABASE;

PROMPT
PROMPT ———————————————————————–|
PROMPT

PROMPT
PROMPT Checking Time since last RMAN backup
PROMPT

select (sysdate-min(t))*24 from
(
select max(b.CHECKPOINT_TIME) t
from v$backup_datafile b, v$tablespace ts, v$datafile f
where INCLUDED_IN_DATABASE_BACKUP=’YES’
and f.file#=b.file#
and f.ts#=ts.ts#
group by f.file#
);

PROMPT
PROMPT ———————————————————————–|
PROMPT

PROMPT
PROMPT Checking Tablespace name and status
PROMPT

column TABLESPACE_NAME format a30
column STATUS format a10
set pagesize 400

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space in tablespaces
PROMPT

column tablespace_name format a30

SELECT tablespace_name ,sum(bytes)/1024/1024 “MB Free” FROM dba_free_space WHERE
tablespace_name ‘TEMP’ GROUP BY tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking freespace by tablespace
PROMPT

column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a16 heading “Tablespace Name”
column bytes format 9,999,999,999,999 heading “Total Bytes”
column used format 99,999,999,999 heading “Used”
column free format 999,999,999,999 heading “Free”
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report

set linesize 132
set termout off
select a.tablespace_name name,
b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id ) –
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) –
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||’.’||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking Size and usage in GB of Flash Recovery Area
PROMPT

SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space In Flash Recovery Area
PROMPT

column FILE_TYPE format a20

select * from v$flash_recovery_area_usage;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking last sequence in v$archived_log
PROMPT

clear screen
set linesize 100

column STANDBY format a20
column applied format a10

–select max(sequence#), applied from v$archived_log where applied = ‘YES’ group by applied;

SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;

prompt
prompt—————-Last log on Primary————————————–|
prompt

select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking switchover status
PROMPT

select switchover_status from v$database;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for 20 Largest items in Database
PROMPT

column SEGMENT_NAME format A50

SELECT * FROM (
SELECT
OWNER, SEGMENT_NAME, BYTES/1024/1024 SIZE_MB
FROM
DBA_SEGMENTS
ORDER BY
BYTES/1024/1024 DESC ) WHERE ROWNUM <= 20;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for Failed jobs
PROMPT

SELECT
OWNER,
LOG_DATE,
JOB_NAME,
STATUS –,
— REQ_START_DATE,
— ACTUAL_START_DATE,
— RUN_DURATION
FROM
DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
STATUS ‘SUCCEEDED’
AND
LOG_DATE > SYSDATE -7;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking for invalid objects
PROMPT

column owner format A15
column object_name format A30 heading ‘Object’
column object_id format 999999 heading “Id#”
column object_type format A15
column status format A8

select owner, object_name, object_id, object_type, status
from dba_objects where status != ‘VALID’ and object_type != ‘SYNONYM’;

PROMPT
PROMPT ————————————————————————|

PROMPT
PROMPT How large is the database
PROMPT

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking the recycle Bin
PROMPT

SELECT
OWNER, SUM(SPACE) AS TOTAL_BLOCKS
FROM
DBA_RECYCLEBIN GROUP BY OWNER
ORDER BY OWNER;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking database versions
PROMPT

column BANNER format A64
select * from v$version;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking control file(s)
PROMPT

column STATUS format a7
column NAME format a68
column IS_RECOVERY_DEST_FILE format a3
set linesize 132

SELECT * FROM V$CONTROLFILE;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking redo logs and group(s)
PROMPT

column member format a90

SELECT group#, member FROM v$logfile;

SPOOL OFF

exit

Create a Full Database Backup (SQL Server) Using SQL Server Management Studio

Create a Full Database Backup (SQL Server) Using SQL Server Management Studio:
———————————————————————————————————-

Recommendations

As a database increases in size full database backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement a full database backup with a series of differential database backups. For more information, see Differential Backups (SQL Server).

You can estimate the size of a full database backup by using the sp_spaceused system stored procedure.

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).

Permissions:

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

To back up a database:

1.After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2.Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.

3.Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4.In the Database list box, verify the database name. You can optionally select a different database from the list.

5.You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).

6.In the Backup type list box, select Full.

7.Note that after creating a full database backup, you can create a differential database backup; for more information, see Create a Differential Database Backup (SQL Server).

8.Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.

Note:
When the Differential option is selected, you cannot create a copy-only backup.

9.For Backup component, click Database.

10.Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

11.Optionally, in the Description text box, enter a description of the backup set.

12.Specify when the backup set will expire and can be overwritten without explicitly skipping verification of the expiration data:
To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings Page). To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

13.Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

14.To view or select the advanced options, click Options in the Select a page pane.

15.Select an Overwrite Media option, by clicking one of the following:

Back up to the existing media set :
For this option, click either Append to the existing backup set or Overwrite all existing backup sets. For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.

Back up to a new media set, and erase all existing backup sets

For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

16.In the Reliability section, optionally check:

Verify backup when finished.

Perform checksum before writing to media, and, optionally, Continue on checksum error.
17.If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

18.SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

Using Transact-SQL to create a full database backup:

1.Execute the BACKUP DATABASE statement to create the full database backup, specifying:

The name of the database to back up.

The backup device where the full database backup is written.

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE database

TO backup_device [ ,…n ]

[ WITH with_options [ ,…o ] ] ;

Examples:

A. Backing up to a disk device
The following example backs up the complete AdventureWorks2012 database to disk, by using FORMAT to create a new media set.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.Bak’
WITH FORMAT,
MEDIANAME = ‘Z_SQLServerBackups’,
NAME = ‘Full Backup of AdventureWorks2012’;
GO

B. Backing up to a tape device
The following example backs up the complete AdventureWorks2012 database to tape, appending the backup to the previous backups.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO TAPE = ‘\\.\Tape0’
WITH NOINIT,
NAME = ‘Full Backup of AdventureWorks2012’;
GO

C. Backing up to a logical tape device
The following example creates a logical backup device for a tape drive. The example then backs up the complete AdventureWorks2012 database to that device.

— Create a logical backup device,
— AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice ‘tape’, ‘AdventureWorks2012_Bak_Tape’, ‘\\.\tape0’;
USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO AdventureWorks2012_Bak_Tape
WITH FORMAT,
MEDIANAME = ‘AdventureWorks2012_Bak_Tape’,
MEDIADESCRIPTION = ‘\\.\tape0’,
NAME = ‘Full Backup of AdventureWorks2012’;
GO

Follow these steps to create the job and put your backup statement inside of it:

1.Expand the ‘SQL Server Agent’ tree and right-click on ‘Jobs’. Then choose the top item, ‘New Job…’

2.Now you’ve got the new job dialog box. Filling in the info is pretty easy. You need to give your job a name, and everything else is optional. Here I’m going to fill in the name of the job as ‘Backup user database’.

3.Next click on the ‘Steps’ pane on the left and you’ll be presented with this screen. It’s blank because you haven’t created any steps yet. So go ahead and click on the ‘New’ button at the bottom.

4. This is where the real magic happens. Again, you have to fill in a name so you know what the step is called. Make it something descriptive. There are several step types to choose from, but the default is T-SQL and since we’re running a T-SQL command that’s clearly the one we want to go with. The database defaults to ‘master’ and that’s just fine with us. Here’s what we have so far. You see the only thing we’ve had to do is fill in the step name.

5. The only thing left to do is to copy your backup statement into the query window. Of course it’s always a good idea to make sure your code will parse before you try to run it. Just click the ‘Parse’ button I’ve circled. Here’s what that looks like.

6.Now click OK and it’ll take you back to your new job window and now you’ll see your job step listed. And you can stack as many as you like inside there.

7. To schedule your job, just click on ‘Schedules’ on the left and then choose the schedule that’s right for you. It works similar to the the way it does in Windows so there’s really not much need for me to rehash it here.

8.Once you click OK all the way out until the new job box is closed, your job will be added to the job tree. You may have to right-click on ‘Jobs’ and refresh the display for it to show up.

Now that you’ve created the job to backup your database I’d like to go back to the command again and add one more part. See, when you backup a database to a file it won’t overwrite the file by default. By default it will append to the file, so your backup file will just get bigger and bigger. So what you want to do is add a special flag to the command that tells it to initialize (or overwrite) the file every time. That makes our backup command look like this now:

BACKUP Database Model
To disk = ‘c:\ModelBackup.bak’

With INIT

This is a good thing to keep in mind while you’re creating your job.

MOVING MASTER, MSSQL SYSTEMRESOURSE DB PRIMARY AND LOG FILES FROM ONE DRIVE TO ANOTHER DRIVE IN THE SAME MACHINE IN MSSQL Server 2005

MOVING MASTER, MSSQL SYSTEMRESOURSE DB PRIMARY AND LOG FILES FROM ONE DRIVE TO ANOTHER DRIVE IN THE SAME MACHINE IN MSSQL Server 2005

1.Goto Start > All programs > Microsoft sq l server 2005 > Configuration Tools > sq l server configuration manager.
2.Right Click on MSSQLSERVER service and click ADVANCED Tab.
Check the START PARAMETER and change the master primary and log file from old location to new location with the file name.(refer below command)

-dD:\MSSQLSystemDB\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\MSSQLSystemDB\mastlog.ldf

where -d is primary file path and -l is log file path

3.Stop the MSSQLSERVER Services.
4.Move the Master and Mssqlsystemresource DB Primary and Log file into New Location(As mentioned in the START PARAMETER)
5.Start the MSSQLSERVER SERVICE in Recovery-mode. Type below command in the cmd-Prompt
> NET START MSSQLSERVER /f /T3608

For a named instance, run the following command
> NET START MSSQL$instancename /f /T3608

6.Goto Start > All programs > Microsoft sq l server 2005 > MS server Managent Studio. Connect the Instance. Click on New Query.

7.Alter the Resource DB primary file and log file.Execute the Query.

–SYSTEMRESOURCE DB Primary File
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘new_path_of_master\mssqlsystemresource.mdf’);
GO
–SYSTEMRESOURCE DB Log File
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘new_path_of_master\mssqlsystemresource.ldf’);
GO

8.Set the MSSQLSYSTEMRESOURCE DB as READ-ONLY mode
alter database mssqlsystemresource set read_only;

9.Stop and Start the service the MSSQLSERVER service
10. Check the Master files.
sp_helpdb master

Note:Master and mssqlsystemresource primary and log files are in the same location.

Moving model,msdb,tempdb(SYSTEM Databases) from one location to another location in the same machine.(1 hour downtime)

1.For reference, Copy the primary,secondary and log files location of model,msdb and tempdb

USE master
Go

SELECT
DB_NAME(database_id) AS “Database Name”
, name AS “Logical File Name”
, physical_name AS “Physical File Location”
, state_desc AS “State”
FROM
sys.master_files
WHERE
database_id IN (DB_ID(N’msdb’), DB_ID(N’model’),DB_ID(N’tempdb’))
ORDER BY
DB_NAME(database_id);
Go

2.Modify the data and log filename for new location of db files by using Alter database. Have to done for each datbases of files.Execute the Query.

USE master
Go

ALTER DATABASE model
MODIFY FILE ( NAME = ‘modeldev’ , FILENAME = ‘D:\MSSQLSystemDB\model.mdf’ );
Go

ALTER DATABASE model
MODIFY FILE ( NAME = ‘modellog’ , FILENAME = ‘D:\MSSQLSystemDB\modellog.ldf’ );
Go

ALTER DATABASE msdb
MODIFY FILE ( NAME = ‘MSDBData’ , FILENAME = ‘D:\MSSQLSystemDB\MSDBData.mdf’ );
Go

ALTER DATABASE msdb
MODIFY FILE ( NAME = ‘MSDBLog’ , FILENAME = ‘D:\MSSQLSystemDB\MSDBLog.ldf’ );
Go

ALTER DATABASE tempdb
MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘D:\MSSQLSystemDB\tempdb.mdf’);
Go

ALTER DATABASE tempdb
MODIFY FILE (NAME = ‘templog’, FILENAME = ‘D:\MSSQLSystemDB\templog.ldf’);
Go

3.Stop MSSQLSERVER Services.
4.Move the model,msdb and tempdb primary and log files into new location.
5.Start MSSQLSERVER Services.
6.Check the System DB files.

MS SQL SERVER – Finding Backup status

–SQL SERVER – Finding Last Backup Time for All Database

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),’-‘) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

——————————————————————————————-

— The following T-SQL Statement lists all of the databases in the server and the last day the backup happened.

SELECT db.name,
case when MAX(b.backup_finish_date) is NULL then ‘No Backup’ else convert(varchar(100),
MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = ‘D’
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC

——————————————————————————————-

— The following T-SQL statement gets all the information related to the current backup location from the msdb database.

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

Change or Set the MySQL Root password (Windows):

Change or Set the MySQL Root password (Windows):
================================================

1. Stop your MySQL server completely. This can be done by accessing the Services
window inside Windows XP and Windows Server 2003, where you can stop the MySQL service.

2. Open your MS-DOS command prompt using “cmd” inside the Run window.
Inside it navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

3. Execute the following command in the command prompt: mysqld.exe -u root –skip-grant-tables

4. Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

5. Navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

6. Enter “mysql” and press enter.

7. You should now have the MySQL command prompt working.
Type “use mysql;” so that we switch to the “mysql” database.

8. Execute the following command to update the password:

UPDATE user SET Password = PASSWORD(‘NewPassword’) WHERE User = ‘root’;

However, you can now run any SQL command that you wish.

After you are finished close the first command prompt and type “exit;” in the second command prompt
windows to disconnect successfully. You can now start the MySQL service.

Please note that the 8 step process above can differ depending on the MySQL version you are using,
how you configured your server, etc. However, many times you can still easily
work around a problem that you experience in any of the steps.

To change root password to an empty:
====================================

UPDATE user SET Password = PASSWORD(“”) WHERE User = ‘root’;

use mysql;
update user set password=null where User=’root’;
flush privileges;
quit;

How to Check and Repair MySQL Tables Using Mysqlcheck

How to Check and Repair MySQL Tables Using Mysqlcheck
=====================================================

Database Name : sms
Table Name: attend

1. Check a Specific Table in a Database

mysqlcheck -c sms attend -u root -p

2. Analyze Tables using Mysqlcheck

# mysqlcheck -a sms attend -u root -p

3. Check All Tables and All Databases

# mysqlcheck -c -u root -p –all-databases

4. Optimize Tables using Mysqlcheck

# mysqlcheck -o sms attend -u root -p

5. Repair Tables using Mysqlcheck

# mysqlcheck -r sms attend -u root -p

SELECT table_name as name, table_rows as rows FROM information_schema.tables as t1 WHERE table_rows > 0

select * from (show table status like ‘%attend%’) as t1;

LIKE ‘attend’ G

MySQL Bin Files Eating Lots of Disk Space

MySQL Bin Files Eating Lots of Disk Space
==========================================

I get a large amount of bin files in the MySQL data directory called “server-bin.n”
or mysql-bin.00000n, where n is a number that increments.

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 8.2G 1.1G 89% /var

Solution:
========

We need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs.

For Safe side I taken backup of the main log files:
—————————————————
[root@ENMDB1 mysql]# cp slow-queries.log /mysqldb/DBBACKUP/slow-queries.log_bak
[root@ENMDB1 mysql]# cp mysql-query.log /mysqldb/DBBACKUP/mysql-query.log_bak

Purge BINARY LOGS Logs (leaving December BINARY LOGS)
Note: For retaing current month log files purge to mysql-bin.000469 (Nov 30 05:08 mysql-bin.000469).

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000469’;
Query OK, 0 rows affected (40.85 sec)

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 888M 8.3G 10% /var

The binary log has two important purposes:

Data Recovery : It may be used for data recovery operations.
After a backup file has been restored, the events in the binary
log that were recorded after the backup was made are re-executed.
These events bring databases up to date from the point of the backup.

High availability / replication : The binary log is used on master
replication servers as a record of the statements to be sent
to slave servers. The master server sends the events contained
in its binary log to its slaves, which execute those events to
make the same data changes that were made on the master.