ORACLE @ Work

Linux CPU stress testing sample script

2018-09-19T21:08:00.001-04:00

for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100; do while : ; do : ; done & done

for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 ; do while : ; do : ; done & done

x="x" ; while : ; do x=$x$x ; echo -n "." ; done

for i in `seq 64`; do perl -e '$z=time()+(10*60); while (time()<$z) { $j++; $sqrt = sqrt($j) for (1..9999); }' & done

fulload() { dd if=/dev/zero of=/dev/null | dd if=/dev/zero of=/dev/null | dd if=/dev/zero of=/dev/null | dd if=/dev/zero of=/dev/null & }; fulload; read; killall dd

Create bigfile:
base64 /dev/urandom | head -c 4000000 > 4mb.txt

SQL Joins

2016-07-08T13:41:00.001-04:00

Codeproject has this image which explains the simple basics of SQL joins, taken from: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

2014-08-06T13:43:00.000-04:00

MySQL 5.6 added comprehensive password security mechanism how password and internally handled and encrypted. One major feature is auto encrypted password generation when mysql first time installed on your system. you can find the encrypted password in /root/.mysql_secret. Once you are into mysql using encrypted password you will get this error until unless root password is not set :

SET PASSWORD = PASSWORD('new_password');

Here is how the error looks like:

-bash-4.1$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.19-enterprise-commercial-advanced

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Just reset the password and you should be able login on next attempt:

-bash-4.1$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.19-enterprise-commercial-advanced

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('newpassword');

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

-bash-4.1$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.6.19-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

“The program can’t start because MSVCR100.dll is missing from your computer.” error on Windows

2013-10-22T10:50:00.000-04:00

I was installing a software on windows 2008R2 64 bit edition and ran into this error.

"The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem"

After doing some online research I figured the Microsoft Visual C++ Redistributable package requires installed prior installing my desired installation.

This Redistribution package is available on microsoft website. Here's the link for both x86 or x64 edition.

32 bit: http://www.microsoft.com/download/en/details.aspx?id=5555
64 bit: http://www.microsoft.com/download/en/details.aspx?id=14632

What's Your Favorite Feature of SQL Server 2012?

2013-10-01T14:38:00.001-04:00

MySQL Import Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

2013-08-23T08:54:00.002-04:00

Today I was importing a MySQL database and ran into this Error “Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes”.

