SCOTT/TIGER for SAP Hana

SAP Hana is becoming increasingly popular these days. It comes with its own demo schema, STUDENT. However, being an old Oracle hack, I ported the SCOTT schema from Oracle to SAP Hana. Every Oracle DBA has a bunch of queries against EMP and DEPT tables which he or she may test on the SAP Hana database. In case you’re interested, the export file is available here:

http://mgogala.byethost5.com/scott_hana.tgz

 


Finding the trace files

In Oracle versions before 12c, it was necessary to resort to complex magic to find out the location of the process trace file. One of the most famous recipes is probably the one invented by René Nyffenegger and available here:

http://www.adp-gmbh.ch/ora/misc/find_trace_file.html

Another trick included utilizing ORADEBUG command “tracefile_name” which would return trace file name.

However, in Oracle 12c the things have changed. Trace file is now available as a column in the V$PROCESS (more...)

ACFS 12.2 Quirks with defragmentation

I have a 12.2 RAC cluster in my lab, on two virtual machines. Checking out what acfsutil can do, I figured out that it can supposedly do defragmentation:

[grid@rac1 ~]$ acfsutil defrag -help

Usage: acfsutil [-h] command ... # (Version 12.2.0.1.0)

-h - help

Command Subcmd Arguments
--------------- --------- ------------------------------------------------------
 defrag dir [-r] <path>
 defrag dir Defrag all files in a specific directory 
 defrag dir [-r] recursively traverse directories
 defrag dir  (more...)

RAC 12.2, part 2

Well, today was an interesting day. My ASM, configured with so much effort, stopped working:

root@rac1 grid]# . ~grid/.bashrc
[root@rac1 grid]# crsctl start ohasd
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[root@rac1 grid]# crsctl start cluster
CRS-2672: Attempting to start 'ora.crf' on 'rac1'
CRS-2672: Attempting to start 'ora.storage' on 'rac1'
CRS-2676: Start of 'ora.crf' on 'rac1' succeeded
ORA-27140: attach to post/wait facility failed
 (more...)

RAC 12.2

I am in the process of installing RAC 12.2. The installation procedure has undergone a significant change, for the better. It’s now much easier to install it. However, memory and disk requirements have skyrocketed. I needed to allocate 10GB of memory for each of my nodes, 12 GB for OCR and 64GB for the management database. My computer will be barely large enough to run both Commserve and those two nodes together:

Screenshot_2017-08-26_17-34-25

I am (more...)

Rename Pluggable Database

I stumbled upon a convenient way to rename a pluggable database:

SQL> alter system enable restricted session;

System altered.

SQL> alter database rename global_name to test12.home.com;

Database altered.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select name from v$pdbs;

NAME
——————————
PDB$SEED
TEST12

In other words, to rename a pluggable database, one only needs to rename its global name. Cute!


12c: OPTIMIZER_DYNAMIC_SAMPLING=11

Oracle 12c has a new feature related to optimizer dynamic sampling. The new level 11 is supposed to put the dynamic sampling on auto pilot and make the statistics available to other users. However, as soon as this was set, I started noticing processes spending significant time waiting for row cache latch. This was strange, especially having in mind that ASMM was configured. I checked the V$ROWCACHE  by using the following, fairly standard, query:

select (more...)

Oracle 12c, big table caching

In Oracle 11g,  there has been annoying change which has frequently had a seriously detrimental effect to the application performance. All tables, larger than 2% of SGA, were considered big tables and were read using direct reads into PGA, instead of SGA. That has had two undesired effects:

  • Increased paging, as PGA had to be expanded, in order to receive all those rows
  • No sharing, since the PGA is not visible from other processes (or (more...)

Smarter DG Broker in Oracle 12c

In Oracle 11G, DG Broker needed to be told whether the standby database was physical or logical. The command to add database in 11G was something like this:

ADD DATABASE <DB Unique Name> AS CONNECT IDENTIFIER is <TNS Descriptor>

MAINTAINED AS [PHYSICAL|LOGICAL];

Oracle 12c is definitely smarter and can figure it out on its own. The “ADD DATABASE” syntax is simpler:

DGMGRL for 64-bit Windows: Version 12.1.0.2.0 – 64bit Production

Copyright (more...)

Synthetic Full Backup for Oracle

Synthetic full backup is a method for combining incremental backup into a full backup. It is not a new thing, many backup utilities can do that with file system backups. It is also well described:

http://www.techworld.com/storage/what-are-synthetic-backups-1149/

http://www.webopedia.com/TERM/S/synthetic_backup.html

However, there is only one backup suite that can do that with Oracle RDBMS: Commvault 11.

How is it done?  Well, Commvault is a very well documented tool, the documentation is here:

(more...)