How to edit the welcome message when ssh to a server. there are basically 2 methods:

1. Edit the /etc/motd and update the message that is required.
2. Edit the /etc/ssh/sshd_config: Change the setting PrintLastLog to "no".

Hope this helps. :)

The below script can be used to check the backup timings for all the database in a SQL server instance.

DECLARE @dbname sysname

SET @dbname = NULL --set this to be whatever dbname you want

SELECT bup.user_name AS [User],

 bup.database_name AS [Database],

 bup.server_name AS [Server],

 bup.backup_start_date AS [Backup Started],

 bup.backup_finish_date AS [Backup Finished]

 ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 

 + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '

 + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'

 AS [Total Time]

FROM msdb.dbo.backupset bup

WHERE bup.backup_set_id IN

  (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset

  WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all

  AND type = 'D' --only interested in the time of last full backup

  GROUP BY database_name) 

ORDER BY bup.database_name;

If you want to find the percent completion of operations like backup, use the below query.

SELECT      command, percent_complete,

            'elapsed' = total_elapsed_time / 60000.0,

            'remaining' = estimated_completion_time / 60000.0

FROM        sys.dm_exec_requests

WHERE       command like 'BACKUP%'

64 Bit Upgradation Of Oracle server

Oracle software comes in 64bit and 32 bit version. The 32 bit oracle has a limitation of addressing the 4 GB of physical memory. The main reason for up gradation is to surmount this limitation posed by 32 bit addressing scheme and also help increasing the scalability of the database server. This document clearly explains the step by step process of upgrading a database bit version from 32-bit to 64-bit.

Step 1 determining the bit version of oracle binaries:

The bit version of oracle binaries can be checked using file command in UNIX. 

For 32 bit it will produce output like this.

cd $ORACLE_HOME/bin

$ file oracle

oracle:         ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

and for 64 bit versions the output will be like below.

oracle:         ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

Step 2: Down load the oracle binaries for upgrade process.

2.1 Download the patch set 9.2.0.8 from metalink and ftp it to the target server.   

We have 9.2.0.6.0 version of oracle in order to mitigate the errors that might      occur during the process of up gradation it’s recommended to apply patch set of 9.2.0.8 (latest patch set is  9.2.0.8).

            

2.2 Download the 64 bit version and 9.2.0.8 patch set           

       Down load the 9.2.0.1 base version and install 64 bit oracle in predetermined oracle HOME. After this activity the new 9.2.0.8 patch should be applied to 64 bit home. The installation of 64 bit oracle and patching up will not affect the availability of instance. So this activity can be completed well in advance.  

      

Step 3: Prepare the OS for addressing larger SGA

Change the kernel parameter for oracle for large SGA. By changing the

The shm_max should be greater than SGA_MAX_SIZE. Otherwise the OS will create multiple shared segments for oracle which may result in suboptimal performance.

shmsys:  shm_max=12884901888  (12g.  Max RAM is 24g). This can be tuned after the release based on the requirement.

semsys:   sem_mns=5000

set semsys:  sem_mni=256     

set semsys:   sem_opm=256

Kernel parameter can be changed by editing the /etc/system(in Solaris)  files by logging as root. After changing the values server needs to be restarted to reflect the new values.

Step 4 : Backing  up the database .

            In this section we need to take the both logical and physical backup of database which will be required if the whole activity needs to be rolled back.  

4.1 Take the full export dump if it’s feasible. If it will take a long time take only the           full structure of database.

4.2 Create the backup of control file in binary and ascii format.

           SQL> create backup control file to ‘absolutefilepath’   --- for binary backup

           SQL>create backup control file to trace – for ascii format.

Copy the ascii format form user_dump_dest .

4.3 Create the list of invalid objects. This list needs to be compared with the invalid

  Objects after the upgradation. Any discrepancy at that time should be addressed. Spool the output of below sql.

SQL>Select owner,object_name,object_type,status from dba_objects where status <> ‘VALID’

                                                   

4.4 Save the list of jobs in the database and their intervals as these jobs may go into broken state during the migration process. 

