Test Your Unix/Linux Level

I recently discovered the following website on the internet;

cmdchallenge

In this web you can test your knowledge of Unix/Linux. You will be asked to go along with a series of exercises that the web proposes to you. Are you able to do them all?

HTH – Antonio NAVARRO

 

 

Advertisements

How To Install Statspack On 9i Windows-box

Today I have been installing a statspack package for a older database, version 9i, maybe it is not relevant for many people but I want to post it in this entry because of I use the blog like a work diary and documentation place.

First of all we need to create a new tablespace where create the new objects for perfstat user, of course, you can use an existing tablepaspace but it is not a good idea;

 


CREATE TABLESPACE "ESTADISTICAS" DATAFILE
'X:\ORACLE\DATOS\ESTADISTICAS.DBF' SIZE 2147483648 -- 2 Gigas
LOGGING ONLINE PERMANENT BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

After we need the spcreate.sql scritp, locate on %ORACLE_HOME%\rdbms\admin

foto1

Connect to database as sysdba and execute de sql;

foto2

This sql script call the following scripts;

  • SPCUSR.SQL: Creates the user and grants privileges
  • SPCTAB.SQL: Creates the tables
  • SPCPKG.SQL: Creates the package

Ask me for the password for user perfstat (in clear text);

foto3.PNG

Next ask me for tablespace where locate tables with the data are recolected from the system. You must write down the tablespace name create before. You need a temporary tablespace too;

foto5.PNG

if all is okay you get a reply similar to the next window, however it is necessary to review the spcpkg.lis file for any errors;

foto7.PNG

Now you must to schedule the job to pick the statistics, with the spauto it pick each hour;

foto7

And the output;

foto8

HTH – Antonio NAVARRO

 

RMAN-00550 And RMAN-02000 When Execute Rman

Today I have discoverd the next error when I was executing rman;

 
C:\>rman 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
 wrong message file version (msg 19 not found)

Search for the error at ML/MOS I have seen that the problem can be the NLS_LANG variable. This database is on Windows 2003 Server and Oracle 9.2, of course it is out of all support and logic but it is a requeriment from the application. I have been perform some testcases with the NLS_LANG but without results.

Executing rman command from oracle_home binary files, it works fine;

 
C:\oracle\ora92\bin>rman

Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN>

I don’t know where is the problem yet, I Think it is relative to PATH variable but looks good.

HTH – Antonio NAVARRO

New View V$LGWRIO_OUTLIER In 12C

Recently I discovered the v$lgwrio_outlier, this view contains entries corresponding to the Log Writer process that have taken more than 500 ms. These entries are the same of trace LGWR log trace file. The advantage here is that you hava into a table the same information and not need to process this information.

This view is a starting point to detect slow disks, when it is on separete device, you can try optimize performance in the other hand if all files are in the same disk you have a problem.
In the next query I show a simple query to get the average, max an min latency (min in this case is 500 because of timeout)

                                 
DB12*ANTO> select max (io_latency), min (io_latency), avg (io_latency) from V$LGWRIO_OUTLIER;

MAX(IO_LATENCY) MIN(IO_LATENCY) AVG(IO_LATENCY)
_______________ _______________ _______________
           2094             500      685,002004

In the next example, the query return the files with more latency, there are three with high latency.

 

DB12*ANTO> select file_name, sum(io_latency)/1000 as "Latency Secs" from  V$LGWRIO_OUTLIER
  2   group by file_name order by 2 desc;

FILE_NAME                                               Latency Secs
_______________________________________________________ ____________
/ORACLE/DB12/RDO2/FINANCIAL_PROD_LOG12.RDO                    78,909
/ORACLE/DB12/RDO1/FINANCIAL_PROD_LOG21.RDO                    74,851
/ORACLE/DB12/RDO1/FINANCIAL_PROD_LOG11.RDO                    72,194
/ORACLE/DB12/RDO2/FINANCIAL_PROD_LOG22.RDO                    63,079
/ORACLE/DB12/RDO1/FINANCIAL_PROD_LOG31.RDO                    44,353
/ORACLE/DB12/RDO2/FINANCIAL_PROD_LOG32.RDO                      8,43

The only problem I have seen is that it need a timestamp or scn to get a time referential.

HTH – Antonio NAVARRO

 

How To Install Grid Agent 10 On Windows 2003 Server

Of course, I know install a Agent 10 on 2003 is not a good idea. My problem was I have had to install a Oracle 9.2 (this is getting better every time) for move platform (from Unix to Windows) of one very older database that it can’t be migrate to any version upper 9.

In this entry I post the steps to complete the Agent installation;

First of all you can download, the convenient (older) version at the next link;

Enterprise Manager Agents

After that unzip the downloaded file to a folder;

foto3

Execute the setup.exe under the correct path (In my case agent 10.2.0.4) and the next window pop-up. Only the agent software, click Next.

foto4.PNG

Select the directory for deploy the binaries and click Next.

foto5.PNG

If all is okay, click Next. Else to correct the errors, warnings is possible to skip but not a good idaea.

foto6.PNG

Write the IP or HOSTNAME for you OMS and click Next.

foto7

In my case one problem more, the OMS is no secure, no data encryted over the net, anyone with a sniffer can view data. With much fear and courage I pressed OK.

foto8

I usually don’t set the access to MOS/ML, click Next.

