How to Spool SqlPlus Output to Microsoft Excel Format




It is very easy to export and save output of executed query in xls sheet using toad but how to do it on sql prompt. To do so you need to use some text formatting options and spool file name to save output.

To Spool SQLPLus output to Excel (.xls) file, the trick is to turn On "MARKUP HTML".


SET PAGESIZE 40000

SET FEEDBACK OFF

SET MARKUP HTML ON

SET NUM 24

SPOOL file_name.xls



---- Execute your query



SPOOL OFF

SET MARKUP HTML OFF

SPOOL OFF



Example:

SQL> SET PAGESIZE 40000

SQL> SET FEEDBACK OFF

SQL> SET MARKUP HTML ON

SQL> SET NUM 24

SQL> SPOOL ora_htl_hist.xls

SQL>

SQL>SELECT COUNT * FROM ORA.HTL_HIST;

SQL>

SQL>SPOOL OFF

SQL>SET MARKUP HTML OFF

SQL>SPOOL OFF


A file name ora_htl_hist.xls will be generated in the current directory. This is the best way to export and save sql plus output to xls sheet.

Read more ...

VI editor shows the error Terminal too wide within Solaris


Terminal too wide


You may receive the following error when using VI editor within Solaris. I got this error when I was adding tns entry in tnsnames.ora file.



$ vi tnsnames.ora

Terminal too wide




Here, Vi will not allow to edit files. For editing you must increase the number of columns as shown below.


To resolve this increase the number of columns using below command:

stty columns 120


Read more ...

How to Change the Default SSH Port When Selinux is Enable on Linux

In this How-To guide we are going to explain you though changing the default SSH port on a Linux system.

SSH - The Secure Shell Protocol by default uses port 22. You can change this port number for security purpose. If you use Oracle Linux 7, Centos 7 or Red Hat Linux 7 versions, you can change port number with below operations.

Change the default SSH port

Accepting this value does not make your system insecure, nor will changing the port provide a
significant variance in security. However, changing the default SSH port will stop attacker from making unauthorized access or from many automated attacks and a bit harder to guess on which port SSH is accessible from.

 If selinux is enabled, you have to add new port number to selinux configuration because of the fact that selinux allows only 22 port number for ssh connections.


How to Change SSH Port When Selinux is Enable in Linux



STEP1 : As root user, edit the sshd configuration file using default VI editor.
 # vi /etc/ssh/sshd_config

Port 2290

Save and Exit


STEP 2: Edit the line which states 'Port 22'. 
But before doing so, you must choose an appropriate port and also make sure that it not currently used by any other application on the system.

# What ports, IPs and protocols we listen for

Port 2290

Note: The Internet Assigned Numbers Authority (IANA) is responsible for the global coordination of the DNS Root, IP addressing, and other Internet protocol resources. It is good practice to follow their port assignment guidelines. Having said that, port numbers are divided into three ranges: Well Known Ports, Registered Ports, and Dynamic and/or Private Ports. The Well Known Ports are those from 0 through 1023 and SHOULD NOT be used. Registered Ports are those from 1024 through 49151 should also be avoided too. Dynamic and/or Private Ports are those from 49152 through 65535 and can be used. Though nothing is stopping you from using reserved port numbers, our suggestion may help avoid technical issues with port allocation in the future.


STEP 3 : Change Selinux Configuration

To change:
 # semanage port -a -t ssh_port_t -p tcp 2290


To list using grep:
# semanage port -l | grep ssh


STEP 4: Restart SSHD Service to Activate New Confiuration
 # systemctl restart sshd.service

Switch over to the new port by restarting SSH.

#/etc/init.d/ssh restart


STEP 5: Verify SSH is listening on the new port by connecting to it.
Now connect using new port no 2290.
ssh username@hostname.com -p 2290

Read more ...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Question: I am getting ORA-32004 error while starting the database but all the parameters in spfile are fine. This issue came after upgrade the database from 10g to 11.2.0.4.0

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance


How to fix the ORA-32004 error?


Cause: ORA-32004 causes because one or more obsolete parameters were specified in the SPFILE. These depreciated parameters are still in use by spfile.

Action: See alert log for a list of parameters that are obsolete or deprecated. Remove it from the SPFILE.

You must check alert log to see the names of the parameters that are obsolete.


