Monday, 19 October 2015

Silent installation of client

There are actually 2 components of such installation: response file, which consists all the arguments for installation and shell call

Response file

Below example of a response file


## I cut the line below for better display




The example above is excerpt from the example file provided by Oracle without comments and a number of other options. The most important here is the Oracle base and home locations and inventory location.
The inventory should be placed in some independent directory, where one may change permissions without affecting the other binaries trees. This is especially important if one plans to install more products as all of them require access to such inventory and may be owned by different users.

Shell call

There is several options to the runInstaller exec. The most simple call looks like this:
./runInstaller -silent -responseFile 
Sometimes -ignorePrereq is useful if one knows, what one's doing.

Pros and cons

The response file preparation may become kind of trouble, but it is worth the effort - after all it is well to apply some convention and such file may become kind of policy document in the client installation regard.
The most important is the installation is way faster than with GUI.

Tuesday, 22 September 2015

Trouble with an installation on AIX

This time I have got quite a simple task - to install Oracle database binaries on a host with AIX. Work as usual.
But after copying files the installer process displayed an error. Below excerpt from the log.
ld: 0711-866 INTERNAL ERROR: Output symbol table size miscalculated.

INFO: ld: 0711-759 INTERNAL ERROR: Source file xoutxx.c, line 574.
        Depending on where this product was acquired, contact your service
        representative or the approved supplier.

INFO: make: 1254-004 The error code from the last command is 16.


INFO: make: 1254-004 The error code from the last command is 2.


INFO: End output from spawned process.
INFO: ----------------------------------
INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'links proc gen_pcscfg' of makefile 
      '/oracle/app/product/'. See 
      '/oracle/oraInventory/logs/installActions2015-09-22_08-11-37AM.log' for details.
Exception Severity: 1

As this is version and the AIX is 7.1 I was afraid this may be due to some lacks in system filesets or so. It seems not so - all the details You may find in "AIX: ERROR IN INVOKING TARGET 'LINKS PROC GEN_PCSCFG' OF MAKEFILE, ld: 0711-593 SEVERE ERROR: Symbol C_BSTAT (Doc ID 985563.1)" on the Metalink.

Wednesday, 9 September 2015


Today we hit (while creating a test environment) ORA-32598: user names cannot be changed in ALTER DATABASE LINK command. An element of creating a test environment is the password change for users and accompanying change in db links' passwords.
As this is the 11g version, we are able to leverage the command ALTER DATABASE LINK.
We run something like:
ALTER DATABASE LINK link1 CONNECT TO user1 identified by "user1test";
And the db engine complains about changing user name, but the name seems perfectly fine. We know that this is copied from a production environment where this link works properly. But wait...
What about the etarnal problem with double quotes and names?
This is the diagnosis and solution is clear then - the db link creator in his/her command used lower case letters and enveloped the name in double quotes. On the production it works even if the user on a remote db is named traditionally (i.e. its name is stored in upper case) as apparently the connect string on the remote side is resolved as usually i.e. in a case-insensitive way.
But on the local database the user name is in lower case so if one does not use double quotes, the engine does not find the match between user already set and the name from the command. Thus the proper command should look like the following:
ALTER DATABASE LINK link1 CONNECT TO "user1" identified by "user1test";
And now all works fine...

After OS upgrade ...

The only important thing after the OS upgrade on a host with Oracle db is to relink binaries.
## before it is good to find all the files owned by root
## after the relink operation they would become again owned by oracle
[oracle@host1 ~]$ find $ORACLE_HOME -user root -exec ls -l {} \;
-rw-r-----   1 root     oinstall    1534 Dec 21  2005 /oracle/product/
-rwsr-x---   1 root     oinstall 1340408 Sep 25  2011 /oracle/product/
-rws--x---   1 root     oinstall   29216 Jun 21  2013 /oracle/product/
-rws--x---   1 root     oinstall   21624 Jun 21  2013 /oracle/product/
-rwsr-x---   1 root     oinstall 1277904 Jun 21  2013 /oracle/product/
-rws--x---   1 root     oinstall   93296 Jun 21  2013 /oracle/product/
-rwsr-x---   1 root     oinstall   26824 Jun 21  2013 /oracle/product/

