Db restore of the standby (more or less manual)

1. remove old db files - controlfiles, log files (online and standby), data files, temp files - it is recommended to do it in the first place before any restore will start as it may be quite easy to choose wrong files for removal if the restored files are mixed with the old ones
2. check db_create_% parameters

   SQL> alter system set db_create_online_log_dest_1='/ora_log/redolog1';
   
   System został zmieniony.
   
   SQL> alter system set db_create_online_log_dest_2='/ora_log/redolog2';
   
   System został zmieniony.
   
   SQL> show parameter db_create
   
   NAME                                 TYPE         VALUE
   ------------------------------------ ------------ ------------------------------
   db_create_file_dest                  string       /ora_db/
   db_create_online_log_dest_1          string       /ora_log/redolog1
   db_create_online_log_dest_2          string       /ora_log/redolog2
   db_create_online_log_dest_3          string
   db_create_online_log_dest_4          string
   db_create_online_log_dest_5          string
   
   

3. (optionally) here one may create a standby controlfile on PRIMARY and copy to planned standby, then mount

   ALTER DATABASE CREATE STANDBY CONTROLFILE 
     AS '/disk1/oracle/oradata/payroll/standby/payroll2.ctl';
   

4. or directly restore from backup (which is much more comfortable)

   connect target sys/pass1;
   connect catalog rman_catalog/pass2@rman_catalog;
   spool log to rman_db_restore_20160505.log
   run
   {
   allocate channel t1 type 'SBT_TAPE' FORMAT 'db_%t_%s_%p' parms 
     'ENV=(TDPO_OPTFILE=/TSM/CFG/tdpo.opt)';
   allocate channel t2 type 'SBT_TAPE' FORMAT 'db_%t_%s_%p' parms 
     'ENV=(TDPO_OPTFILE=/TSM/CFG/tdpo.opt)';
   # in case one restored controlfiles already - next 2 lines has to be removed
   restore standby controlfile; 
   alter database mount;
   SET NEWNAME FOR DATABASE TO NEW;
   restore database;
   SWITCH DATAFILE ALL;
   SWITCH TEMPFILE ALL;
   recover database DELETE ARCHIVELOG MAXSIZE 90G;
   
   release channel t1;
   release channel t2;
   }
   exit;
   

Now it is possible we have to add more archivelogs, but that would mean repeating the last line from the script above, until db is fine. Then one may want to fix some issues like paths to online and standby redo log files, etc.
From this time on the standby is ready to use - the main point at this moment is to restore the managed standby database recovery process. This task would require first checking the parameters like log_archive_config, log_archive_dest_* and log_archive_dest_state_*, fal_server and fal_client if they are properly configured. Of course in the first place the communication between primary and standby db nodes has to be checked by sqlplus for example.
Now we call

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

The output in alert logs on both nodes should confirm that the connections were built and the transfer of archivelogs has started.

---

Good to know

There exists bug 7553431 - RMAN restore controlfile for standby does not create correct controlfile type (Doc ID 7553431.8), which influences the role of restored database, fixed in 12c version.

ORA-29283: invalid file operation

Today I 've got the following problem: the procedure, which was planned to write a file through UTL_FILE to a mounted resource, is unable to do so despite the proper privileges on the directory. At the same time the local OS user (db owner) was able to do it without problem. The whole thing was done on an AIX OS, and the mounted resource was presented through NFS - in general many points of problems.

The investigation

Running a test procedure produced something like:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "TCCS.TEST_UTL_FILE", line 6
ORA-06512: at line 1
29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

The truss on the db process while calling the test procedure