4.5 Prepare the script for db links in the database by login as sys. Since the database   has db links pointing to Sybase database. Since its 9i we can get the password for db links by querying the sys.link$.

4.5 Check the components installed in the target database. These components needs to be installed in the after the 64 bit up gradation.

SQL>select comp_id,comp_name,version,status from dba_registry;

Step 5: Patching of 32bit oracle .

  5.1. Check the saherd_pool_size and shared_pool_reserved_size in database and set it above 150 MB.

  5.2. Apply the patch set 9.2.0.8 on top of 9.2.0.6 oracle.

          The patch set can be applied either through GUI or from command line through response files. For GUI VNc server needs to be started first.

Then run the oracle installer ./runinstaller.

5.3. After the patching the oracle migrate scripts needs to be executed from sqlplus.

shutdown database & listener

           

          LSNRTCTL>STOP     (default listener is configured for this db)

SQL>startup migrate

SQL>spool patch.log

SQL>@?/rdbms/admin/catpatch.sql

SQL>shutdown

SQL>startup

SQL>@?/rdbms/admin/utlrp.sql ----for compiling the invalid objects

check invalid objects count

Note: The alert logs should be monitored while startup and shutdown.

Step 6: Starting up of database in 64 bit oracle Home

6.1 copy the initfile, spfile,pwfile  into new ORACLE_HOME/dbs

6.2 copy the listener.ora  and tnsnames.ora to new $ORACLE_HOME/network/admin

       6.3 Copy the init files for gateways to their respective locations. All the Sybase    gateways should be copied to $ORACLE_HOME(Newone)/ tg4sybs/admin from $ORACLE_HOME(Newone)/ tg4sybs/admin.

       6.4 Check for ORACLE_HOME variables in .profile & change it and also update  

wherever applicable and also check for the Oracle database links including the    Sybase connectivity.

        6.5. Add the variable LD_LIBRARY_PATH = ORACLE_HOME/lib32 In .profile.

      6.6. Make following changes to init.ora
                 aq_tm_processes=0   
                 job_queue_processes=0
                 _system_trig_enabled= FALSE

             These settings are recommended by oracle during the upgrade process.

  

      6.7. Increasing the following memory parameters to address double the memory.
            SHARED_POOL_SIZE
            SHARED_POOL_RESERVED_SIZE

           LARGE_POOL_SIZE. These memories need to be doubled as the internal oracle      structures will use the double memory size. As the internal oracle pointers will be using double the size in 64bit versions (As in case of float data type in c)

Parameter name	Current Value (Bytes)	Values to be resized(Bytes)
SHARED_POOL_SIZE	15000000	30000000
SHARED_POOL_RESERVED_SIZE	436207616	872415232
LARGE_POOL_SIZE	67108864	134217728

  

     .

  6.8. Startup the database using pfile

     SQL>  Startup migrate pfile= ‘$RACLE_HOME/dbs/initMNDP.ora’

     SQL> @?/rdbms/admin/catalog.sql

   6.9 Check the validity of the DBMS_STANDARD package:

 SQL> select status from dba_objects where object_name='DBMS_STANDARD'   and         object_type='PACKAGE'  and owner='SYS';

    If the package is invalid recompile it.

   SQL> alter package dbms_standard compile;

6.10 Run the following scripts:

 SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

   After running this script, check for invalid objects:

 SQL> select owner, object_name, object_type from dba_objects
         where status <> 'VALID';

    Recompile any invalid objects to avoid problems while running the utlirp.sql script

The components catproc and catalog should be in valid state. If any of these components are invalid , ORA-600 error will be logged in alert logs. These components   can be made valid by executing the cataproc.sql for CATPROC, and catalog.sql for CATALOG.

        6.11 Set the system to spool results to a log file for later verification of success:

   

        SQL> SPOOL catoutw.log

        If you want to see the output of the script you will run on your screen,
        then you can also issue a SET ECHO ON statement:

        SQL> SET ECHO ON