[oracle@host1 ~]$ cd $ORACLE_HOME
[oracle@host1 ~]$ ./bin/relink all

## after the relink operation some of binaries, which have to be owned by root are 
## again owned by oracle; after the installation the permissions are set by
## now it is enough to run the commands below, as does more things

[oracle@host1 ~]$ chown root $ORACLE_HOME/rdbms/admin/externaljob.ora
[oracle@host1 ~]$ chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora
[oracle@host1 ~]$ chown root $ORACLE_HOME/bin/oradism
[oracle@host1 ~]$ chmod 4750 $ORACLE_HOME/bin/oradism
[oracle@host1 ~]$ chown root $ORACLE_HOME/bin/nmo
[oracle@host1 ~]$ chmod 4710 $ORACLE_HOME/bin/nmo 
[oracle@host1 ~]$ chown root $ORACLE_HOME/bin/nmb
[oracle@host1 ~]$ chmod 4710 $ORACLE_HOME/bin/nmb
[oracle@host1 ~]$ chown root $ORACLE_HOME/bin/extjob
[oracle@host1 ~]$ chmod 4750 $ORACLE_HOME/bin/extjob
[oracle@host1 ~]$ chown root $ORACLE_HOME/bin/nmhs
[oracle@host1 ~]$ chmod 4710 $ORACLE_HOME/bin/nmhs
[oracle@host1 ~]$ chown root $ORACLE_HOME/bin/jssu
[oracle@host1 ~]$ chmod 4750 $ORACLE_HOME/bin/jssu

Monday, 31 August 2015

Error on auto execute of job SYS.ORA$_AT_SA_SPC_SY (external link)

Today I've got a ticket to service with the content more or less like the following:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_1202"
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at "SYS.DBMS_SPACE", line 2480
ORA-06512: at "SYS.DBMS_SPACE", line 2553

The issue is not a big deal for me - a problem with space advisor is not a critical thing IMHO, but by the way I found short nice notice on the subject

Tuesday, 16 June 2015

Restore points

Below You may find a 'session' from enabling restore point technology.
-- first try old a hacker method - just start do it ;-)
SQL> create restore point rp1 guarantee flashback database;
create restore point rp1 guarantee flashback database
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP1'.
ORA-38785: Media recovery must be enabled for guaranteed restore point.
-- thus I see one needs to set db into ARCHIVELOG mode
-- despite that we need also settings for db_recovery_file_dest
-- though we do not need to enable flashback mode

-- creating pfile in order to edit settings
SQL> create pfile from spfile;

File created.

-- create directory for fast recovery area (FRA)
SQL> !mkdir [path]

-- add settings for FRA
SQL> !vim $ORACLE_HOME/dbs/initcdw.ora
SQL> create spfile from pfile;

File created.

-- now enabling ARCHIVELOG mode
SQL> startup nomount

ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size      2228200 bytes
Variable Size    503316504 bytes
Database Buffers  1627389952 bytes
Redo Buffers      4952064 bytes

-- create directory for archive logs
SQL> !mkdir /ora_csdb/oradata/CDW/arch

-- set local destination for archive logs to newly created directory
-- of course it was possible to add it to pfile when editing
-- in case of not setting a custom location the archive logs are stored
-- in $ORACLE_HOME/dbs
SQL> alter system set 
SQL> log_archive_dest_1= 'LOCATION=/ora_csdb/oradata/CDW/arch' 
SQL> ;

System altered.

-- switching to ARCHIVELOG mode
SQL> alter database mount;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

-- there is no switch of flashback on - so as You see this is quite minimal scenario
-- enabling flashback is not required but at a price - one cannot restore to
-- point in time between snapshots
-- as You may see there is special state for such situation provided 
-- in the FLASHBACK_ON column of V$DATABASE
SQL> select flashback_on from v$database;


-- recovery writer RVWR is started
-- as You may see, the technology makes use of flashback fascilites

-- creating a restore point
-- as You see now all works properly 
SQL> create  restore point rp1 guarantee flashback database;
Wed Jun 10 12:44:47 2015
Starting background process RVWR
Wed Jun 10 12:44:47 2015
RVWR started with pid=28, OS id=17240 
Allocated 8388608 bytes in shared pool for flashback generation buffer
Wed Jun 10 12:45:27 2015
Created guaranteed restore point RP1
Restore point created.