kread(16, "\0\v\0\0\f\0\0\001\002 i".., 8208) (sleeping...)
kread(16, "\0 _\0\006\0\0\0\0\011 i".., 8208)   = 95
statx("/home/tccs/files/out/utlout.txt", 0x0FFFFFFFFFFE9140, 176, 010) Err#13 EACCES
statfs("/home/tccs/files/out/utlout.txt", 0x0FFFFFFFFFFE8D20) Err#13 EACCES
statx("/home/tccs/files/out/utlout.txt", 0x0FFFFFFFFFFE8FF8, 176, 011) Err#13 EACCES
kopen("/home/tccs/files/out/utlout.txt", O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE, S_IRUSR|S_IWUSR|S_IRGRP|S_IWGRP|S_IROTH|S_IWOTH) Err#13 EACCES
kwrite(16, "\0\v\0\0\f\0\0\001\001", 11)        = 11
kwrite(16, "\0\v\0\0\f\0\0\001\002", 11)        = 11
kread(16, "\0\v\0\0\f\0\0\001\002 i".., 8208)   = 11
lseek(5, 701440, 0)                             = 701440
kread(5, "\00F r V\0\0\0 b r W\0\0".., 512)     = 512
lseek(5, 162304, 0)                             = 162304
kread(5, "\0\n19 n\0\0\0 D19 o\0\0".., 512)     = 512
lseek(5, 701440, 0)                             = 701440
kread(5, "\00F r V\0\0\0 b r W\0\0".., 512)     = 512
lseek(5, 162304, 0)                             = 162304
kread(5, "\0\n19 n\0\0\0 D19 o\0\0".., 512)     = 512
kwrite(16, "\0 �\0\006\0\0\0\0\00401".., 233)   = 233
kread(16, "\0\v\0\0\f\0\0\001\002 i".., 8208) (sleeping...)

The 1st investigation point was brought by the article at http://jakub.wartak.pl/blog/?p=272. But after a while it become clear that, though the conditions were met and we changed the ownership of the mount point, the problem has persisted.
The 2nd guess was by suggestion in the article on MOS:Session Accessing File On Disk Fail with OS File Permission Errors (Doc ID 392647.1). And simple check that the local user connected on BEQUEATH is able to run such procedure without problem proofed that this is the right way - after the listener restart the remote users also acquired the ability to run procedure without problems. The root cause was that the db owner was added to the group being the owner of the target directory long after the listener was started - hence the remote sessions did not acquire the proper permissions on the directory, while the local sessions with "fresh" permissions worked ok.

Long DROP USER command

Lately I called a DROP USER command - as this was a slow environment (and I once called this command on the same user previously) I expected it to take long time. The working hours passed by, I was observing it - it waited on the "db file sequential read", so I did not bother to check the db health at all - I went home.
The next day I saw the command failed with error, that there are no temporary files' space.

---

Seems I forgot to finish this note as Ivan Korac has pointed me. I do not remember correctly now, as it was some time ago. The finish is I've found somewhere a note about that (possibly on Metalink) with supposition to drop that user objects first - and I did a test for that. It showed that time for dropping a user without preparations is almost the same as dropping objects first (this one schema has really many of them) and drop empty schema afterwards.
One difference though is that dropping one object is faster after all. The lack of temporary files space was an additional issue which went along with my command by accident IIRC.

Using wallet

1. wallet creation

   # I usally place it under TNS_ADMIN or one level below in a separate 
   # directory e.g. TNS_ADMIN=/oracle/admin/network/admin and then wallet
   # is in /oracle/admin/network/wallet
   # the call requires to specify a password twice, which is a global 
   # password to the wallet
   $ mkstore -wrl /oracle/admin/network/wallet/my.wallet -create
   Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
   Copyright (c) 2004, 2013, Oracle and/or its affiliates. 
   All rights reserved.
   
   Enter password:            
      
   Enter password again:            
      
   

2. adding entries to the wallet
   • creating an entry in the tnsnames.ora - as one will connect through that entry without password

     alias_in_tns = (DESCRIPTION = 
       (ADDRESS = (PROTOCOL = TCP) (HOST = host1) (PORT = 1521))
       (CONNECT_DATA = (SERVICE_NAME = service1)))
     

   • creating credentials for user1 in the wallet (db_user1 is TNS entry, user1 the schema name, pass is a password)

     [oracle@ap-murex1t admin]$ mkstore -wrl [path to wallet directory] \
      -createCredential alias_in_tnsnames user1 pass
     Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
     Copyright (c) 2004, 2013, Oracle and/or its affiliates. 
     All rights reserved.
     
     Enter wallet password:            
        
     Create credential oracle.security.client.connect_string1
     