Run utlirp.sql:

SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

    The utlirp.sql script recompiles existing PL/SQL modules in the format required by the new database. This script first alters certain dictionary tables. Then, it reloads package STANDARD and DBMS_STANDARD,  which are necessary for using PL/SQL. Finally, it triggers a recompile of all PL/SQL modules, such as packages, procedures, types, and so on.

            Turn off the spooling of script results to the log file:
       SQL> SPOOL OFF
        Then, check the spool file and verify that the packages and procedures
 compiled successfully. You named the spool file in Step 6 the suggested
 name was catoutw.log. Correct any problems you find in this file.

    If you specified SET ECHO ON, then you may want to SET ECHO OFF now:

    SQL> SET ECHO OFF

6.12. Remove these from the pfile

       aq_tm_processes=0
      job_queue_processes=0
      _system_trig_enabled=false

6.13. The SGA and memory related parameters needs to be checked and tuned after the upgrade based on the requirements:

shared_poolsize=600  (doubled the current one 304)
db_cache_size=3500m
db_keep_cache_size=500m
db_recycle_cache_size=500m
sga_max_size=6000m
pga_aggregate_target=3000m
log buffer = 4096000  (the current one of 20mb is way above normal, may cause log file sync waits at times)

6.14. Bounce the DB and release to Application team  for testing

    

 DBA can verify the changes from select address from v$sql (it would be 16 char long as compared to the 8chr of 32-bit).

Steps to login to the mysql database server :

Unix OS Prompt > mysql –h hostname –u root –p (in case if the mysql database is in local machine, -h parameter is not required).

e.g. given below

admin:~> mysql -u root -p

Enter password:

List down all active process:

admin:~> show full processlist

Creating a database:

create database [DBNAME];

e.g.

mysql> create database test1234;

Query OK, 1 row affected (0.05 sec)

mysql>

Identify the databases present in the mysql server:

show databases;

e.g.

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| | test               |

| test1234           |

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

4 rows in set (0.00 sec)

mysql>

In the above, the information_schema and the mysql are the default databases present in the mysql server always.

Switch to a database:

use [db name];

e.g.

mysql> use test1234;

Database changed

mysql>

List all the tables in the dabase.

show tables;

e.g.

mysql> show tables;

Empty set (0.00 sec)

Create a table in the database:

Create table <tablename> (colname coltype, col2 coltype2,..).

e.g.

mysql> create table test123(eno integer);

Query OK, 0 rows affected (0.10 sec)

mysql>

Describe a table:

describe [table name];

e.g.

mysql> desc test123;

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

| Field | Type    | Null | Key | Default | Extra |

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

| eno   | int(11) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

mysql>

DROP a database:

drop database [db name];

DROP a table:

drop table [table name];

List the sessions of the database:

mysql> SHOW PROCESSLIST ;

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

| Id     | User | Host                | db             | Command | Time | State | Info             |

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

 356505 | root | localhost           | test1234       | Query   |    0 | NULL  | SHOW PROCESSLIST |

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

1 rows in set (0.01 sec)

Display records of a table:

SELECT * FROM <tablename>;

SELECT * FROM [table name] WHERE [field name] = "<filtercindition>”;

Change user password:

In mySQL, the authentication and permissions happens for any user happens based on the permissions given to the IP address of a host name and hence the below commands to be used.

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('password');

grant usage on *.* to <username>@localhost identified by 'passwordd';

grant all privileges on databasename.* to username@localhost;

Update database permissions/privileges.

FLUSH PRIVILEGES;

DROP A Column in a table

alter table [table name] drop column [column name];

Export of a database in a dump file:

mysqldump -u root -ppassword --opt > <filename for the dump>

e.g.

admin:~> mysqldump --all-databases

-- MySQL dump 10.13  Distrib 5.1.41, for unknown-linux-gnu (x86_64)

--

-- Host: localhost    Database:

-- ------------------------------------------------------

-- Server version       5.1.41-community

--

-- Current Database: `test`

--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-07-15 20:53:43

