Sunday, August 13, 2017

Partial restore and recover of Oracle database

There was the need to restore part (system, sysaux, undo tablespaces and small data tablespace) of large Oracle RAC database (5,5TB) from tape backup, made 2 weeks before. Backup was made on standby site (that is backup consisted  fully of files of standby database), primary db during the restore time was not awailable.

RMAN repository was not used. The new auxiliary instance with small sga and other related parameters was created for that purpose. One of the possible steps of restoration presented below.

1. We have to restore datafiles from backup. It's possible to use existing standby controlfile, if it "knows" about interesting backup's details. If not, older standby controlfile has to be restored from backup, for example:

RMAN> restore controlfile to '/ora/oradata/aaa_temp/control_aaa_temp_01.ctl' from 'c-2517399282-20170801-00';

or

RMAN> restore controlfile to '/ora/oradata/aaa_temp/control_aaa_temp_01.ctl' from autobackup;

2. Then mount auxiliary instance with recovered controlfile and restore only interested datafiles in the similar way like this rman script does (because we have cluster database, we have to restore ALL undo tablespaces) :

run {
  allocate channel ch00 device type sbt_tape ;
  allocate channel ch01 device type disk ;


  # set recover database condition
  set until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;

  # set newname for interested datafiles
  set newname for  datafile 1 to '/rab/oradata/aaa_temp/system.282.870241429'  ;
  set newname for  datafile 8 to '/rab/oradata/aaa_temp/sysaux.268.870241429'  ;
  set newname for  datafile 9 to '/rab/oradata/aaa_temp/undotbs1.283.870241429'  ;
  set newname for  datafile 10 to '/rab/aaa_temp/undotbs2.270.870241429'  ;
  set newname for  datafile 11 to '/rab/aaa_temp/undotbs3.285.870241429'  ;
  set newname for  datafile 18 to '/rab/aaa_temp/undotbs4.284.870241429'  ;
  set newname for  datafile 19 to '/rab/aaa_temp/userdata.ora'  ;

  restore datafile 1,8,9,10,11,18 ;

  set archivelog destination to '/temporary_stage' ;
  restore archivelog until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;

  # next statement will not run because controlfile is a standby control file
  # recover database skip forever tablespace ;

  release channel ch00 ;

  release channel ch01 ;
}

3. At this point we need backup controlfile from primary db, or we have to create it from scratch. Because primary database was unavailable, we had to, using sql statement 'alter database backup controlfile to trace as '...' ;' create sql pattern for creating new controlfile for recovered datafiles with resetlogs section. It's also possible to modify quantity and size of redo log files and other parameters influencing to final size of new controlfile. Next is example of new controlfile creation script :

shutdown immediate
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "AAA" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 8
    MAXLOGMEMBERS 1
    MAXDATAFILES 10
    MAXINSTANCES 4
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 (
    '/rab/oradata/aaa_temp/group_3.2964.914404769'
  ) SIZE 1024M,
  GROUP 2 (
    '/rab/oradata/aaa_temp/group_6.2994.892222399'
  ) SIZE 1024M
DATAFILE
  '/rab/oradata/aaa_temp/system.282.870241429',
  '/rab/oradata/aaa_temp/sysaux.268.870241429',
  '/rab/oradata/aaa_temp/undotbs1.283.870241429',
  '/rab/aaa_temp/undotbs2.270.870241429',
  '/rab/aaa_temp/undotbs3.285.870241429',
  '/rab/aaa_temp/undotbs4.284.870241429',
  '/rab/aaa_temp/userdata.ora'
CHARACTER SET RU8PC866
;ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 3 (
    '/rab/oradata/aaa_temp/group_14.757.914404773'
  ) SIZE 1024M REUSE,
  GROUP 4 (
    '/rab/oradata/aaa_temp/group_15.25922.914404775'
  ) SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE THREAD 3
  GROUP 5 (
    '/rab/oradata/aaa_temp/group_28.25921.914404779'
  ) SIZE 1024M REUSE,
  GROUP 6 (
    '/rab/oradata/aaa_temp/group_30.4341.914404783'
  ) SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE THREAD 4
  GROUP 7 (
    '/rab/oradata/aaa_temp/group_35.4226.914404787'
  ) SIZE 1024M REUSE,
  GROUP 8 (
    '/rab/oradata/aaa_temp/group_36.642.914404791'
  ) SIZE 1024M REUSE ;

