Jephe Wu - http://linuxtechres.blogspot.com
Environment: Windows 2008 server R2 64bit with MS SQL Server 2008. Initially, user1 who belongs to Windows local Administrators group installed this SQL server,
after he resigned, another user who also belongs to local administrators users group cannot login database engine.
Objective: to make user2 to login to database engine.
Errors: When user2 tries to login sql server 2008 with Windows authentication mode, it gives the following error messages in event log:
Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
Steps:
1. login sql server management studio as the initial user1 who set up the sql server.
use [local] to login, you may want to try computer name in server name part
2. under 'security - login' tab, create a new user login name as \\computername\jwu for Windows authentication, grant public and sysadmin server role , and enable 'grant permission to login database engine' under status tab.
3. logout user1, login as local administrator user 'jwu', use computername as servername to login sql server management studio.
4. you should be able to login now.
References:
1. Choose an authenticatin mode - http://msdn.microsoft.com/en-us/library/ms144284.aspx
2. How to change server authentication mode - http://msdn.microsoft.com/en-us/library/ms188670.aspx
How to login MS SQL server 2008 database engine
How to make SQL server 2005 logshipping secondary database to re-sync with primary database again
Jephe Wu - http://linuxtechres.blogspot.com
Objective: to make secondary db server to sync with primary again, logshipping is not working due to some required log files were deleted by some job.
Environment: Windows 2003 32bit, MS SQL server 2005 (management studio)
Problem: logshipping is not working according to Windows event log and sql job history, due to some required files were deleted, so the restoring is not working.
Steps:
1. disable restoration job in job agent from SQL management studio
SQL Server Agent -> Jobs - LSRestore_DBxxxx\DBRSQLP4_xxxxx
also, disable any other sql server agent job which might affect restoration process.
2. restore the full database backup from primary
a. right clicking database DR_DB1 on secondary server.
b. choose 'task', 'restore database', General, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it.
c. go to options, choose 'overwrite the existing database', do not care about 'Restore the database file as' part even the 'Restore As' name is not same as what the DR server is having now for that database.
Because we have already choosen 'Overwrite the existing database', so don't have to care, it will overwrite the existing database name in DR server.
For 'Recovery state', choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.
After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery .
5. right click on the restoratin job to check history, It will check all transaction log files in the specified directory, skip those which already contained in the restored database. click refresh and get more information.
6. check reports for transaction log.
References:
1. Index rebuild generate huge log file makes logshipping and mirroring out of sync - http://connect.microsoft.com/SQLServer/feedback/details/352338/index-rebuild-generate-huge-log-file-makes-logshipping-and-mirroring-out-of-sync
2. How to: Remove Log Shipping (SQL Server Management Studio)
This topic explains how to remove log shipping using Microsoft SQL Server Management Studio.
To remove log shipping
Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
Under Select a page, click Transaction Log Shipping.
Clear the Enable this as a primary database in a log shipping configuration check box.
Click OK to remove log shipping from this primary database.
=============
Part II - after DR test, reconfiguring logshipping
You don't have to delete database on secondary, just re-configure everything from primary; if you don't have enough space, you can delete it.
Steps:
1. right click database on primary, transaction log shipping , click 'enable this as a primary atabase in a log shipping configuration
2. click backup settings.type in network path of the folder on primary where we will generate transaction log for sharing with secondary for read
\\pridb\logshipping
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\logshipping
note:
a. grant read/write permission to the sql server agent service account of primary server instance so that the backup job on primary can delete old transaction log files successfully.
b. read only permission to sql server agent service account for the secondary server instance in the domain
c. grant sysadmin privilege to primary db sql server agent service account on secondary db, so that the logshipping status 'last backup created' can be updated on primary db , also on secondary db server log shipping report page, Backup - Time Since Last will also be updated. Otherwise, it will only show the initial primary db backup date during logshipping configuration wizard.
in Schedule, to change default interval from 15 minutes to whatever you need. (e.g. 2 mins)
3. click 'add' to add secondary server instance, click connect to choose secondary server name and windows authentication.
click to choose a existing secondary database or enter a name to create new database. e.g. 'DR_PRODUCTION'
in 'Initialize Secondary Database' tab, choose one of three options, if you have recent full backup, choose the second option, then type in a
network path to the backup file that is accessible by secondary instance, e.g. \\primdb\backup\PRODUCTION_backup_201205150110.bak
or
after copying file to secondary, use \\secdb\d$\PRODUCTION_backup_201205150110.bak
Note: you cannot directly use d:\PRODUCTION_backup_201205150110.bak since it's asking network path, otherwise, you are not able to add it once clicking 'add'.
click 'restore options', type in data and log files path on secondary server: e.g.
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION
in 'copy files' tab, type in destination folder on secondary where transaction log should be copied to
e.g. D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\logshipping
click on 'Schedule', change default 15 minutes interval to whatever you need (e.g. 2mins)
click on 'Restore Transaction Log' tab, choose 'no recovery mode' or 'standby mode' (readonly while applying transaction log).
choose 45 minutes for alert if no restore occurs within.
Note: you can also restore the full backup from secondary database first by right clicking 'database', choose 'restore', in 'options', choose the second option to leave database in non-operational state to be able to apply for transaction log and do not undo the uncommited transaction.
4. in Monitor server instance, choose secondary server.
5. user zabbix/dbforbix to monitor logshipping progress
refer to http://sqlmonitormetrics.red-gate.com/time-since-last-restore/
Measure the time since the last restore, in minutes.
Enter the T-SQL query that will collect data:
SELECT DATEDIFF(MINUTE, restore_date, GETDATE())
FROM (SELECT TOP 1 restore_date
FROM msdb.dbo.restorehistory
WHERE destination_database_name = DB_NAME()
ORDER BY restore_date DESC
) rd;
Vmware FAQ
Jephe Wu - http://linuxtechres.blogspot.com
- Performance issue when copying files between Windows 7 64bit host and Linux guest
- Click Start > Run.
- Type regedit and press Enter.
- Browse to the following location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
- Create the key DisableTaskOffload (type: DWORD).
- Set the value of the key to 1 .
- Close the Registry Editor and restart the computer.
Oracle RAC Documentation
Jephe Wu - http://linuxtechres.blogspot.com
- How to check if it's running RAC
BEGIN
IF dbms_utility.is_cluster_database THEN
dbms_output.put_line('Running in SHARED/RAC mode.');
ELSE
dbms_output.put_line('Running in EXCLUSIVE mode.');
END IF;
END;
/or
SQL> show parameter CLUSTER_DATABASE
2. How to stop/start dbconsole?
Linux:
$ export ORACLE_HOME=xxx
$ export ORACLE_SID=xxx
$ ORACLE_HOME/bin/emctl stop dbconsoleWindows:set ORACLE_HOME=xxxset ORACLE_SID=xxx ORACLE_HOME\bin\emctl stop dbconsole
Linux:
$ export ORACLE_HOME=xxx
$ export ORACLE_SID=xxx
$ ORACLE_HOME/bin/emctl start dbconsoleset ORACLE_HOME=xxxset ORACLE_SID=xxx
ORACLE_HOME\bin\emctl start dbconsoleReference: Enterprise Manager Database Console FAQ [ID 863631.1] 3. How to stop/start RAC Bring up the inst1 of database db1 $ srvctl start instance -d db1 -i inst1Stop the db1 database: all its instances and all its services, on all nodes.
$ srvctl stop database -d db1 4. database uptime 5. alert log locationshow parameter BACKGROUND_DUMP_DEST; 6. stop/start RACStarting the Oracle 10g RAC Cluster 10g Environment: - Run as oracle: su - oracle $ export ORACLE_SID=orcl1
$ srvctl start nodeapps -n linux1 $ srvctl start asm -n linux1 $ srvctl start instance -d orcl -i orcl1 $ emctl start dbconsole Start/Stop All Instances with SRVCTL Stopping the Oracle 10g RAC Cluster 10g Environment: - Run as oracle: su - oracle $ export ORACLE_SID=orcl1 $ emctl stop dbconsole $ srvctl stop instance -d orcl -i orcl1
srvctl status instance -d dbname -i instancename $ srvctl stop asm -n linux1 [-o immediate] $ srvctl stop nodeapps -n linux1 Starting the Oracle RAC 10g Environment To stop or start both database instances at once: $ srvctl start database -d orcl [-o open | -o mount | -o nomount] $ srvctl stop database -d orcl [-o normal | -o transactional | -o immediate | -o abort]
srvctl status database -d dbname
srvctl config database -d dbname (shows instances name, node and oracle home) ========== Services:srvctl status service -d dbname
srvctl config service -d dbname
srvctl start service -d dbname -s servicename
srvctl stop service -d dbname -s servicename ７. lsnrctl PLSExtProc, XDB and XPTPLSExtProc: This is used to Call OS Commands from PL/SQL using External Procedures. Default in the Listener file. the XDB service is used for the XML DB database option
the XPT service is used for Dataguard.
All three of these can be disabled if you are not using their associated features.
Oracle 10g and 11g Installation Documentation for Linux and Windows
Jephe Wu - http://linuxtechres.blogspot.com
Part I: Documentation from Oracle website itself:
- You can access https://apex.oracle.com/pls/apex/f?p=44785:1:0 to search all the tags
- Installing Oracle Database 10g on Windows - QBE - including creating oracle user - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/install/wininst/wininst_otn.htm
- Installing Oracle Database 11g on Windows - QBE -without creating oracle user - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/windbinst2.htm
- Installing and Configuring Oracle Enterprise Linux 5 General Availability - including creating oracle user before installing database 11g - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/oel5gainst/oel5gainst.htm
- Installating Oracle Database 11g on Linux - QBE -without creating oracle user - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/dbinst.htm
- Installing Oracle Software and Building the Database - QBE - http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/install/dbinstr2/dbinst.htm
Part II: Documentation from third-party website:
- Installing Oracle on Enterprise Linux & RHEL - http://wiki.oracle.com/page/Installing+Oracle+on+Enterprise+Linux+%26+RHEL
- http://www.puschitz.com/InstallingOracle10g.shtm
- Install 10g on RHEL4 64bit - installation requirement , similar with 11g installation steps:
- yum install xorg-x11-deprecated-libs
2. yum install libaio*
3. run this:
yum install xorg-x11-deprecated-libs
yum install sysstat*
yum install libaio*
yum install unixODBC*
yum install binutils*
yum install compat-db*
yum install gcc-c++*
yum install libstdc++*
yum install make*
yum install sysstat*
yum install glibc*
yum install control-center*
Install Zabbix on CentOS 5.6
Jephe Wu - http://linuxtechres.blogspot.com
- PHP and Zabbix
[Wed Jun 15 11:08:14 2011] [error] [client 192.168.1.3] PHP Fatal error: Call to undefined function bcscale() in /var/www/html/zabbix/include/defines.inc.php on line 794
Solution: yum install bcmath
- Check of pre-requisites
vi /etc/php.ini to change:
timezone = Australia/Sydney
post_max_size = 32M
upload_max_filesize = 2M
max_execution_time = 600
max_input_time = 600
yum install php-mbstring
yum install php-gd
- PHP and PHP53
vi /etc/yum.conf to add
exclude=php53*
or yum install php* --exclude=php53*
- References
Posts