Export of a table from database:

mysqldump -c -u username –p password DBNAME TABNAME >  tablename.sql

Restore database from dump backup:

mysql -u username -ppassword <dbname>  < DB.SQL

Script to Check the Concurrent Manager Programs that ended up in an failure:

spool ${ORACLE_SID}_concerr_rpt.txt

REM $Header:chk_conc_error.sql$

REM

REM  | DESCRIPTION

REM  |    Lists the Concurrent Manager programs that ended with status other than Normal

REM  +=======================================================================*/

PROMPT ********************************************************

PROMPT        Database Concurrent Manager Status Report

PROMPT ********************************************************

PROMPT

PROMPT

SET SERVEROUTPUT ON

REM------------------------------------------------------

REM Description:

REM   Displays Concurrent Programs ended with an Error

REM------------------------------------------------------

PROMPT

PROMPT

PROMPT ********************************************************

PROMPT  Concurrent Manager Programs that ended with an Error

PROMPT ********************************************************

PROMPT

PROMPT Request Id     ConcurrentID Start Date    Pgm. Name   Status    Phase

PROMPT **********************************************************************

PROMPT

COLUMN a.request_id FORMAT 999999999 HEADING "Request Id"

COLUMN a.CONCURRENT_PROGRAM_ID FORMAT 99999999 HEADING "Concurrent ID"

COLUMN a.ACTUAL_START_DATE FORMAT A10 HEADING "Start Date"

COLUMN b.CONCURRENT_PROGRAM_NAME FORMAT A14 HEADING "Program Name"

COLUMN a.Status_code FORMAT A10 HEADING "Status Code"

COLUMN a.Phase_code FORMAT A10 HEADING "Phase Code"

PROMPT

select a.request_id "RequestID",a.CONCURRENT_PROGRAM_ID "Conc.ID",a.ACTUAL_START_DATE "StartDate",b.CONCURRENT_PROGRAM_NAME "Program",

a.Status_code "Status",a.Phase_code "Phase" from fnd_concurrent_requests a,Fnd_Concurrent_Programs b where a.STATUS_CODE='E'

and Phase_code='C' and a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and (trunc(sysdate-a.ACTUAL_START_DATE)<2);

/

PROMPT

PROMPT ********************************************************

PROMPT  Concurrent Manager Programs that ended with Warnings

PROMPT ********************************************************

PROMPT

PROMPT Request Id     ConcurrentID Start Date    Pgm. Name   Status    Phase

PROMPT **********************************************************************

PROMPT

COLUMN a.request_id FORMAT 999999999 HEADING "Request Id"

COLUMN a.CONCURRENT_PROGRAM_ID FORMAT 99999999 HEADING "Concurrent ID"

COLUMN a.ACTUAL_START_DATE FORMAT A10 HEADING "Start Date"

COLUMN b.CONCURRENT_PROGRAM_NAME FORMAT A14 HEADING "Program Name"

COLUMN a.Status_code FORMAT A10 HEADING "Status Code"

COLUMN a.Phase_code FORMAT A10 HEADING "Phase Code"

PROMPT

select a.request_id "RequestID",a.CONCURRENT_PROGRAM_ID "Conc.ID",a.ACTUAL_START_DATE "StartDate",b.CONCURRENT_PROGRAM_NAME "Program",

a.Status_code "Status",a.Phase_code "Phase" from fnd_concurrent_requests a,Fnd_Concurrent_Programs b where a.STATUS_CODE='G'

and Phase_code='C' and a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and (trunc(sysdate-a.ACTUAL_START_DATE)<2);

/

PROMPT

PROMPT

PROMPT

PROMPT *****************************************************************

PROMPT  Concurrent Manager Programs that ended with status as Cancelled

PROMPT *****************************************************************

PROMPT

PROMPT Request Id     ConcurrentID Start Date    Pgm. Name   Status    Phase

PROMPT **********************************************************************

PROMPT

COLUMN a.request_id FORMAT 999999999 HEADING "Request Id"