Basically , When you dump table data from MySQL, you may end up pulling a large chunk of data and it may exceed the MySQL client’s max_allowed_packet variable. If that happens, you might catch an error like this:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tablename` at row: 1627

The default max_allowed_packet size is 25M, and you can adjust it for good within your my.cnf by setting the variable in a section for mysqldump:

The fix is to increase the MySQL daemon’s max_allowed_packet. You can do this to a running daemon by logging in as Super and running the following commands. Keeping the session open create a 2nd session in which to run the import.

mysql> set global net_buffer_length=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected (0.00 sec)

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..

MySQL : Monitor live MySQL queries

2013-07-31T10:44:00.001-04:00

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for as this won’t show the whole SQL text.

Here are the steps to dump sql into a file for analysis.

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+

| Variable_name | Value |

+------------------+----------------------------+

| general_log | OFF |

| general_log_file | /var/run/mysqld/mysqld.log |

+------------------+----------------------------+

Turn on the SQL Logging and go to the general_log_file location.

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

Then don't forget to disable the general_log parameter to OFF.

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

MSSQL : Bring database back to normal from Restoring State

2013-07-26T16:17:00.002-04:00

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last. If your database is still in the restoring state and you want to recover it without restoring additional backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to use.

Here is the syntax.

RESTORE DATABASE <DATABASE_NAME>

WITH RECOVERY

SQL Server: Linked Server is not configured for RPC error:

2013-07-19T09:44:00.001-04:00

Workaround for calling table-valued function remotely in SQL Server over Linked Server needs some fancy in the coding. Something like this

EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'

Now what if the SQL fails due to below error:

Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.

This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.

For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.

That's it. Problem solved.

MSSQL Server to MSSQL Server Instance connectivity with Linked Server

2013-07-19T09:39:00.002-04:00

In order to create LINKED Server the user needs to have SYSADMIN privileges. Here in this demonstration I’m going to show the preferred LINKED SERVER option called “Be made using the login’s current security context”. Condition is, LOGIN needs to stay on both the servers. This is the preferred and recommended method in terms of security as other users can’t use this link except for the Login I’ll be using.

Here are the steps:

1. I’m going to use a user called TEST for this demonstration, and going to provide SYSADMIN privilege to this user. Once the link created and tested, I’ll revoke SYSADMIN privilege from the TEST user.

2. Login as TEST user

3. Open SSMS, Go to Server Objects-->Right Click Linked Servers--> New Linked Server. The below form pops up

4. Give a name for “Linked Server” , choose the provider as shown on form. Product name here I mentioned ‘*’ to specify any SQL Server product. On the Data source I’m mentioning the instance name from where I’d be pulling data. It’s “MSSQLTEST” for my case.

5. On the security tab select “Be made using the login’s current security context”. This would use my used Loging ie, “TEST” for this case.

6. Click OK, now you can browse through the objects using SSMS-->Linked Servers--> TEST_LINK-->Catalogs or you could use below syntax to fetch any table data

select * from [TEST_LINK].[TEST_DB].[dbo].[TEST_TABLE];
7. Revoke “Sysadmin” privilege from “TEST” user

My “Linked Servers” has been created!

SQL Server - Maintenace plan integrity checks fail with "Alter Failed for server xxx"

2013-07-18T09:14:00.001-04:00

My maintenance plan integrity check task was failing with "Alter failed for server XXX".

After tracing i found the maintenance plan executes sp_configure 'USER OPTIONS',xxxx followed by RECONFIGURE. The reconfigure statement then generates an error causing the plan to fail. This was because the server configuration option "Allow Updates" was set to 1. Changed the setting back to 0 and the reconfigure statement started to work again!

Following command changes "Allow Updates" parameter to "0"

SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE
GO

SQL Server Performance Dashboard Reports in SSMS

2013-06-28T12:03:00.002-04:00

The step by step procedure has been published on Microsoft MSDN blogs both for 2005 & 2008.
Couple of tweaks required for 2008, but everything is documented.
Here's the link.

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx

This is how it looks like:

When did a user changed his password in Oracle?

2013-06-11T11:42:00.002-04:00

The table sys.user$ contains the field ptime, which keeps the time when the password was changed the last time over. Do not confound it with ctime, which is the "creation time", nor with ltime, which is the time the account has been locked (if any).

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED"

FROM USER$

WHERE ptime IS NOT NULL

ORDER BY ptime DESC;

Recover MySQL Root Password

2013-06-07T14:25:00.001-04:00

If you already have the root password, but just want to change it. Then it's simple and can be

done when MySQL Database is up & running.

/usr/bin/mysqladmin -u root password 'new-password’

But If ROOT password is lost then following steps needs to be performed and requires reboot of

MySQL Database:

STOP MySQL Service:

/etc/init.d/mysql stop

START MySQL Without grants:

mysqld_safe --skip-grant-tables &

Connect to mysql server using mysql client:

mysql -u root

Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Stop MySQL Server:

# /etc/init.d/mysql stop

Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p

Create oracle as is user

2013-06-04T16:50:00.003-04:00

The following script takes the input for SOURCE user and for the user I want to create. This script can go through many customization to suit your needs.
This can extract existing user hash password, tablespace quota, object/system/role privileges. Infact everything associated with the source user.

SET SERVEROUTPUT ON

SET LINESIZE 200

DECLARE

TYPE T_CURSOR IS REF CURSOR;

V_CURSOR T_CURSOR;

V_USER VARCHAR2 (30) := UPPER ('&1');

V_NEW_USER VARCHAR2 (30) := UPPER ('&2');

V_PASSWORD1 VARCHAR2 (30);

V_PASSWORD2 VARCHAR2 (30);

V_D_TBS VARCHAR2 (30);

V_T_TBS VARCHAR2 (30);

V_PROFILE VARCHAR2 (30);

V_LOCK_DATE DATE;

V_COUNTER NUMBER;

V_MAX_BYTES NUMBER;

V_PRIVILEGE VARCHAR2 (40);

V_ADMIN_OPT VARCHAR2 (3);

V_ROLE VARCHAR2 (30);

V_DEF_ROLE VARCHAR2 (3);

V_DEFROLE NUMBER;

V_OUTPUT VARCHAR2 (500);

V_TABLE_NAME VARCHAR2 (30);

V_OWNER VARCHAR2 (30);

V_GRANT_OPT VARCHAR2 (3);

V_HIER_OPT VARCHAR2 (3);

V_COL_NAME VARCHAR2 (30);

V_AUDIT_OPT VARCHAR2 (40);

V_SUCCESS VARCHAR2 (10);

V_FAILURE VARCHAR2 (10);

BEGIN

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_USERS

WHERE USERNAME = V_USER;

IF V_COUNTER <> 0

THEN

IF V_NEW_USER IS NULL

THEN

V_NEW_USER := V_USER;

END IF;

SELECT B.PASSWORD, A.PASSWORD, A.DEFAULT_TABLESPACE, A.TEMPORARY_TABLESPACE, A.PROFILE, A.LOCK_DATE

INTO V_PASSWORD1, V_PASSWORD2, V_D_TBS, V_T_TBS, V_PROFILE, V_LOCK_DATE

FROM DBA_USERS A, SYS.USER$ B

WHERE A.USERNAME = V_USER

AND A.USERNAME = B.NAME;

--Create User Command * Begin

DBMS_OUTPUT.PUT_LINE ('--Create User command');

DBMS_OUTPUT.PUT_LINE ('CREATE USER ' || V_NEW_USER);

IF V_PASSWORD2 = 'EXTERNAL'

THEN

DBMS_OUTPUT.PUT_LINE ('IDENTIFIED EXTERNALLY');

ELSE

DBMS_OUTPUT.PUT_LINE ('IDENTIFIED BY VALUES ''' || V_PASSWORD1 || '''');

END IF;

DBMS_OUTPUT.PUT_LINE ('DEFAULT TABLESPACE ' || V_D_TBS);

DBMS_OUTPUT.PUT_LINE ('TEMPORARY TABLESPACE ' || V_T_TBS);

DBMS_OUTPUT.PUT_LINE ('PROFILE ' || V_PROFILE);

IF V_LOCK_DATE IS NULL THEN

DBMS_OUTPUT.PUT_LINE ('ACCOUNT UNLOCK;');

ELSE

DBMS_OUTPUT.PUT_LINE ('ACCOUNT LOCK;');

END IF;

--Create User Command * End

--Tablespace Quotas * Begin

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_TS_QUOTAS

WHERE USERNAME = V_USER;

IF V_COUNTER > 0

THEN

DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' tablespace quotas');

OPEN V_CURSOR FOR

SELECT TABLESPACE_NAME, MAX_BYTES

FROM DBA_TS_QUOTAS

WHERE USERNAME = V_USER;

LOOP

FETCH V_CURSOR

INTO V_D_TBS, V_MAX_BYTES;

EXIT WHEN V_CURSOR%NOTFOUND;

IF V_MAX_BYTES = -1

THEN

DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' QUOTA UNLIMITED ON ' || V_D_TBS || ';');

ELSE

DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' QUOTA ' || V_MAX_BYTES || ' ON ' || V_D_TBS || ';');

END IF;

END LOOP;

CLOSE V_CURSOR;

END IF;

--Tablespace Quotas * End

--Roles * Begin

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_ROLE_PRIVS

WHERE GRANTEE = V_USER;

IF V_COUNTER > 0

THEN

DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' Roles');

SELECT DEFROLE

INTO V_DEFROLE

FROM SYS.USER$

WHERE NAME = V_USER;

CASE V_DEFROLE

WHEN 0

THEN

DBMS_OUTPUT.PUT_LINE (

'ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE NONE;');

WHEN 1

THEN

DBMS_OUTPUT.PUT_LINE (

'ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE ALL;');

ELSE

NULL;

END CASE;

OPEN V_CURSOR FOR

SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE

FROM DBA_ROLE_PRIVS

WHERE GRANTEE = V_USER;

LOOP

FETCH V_CURSOR

INTO V_ROLE, V_ADMIN_OPT, V_DEF_ROLE;

EXIT WHEN V_CURSOR%NOTFOUND;

V_OUTPUT := 'GRANT ' || V_ROLE || ' TO ' || V_NEW_USER;

IF V_ADMIN_OPT = 'NO'

THEN

V_OUTPUT := V_OUTPUT || ';';

ELSE

V_OUTPUT := V_OUTPUT || ' WITH ADMIN OPTION;';

END IF;

DBMS_OUTPUT.PUT_LINE (V_OUTPUT);

IF V_DEFROLE = 2 AND V_DEF_ROLE='YES'

THEN

DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE ' || V_ROLE || ';');

END IF;

END LOOP;

CLOSE V_CURSOR;

END IF;

--Roles * End

--System Privileges * Begin

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_SYS_PRIVS

WHERE GRANTEE = V_USER;

IF V_COUNTER > 0

THEN

DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' system privileges');

OPEN V_CURSOR FOR

SELECT PRIVILEGE, ADMIN_OPTION

FROM DBA_SYS_PRIVS

WHERE GRANTEE = V_USER;

LOOP

FETCH V_CURSOR

INTO V_PRIVILEGE, V_ADMIN_OPT;

EXIT WHEN V_CURSOR%NOTFOUND;

V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' TO ' || V_NEW_USER;

IF V_ADMIN_OPT = 'NO'

THEN

V_OUTPUT := V_OUTPUT || ';';

ELSE

V_OUTPUT := V_OUTPUT || ' WITH ADMIN OPTION;';

END IF;

DBMS_OUTPUT.PUT_LINE (V_OUTPUT);

END LOOP;

CLOSE V_CURSOR;

END IF;

--System Privileges * End

--Object Privileges * Begin

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_TAB_PRIVS

WHERE GRANTEE = V_USER;

IF V_COUNTER > 0

THEN

DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' object privileges');

OPEN V_CURSOR FOR

SELECT PRIVILEGE, OWNER, TABLE_NAME, GRANTABLE, HIERARCHY

FROM DBA_TAB_PRIVS

WHERE GRANTEE = V_USER;

LOOP

FETCH V_CURSOR

INTO V_PRIVILEGE, V_OWNER, V_TABLE_NAME, V_GRANT_OPT, V_HIER_OPT;

EXIT WHEN V_CURSOR%NOTFOUND;

V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' ON ' || V_OWNER || '.' || V_TABLE_NAME || ' TO ' || V_NEW_USER;

IF V_HIER_OPT = 'YES'

THEN

V_OUTPUT := V_OUTPUT || ' WITH HIERARCHY OPTION';

END IF;

IF V_GRANT_OPT = 'NO'

THEN

V_OUTPUT := V_OUTPUT || ';';

ELSE

V_OUTPUT := V_OUTPUT || ' WITH GRANT OPTION;';

END IF;

DBMS_OUTPUT.PUT_LINE (V_OUTPUT);

END LOOP;

CLOSE V_CURSOR;

END IF;

--Object Privileges * End

--Column Privileges * Begin

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_COL_PRIVS

WHERE GRANTEE = V_USER;

IF V_COUNTER > 0

THEN

DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' column privileges');

OPEN V_CURSOR FOR

SELECT PRIVILEGE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTABLE

FROM DBA_COL_PRIVS

WHERE GRANTEE = V_USER;

LOOP

FETCH V_CURSOR

INTO V_PRIVILEGE, V_OWNER, V_TABLE_NAME, V_COL_NAME, V_GRANT_OPT;

EXIT WHEN V_CURSOR%NOTFOUND;

V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' (' || V_COL_NAME || ') ON ' || V_OWNER || '.' || V_TABLE_NAME || ' TO ' || V_NEW_USER;

IF V_GRANT_OPT = 'NO'

THEN

V_OUTPUT := V_OUTPUT || ';';

ELSE

V_OUTPUT := V_OUTPUT || ' WITH GRANT OPTION;';

END IF;

DBMS_OUTPUT.PUT_LINE (V_OUTPUT);

END LOOP;

CLOSE V_CURSOR;

END IF;

--Column Privileges * End

--Auditing options * Begin

SELECT COUNT (*)

INTO V_COUNTER

FROM DBA_STMT_AUDIT_OPTS

WHERE USER_NAME = V_USER;

IF V_COUNTER > 0

THEN

DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' auditing options');

OPEN V_CURSOR FOR

SELECT AUDIT_OPTION, SUCCESS, FAILURE

FROM DBA_STMT_AUDIT_OPTS

WHERE USER_NAME = V_USER;

LOOP

FETCH V_CURSOR

INTO V_AUDIT_OPT, V_SUCCESS, V_FAILURE;

EXIT WHEN V_CURSOR%NOTFOUND;

IF V_SUCCESS <> 'NOT SET'

THEN

DBMS_OUTPUT.PUT_LINE('AUDIT '||V_AUDIT_OPT||' BY '||V_NEW_USER||' '||V_SUCCESS||' WHENEVER SUCCESSFUL;');

END IF;

IF V_FAILURE <> 'NOT SET'

THEN

DBMS_OUTPUT.PUT_LINE('AUDIT '||V_AUDIT_OPT||' BY '||V_NEW_USER||' '||V_FAILURE||' WHENEVER NOT SUCCESSFUL;');

END IF;

END LOOP;

CLOSE V_CURSOR;

END IF;

--Auditing options * End

ELSE

DBMS_OUTPUT.PUT_LINE ('User ' || V_USER || ' does not exist.');

END IF;

END;

MySQL - Reading Bin Logs

2013-06-03T16:52:00.001-04:00

BinLogs can be read in couple of ways. Here it is described in 3 ways:

Using phpMyAdmin:

Login to phpMyAdmin.
Go to “Binary Log” tab
Select Binary Logs to view
Click GO

By default this would show 30 lines. So if you want to customize, click on EDIT and change the query according to your need.

Using SQL Query:

Determine existing binary logs on the system

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000022 | 104858289 |

| mysql-bin.000023 | 104857671 |

| mysql-bin.000024 | 104857898 |

| mysql-bin.000025 | 104857690 |

| mysql-bin.000026 | 104860121 |

| mysql-bin.000027 | 104858120 |

| mysql-bin.000028 | 104858421 |

| mysql-bin.000029 | 52091311 |

+------------------+-----------+

From MySQL run the following for any existing binlog to read. I’ll try for 100 lines.

SHOW BINLOG EVENTS IN 'bin-log.000022' LIMIT 0, 100

Using UNIX Commands:

From OS, we can directly read the binlogs. Here is how:

mysqlbinlog bin-log.000022

This can be modified and run with unix commands for customized reports.

mysqlbinlog bin-log.000022 |grep table_name |grep -v INSERT |cut -c1-1000

MySQL - Purging Binary Logs

2013-06-03T16:25:00.001-04:00

Here is a simple demonstration of how to purge MySQL binary logs.

The default value of the variable expire_logs_days is 10 days. MySQL expire_logs_days is a dynamic variable so we can edit it while MySQl is up & running, we don’t need to restart the server.

First check your slave status:

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.20.1.51

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000029

Read_Master_Log_Pos: 52089474

Relay_Log_File: mysqld-relay-bin.000036

Relay_Log_Pos: 52089620

Relay_Master_Log_File: mysql-bin.000029

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 52089474

Relay_Log_Space: 52089820

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Now check to see the binary logs:

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000022 | 104858289 |

| mysql-bin.000023 | 104857671 |

| mysql-bin.000024 | 104857898 |

| mysql-bin.000025 | 104857690 |

| mysql-bin.000026 | 104860121 |

| mysql-bin.000027 | 104858120 |

| mysql-bin.000028 | 104858421 |

| mysql-bin.000029 | 52091311 |

+------------------+-----------+

In my case, I want a retention of 5 days so I can start by deleting from

mysql-bin.000024 to mysql-bin.000022.

mysql> PURGE BINARY LOGS TO 'mysql-bin.000025';

Query OK, 0 rows affected (0.45 sec)

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000025 | 104857690 |

| mysql-bin.000026 | 104860121 |

| mysql-bin.000027 | 104858120 |

| mysql-bin.000028 | 104858421 |

| mysql-bin.000029 | 52313463 |

+------------------+-----------+

5 rows in set (0.00 sec)

Eventually set the new value for expire_logs_days and don’t forget to edit your my.cnf:

mysql> SET GLOBAL expire_logs_days=5;

Query OK, 0 rows affected (0.00 sec)

Now see the reflection.

mysql> SHOW VARIABLES LIKE 'expire_logs_days';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| expire_logs_days | 5 |

+------------------+-------+

1 row in set (0.00 sec)

Again, once the parameter is changed in memory, change the my.cnf parameter accordingly

so that the instance doesn't startup with old parameter in next instance reboot.

Purging Binary Log with Date Range:

We can also binary logs with date & time range.

PURGE BINARY LOGS BEFORE '2013-05-21 10:46:25';

Logoff Remote Desktop user remotely

2013-04-10T12:03:00.003-04:00

In serveral situations, you might run into a situation where you can’t login to server using RDP because "The terminal server has exceeded the maximum number of allowed connection".

Even without asking the system admin you can detect and kill one of your already connected session and then login. Here is how we can do that:

We could use QWinsta windows tool to figure out what users are remotely connected to the server.

Replace the IP with your server IP.

Y:\>QWinsta /server:192.168.0.12

SESSIONNAME USERNAME ID STATE TYPE DEVICE

services 0 Disc

console 1 Conn

rdp-tcp#0 dbmgmt 2 Active rdpwd

rdp-tcp 65536 Listen

I want to logout dbmgmt user. So I’ll have to take ID assigned to it and run the following command:

Y:\>logoff /server:193.168.0.12 2 /v

Logging off session ID 2

MySQL Replication Setup:

2013-04-05T14:05:00.005-04:00

Here is a simple demonstration of MySQL Replication setup. For my case, I’ve installed MySQL 5.1 database on two servers.
Here are the steps:

1. Enable Binary Logging & Assigning Unique Server ID (Master)

On a replication master, you must enable binary logging and establish a unique server ID. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.

Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you.

Edit your my.cnf file like this:

server-id = 1

log-bin = /u01/app/mysql/mysqllogs/bin-log

2. Restart mysql (as root)

service mysqld restart

3. Create REPLICANT user on MASTER

Create the REPLICANT user with REPLICATION SLAVE privilege. This user would pull data from SLAVE conneting to MASTER DB.

create user 'replicant'@%' identified by 'password';

GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';

4.  Transferring DUMPS to SLAVE with Master DB State Information:

Stop the production server, and obtain its state information. Most importantly, note the File and Position values:mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+

+---------------+----------+--------------+------------------+

+---------------+----------+--------------+------------------+

While the database has the READ LOCK applied, export the data using mysqldump, including master data information:

mysqldump --all-databases --master-data > dbdump.db

Then unlock the master database:

mysql> UNLOCK TABLES;

5. Setting the Replication Slave Configuration

On a replication slave, you must establish a unique server ID. Edit the my.cnf like this:

# Slave Config

[mysqld]

server-id = 2

read-only = 1

relay-log = /u01/app/mysql/mysqllogs/relay-log

relay-log-space-limit = 16G

master-host =

master-user = replicant

master-password =

master-connect-retry = 30

After making the changes, restart the server.

6.  Importing dumps into SLAVE DB:

Transfer the data (dbdump.db) file from Master and load it into the database:

shell> mysql < fulldb.dump

Set the file information – this needs to match the values you observed on the master when you locked the tables above. In this example the filename was mysql-bin.003 and the log position was 240:

mysql> change master to

MASTER_LOG_FILE='mysql-bin.003',

MASTER_LOG_POS=240;

7.  Start the slave:

mysql> start slave;

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.13.60

Master_User: replicant

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: bin-log.000016

Read_Master_Log_Pos: 462

Relay_Log_File: relay-log.000040

Relay_Log_Pos: 413

Relay_Master_Log_File: bin-log.000016

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 462

Relay_Log_Space: 705

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

Thats it!! Now you can create table/database and see the reflection on slave database!

MySQL Database User Permissions

2013-04-04T10:39:00.001-04:00

MYSQL Server uses an internal database called mysql to store database user information. We can play around with several information tables liket the followings to get idea about user permissions and related information.

select * from mysql.user;

select * from mysql.db;

select * from mysql.tables_priv;

select * from mysql.columns_priv;

select * from mysql.procs_priv;

Followings are some formatted reports based upon these basic tables.

List of DATABASE Privileges:

SELECT md.host `Host`,

md.user `User`,

md.db `Database`,

REPLACE(

RTRIM(

CONCAT(

IF(md.Select_priv = 'Y', 'Select ', ''),

IF(md.Insert_priv = 'Y', 'Insert ', ''),

IF(md.Update_priv = 'Y', 'Update ', ''),

IF(md.Delete_priv = 'Y', 'Delete ', ''),

IF(md.Create_priv = 'Y', 'Create ', ''),

IF(md.Drop_priv = 'Y', 'Drop ', ''),

IF(md.Grant_priv = 'Y', 'Grant ', ''),

IF(md.References_priv = 'Y', 'References ', ''),

IF(md.Index_priv = 'Y', 'Index ', ''),

IF(md.Alter_priv = 'Y', 'Alter ', ''),

IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),

IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),

IF(md.Create_view_priv = 'Y', 'Create_view ', ''),

IF(md.Show_view_priv = 'Y', 'Show_view ', ''),

IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),

IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),

IF(md.Execute_priv = 'Y', 'Execute ', ''),

IF(md.Event_priv = 'Y', 'Event ', ''),

IF(md.Trigger_priv = 'Y', 'Trigger ', ''))),

' ',

', ')

AS `Privileges`

FROM mysql.db md

ORDER BY md.Host, md.User, md.Db

LIST OF GLOBAL PRIVILEGES:

SELECT mu.host `Host`,

mu.user `User`,

REPLACE(

RTRIM(

CONCAT(

IF(mu.Select_priv = 'Y', 'Select ', ''),

IF(mu.Insert_priv = 'Y', 'Insert ', ''),

IF(mu.Update_priv = 'Y', 'Update ', ''),

IF(mu.Delete_priv = 'Y', 'Delete ', ''),

IF(mu.Create_priv = 'Y', 'Create ', ''),

IF(mu.Drop_priv = 'Y', 'Drop ', ''),

IF(mu.Reload_priv = 'Y', 'Reload ', ''),

IF(mu.Shutdown_priv = 'Y', 'Shutdown ', ''),

IF(mu.Process_priv = 'Y', 'Process ', ''),

IF(mu.File_priv = 'Y', 'File ', ''),

IF(mu.Grant_priv = 'Y', 'Grant ', ''),

IF(mu.References_priv = 'Y', 'References ', ''),

IF(mu.Index_priv = 'Y', 'Index ', ''),

IF(mu.Alter_priv = 'Y', 'Alter ', ''),

IF(mu.Show_db_priv = 'Y', 'Show_db ', ''),

IF(mu.Super_priv = 'Y', 'Super ', ''),

IF(mu.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),

IF(mu.Lock_tables_priv = 'Y', 'Lock_tables ', ''),

IF(mu.Execute_priv = 'Y', 'Execute ', ''),

IF(mu.Repl_slave_priv = 'Y', 'Repl_slave ', ''),

IF(mu.Repl_client_priv = 'Y', 'Repl_client ', ''),

IF(mu.Create_view_priv = 'Y', 'Create_view ', ''),

IF(mu.Show_view_priv = 'Y', 'Show_view ', ''),

IF(mu.Create_routine_priv = 'Y', 'Create_routine ', ''),

IF(mu.Alter_routine_priv = 'Y', 'Alter_routine ', ''),

IF(mu.Create_user_priv = 'Y', 'Create_user ', ''),

IF(mu.Event_priv = 'Y', 'Event ', ''),

IF(mu.Trigger_priv = 'Y', 'Trigger ', ''))),

' ',

', ')

AS `Privileges`

FROM mysql.user mu

ORDER BY mu.Host, mu.User

List of privileges for TABLES:

SELECT mt.host `Host`,

mt.user `User`,

CONCAT(mt.Db, '.', mt.Table_name) `Tables`,

REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`

