3 Ways to Log In Through Sqlplus as ‘sysdba’

1.If you are a member of DBA group on the Linux/Unix OS level:

sqlplus '/ as sysdba'

2.Or you can first log on as ‘nolog’

sqlplus /nolog

And then

SQL>connect / as sysdba

3. Directly connect to Oracle instance as sysdba

sqlplus 'sys as sysdba'

You’ll be prompted to type in the password and all will be OK.

Advertisements
Posted in Oracle | Leave a comment

How to Type Escape Key while Using Bluetooth Keyboard Connected with Android System

Well, every user’s situation is unique, so I’ll just present mine here for starter:
Phone: Galaxy S2
Andorid Version:4.0.3
Keyboard: B.O.W hb086 (it is a cheap Chinese knockoff, but probably your own high-end couterpart is using the same set of hardware inside)

I first had trouble with the Ctrl key, and later I fixed it somehow by toggling around some configs in the phone input method.

Then I had trouble with typing ESC key while using VIM, but later found out that you can simply press Ctrl+[ to simulate the same gesture.

Hopefully this will be helpful if Google leads you up here.

Posted in Linux | Tagged , , , | Leave a comment

Best Way to Set up a Server Side Trace

Accidentally bumped into this tutorial about setting up server side trace. By using SQL profile and scripting our the trace definition, you’d save a lot of work by defining various events that you’d like to trace. I am taking a note here for future reference.

Some other useful T-SQL commands that involve the control of server side tracing are:

--To get the list of currently running traces
SELECT * FROM ::fn_trace_getinfo(DEFAULT)

--To get the list of all traces (running or not)
SELECT * FROM sys.traces

--To pause a running trace
EXEC sp_trace_setstatus @traceid = <traceid> , @status = 0

--To close and delete a trace 
EXEC sp_trace_setstatus @traceid = <traceid> , @status = 2
Posted in SQL Server, T-SQL Script | Tagged | Leave a comment

T-SQL Function to Get the Default Backup Folder

Again, DBA’s are often faced with adhoc backup tasks that shall dump relevant data or log to the existing backup location for all other data/log dumps. Quick way to do so will invovling concatenating dynamic sql commands with the default backup folder location.

/*

Script to create a function to get default backup directory of SQL Server.

SQL Version: 2005+

Last modified by Nick Xu on 2014-08-01

--Usage
SELECT dbo.fn_GetBackupDir()
go
*/

--For generic setting
USE master
GO

IF OBJECT_ID('dbo.fn_GetBackupDir') IS NOT NULL 
   DROP FUNCTION dbo.fn_GetBackupDir 
GO 


CREATE FUNCTION dbo.fn_GetBackupDir() 
RETURNS NVARCHAR(4000) 
AS 
BEGIN 

   DECLARE @path NVARCHAR(4000) 

   EXEC master.dbo.xp_instance_regread 
            N'HKEY_LOCAL_MACHINE', 
            N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', 
            @path OUTPUT,  
            'no_output' 
   RETURN @path 

END;

And to take one step further, the below script prints the commands your need to backup all non-system DB’s in the default backup folder with the current timestamp:

SELECT 'BACKUP DATABASE [' + name + '] TO DISK = ''' + 
DBADB.dbo.fn_GetBackupDir() + '\' + name + '_' + DBADB.dbo.fn_GetTimeStamp() + '.bak WITH COMPRESSION, STATS=1,  CHECKSUM''' 
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb')

And even one more step futher is the below script that deletes backups that are older than N days from the default bacup folder, courtersy to Patrick Keisler’s good work:

DECLARE @Path AS VARCHAR(500)

SELECT @Path = DBADB.dbo.fn_GetBackupDir()

DECLARE @DeleteDate DATETIME = DATEADD(DAY,-3,GETDATE());

EXEC master.sys.xp_delete_file 0,@Path, 'BAK',@DeleteDate,0;
Posted in SQL Server, T-SQL Script, User Defined Function | Tagged , | Leave a comment

T-SQL Function to Return Current Timestamp as String

Sometimes, a DBA will have to make adhoc backups and it is ideal that these backup files are named with proper timestamp info. The below function returns current date and time as a string fomartted in ‘yyymmdd_hhmmss’ style:

/*

Script to create a function to get current timestamp in the format of YYYYmmdd_hhmmss; particularly useful for concatenating backup t-sql commands

SQL Version: 2005+

Last modified by Nick Xu on 2013-08-01

--Usage
SELECT DBADB.dbo.fn_GetTimeStamp()
GO
*/

--For generic setting
USE master
GO

IF OBJECT_ID('dbo.fn_GetTimeStamp') IS NOT NULL 
   DROP FUNCTION dbo.fn_GetTimeStamp 
GO 


CREATE FUNCTION dbo.fn_GetTimeStamp() 
RETURNS VARCHAR(15) 
AS 
BEGIN 

   RETURN CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')

END;
Posted in SQL Server | Tagged , | Leave a comment

Linux 101: How to Sync Two Folders on Two Hosts

This is a genuine request from a friend of mine who has a website and needs to be backed up in a hot stand-by server in case the production goes down. The command that I am using is ‘rsync’.

Firstly, you’ll have to install rsync on both boxes if you have not done so:

sudo yum install rsync

usually will do the trick

Secondly, I use the following command on the stand-by server to 'back up' all the PROD website's files:

sudo rsync -rtavue ssh nick@www.website.com:/home/wwwroot/default/ /home/wwwroot/default/ --delete

The above command will ask your local password first and then your password on the PROD server. --delete gets rid of all files that no longer exist on the PROD side.

BTW, here is a better tutorial all about ‘rsync’.

Posted in Linux | Leave a comment

Linux 101: How to Recovery Forgotten Root’s Password

It is recommended to avoid using root account frequently and this does cause trouble when admins suddenly would like to connect to a box under root account. One of the best ways to recover a forgotten root password is to use another account that has sudo privilege and can reset password for root. I am using my own account ‘nick’ in the below example:

Step 1:
Log on as ‘root’ and then create ‘nick’ account with strong password

useradd nick
passwd nick

Step 2:
Open visuo by running the below command

visudo

And then like you are using VIM, add one line at the bottom of the file

nick ALL=(ALL) ALL

Step 3:
You can log off ‘root’ and log on as ‘nick’ now and type

sudo su -

Then you become ‘root’ immediately and by using

passwd

you can easily re-set password for ‘root’.

All above 3 steps are well tested on CentOS. Hopefully this can be helpful.

Posted in Linux | Tagged , , , | Leave a comment

Linux 101: Vsftp Service Configuration Tutorial

I am reposting this tutorial for future’s reference.

Also, to check if vsftpd service is running or not, you can use


shell>service --status-all | grep vsftpd

And to control vsftpd service, you can use

shell>/etc/init.d/vsftpd start
shell>/etc/init.d/vsftpd stop
shell>/etc/init.d/vsftpd restart

And to make vsftpd automatically start everytime system is rebooted, you can run

shell>chkconif vsftpd on

In addittion, it is recommended that you set up a dedicated ftp user account whoes SSH connection is denied and to do so:

shell>useradd ftp_user
shell>passwd ftp_user
shell>usermod -s /sbin/nologin ftp_user

Posted in Linux | Tagged , | Leave a comment

MySQL Newbie 002: PhpMyAdmin Error: “Wrong permissions on configuration file, should not be world writable”

The titled error message is referring to wrong permissions of configuration file “config.inc.php” and a simple chmod command will suffice:

shell> chmod 0755 config.inc.php

In case you wonder where to find this config.inc.php file, it is located in the same folder where PhpMyAdmin is installed. And for a more detailed solution on both Linux and Windows systems, please click here.

Posted in MySQL, PhpMyAdmin | Leave a comment

MySQL Newbie 001: How to Check if MySQL Service is Running on CentOS

As I am new comer in the MySQL domain, I am going to write up a few trivial posts about administrating MySQL. Today this is the first one:

The BASH command you are looking for to achieve the MySQL service status on CentOS system is:


shell> /etc/init.d/mysql status

And to connect to localhost’s MySQL service:


shell> mysql -h localhost -u root -p

Posted in MySQL | Leave a comment