4. Catalog archivelog files and recover part of database with the similar rman script :

run {
  allocate channel ch00 device type sbt_tape ;
  allocate channel ch01 device type disk ;

  set until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;
  catalog start with 'temporary_stage' noprompt ;
  recover database ;

  release channel ch00 ;
  release channel ch01 ;
}


5. Now you can open database with resetlogs option and have a lot of fun :-).

P.S. There will be datafiles with 'MISSING' word in their names. If you don't need dictionary data linked with objects in these missing files (for example, AWR or ASH data), remove them with help of the following dynamic sql :

> spool drop_missing.sql
> select distinct 'drop tablespace '|| b.name||' including contents and datafiles ;' from v$datafile a,v$tablespace b where a.ts#=b.ts# and a.name like '%MISSING%'  ;
> spool off
> start missing.sql

P.P.S. Accessibility of primary database controlfile gives more opportunities to achieve the same results: a ) rman command 'recover database skip forever tablespace' ; b) sql command 'alter database datafile n offline for drop' etc.
P.P.P.S. Improtant thing mentioned before - if you have cluster database,  for partial database restoration you have to restore all undo tablespaces, otherwise ora-00600 and other 'unexplained', 'wierd' errors will occur.

Friday, June 2, 2017

sqlplus easy connect could not connect on linux ORA-12504 ORA-01017

When you try to connect to database using sqlplus with collaboration of easy connect feature, you may get a bit confused by not having successfully logged in when you don't enter user's password inline. In other case (with inline password), the following command will be ok :

sqlplus username/password@IP_OR_HOSTNAME:PORT/SERVICE_NAME

and following will not:

sqlplus username@IP_OR_HOSTNAME:PORT/SERVICE_NAME

In such situation you'll get sqlplus breaf help text. The workaround is using single or double quotes with escape symbols :

sqlplus username@\"IP_OR_HOSTNAME:PORT/SERVICE_NAME\"

or

sqlplus username@\'IP_OR_HOSTNAME:PORT/SERVICE_NAME\'

You may catch errors ORA-12504 ORA-01017 if you place quotes in wrong place. For example, you'll get ORA-01017 in the following case :

sqlplus \'username@IP_OR_HOSTNAME:PORT/SERVICE_NAME\'

or  ORA-12504 when you'll not use escape quoting, like in :

sqlplus username@"IP_OR_HOSTNAME:PORT/SERVICE_NAME"

Good luck !

Thursday, January 12, 2017

mount.cifs error 13 another case

If you try to mount windows shared resource to your linux folder and get "mount error(13): Permission denied" error, even if username and password are correct and checked 100 times, another credential setting like "domain=" could help you.

1. If your windows in domain and windows user is a local windows user, then use "domain=windows_hostname" credential setting.

2. If your windows in domain and windows user is a domain windows user, then use "domain=windows_domain_name" credential setting.

Good luck !

Thursday, September 29, 2016

Oracle offline documentation links always open in new window

First of all, try to renew downloaded documentation, recent versions (at least September 2016) doesn't have such issue. If you can't, the following is for you.

Later offline versions of Oracle 11g and 12c offline documentations use java scripts for generating links. During offline browsing every link opens new window, which is uncomfortable for navigating/reading etc. Playing with styles in the directory

(unzipped_documentation_directory)/dcommon

 didn't bring any result as well as modifying files

(unzipped_documentation_directory)/dcommon/js/common.js
(unzipped_documentation_directory)/dcommon/js/headfoot.js
(unzipped_documentation_directory)/dcommon/js/headfoot_prev.js

 for changing strings containing 'img.style.display = "inline"' to 'img.style.display = "none"' etc.

The following workaround doesn't claim to be a panacea.

For myself I've decided to change firefox setting "browser.link.open_newwindow" to 1 with help of about:config. This setting prevents to open new windows and tabs for all web pages. But it's always possible to use right mouse button or shift/ctrl+left mouse button to open links in new window/tab respectivly, allowing you to make decision whether display page in the current window or not.

The similar possibility exists in other popular browsers.

Tuesday, February 3, 2015