FROM mysql.tables_priv mt

WHERE mt.Table_name IN

(SELECT DISTINCT t.table_name `tables`

FROM information_schema.tables AS t

WHERE t.table_type IN

('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')

OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)

ORDER BY mt.Host,

mt.User,

mt.Db,

mt.Table_name;

List of privileges for VIEWS:

SELECT mv.host `Host`,

mv.user `User`,

CONCAT(mv.Db, '.', mv.Table_name) `Views`,

REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`

FROM mysql.tables_priv mv

WHERE mv.Table_name IN (SELECT DISTINCT v.table_name `views`

FROM information_schema.views AS v)

ORDER BY mv.Host,

mv.User,

mv.Db,

mv.Table_name;

List of privileges for TABLE COLUMNS:

SELECT mtc.host `Host`,

mtc.user `User`,

CONCAT(mtc.Db,

'.',

mtc.Table_name,

'.',

mtc.Column_name)

`Tables Columns`,

REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`

FROM mysql.columns_priv mtc

WHERE mtc.Table_name IN

(SELECT DISTINCT t.table_name `tables`

FROM information_schema.tables AS t

WHERE t.table_type IN

('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')

OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)

ORDER BY mtc.Host,

mtc.User,

mtc.Db,

mtc.Table_name,

mtc.Column_name;