-- now preparations for a short test
SQL> conn / as sysdba
SQL> alter user rems identified by simple1;

User altered.

SQL> conn rems/simple1
SQL> create table t1 (a number)
  2  ;

Table created.

SQL> insert into t1 select object_id from dba_objects;

473175 rows created.

SQL> commit;

Commit complete.

-- now we return to the state marked by RP1
SQL> conn / as sysdba

-- we need to stop the database and mount it
SQL> flashback database to restore point rp1;
flashback database to restore point rp1
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size      2228200 bytes
Variable Size    503316504 bytes
Database Buffers  1627389952 bytes
Redo Buffers      4952064 bytes
Database mounted.
SQL> flashback database to restore point rp1;
Wed Jun 10 13:12:29 2015
Flashback Restore Start
Wed Jun 10 13:13:05 2015
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Wed Jun 10 13:15:29 2015
db_recovery_file_dest_size of 10240 MB is 0.30% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Jun 10 13:17:42 2015
Parallel Media Recovery started with 2 slaves
Wed Jun 10 13:17:43 2015
Recovery of Online Redo Log: Thread 1 Group 7 Seq 20393 Reading mem 0
  Mem# 0: /ora_csdb/oradata/CDW/onlinelog/o1_mf_7_97grvlnx_.log
  Mem# 1: /ora_csdb/oradata/CDW/onlinelog/o1_mf_7_97grvp66_.log
Incomplete Recovery applied until change 117062103544 time 06/10/2015 12:45:28
Flashback Media Recovery Complete
Wed Jun 10 13:20:53 2015
Completed: flashback database to restore point rp1
Flashback complete.

-- after flashback we have to open with RESETLOGS
SQL> alter database open
alter database open
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;    

SQL> alter system set log_archive_format='cdw_%d_%r_%T_%S.arc';

-- %r, %T/%t, %S/%s have to be contained within the archive log format

Wednesday, 10 June 2015

Today's action


During the night we've got some messages from one of the databases' hosts with warning with message about storage space threshold has been crossed. This was a resource on which we put archivelogs, so quite important.


One of my colleagues started with launching manually standard script for backup. Normally such script is called by cron in scheduled moments. Unfortunately and unexpectedly the database has produced a massive number of archivelogs, on which the system has not been prepared at all - thus we saw that apparently there is something wrong with application processing. But then it went to the point the backup script has not been working fast enough to free storage space for further logs and due to some other circumstances jammed itself and backup system administrator has to kill it.

The diagnosis was done with the following tools:
  • OEM - with this we were able to find the malicious application and call for its restart
  • an ad hoc script showed that the problematic transactions frequently finished with rollback, which was the direct cause of massive archivelogs growth. The script allows for realizing the status of a transaction and its progress in the form of used_ublk (i.e. undo blocks used in the transaction). Subsequent calls show the change in number of used blocks - in case of rollbacks the value diminishes of course.
    set linesize 300 pagesize 1000
    col rollback for a25
    SELECT s.username,
           round(r.rssize/1024/1024) rs_sz_mb,
           when bitand(t.flag,power(2,7)) > 0 
           then 'rollback in progress'
           else 'other'
           end as "F Status"
    FROM   v$transaction t,
           v$session s,
           v$rollstat r,
           dba_rollback_segs rs
    WHERE  s.saddr = t.ses_addr
    AND    t.xidusn = r.usn
    AND   rs.segment_id = t.xidusn
    ORDER BY t.used_ublk DESC
Finally the application has been restarted, and the application behaviour returned to its normal pace.


Yet during the diagnosis phase I have started again the backup script to free the space, but the script finished with errors due to earlier move of some archivelogs to another location. I did not know at that time what happened with those logs and unable to contact my colleague, so crosschecked the archivelogs and run backup again. This time all has been working properly. In the meantime sysadmins added some 20G to the archivelog storage resource because still the archivelog production has outpaced the backup process. After the application restart there has been some 20 minutes when some transactions still has been rolled back, but after this time the used storage space has dropped to usual state.
The last part of clean-up was to synchronize rman catalog, register the moved archivelogs and backup them as well.
resync catalog ;
catalog start with '[path]';
backup archivelog sequence between 40556 and 40999 delete all input;