undefined reference to `nnfyboot'


When installing or relinking Oracle executables, it may be possible to see such error. The solution is :
1. Install addtitional packages (if not installed, 32bit packages such as glibc-32bit,glibc-devel-32bit,gcc-32bit and other required).
2. look through install.log and search messages kind of 'No such file or directory'. Try to fix errors (see item 1) and run all failed commands by hand in console, using oracle account.
For example, in following text

INFO: (if [ "compile" = "compile" ] ; then \
          echo "Building 32bit version of nnfgt.o"; \
          /opt/oracle/product/10gR2/db/bin/gennfgt > nnfgt.c ;\
          gcc -m32  -c nnfgt.c ;\
          rm -f /opt/oracle/product/10gR2/db/lib32/nnfgt.o ;\
          mv nnfgt.o /opt/oracle/product/10gR2/db/lib32/ ;\
          /usr/bin/ar rv /opt/oracle/product/10gR2/db/lib32/libn10.a /opt/oracle/product/10gR2/db/lib32/nnfgt.o ;\
          echo "Building 64bit version of nnfgt.o"; \
          /opt/oracle/product/10gR2/db/bin/gennfgt > nnfgt.c ;\
          gcc  -c nnfg
INFO: t.c ;\
          rm -f /opt/oracle/product/10gR2/db/lib/nnfgt.o ;\
          mv nnfgt.o /opt/oracle/product/10gR2/db/lib/ ;\
          /usr/bin/ar rv /opt/oracle/product/10gR2/db/lib/libn10.a /opt/oracle/product/10gR2/db/lib/nnfgt.o ; fi)

INFO: Building 32bit version of nnfgt.o

INFO: In file included from /usr/include/features.h:371,
                 from /usr/include/sys/types.h:27,
                 from nnfgt.c:7:
/usr/include/gnu/stubs.h:7:27: error: gnu/stubs-32.h: No such file or directory

INFO: mv: cannot stat `nnfgt.o': No such file or directory

INFO: /usr/bin/ar: /opt/oracle/product/10gR2/db/lib32/nnfgt.o: No such file or directory

INFO: Building 64bit version of nnfgt.o

INFO: r - /opt/oracle/product/10gR2/db/lib/nnfgt.o

The file gnu/stubs-32.h, needed to build 32-bit version of nnfgt.o, needed for construction of libclntsh.so, is absent. Install required package glibc-devel-32bit and run by hand above commands.

3. relink executables running relink command.

Friday, January 9, 2015

The active version of Oracle Clusterware is not 10g Release 2

Silent install of 10gR2 database on 12c or 11g clusterware environment fails with following error :

"The active version of Oracle Clusterware is not 10g Release 2"

Flags such as "-ignore..." don't help.

So the solution is to edit file /stage/prereq/db/db_prereq.xml and remove whole PREREQUISITE NAME="Detect10.2CRS" section, or remove its content,related to rules.

After that you'll be able to complete the installation. 

And don't forget to pin cluster nodes with:

/bin/crsctl pin css -n

Thursday, January 8, 2015

Hanged or stalled Grid Infrastructure (11g or 12c) installation performing remote operations on Linux

The installation process goes fine, but suddenly is's stalled in the middle (50-60%) for unknown reason. That's becouse java installation process unable to transmit copied data (files and dirs) to oracle processes named ractrans, running on remote nodes in listening mode and basically performing all work for filling up remote oracle homes.

In my case that was becouse of sysctl.conf settings.

The content I had :

net.core.rmem_default = 25165824
net.core.wmem_default = 25165824
net.core.rmem_max = 25165824
net.core.wmem_max = 25165824
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
net.ipv4.tcp_rmem = 134217728 134217728 134217728
net.ipv4.tcp_wmem = 134217728 134217728 134217728
net.core.netdev_max_backlog = 300000

The content I had to modify :

net.core.rmem_default = 4194304
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 262144
net.ipv4.tcp_rmem = 4096        87380   174760
net.ipv4.tcp_wmem = 4096        16384   131072
net.core.netdev_max_backlog = 1000

Honestly, I suspect, only net.ipv4.tcp_rmem and net.ipv4.tcp_wmem parameters play main role in this issue. Just in case I decided to set default values for other in the list above.

I.e., decreasing amount of memory per tcp socket and buffer space, slowing ethernet speed, took results.

P.S.

1. Just as surveillance. Oracle Universal Installer creates six (6) process per remote node to sync contents of grid_home. If you perform installation only for two nodes, you could not face with this issue.

2. It generally depends on speed of your public network stack. If you have fast enough switches and adapters, probably you will not bump into it.

3. It also depends on speed your storage (including mounting options). Any measure decreasing speed of copying files, can help.