You can also find the obsolete parameters with this SQL*Plus query:
select name from v$obsolete_parameter where isspecified='TRUE';
                                           OR
select p.name,p.value
from v$parameter p, v$spparameter s
where s.name=p.name
and p.isdeprecated='TRUE'
and s.isspecified='TRUE';

Once found, you must remove it from the spfile or pfile.  You can use alter system command to remove it from spfile.

Read more ...

[A-Z] VI Editor Commands for Editing Files in Linux

For operating systems like Solaris and Linux, you must know some of the basic vi editor commands for the smooth and frequent editing of files. These commands are frequently used in searching patterns within a file, moving arrows up, down, left and right, deleting characters/lines, inserting text etc.

vi editor commands in Linux


vi Editor Commands




Editing and Saving Files


        To create a new file: vi filename
        To exit vi and save changes: [esc][colon] ZZ or wq like Press esc then :wq
        To exit vi without saving changes: :q!
        To saves the current file without quitting: :w
        To enter vi command mode: [esc]
        To enter vi insert mode: Press i



Moving Cursor Within a file


h       move left (backspace)

j       move down

k       move up

l       move right (spacebar)

[return]   move to the beginning of the next line

$       last column on the current line

0       move cursor to the first column on the
current line

^       move cursor to first nonblank column on the
current line

w       move to the beginning of the next word or
punctuation mark

W       move past the next space

b       move to the beginning of the previous word
or punctuation mark

B       move to the beginning of the previous word,
ignores punctuation

        e       end of next word or punctuation mark

        E       end of next word, ignoring punctuation

        H       move cursor to the top of the screen

        M       move cursor to the middle of the screen

        L       move cursor to the bottom of the screen



 Finding Patterns in a File

?       finds a word going backwards

/       finds a word going forwards

        f       finds a character on the line under the
cursor going forward

        F       finds a character on the line under the
cursor going backwards

        t       find a character on the current line going
forward and stop one character before it

T       find a character on the current line going
backward and stop one character before it

; repeat last f, F, t, T



Screen Movement

       G        move to the last line in the file

       xG       move to line x

       z+       move current line to top of screen

       z        move current line to the middle of screen

       z-       move current line to the bottom of screen

       ^F       move forward one screen

       ^B       move backward one line

       ^D       move forward one half screen

       ^U       move backward one half screen

       ^R       redraw screen
( does not work with VT100 type terminals )

       ^L       redraw screen
( does not work with Televideo terminals )

:# move to line #

:$ move to last line of file



Inserting character in a File

       r        replace character under cursor with next
character typed

       R        keep replacing character until [esc] is hit

       i        insert before cursor

       a        append after cursor

       A        append at end of line

       O        open line above cursor and enter append mode



Deleting words and characters within a file

x       delete character under cursor

dd      delete line under cursor

        dw      delete word under cursor

        db      delete word before cursor



Useful Miscellaneous Commands

. repeat last command

u undoes last command issued

U undoes all commands on one line

xp deletes first character and inserts after
second (swap)

J join current line with the next line

^G display current line number

% if at one parenthesis, will jump to its mate

mx mark current line with character x

'x find line marked with character x

NOTE: Marks are internal and not written to the file.

        yy      (yank)'copies' line which may then be put by
the p(put) command. Precede with a count for
multiple lines.



Put Command
        brings back previous deletion or yank of lines,
words, or characters

        P       bring back before cursor

        p       bring back after cursor



Reading Files
copies (reads) filename after cursor in file
currently editing

:r filename



Shell Escape
executes 'cmd' as a shell command.

:!'cmd'

Read more ...

5 Steps to Move Control File from File System to ASM Disk

Moving Control File to ASM



Moving controlfile from filesystem to ASM disk using RMAN in RAC.


You can migrate controlfile from file system to asm disk or to different diskgroup in ASM. For that you have to stop the database and startup in nomount state.


STEP 1: Stop the Database and startup in nomount state
SQL> shutdown immediate;
SQL> startup nomount;


STEP 2:Connect the target database using RMAN
$ rman
RMAN>connect target /
RMAN>restore controlfile to '<DISKGROUP_NAME>' from '<OLD_PATH>';
RMAN> restore controlfile to '+DATA' from '/oradatao1/test11/oradata/san/control01.ctl';