foto9.PNG

The summary, click Next.

foto10

click Install.

foto11.PNG

Starting

foto12

All Ok. Click Exit.

foto14

Now from you OMS to check you can see the new machine.

HTH – Antonio Navarro

 

 

 

RMAN-00554 RMAN-04005 ORA-01031 Errors

Today a coworker ask about the next error when He was configuring a Rman backup;

 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges

The core problem is the ORA-01031: insufficient privileges to perform the backup. Check the next items, it must solve the problem in the majority of cases;

 

i) Verify the password file exits. You can query the password file contain by the next query;

 
   SELECT * FROM V$PWFILE_USERS;
   

 

ii) check if remote_login_passworfile is set to EXCLUSIVE if not you can set by the next command;

 alter system set remote_login_passwordfile = EXCLUSIVE scope=both;

 

HTH – Antonio NAVARRO

How To Install SQL Server 2016 on Ubuntu Linux

Recently Microsoft announced the first beta of SQL Server 2016 for Linux platform. I think it a positive notice for the database world. I’ll like show in this blog entry a simple how to for set up a SQL Server 2016 on Ubuntu Linux.

First of all you need download the Ubuntu distro, please follow the next link to download it;

Ubuntu Downloads

I recommend you the desktop version.

Start VirtualBox and click NEW.

ubuntu_mssql_1

Set up the virtual machine you want, in this case Ubuntu, remember x64 bits architecture and click NEXT.

ubuntu_mssql_2

Now set the memory RAM for the system, I think the 4 Gb is the minimal to work fine, click NEXT.

ubuntu_mssql_3

In disk machine I created a specific virtual hard disk, click CREATE.

FOTO4.png

The more simple is VDI type, click NEXT;

FOTO5.png

I usually prefer to allocate space dynamically to have the space allocted from the beginning. The first method is slow the first time it has to be allocated and can seriously affect performance, in the second case is slow when creating the virtual machine. As I said before as this is a test machine, and will never be production, performance is not important and allocating space dynamically, is in my case to take advantage of the space of my very saturated laptop.

foto6

Configure the space of the new disk. In this case only puts 8 Gb although it is recommended to put more, double, 16 Gb would be fine. Later on we install Ubuntu, although it is not mandatory if it is highly recommended, install a volume manager, click CREATE.

FOTO7.png

We already have our machine created, press START.

FOTO8.png

Tell us that we have no operating system installed, so we must provide one.

FOTO9.png

In my case in the download folder, is the Ubuntu ISO. Select and Open.

FOTO10.png

Click START.

FOTO11.png

 

We get the next screen, click  INSTALL UBUNTU;

UBUNTU_MSSQL_12.png

Do not select anything and click NEXT.

ubuntu_mssql_13

Select ERASE DISK (Mandatory) and it is a good idea install a volume manager. When operating system has been installed install a GPARTED, it make simple to resize and change parameters. Click INSTALL NOW.

LVM.png

Click CONTINUE.

UBUNTU_MSSQL_16.png

In my case I have selected Euro Zone. Click CONTINUE.

UBUNTU_MSSQL_17.png

Select your keyboard layout, and click CONTINUE.

UBUNTU_MSSQL_19.png

Time to set passwords, click CONTINUE.

UBUNTU_MSSQL_20.png

Start the installation.

UBUNTU_MSSQL_21.png

When the installation is complete, click RESTART NOW.

UBUNTU_MSSQL_22.png

Remove the iso software to boot from hard disk elseIt will try to install again.

ubuntu_mssql_25

Time to get the software. First of all you need download a public key

especial_28.png

Now we need add the next lines to the repository;

sudo sh -c “echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main > /etc/apt/sources.list.d/sql-server.list”
sudo sh -c “echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main >> /etc/apt/sources.list.d/sql-server.list”

UBUNTU_MSSQL_33.png

Perform an update.

UBUNTU_MSSQL_34.png

Now install the Microsoft SQL Server, by execute the next command;

sudo apt-get install mssql-server mssql-tools -y

 

UBUNTU_MSSQL_35.png

When we are requested the license terms select (by tab key) the YES option.

UBUNTU_MSSQL_36.png

UBUNTU_MSSQL_37.png

The installation wil be continued.

UBUNTU_MSSQL_38.png

Now execute the next command, like show in the previus picture;

sudo /opt/mssql/bin/sqlservr-setup

The configuration ask for a password for SA;

UBUNTU_MSSQL_39.png

I have set everything to YES (Start SQL Server and Start on boot)

UBUNTU_MSSQL_40.png

The configuration have finished. Check the SQL Server status by one of the nexts commands;

ps -ef | grep mssql

systemctl status mssql-server

Using the first option you get the next picture.

UBUNTU_MSSQL_41.png

Now connect the SQL Server like SA user;

UBUNTU_MSSQL_45.png

To stop the SQL Server, the correct way (from OS) is with systemctl;

systemctl stop mssqsl-server

A pop-up ask for password;

UBUNTU_MSSQL_46.png

Check the status, failed means the SQL Server is down;

UBUNTU_MSSQL_47.png

Start up the database again, and a new pop-up require the password;

systemctl start mssql-server

UBUNTU_MSSQL_48.png

Time to play with this new toy. that’s all folks.

HTH – Antonio NAVARRO