I recently discovered the following website on the internet;
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
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
Connect to database as sysdba and execute de sql;
This sql script call the following scripts;
SPCUSR.SQL: Creates the user and grants privilegesSPCTAB.SQL: Creates the tablesSPCPKG.SQL: Creates the packageAsk me for the password for user perfstat (in clear text);
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;
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;
Now you must to schedule the job to pick the statistics, with the spauto it pick each hour;
And the output;
HTH – Antonio NAVARRO
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
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
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;
After that unzip the downloaded file to a folder;
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.
Select the directory for deploy the binaries and click Next.
If all is okay, click Next. Else to correct the errors, warnings is possible to skip but not a good idaea.
Write the IP or HOSTNAME for you OMS and click Next.
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.
I usually don’t set the access to MOS/ML, click Next.
The summary, click Next.
click Install.
Starting
All Ok. Click Exit.
Now from you OMS to check you can see the new machine.
HTH – Antonio Navarro
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
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;
I recommend you the desktop version.
Start VirtualBox and click NEW.
Set up the virtual machine you want, in this case Ubuntu, remember x64 bits architecture and click NEXT.
Now set the memory RAM for the system, I think the 4 Gb is the minimal to work fine, click NEXT.
In disk machine I created a specific virtual hard disk, click CREATE.
The more simple is VDI type, click NEXT;
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.
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.
We already have our machine created, press START.
Tell us that we have no operating system installed, so we must provide one.
In my case in the download folder, is the Ubuntu ISO. Select and Open.
Click START.
We get the next screen, click INSTALL UBUNTU;
Do not select anything and click NEXT.
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.
Click CONTINUE.
In my case I have selected Euro Zone. Click CONTINUE.
Select your keyboard layout, and click CONTINUE.
Time to set passwords, click CONTINUE.
Start the installation.
When the installation is complete, click RESTART NOW.
Remove the iso software to boot from hard disk elseIt will try to install again.
Time to get the software. First of all you need download a public key
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”
Perform an update.
Now install the Microsoft SQL Server, by execute the next command;
sudo apt-get install mssql-server mssql-tools -y
When we are requested the license terms select (by tab key) the YES option.
The installation wil be continued.
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;
I have set everything to YES (Start SQL Server and Start on boot)
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.
Now connect the SQL Server like SA user;
To stop the SQL Server, the correct way (from OS) is with systemctl;
systemctl stop mssqsl-server
A pop-up ask for password;
Check the status, failed means the SQL Server is down;
Start up the database again, and a new pop-up require the password;
systemctl start mssql-server
Time to play with this new toy. that’s all folks.
HTH – Antonio NAVARRO