You can also multiplex controlfile to FRA like:
RMAN> restore controlfile to '+FRA' from '/oradatao1/test11/oradata/san/control01.ctl';


STEP 3: Show Location Of New Control Files
ASMCMD> find -t controlfile . *
+DATA/DG/CONTROLFILE/current.321.780791421
+FRA/DG/CONTROLFILE/current.368.870851527


STEP 4: On the database side:
 Modify init.ora or spfile, replacing the new path to the init parameter control_files.
 if using init<SID>.ora, just modify the control_files parameter and restart the database.
 If using spfile, Change Parameter for new locations.
alter system set control_files='+DATA/DG/CONTROLFILE/current.321.780791421','+FRA/DG/CONTROLFILE/current.368.870851527' scope=spfile;


STEP 5: Shutdown the database and open for use
SQL>shutdown immediate
SQL> alter database open;


Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

If you do not use ASM and If you want to new control file in file system, you can copy – paste controlfile to clone after stop database. And you have to change control_files parameter when database is nomount mode or stop.

Read more ...

Top 10 Commands to Check CPU Information on Linux

CPU info includes detailed information about the processor, like it's architecture, vendor name, model, number of cores, speed of each core etc. In Linux, there are many command line or GUI-based tools that can be used to show detailed information about your CPU hardware, however we are exploring some of the best and useful commands using which you can get much more detailed information about Linux CPU.

Get CPU info using /proc/cpuinfo on Linux

Linux /proc/cpuinfo file contains details about individual cpu cores. Proc (/proc) file system provides information about CPU and their speed which is a pseudo-filesystem. It is used as an interface to kernel data structures. It is commonly mounted at /proc. You can use command like more, less or grep to see the contents of this file.

Commands to Get CPU Details on Linux


$ less /proc/cpuinfo
siblings        : 16
core id         : 0
cpu cores       : 8
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 x2apic popcnt aes xsave avx f16c rdrand lahf_lm ida arat xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
bogomips        : 5199.62
clflush size    : 64
cache_alignment : 64
address sizes   : 46 bits physical, 48 bits virtual
power management:
processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
................ etc

You can also get details using more and cat command like:
$ more /proc/cpuinfo
$ cat /proc/cpuinfo


To display number of processors in the system
Every processor or core is listed separately and the various details about speed, cache size and model name are included in the description. To count the number of processing units use grep command with wc.
$cat /proc/cpuinfo | grep processor | wc -l
32
$ grep processor /proc/cpuinfo 
processor       : 0
processor       : 1
processor       : 2
processor       : 3
processor       : 4
processor       : 5
processor       : 6
processor       : 7
processor       : 8
........... ...........
processor       : 29
processor       : 30
processor       : 31 

Note: The number of processors shown by /proc/cpuinfo might be different from the actual number of cores on the processor. For example a processor with 2 cores and hyperthreading would be reported as a processor with 4 cores.


To get the actual number of cores, check the core id for unique values
$ cat /proc/cpuinfo | grep 'core id'
$ cat /proc/cpuinfo | grep 'core id'| wc -l
32


lscpu
lscpu is a small and quick command that does not need any options. It would simply print the cpu hardware details in a human readable format.
$ lscpu


To Find CPU vendor 
If you’re interested in just knowing the CPU vendor, go with cat /proc/cpuinfo along with with the grep command.
$ cat /proc/cpuinfo | grep vendor | uniq
vendor_id       : GenuineIntel


To Find Model Name
$ cat /proc/cpuinfo | grep 'model name' | uniq
model name      : Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz


Hardinfo 
Hardinfo is a gtk based gui utility that generates reports about various hardware components including memory, storage, PCI devices, storage, USB devices etc plus information on the OS, kernel, and networking. But it can also run from the command line only if there is no gui display available.

To install hardinfo
If hardinfo is not installed on your Linux box, you can do so with the following command on your Ubuntu system.
$ sudo apt-get install hardinfo
Let's Look into this command
$ hardinfo | less
It would produce a large report about many hardware parts, by reading files from the /proc directory. The cpu information is towards the beginning of the report. The report can also be written to a text file. Hardinfo also performs a few benchmark tests taking a few minutes before the report is displayed.


To find the number of processing units available
 The nproc command just prints out the number of processing units available. Note that the number of processing units might not always be the same as number of cores.
$ nproc
4