List of privileges for VIEW COLUMNS:

SELECT mvc.host `Host`,

mvc.user `User`,

CONCAT(mvc.Db,

'.',

mvc.Table_name,

'.',

mvc.Column_name)

`Views Columns`,

REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`

FROM mysql.columns_priv mvc

WHERE mvc.Table_name IN (SELECT DISTINCT v.table_name `views`

FROM information_schema.views AS v)

ORDER BY mvc.Host,

mvc.User,

mvc.Db,

mvc.Table_name,

mvc.Column_name;

List of privileges for PROCEDURES:

SELECT mp.host `Host`,

mp.user `User`,

CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,

REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`

FROM mysql.procs_priv mp

WHERE mp.Routine_type = 'PROCEDURE'

ORDER BY mp.Host,

mp.User,

mp.Db,

mp.Routine_name;

List of privileges for FUNCTIONS:

SELECT mf.host `Host`,

mf.user `User`,

CONCAT(mf.Db, '.', mf.Routine_name) `Procedures`,

REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`

FROM mysql.procs_priv mf

WHERE mf.Routine_type = 'FUNCTION'

ORDER BY mf.Host,

mf.User,

mf.Db,

mf.Routine_name;

Oracle BI Discoverer 11g Installation

2013-04-03T12:19:00.004-04:00

We are going to install OBI 11g with following sequences.

Install Oracle database 11.2.0.3 standard edition database.
Install JDK jdk 1.6 or 1.7
Install Repository Creation Utility (RCU) 11.1.0.6 (ofm_rcu_win_11.1.1.6.0_disk1_1of1.zip)
Weblogic Server 10.3.6 (wls1036_generic.jar)
Oracle Portal, Reports,Forms & Discoverer 11g and patching with 11.1.6
Run configuration for Discoverer

pfrd 11.1.1.2 Includes

V18772-01_1of4.zip

V18772-01_2of4.zip

V18772-01_3of4.zip

V18772-01_4of4.zip

pfrd 11.1.1.6 Includes

V29883-01.zip

./config.sh to configure 11.1.1.6

Environments:

Make sure the /etc/hosts are like this

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

#::1 localhost6.localdomain6 localhost6

192.168.10.11 biserver.domain.com biserver

Packages:

With an Oracle Database 11g installed, you need only to install one package - compat-db

Install OBI Repository Creation Utility (RCU)

Download the installation files from the http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html on your desktop.

(Note** It has to be the same version as BI software). The name of the installer would be fm_rcu_win_11.1.1.6.0_disk1_1of1.zip

Unzip the file, navigate to the bin directory and start the RCU => rcu.bat.

Welcome > Next

Accept default of Create. Click Next:

· Leave default of Oracle Database:

· Set hostname

· Set port

· Set service name

· Set username

· Set password

· Leave Role as SYSDBA

Select the Oracle Business Intelligence Component.This action automatically selects the Metadata Services (MDS) schema (under the AS Common Schemas group).

So at this point, Repository Creation succeded!!

Our next step is to Install Weblogic server & creating middleware home

Install Oracle WebLogic Server and Create the Middleware Home:

Environment Preperation:

We need to make sure that weblogic is using jdk1.7.0_17 while running installer.

SETTING JAVA HOME:

We have installed JDK1.7 into following directory:

Cd /usr/java/jdk1.7.0_17/bin

[oracle@test bin]$ ./java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

[oracle@test bin]$ cd..

[oracle@test jdk1.7.0_17]$ echo $PATH

/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/fnadvi/bin

[oracle@test jdk1.7.0_17]$ pwd

/usr/java/jdk1.7.0_17

[oracle@test jdk1.7.0_17]$ export JAVA_HOME=$PWD

[oracle@test jdk1.7.0_17]$ echo $JAVA_HOME

/usr/java/jdk1.7.0_17

[oracle@test jdk1.7.0_17]$ export PATH=$JAVA_HOME/bin:$PATH

[oracle@test jdk1.7.0_17]$ echo $PATH

/usr/java/jdk1.7.0_17/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/fnadvi/bin

[oracle@test jdk1.7.0_17]$ cd ../

[oracle@test java]$ java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

Install weblogic 10.3.6

Make sure that the weblogic is running using jdk1.7.0 java binaries by running it from java directory, as shown below:

[oracle@test distr]$ /usr/java/jdk1.7.0_17/bin/java -jar wls1036_generic.jar

Extracting 0%....................................................................................................100%

Now that the Weblogic server is installed. Our final step is installing Oracle Discoverer 11g.

Installing Forms,Reports & Discoverer 11.1

1. We will be installing Oracle Portal, Reports, Forms & Discoverer 11.1.1.2 and then will pathc it with 11.1.1.6

NOTE: Select Install Software - DO NOT CONFIGURE****

We'll do that after applying patches.

Patching to 11.1.1.6:

Configure Discoverer under 11.1.1.6 patched home:

Once everything is installed and patched. Run configure Portal,Forms,Reports & Discoverer.
Configure CONFIG.sh

cd /u01/app/oracle/middleware/as_1/bin

./config.sh

At this point.....Installation of Oracle Discoverer 11g has been successfully installed.

Just one additional task is, you need to copy the database tns file, (where EUL Schema resides) needs to be copied over to Middleware Instance Directory. Ie, for my case it's

/u01/app/oracle/middleware/asinst_1/config

The link to use Discover would be prety much like this:http://hostname:8888/discoverer/viewer OR
http://hostname:8090/discoverer/viewer

Note that, even if webcache is down, you can still access Discoverer with the 8888 port!!!

Move SQL Server transaction log files to a different location via TSQL

2013-03-27T20:00:00.000-04:00

To optimize I/O performance of a database, it's always a good idea to keep LOG file & DATA file in separate physical drives.

The transaction log file records every data change and DML transaction executed in the database. Writing to the transaction log file is sequential in nature as compared to the database files which are typically random I/O. As such, placing the log file on separate physical disk from database will allow the disk to work in sequential manner and perform optimally.

I'm going to show a demonstration of moving LOG files to another drive.

1: Capture database and transaction log file information

USE AdventureWorks
GO

sp_helpfile
GO

2: Set database to single user mode and detach database

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO

-- Detach the database
sp_detach_db 'AdventureWorks'
GO

*** Now the database is detached. Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer. Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:

3: Attach database with log file at new location

USE master
GO

-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO

4. Validate the LOG moving

After the final attach command transaction log file has been moved to new location and database is operational with log file on new location. Verifying the new database transaction log location can be accomplished by re-running

USE AdventureWorks
GO

sp_helpfile
GO

Bringing a database out of Restoring state in SQL Server

2013-03-14T12:37:00.001-04:00

What if I restored a database with “NO RECOVERY” option keeping in mind that I’d keep continuing with another Differential backup set / Transactional backup set next. But then I’ve decided not to proceed further?

At this moment, database is in “Restoring” state and inaccesible.

I need to run following script bringing a database back from restoring state to normal.

RESTORE DATABASE

WITH RECOVERY

MSSQL Server : The log or differential backup cannot be restored because no files are ready to rollforward

2013-03-14T12:03:00.001-04:00

I was facing following error while restoring Differential backup.

Restore failed for Server ''. (Microsoft.SqlServer.Smo) Additional Information: System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

The reason for this erro is: no database that was left in non-operational mode, and thus has not been cleaned up such that uncommitted transactions have not been rolled back.

The easy way to reproduce this error is to

-Backup the database using full recover mode
- Do full and differential backups.
- Restore the DB, First from Full backup then Differential

I got the above error while trying to restore the differential backup (after you just restored the full backup).

Reason for the error:

I restored the Full backup with Recovery option. It must be restored with NORECOVERY option to allow rest of the backup sets to be restored (Differential/Transactional)

In the Microsoft SQL Server Management Studio there are three options on the Option "page" while restoring a database.

Option 1 (the default): Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)

Option 2: Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY)

To properly restore a database that is backup up using full recovery mode with full and differential backups, here are steps:

Restore Full Backup:

Open the Restore Database window in Microsoft SQL Server Management Studio

Ensure the To database field is filled in with the name you want.

Choose From device as the Source for restore.

Choose the full backup file you want to restore. In most cases this is the most recent full backup file.

Click the Options page on the left navigation.

Choose Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY).

Restore Differential backup:

Open the Restore Database window in Microsoft SQL Server Management Studio
Ensure the To database field is filled in with the name you want. The same that you specified in step 2 for the Restore Full backup
Choose From device as the Source for restore.
Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.
Click the Options page on the left navigation.
Choose the default: Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)

If you want to restore Transactional log after Differential backup restore, then Choose "RESTORE WITH NORECOVERY" instead of "RESTORE WITH RECOVERY".

RESTORE WITH RECOVERY Needs to be the final step of a recovery process.

MSSQL Server: The tail of the log for the database "DATABASE" has not been backed up.

2013-03-14T11:48:00.002-04:00

I was facing the following error error when attempting to restore a database in Microsoft SQL Server:

System.Data.SqlClient.SqlError: The tail of the log for the database "DATABASE" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Solution:

Select "Overwrite the existing database" from Restore option.

This will overwrite the tail of of database log , as my DB is corrupted and wanted to restore from last full backup so it's fine for my case.

ORACLE @ Work

Linux CPU stress testing sample script

SQL Joins

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

“The program can’t start because MSVCR100.dll is missing from your computer.” error on Windows

What's Your Favorite Feature of SQL Server 2012?

MySQL Import Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

MySQL : Monitor live MySQL queries

MSSQL : Bring database back to normal from Restoring State

SQL Server: Linked Server is not configured for RPC error:

MSSQL Server to MSSQL Server Instance connectivity with Linked Server

SQL Server - Maintenace plan integrity checks fail with "Alter Failed for server xxx"

SQL Server Performance Dashboard Reports in SSMS

When did a user changed his password in Oracle?

Recover MySQL Root Password

Create oracle as is user

MySQL - Reading Bin Logs

Using phpMyAdmin:

Using SQL Query:

Determine existing binary logs on the system

MySQL - Purging Binary Logs

Logoff Remote Desktop user remotely

MySQL Replication Setup:

MySQL Database User Permissions

Oracle BI Discoverer 11g Installation

Move SQL Server transaction log files to a different location via TSQL

Bringing a database out of Restoring state in SQL Server

MSSQL Server : The log or differential backup cannot be restored because no files are ready to rollforward

MSSQL Server: The tail of the log for the database "DATABASE" has not been backed up.