COLUMN a.CONCURRENT_PROGRAM_ID FORMAT 99999999 HEADING "Concurrent ID"

COLUMN a.ACTUAL_START_DATE FORMAT A10 HEADING "Start Date"

COLUMN b.CONCURRENT_PROGRAM_NAME FORMAT A14 HEADING "Program Name"

COLUMN a.Status_code FORMAT A10 HEADING "Status Code"

COLUMN a.Phase_code FORMAT A10 HEADING "Phase Code"

PROMPT

select a.request_id "RequestID",a.CONCURRENT_PROGRAM_ID "Conc.ID",a.ACTUAL_START_DATE "StartDate",b.CONCURRENT_PROGRAM_NAME "Program",

a.Status_code "Status",a.Phase_code "Phase" from fnd_concurrent_requests a,Fnd_Concurrent_Programs b where a.STATUS_CODE='D'

and Phase_code='C' and a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and (trunc(sysdate-a.ACTUAL_START_DATE)<1);

/

PROMPT

PROMPT

PROMPT

PROMPT *****************************************************************

PROMPT  Concurrent Manager Programs that ended with status as Paused

PROMPT *****************************************************************

PROMPT

PROMPT Request Id     ConcurrentID Start Date    Pgm. Name   Status    Phase

PROMPT **********************************************************************

PROMPT

COLUMN a.request_id FORMAT 999999999 HEADING "Request Id"

COLUMN a.CONCURRENT_PROGRAM_ID FORMAT 99999999 HEADING "Concurrent ID"

COLUMN a.ACTUAL_START_DATE FORMAT A10 HEADING "Start Date"

COLUMN b.CONCURRENT_PROGRAM_NAME FORMAT A14 HEADING "Program Name"

COLUMN a.Status_code FORMAT A10 HEADING "Status Code"

COLUMN a.Phase_code FORMAT A10 HEADING "Phase Code"

PROMPT

select a.request_id "RequestID",a.CONCURRENT_PROGRAM_ID "Conc.ID",a.ACTUAL_START_DATE "StartDate",b.CONCURRENT_PROGRAM_NAME "Program",

a.Status_code "Status",a.Phase_code "Phase" from fnd_concurrent_requests a,Fnd_Concurrent_Programs b where a.STATUS_CODE='W'

and Phase_code='C' and a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and (trunc(sysdate-a.ACTUAL_START_DATE)<2);

/

PROMPT

PROMPT

PROMPT

PROMPT *****************************************************************

PROMPT  Concurrent Manager Programs  - Terminating Requests

PROMPT *****************************************************************

PROMPT

PROMPT Request Id     ConcurrentID Start Date    Pgm. Name   Status    Phase

PROMPT **********************************************************************

PROMPT

COLUMN a.request_id FORMAT 999999999 HEADING "Request Id"

COLUMN a.CONCURRENT_PROGRAM_ID FORMAT 99999999 HEADING "Concurrent ID"

COLUMN a.ACTUAL_START_DATE FORMAT A10 HEADING "Start Date"

COLUMN b.CONCURRENT_PROGRAM_NAME FORMAT A14 HEADING "Program Name"

COLUMN a.Status_code FORMAT A10 HEADING "Status Code"

COLUMN a.Phase_code FORMAT A10 HEADING "Phase Code"

PROMPT

select a.request_id "RequestID",a.CONCURRENT_PROGRAM_ID "Conc.ID",a.ACTUAL_START_DATE "StartDate",b.CONCURRENT_PROGRAM_NAME "Program",

a.Status_code "Status",a.Phase_code "Phase" from fnd_concurrent_requests a,Fnd_Concurrent_Programs b where a.STATUS_CODE='T'

and Phase_code='R' and a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and (trunc(sysdate-a.ACTUAL_START_DATE)<2);

/

PROMPT

PROMPT

PROMPT ********************************************************

PROMPT  End of Concurrent Manager Program Report

PROMPT ********************************************************

Spool Off

/

SET SERVEROUTPUT OFF