To Display name of the Operating System.
$ uname -a
Linux 2.6.39-400.215.10.el5uek #1 SMP Tue Sep 10 22:51:46 PDT 2014 x86_64 x86_64 x86_64 GNU/Linux


Inxi
 One of my all time favorite command line tools and I have it running on all my Linux systems. This is an excellent script that provides information in an easy to understand format.

To install Inix on Ubuntu
$ sudo apt-get install inxi

To install Inix on CentOS/Fedora
$ sudo yum install inxi

Print out cpu/processor related information 
$ inxi -C


dmidecode
The dmidecode command displays some information about the cpu, which includes the socket type, vendor name and various flags. Following command with type id 4 will get the information about CPU of the system.
# dmidecode -t 4
# dmidecode 2.9
$sudo demidecode -t processor
$ sudo dmidecode -t 4
Get BIOS information using dmidecode
# dmidecode -t bios
# dmidecode 2.9
View Manufacturer, Model and Serial number of the equipment using dmidecode
# dmidecode -t system
# dmidecode 2.9


cpuid
The cpuid command fetches CPUID information about Intel and AMD x86 processors.

This program can be installed with apt on ubuntu
$ sudo apt-get install cpuid

To install cpuid on Fedora and CentOS distributions.
$ sudo yum install cpuid

And here is sample output
$ cpuid

Run the command with less to get a readable output.
$ cpuid | less


You can also do R&D with grep command to find more cpu info in Linux/Unix. From the readers, your comment is valuable to us. Please share if you have more valuable commands to get more detailed output.


Other similar sources:

nixCraft: Number of CPUs and Their Speed
redhax: /proc/cpuinfo.html
linuxquestions.org
Read more ...

ORA-27211: Failed to load Media Management Library

RMAN backup failed With below error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 06/20/2015 09:17:05
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 4005


How to fix ORA-19554 and ORA-27211 errors?


CAUSE:

Before using RMAN with a media manager, media management software must be installed and configured on the target host or production network.

When allocating or configuring channels for RMAN to use to communicate with a media manager, specify the SBT_LIBRARY parameter to provide the path to the media management software library. When RMAN allocates channels to communicate with a media manager, it attempts to load the library indicated by the SBT_LIBRARY parameter.

If you do not provide a value for this parameter, RMAN looks in a platform-specific default location. On Linux/UNIX, the default library filename is $ORACLE_HOME/lib/libobk.so, with the extension name varying according to platform: .so, .sl, .a, and so on. On Windows the default library location is %ORACLE_HOME%\bin\orasbt.dll.

If the database is unable to locate a media management library in the location specified by the SBT_LIBRARY parameter or the default location, then RMAN issues an ORA-27211 error and exits.


Resolving "Failed to load Media Management Library" Issue

Link libobk.so to your backup vendor supplied rman library using symbolic link.

Go to <ORACLE_HOME/lib/> directory.

$ pwd
/ora/app/oracle12c/product/12.1.0.2/dbhome_1/lib
$ cd  /usr/openv/netbackup/bin
$ ls -ltr libobk.so64
-r-xr-xr-x 1 root bin 1567895 Jun  20  2015 libobk.so64

$ cd /ora/app/oracle12c/product/12.1.0.2/dbhome_1/lib
$ ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so
$ ls -ltr libobk.so
lrwxrwxrwx 1 oracle dba 36 Jun 20 09:27 libobk.so -> /usr/openv/netbackup/bin/libobk.so64

Now start backup, if it gets completed successfully then no issues, if fail again then create below link in the same location as above.

$ ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.a
$ ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so64
$ ls -lrt libobk*
lrwxrwxrwx 1 oracle dba 36 Jun 20 09:45 libobk.so -> /usr/openv/netbackup/bin/libobk.so64
lrwxrwxrwx 1 oracle dba 36 Jun 20 10:00 libobk.a -> /usr/openv/netbackup/bin/libobk.so64
lrwxrwxrwx 1 oracle dba 36 Jun 20 10:00 libobk.so64 -> /usr/openv/netbackup/bin/libobk.so64


For "ORA-19554: error allocating device, device type: SBT_TAPE" 
and "ORA-27211: Failed to load Media Management Library" you can get more info at: Backup and Recovery

Read more ...

CONTACT

Name

Email *

Message *