The default privileges on the wallet directory are set only for the wallet owner, so other users would get the ORA-12578 and be asked for credentials. Of course this has a perfect sense to limit access to the wallet. However sometimes You need it on a test environment and may abuse it simply by setting permissions also for others.

---

The whole thing works of course only if one provide correct information in the sqlnet.ora file.

WALLET_LOCATION = (SOURCE = 
  (METHOD = FILE)(METHOD_DATA = (DIRECTORY = [path to wallet directory])))
SQLNET.WALLET_OVERRIDE = TRUE 

minact-scn: useg scan erroring out with error e:12751

Lately I've met in alert.log entries the following:

minact-scn: useg scan erroring out with error e:12751

According to the Metalink it is bound with high load on the system and that an undo segment scan has failed with an ORA-12751 error.
And ORA-12751 is described as "cpu time or run time policy violation"

In my case though the load on the system is not very high and the system is actually used currently for one task only, which is a massive data pump import. The import leads to temporary lack of space for archivelogs (ORA-16038, ORA-19504, ORA-16014). Of course there is a pressure on the instance trying to save archivelogs and I suppose that this state causes such notes in alert.log.

Also emergency flushes occur then:
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 8388608 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

select total_size,awr_flush_emergency_count from v$ash_info; 

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

Today I run into the following error:

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

What was the most annoying, it was the fact, I can not start the instance due to this error occurence. In general within a screen session I opened sqlplus session and within it the vim for editing the newly generated pfile. Then I shut down the instance and further I edited some memory settings. So far, so good - I started the instance and got this error. And I did not even specified this parameter in the configuration.
Tried to find something on the internet and on the Metalink:

1. http://askdba.org/weblog/2008/04/tweaking-_shared_pool_reserved_min_alloc-and-ora-4031/
2. Ora-00828 With Automatic Shared Memory Management (Doc ID 1251784.1)

I have checked all the possibilities - look for mistakes and misspellings in settings specification and all for nothing. Finally the colleague of mine created a pfile, then created a spfile from that pfile (no changes at all), then started instance with force and voila, the instance worked like a charm.
Not sure what was wrong - the only clue is the possibility I introduced into pfile some hidden character, so the true reason was similar to the one described under the 1st link mentioned above.
Such theory is supported by the following facts:

• the error message as the upper bound displays 0 (which is nonsense) - however I suppose the lower bound is simply hard coded as 4000 (or 4400) bytes and the upper bound derives its value from the calculation based on the value of shared pool size.
• assuming something was wrong with the shared pool size specification I suppose it would be set to 0 and hence the upper bound also would be 0 (as any % of 0 is still 0 ;-))

The conclusion: if the upper bound of the ORA-00093 message is set to 0, check Your pfile and spfile byte by byte.
The update Hit this error again, and from this occurrence I draw another conclusion. This is rather not about character in the pfile or spfile. This time the version used is 12c. I set the SGA settings too low and here is the dialog with the database:

-- SGA set to 1G
SQL> startup
ORA-00821: Specified value of sga_target 1024M is too small, needs to be at least 1456M
SQL> create spfile from pfile='db4b.pfile.20170321' ;

File created.

-- SGA increased to 1600M, 1800M, finally 2G (which was the initial setting)
-- still ORA-00093
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
[..]
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

-- and here the only difference - force
SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size      3712904 bytes
Variable Size   1577060472 bytes
Database Buffers   553648128 bytes
Redo Buffers     13062144 bytes
Database mounted.
Database opened.

As You see the startup force show up as the key step to solve the problem - apparently when I opened sqlplus some settings were established and not cleared on startup failure.

Large Pages Implementation in Windows (external link)

Short note on how to enable large pages support for Oracle on Windows - link