Thinking Out Loud

January 20, 2017

Goldengate Network Troubleshooting

Filed under: GoldenGate — mdinh @ 2:50 pm

We encounter the following error:

GGS ERROR 150  Oracle GoldenGate Capture for Oracle, pump.prm:  TCP/IP error 9 (Bad file descriptor).

Note: server collectors at target will not be started and ports not opened until pump at source is started.
Using nc to test port.

$ nc -v -z -w 3 target.local 7960; echo $?
nc: connect to target.local port 7960 (tcp) failed: Connection refused
1

$ nc -v -z -w 3 target.local 7970; echo $?
Connection to target.local 7970 port [tcp/*] succeeded!
0

Not preferred and does not mean it’s wrong – Entry starting at 16.

DYNAMICPORTLIST 7960-7980 (20)

In hindsight, I should have started a few more pump extracts
to determine if Entry values cycle back to 0 and
if port assignment will start at 7960 or will fail.

This is what happens when dozen of pump extracts are stopped and started in groups while manager is RUNNING.

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 20
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
  16   7976     0       6260   2017/01/19 12:06:12  Server
  17   7977     0       6261   2017/01/19 12:06:12  Server
  18   7978     0       6262   2017/01/19 12:06:12  Server
  19   7979     0       6263   2017/01/19 12:06:12  Server

Houston, we have a problem.
Look at Error column.
Anything other than 0 is not good.
Look at the date assigned.
These look to be orphaned processes and manager still thinks port is assigned.

GGSCI> SEND MANAGER GETPORTINFO

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 18
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7841    98      31663   2016/12/30 08:03:18  Server
   1   7842    98      31664   2016/12/30 08:03:18  Server
   2   7843    98                
   3   7844    98                
   4   7845    98                
   5   7846    98       1243   2016/12/30 08:14:01  Server
   6   7847    98       4543   2016/12/30 08:34:28  Server
   7   7848    98       4815   2016/12/30 08:35:55  Server
   8   7849    98       5094   2016/12/30 08:37:07  Server
   9   7850    98       5151   2016/12/30 08:37:20  Server
  10   7851    98       5152   2016/12/30 08:37:25  Server
  11   7852    98      26856   2017/01/17 21:57:38  Server
  12   7853    98      32133   2017/01/17 22:30:35  Server
  13   7854    98      16390   2017/01/06 03:56:56  Server
  14   7855    98      32220   2017/01/17 22:30:41  Server
 
  15   7856     0       4774   2017/01/17 22:57:40  Server
  16   7857     0       4777   2017/01/17 22:57:52  Server
  17   7858     0       4779   2017/01/17 22:57:59  Server
  
  18   7859    98      26854   2017/01/17 21:57:38  Server
  19   7860    98      26855   2017/01/17 21:57:38  Server

This is what I like.
Notice the timestamp for Assigned are all the same.
This is because pump from source was started using wildcard, i.e. start p*
Oracle support does not recommend this and YMMV.

GGSCI> !
SEND MANAGER GETPORTINFO

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 14
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7960     0       7744   2017/01/19 12:15:13  Server
   1   7961     0       7745   2017/01/19 12:15:13  Server
   2   7962     0       7746   2017/01/19 12:15:13  Server
   3   7963     0       7747   2017/01/19 12:15:13  Server
   4   7964     0       7748   2017/01/19 12:15:13  Server
   5   7965     0       7749   2017/01/19 12:15:13  Server
   6   7966     0       7750   2017/01/19 12:15:13  Server
   7   7967     0       7751   2017/01/19 12:15:13  Server
   8   7968     0       7752   2017/01/19 12:15:13  Server
   9   7969     0       7753   2017/01/19 12:15:13  Server
  10   7970     0       7754   2017/01/19 12:15:13  Server
  11   7971     0       7755   2017/01/19 12:15:13  Server
  12   7972     0       7756   2017/01/19 12:15:13  Server
  13   7973     0       7757   2017/01/19 12:15:13  Server

GGSCI> sh ps -ef|grep ./server

512       7744  7742  0 12:15 ?        00:00:00 ./server -p 7960 -k -l /ggs1040/ggserr.log
512       7745  7742  0 12:15 ?        00:00:00 ./server -p 7961 -k -l /ggs1040/ggserr.log
512       7746  7742  0 12:15 ?        00:00:00 ./server -p 7962 -k -l /ggs1040/ggserr.log
512       7747  7742  0 12:15 ?        00:00:00 ./server -p 7963 -k -l /ggs1040/ggserr.log
512       7748  7742  0 12:15 ?        00:00:00 ./server -p 7964 -k -l /ggs1040/ggserr.log
512       7749  7742  0 12:15 ?        00:00:00 ./server -p 7965 -k -l /ggs1040/ggserr.log
512       7750  7742  0 12:15 ?        00:00:00 ./server -p 7966 -k -l /ggs1040/ggserr.log
512       7751  7742  0 12:15 ?        00:00:00 ./server -p 7967 -k -l /ggs1040/ggserr.log
512       7752  7742  0 12:15 ?        00:00:00 ./server -p 7968 -k -l /ggs1040/ggserr.log
512       7753  7742  0 12:15 ?        00:00:00 ./server -p 7969 -k -l /ggs1040/ggserr.log
512       7754  7742  0 12:15 ?        00:00:00 ./server -p 7970 -k -l /ggs1040/ggserr.log
512       7755  7742  0 12:15 ?        00:00:00 ./server -p 7971 -k -l /ggs1040/ggserr.log
512       7756  7742  0 12:15 ?        00:00:00 ./server -p 7972 -k -l /ggs1040/ggserr.log
512       7757  7742  0 12:15 ?        00:00:00 ./server -p 7973 -k -l /ggs1040/ggserr.log
512       7759  7741  0 12:16 pts/1    00:00:00 sh -c ps -ef|grep ./server
512       7761  7759  0 12:16 pts/1    00:00:00 grep ./server

GGSCI>

Good followup reading.
OGG GGS Error 150: No Dynamic Ports Available Orphan Ports Server Collector (Doc ID 965356.1)

A SERVER process is an "orphan" if netstat or lsof shows only a "listening" port, with no "ESTABLISHED" connections.

January 7, 2017

Not Another Post Configuring HugePages for Oracle on Linux (x86-64)

Filed under: 11g,linux,oracle — mdinh @ 6:46 am

USE_LARGE_PAGES (TRUE/FALSE/ONLY)

Test case is only for one database instance on server.

DB is using memory_target.

SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 768M
sga_target                           big integer 0

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 1G
memory_target                        big integer 1G
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 0
sga_target                           big integer 0

SQL> show sga

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
SQL> exit

Gather memory configuration.

$ grep Huge /proc/meminfo
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

++++++++++

$ grep PageTables /proc/meminfo
PageTables:        24428 kB

++++++++++

$ free
             total       used       free     shared    buffers     cached
Mem:       1534604    1484476      50128       1512      20352     335916
-/+ buffers/cache:    1128208     406396
Swap:      4194300          0    4194300

Calculate memlock.

SQL> select round(1534604*.875) from dual;

ROUND(1534604*.875)
-------------------
            1342779

Edit /etc/security/limits.conf to add memlock as shown below.
Logout, login, check ulimit -l

$ grep memlock /etc/security/limits.conf
#        - memlock - max locked-in-memory address space (KB)
oracle   soft   memlock    1342779
oracle   hard   memlock    1342779

++++++++++

$ ulimit -l
134217728

Run hugepages_settings.sh ERROR due to memory target being used.

$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

***********
** ERROR **
***********
Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured

Remove memory target configuration from DB.
Note reset was used as shown:
alter system reset memory_target scope=spfile sid=’*’;
alter system reset memory_max_target scope=spfile sid=’*’;

oracle@arrow1:HAWKA:/home/oracle
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 6 21:33:31 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileHAWKA.ora
SQL> alter system reset memory_target scope=spfile sid='*';

System altered.

SQL> alter system reset memory_max_target scope=spfile sid='*';

System altered.

SQL> alter system set sga_max_size=768M scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=768M scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=256M scope=spfile sid='*';

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
Database mounted.
Database opened.
SQL> @show.sql
SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 768M
sga_target                           big integer 768M
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 256M
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 256M
sga_target                           big integer 768M
SQL> show sga

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
oracle@arrow1:HAWKA:/home/oracle
$

Run hugepages_settings.sh – Recommended setting: vm.nr_hugepages = 388

$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

Recommended setting: vm.nr_hugepages = 388

Manually calculate vm.nr_hugepages in KB using [sga_max_size(768M) * 1024 * Hugepagesize(2048 kB)]

SQL> select round(768*1024/2048)+1 from dual;

ROUND(768*1024/2048)+1
----------------------
                   385

SQL>

From alert log – vm.nr_hugepages=385

Fri Jan 06 21:34:33 2017
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 128 GB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
  Total System Global Area size is 770 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by at least 385 
     (page size 2048 KB, total size 770 MB) system wide to get 
     100% of the System Global Area allocated with large pages
********************************************************************

Configure Dynamic vm.nr_hugepages=385

[root@arrow1 ~]# sysctl -w vm.nr_hugepages=385
vm.nr_hugepages = 385
[root@arrow1 ~]# grep Huge /proc/meminfo
HugePages_Total:     353
HugePages_Free:      353
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Configure Static vm.nr_hugepages=385 and restart server – Oracle recommended.

[root@arrow1 ~]# grep vm.nr_hugepages /etc/sysctl.conf
[root@arrow1 ~]# vi /etc/sysctl.conf
[root@arrow1 ~]# grep vm.nr_hugepages /etc/sysctl.conf
vm.nr_hugepages=385
[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6
[root@arrow1 ~]#

HugePages on Oracle Linux 64-bit (Doc ID 361468.1)
Modified:Mar 7, 2016

Step 6: Stop all the database instances and reboot the server
(Although settings could have been done dynamically they would not be effective to the extent we require before a reboot.
The best practice is to do a persistent system configuration and perform a reboot to complete the configuration as presented through the steps above)

ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
cat /boot/grub/grub.conf

January 6, 2017

Check Oracle Preinstallation/Validated RPM Logs

Filed under: 11g,12c,linux,oracle — mdinh @ 12:50 am

Quick and dirty note to self.

Grid Infrastructure Installation Guide
https://docs.oracle.com/cd/E11882_01/install.112/e41961/prelinux.htm#CWLIN2928

Check the RPM log file to review the system configuration changes. For example:

Oracle Linux 6:
/var/log/oracle-rdbms-server-11gR2-preinstall/results/orakernel.log

Oracle Linux 5 and Oracle Linux 4:
/var/log/oracle-validated/results/orakernel.log

Here are the results for 12c with Oracle Linux 6
[root@arrow1 ~]# cd /var/log/oracle-rdbms-server-12cR1-preinstall/
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]# ls
backup results
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]# ll *
backup:
total 8
drwx——. 2 root root 4096 Apr 26 2014 Apr-26-2014-08-37-17
drwx——. 2 root root 4096 Jun 24 2015 Jun-24-2015-17-20-47

results:
total 4
-rw-r–r–. 1 root root 3322 Jun 24 2015 orakernel.log
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]# cat /etc/system-release
Oracle Linux Server release 6.6
[root@arrow1 oracle-rdbms-server-12cR1-preinstall]#

Here are the results for 12c with Oracle Linux 7
[root@owl ~]# cd /var/log/oracle-rdbms-server-12cR1-preinstall/
[root@owl oracle-rdbms-server-12cR1-preinstall]# ls
backup results
[root@owl oracle-rdbms-server-12cR1-preinstall]# ll *
backup:
total 0
drwx——. 2 root root 91 Dec 13 00:03 Dec-13-2016-00-03-21

results:
total 4
-rw-r–r–. 1 root root 3584 Dec 13 00:03 orakernel.log
[root@owl oracle-rdbms-server-12cR1-preinstall]# cat /etc/system-release
Oracle Linux Server release 7.3
[root@owl oracle-rdbms-server-12cR1-preinstall]#

December 18, 2016

Cloning 12c SE2 Oracle Home for Windows 2012 R2

Filed under: 12c,cloning,VirtualBox,Windows — mdinh @ 6:13 am

Process is pretty much similar to *nix environment with a few exceptions.

It was harder than it should be since I wanted to perform task using CLI vs GUI.

This does not cover zip and unzip of OH and I cannot believe how junky Winzip has become since I have typically been using 7-Zip.

Click link below for details.
Cloning 12c SE2 Oracle Home for Windows 2012 R2

 

 

December 16, 2016

RMAN MAXSETSIZE, MAXPIECESIZE, FILESPERSET Unveil

Filed under: 11g,RMAN — mdinh @ 2:14 am

First, any backup implementation should have recovery point and time objective.

Next, there’s no right or wrong, just what fits the requirements.

FILESPERSET controls maximum number of files for each backupset.
MAXPIECESIZE controls maximum size of backuppiece.
MAXSETSIZE controls maximum size of backupset.

Typically, if MAXSETSIZE is set, it should be equal to or greater than maximum size of all data files.
In general, it is not recommended to set MAXSETSIZE.

Backupset can contains 1 or more backuppiece; hence, why set a limitation on maxsetsize when it may be better to set limitation for maxpiecsize?

Justification for filesperset=1 is faster recovery and filesperset>1 is to reduce management of backup, e.g. crosscheck.

Consideration, backup versus restore frequencies.

Here’s an analogy, you are at bank withdrawing $2000 and the teller ask, how do you want the bill?
Would you like 20 $100 bills or multiple small bills 1,5,10,20 or combination of there of?

Same concept applies to backupset and filesperset.

Note: FILESPERSET and MAXOPENFILES affect multiplexing, number of buffers, size of each buffer and is not the scope of this blog post.

Actual results for TB size DB.
Note: the objective is not to improve backup time performance but to reduce number of backuppiece.

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
MAXPIECESIZE 30 G MAXOPENFILES 1;
Increase filesperset 8 from filesperset 1.

+++ DEC 14 Level 1 backup (before)
Completed at: 14-DEC-2016 20:12:49
187 backuppiece
923352M total

+++ DEC 15 Level 1 backup (after)
Competed at: 15-DEC-2016 20:11:36
83 backuppiece
1012399M total

If there are more than 1 backuppiece per backupset, this would be the expected results.
63M     /oradata/backup/HAWK_3183859104_20161115_1irkveig_1_1.bkp
63M     /oradata/backup/HAWK_3183859104_20161115_1irkveig_2_1.bkp
15M     /oradata/backup/HAWK_3183859104_20161115_1irkveig_3_1.bkp

+++ Interesting to find there is only 1 backuppiece per backupset. 
du -sc -B 1073741824 *2092127717_20161215*|sort -n
1	2092127717_20161215_rhrni9ns_1_1
1	2092127717_20161215_rlrni9oc_1_1
1	2092127717_20161215_rrrni9qo_1_1
1	2092127717_20161215_rvrni9ro_1_1
1	2092127717_20161215_s0rni9rv_1_1
1	2092127717_20161215_s2rni9s8_1_1
1	2092127717_20161215_s4rni9sm_1_1
1	2092127717_20161215_s5rni9t5_1_1
1	2092127717_20161215_s8rni9tl_1_1
1	2092127717_20161215_s9rni9tl_1_1
1	2092127717_20161215_serni9v2_1_1
1	2092127717_20161215_sgrni9vb_1_1
1	2092127717_20161215_shrni9vq_1_1
1	2092127717_20161215_skrnia0b_1_1
1	2092127717_20161215_slrnia1k_1_1
1	2092127717_20161215_smrnia1k_1_1
1	2092127717_20161215_srrnia3f_1_1
1	2092127717_20161215_ssrnia3h_1_1
2	2092127717_20161215_rfrni9ns_1_1
2	2092127717_20161215_rgrni9ns_1_1
2	2092127717_20161215_rirni9oc_1_1
2	2092127717_20161215_rkrni9oc_1_1
2	2092127717_20161215_rmrni9or_1_1
2	2092127717_20161215_rtrni9r8_1_1
2	2092127717_20161215_rurni9r8_1_1
2	2092127717_20161215_s1rni9s6_1_1
2	2092127717_20161215_s6rni9t5_1_1
2	2092127717_20161215_s7rni9t5_1_1
2	2092127717_20161215_scrni9tv_1_1
2	2092127717_20161215_sirni9vr_1_1
2	2092127717_20161215_sjrnia0a_1_1
2	2092127717_20161215_sornia24_1_1
2	2092127717_20161215_sqrnia2b_1_1
3	2092127717_20161215_rerni9ns_1_1
3	2092127717_20161215_rnrni9os_1_1
3	2092127717_20161215_s3rni9sf_1_1
3	2092127717_20161215_sarni9tl_1_1
3	2092127717_20161215_sdrni9v2_1_1
3	2092127717_20161215_snrnia1k_1_1
4	2092127717_20161215_sprnia24_1_1
5	2092127717_20161215_rprni9pl_1_1
5	2092127717_20161215_rqrni9pl_1_1
9	2092127717_20161215_t6rnigpe_1_1
10	2092127717_20161215_rjrni9oc_1_1
11	2092127717_20161215_t1rnia4m_1_1
11	2092127717_20161215_t3rnigpe_1_1
11	2092127717_20161215_t4rnigpe_1_1
11	2092127717_20161215_t5rnigpe_1_1
15	2092127717_20161215_t0rnia4l_1_1
17	2092127717_20161215_surnia4l_1_1
17	2092127717_20161215_svrnia4l_1_1
19	2092127717_20161215_qorngs1e_1_1
20	2092127717_20161215_qerng6ug_1_1
21	2092127717_20161215_rorni9os_1_1
21	2092127717_20161215_rsrni9qp_1_1
21	2092127717_20161215_sfrni9v4_1_1
22	2092127717_20161215_r7rnhrm3_1_1
23	2092127717_20161215_qdrng6ug_1_1
23	2092127717_20161215_r2rnhh4i_1_1
24	2092127717_20161215_qbrng6ug_1_1
24	2092127717_20161215_qcrng6ug_1_1
24	2092127717_20161215_sbrni9tt_1_1
25	2092127717_20161215_qjrnghfr_1_1
25	2092127717_20161215_qnrngs1e_1_1
25	2092127717_20161215_rbrni67g_1_1
25	2092127717_20161215_rcrni67g_1_1
27	2092127717_20161215_qlrngs1d_1_1
27	2092127717_20161215_qmrngs1d_1_1
28	2092127717_20161215_qhrnghfr_1_1
28	2092127717_20161215_qirnghfr_1_1
28	2092127717_20161215_r6rnhrm3_1_1
28	2092127717_20161215_r9rni67g_1_1
28	2092127717_20161215_rarni67g_1_1
30	2092127717_20161215_qgrnghfr_1_1
30	2092127717_20161215_qtrnh6j5_1_1
30	2092127717_20161215_r5rnhrm3_1_1
31	2092127717_20161215_qqrnh6j5_1_1
31	2092127717_20161215_qrrnh6j5_1_1
31	2092127717_20161215_qsrnh6j5_1_1
31	2092127717_20161215_qvrnhh4i_1_1
31	2092127717_20161215_r0rnhh4i_1_1
31	2092127717_20161215_r1rnhh4i_1_1
31	2092127717_20161215_r4rnhrm3_1_1
989	total

From the DEMO, there is only 1 backuppiece for each backupset and depending on maxsetsize, the number of backupsets created is different.

MAX DATAFILE SIZE IS 513M

ARROW1:(SYS@HAWKA):PRIMARY> select max(bytes)/1024/1024 from v$datafile;

MAX(BYTES)/1024/1024
--------------------
                 513

ARROW1:(SYS@HAWKA):PRIMARY> select name, bytes/1024/1024 from v$datafile;

NAME                                                    BYTES/1024/1024
------------------------------------------------------- ---------------
/oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf                  513
/oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf                  257
/oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf                 256
/oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf                   129
/oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf                    16

ARROW1:(SYS@HAWKA):PRIMARY>

Notice the distribution and size of datafiles in backupsets.

CONFIGURE MAXSETSIZE TO UNLIMITED

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
4 BACKUPSETS CREATED FOR LEVEL0 (2 datafiles, 1 controlfile, 1 spfile)
4 BACKUPSETS CREATED FOR LEVEL1
8 BACKUPSETS TOTAL SIZE 413680k
---------------------------------------------------------------------------
Datafiles backupset 1 (181.24M)
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf (257M)
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf(256M)
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  (129M)

NOTICE: sum of datafiles > 513M since maxsetsize is unlimited and backuppiece < 513M
Datafiles backupset 2 (190.24M)
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf (513M)
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf  ( 16M)

CONFIGURE MAXSETSIZE TO 513 M

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
5 BACKUPSETS CREATED FOR LEVEL0 (3 datafiles, 1 controlfile, 1 spfile)
5 BACKUPSETS CREATED FOR LEVEL1
10 BACKUPSETS TOTAL SIZE 413808k
---------------------------------------------------------------------------
NOTICE: backupset is only 3.59M and no other datafiles can be considered since it would exceed maxsetsize of 513M
Datafiles backupset 1 (3.59M) 
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf (256M)
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf   (129M)

Before backup, RMAN does not know datafile usage and it would be too time consumsing to check. Only datafile size is checked.
RMAN performed NULL compression since backupset is 189M and datafile is 513M.

NOTICE: sum of datafiles = 513M, maxsetsize is 513M, backuppiece < 513M
Datafiles backupset 2 (189.23M)
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf  (513M)

Datafiles backupset 3 (178.68M)
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf  (257M)
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf   ( 16M)

All the details.

CONFIGURE MAXSETSIZE TO UNLIMITED

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_MAX';
RMAN> list backup summary tag L0_MAX;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
213     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
214     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
215     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX
216     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX

RMAN> list backup tag L0_MAX;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
213     Incr 0  181.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 243   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp
  List of Datafiles in backup set 213
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf 
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  
  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
214     Incr 0  190.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 244   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
  List of Datafiles in backup set 214
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
215     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 245   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
216     Incr 0  16.08M     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 246   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
  Control File Included: Ckp SCN: 1706149      Ckp time: 2016-DEC-14 19:38:45

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_MAX';
RMAN> list backup summary tag L1_MAX;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
217     B  1  A DISK        2016-DEC-14 19:39:14 1       1       NO         L1_MAX
218     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
219     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
220     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX

RMAN> list backup tag L1_MAX;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
217     Incr 1  40.00K     DISK        00:00:02     2016-DEC-14 19:39:14
        BP Key: 247   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
  List of Datafiles in backup set 217
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
218     Incr 1  48.00K     DISK        00:00:03     2016-DEC-14 19:39:15
        BP Key: 248   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
  List of Datafiles in backup set 218
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  3    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
219     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 249   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
220     Incr 1  16.08M     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 250   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
  Control File Included: Ckp SCN: 1706215      Ckp time: 2016-DEC-14 19:39:15

RMAN> exit

oracle@arrow1:HAWKA:/home/oracle
$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
48      /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
56      /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
185604  /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp 
194820  /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
413680  total
oracle@arrow1:HAWKA:/home/oracle
$

MAX DATAFILE SIZE IS 513M AND CONFIGURE MAXSETSIZE TO 513 M

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO 513 M;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_513';
RMAN> list backup summary tag L0_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
221     B  0  A DISK        2016-DEC-14 19:42:36 1       1       NO         L0_513
222     B  0  A DISK        2016-DEC-14 19:42:37 1       1       NO         L0_513
223     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
224     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
225     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513

RMAN> list backup tag L0_513;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
221     Incr 0  3.59M      DISK        00:00:00     2016-DEC-14 19:42:36
        BP Key: 251   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
  List of Datafiles in backup set 221
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
222     Incr 0  189.23M    DISK        00:00:01     2016-DEC-14 19:42:37
        BP Key: 252   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
  List of Datafiles in backup set 222
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
224     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:42:38
        BP Key: 254   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
225     Incr 0  16.08M     DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 255   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
  Control File Included: Ckp SCN: 1706522      Ckp time: 2016-DEC-14 19:42:37

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_513';
RMAN> list backup summary tag L1_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
226     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
227     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
228     B  1  A DISK        2016-DEC-14 19:44:22 1       1       NO         L1_513
229     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513
230     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513

RMAN> list backup tag L1_513;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
226     Incr 1  56.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 256   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
  List of Datafiles in backup set 226
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
227     Incr 1  32.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 257   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
  List of Datafiles in backup set 227
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706667    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
228     Incr 1  96.00K     DISK        00:00:00     2016-DEC-14 19:44:22
        BP Key: 258   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
  List of Datafiles in backup set 228
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
229     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:44:23
        BP Key: 259   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
230     Incr 1  16.08M     DISK        00:00:01     2016-DEC-14 19:44:23
        BP Key: 260   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
  Control File Included: Ckp SCN: 1706671      Ckp time: 2016-DEC-14 19:44:22

RMAN> list backuppiece '/oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp';

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
253     223     1   1   AVAILABLE   DISK        /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp

RMAN> list backupset 223;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

RMAN>

$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
40      /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
64      /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
104     /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
3688    /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
182976  /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
193784  /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
413808  total

December 13, 2016

Linux locate/print block device attributes ASMLib

Filed under: ASM,linux — mdinh @ 2:38 pm

Just learned about this specifically to identify whether disk is being used by ASMLib

blkid – command-line utility to locate/print block device attributes

Oracle Linux Server release 6.4

[root@rac02:/root]
# ll /etc/*release*
-rw-r--r--. 1 root root 32 Feb 22  2013 /etc/oracle-release
-rw-r--r--. 1 root root 55 Feb 22  2013 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Nov 29  2014 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Feb 22  2013 /etc/system-release-cpe

[root@rac02:/root]
# cat /etc/system-release
Oracle Linux Server release 6.4

[root@rac02:/root]
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)

[root@rac02:/root]
# /sbin/blkid |sort
/dev/mapper/vg01-lv_root: UUID="97968448-9997-42a0-a106-c438a47345b8" TYPE="ext4"
/dev/mapper/vg01-lv_swap: UUID="8fe8b719-a367-4448-9ae2-76b376ad91d5" TYPE="swap"
/dev/sda1: UUID="33162370-b7a1-45ae-9c8c-966b8bd720e3" TYPE="ext4"
/dev/sda2: UUID="qHuGcf-Ntnr-hoLR-qtEb-cdgz-2sph-jgaKDK" TYPE="LVM2_member"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdc1: LABEL="DISK2" TYPE="oracleasm"
/dev/sdd1: LABEL="DISK3" TYPE="oracleasm"
/dev/sde1: LABEL="DISK4" TYPE="oracleasm"
/dev/sdf1: LABEL="DISK5" TYPE="oracleasm"
/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK8" TYPE="oracleasm"

Oracle Linux Server release 6.6

[root@arrow1 ~]# ll /etc/*release*
-rw-r--r--. 1 root root 32 Oct 15  2014 /etc/oracle-release
-rw-r--r--. 1 root root 55 Oct 15  2014 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Jun 24  2015 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Oct 15  2014 /etc/system-release-cpe

[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6

[root@arrow1 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)

[root@arrow1 ~]# /sbin/blkid
/dev/sda1: UUID="7c42cea0-f23d-4fec-ad0f-b1bf4b50b17e" TYPE="ext4"
/dev/sda2: UUID="ZVWdTj-8EQN-F3ac-3Tev-xTsb-ssL8-7euz5X" TYPE="LVM2_member"
/dev/sdb1: UUID="KUEy1I-X5i2-CuSm-krTA-R5K5-i4je-Ek56ZK" TYPE="LVM2_member"
/dev/mapper/vg01-LogVol01: UUID="8e2c236c-dd87-4be6-9eaf-f72f32f0dcc6" TYPE="ext4"
/dev/mapper/vg01-LogVol00: UUID="919bab13-82b6-425b-95c8-87975cb0bf20" TYPE="swap"

Oracle Linux Server release 7.3

[root@owl ~]# ll /etc/*release*
-rw-r--r--. 1 root root  32 Nov  7 22:07 /etc/oracle-release
-rw-r--r--. 1 root root 398 Nov  7 22:07 /etc/os-release --- (New as of OEL7?)
-rw-r--r--. 1 root root  52 Nov  7 22:07 /etc/redhat-release
lrwxrwxrwx. 1 root root  14 Dec 12 23:31 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root  31 Nov  7 22:07 /etc/system-release-cpe

[root@owl ~]# cat /etc/system-release
Oracle Linux Server release 7.3

[root@owl ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)

[root@owl ~]# cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.3"
ID="ol"
VERSION_ID="7.3"
PRETTY_NAME="Oracle Linux Server 7.3"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:3:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.3
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.3

[root@owl ~]# /sbin/blkid
/dev/sr0: UUID="2016-11-21-16-51-51-00" LABEL="VBOXADDITIONS_5.1.10_112026" TYPE="iso9660"
/dev/sda1: UUID="65bf9c73-fb56-49c3-b55e-85de8f318892" TYPE="xfs"
/dev/sda2: UUID="p4rqW2-uzvr-6DpX-nof0-7tgf-Yfvn-n8ehaB" TYPE="LVM2_member"
/dev/mapper/vg01-root: UUID="de9c65d9-60cc-45b8-b9fa-7459ef3f3850" TYPE="xfs"
/dev/mapper/vg01-swap: UUID="1f85c253-261e-4021-9468-651de69b8e7a" TYPE="swap"
[root@owl ~]#

December 9, 2016

GoldenGate Capture using Active DataGuard

Filed under: Dataguard,GoldenGate — mdinh @ 3:38 am

How to Configure Extract on Standalone Active Data Guard System if Primary is RAC Multipe Nodes (Doc ID 1962336.1)

Configuring Classic Capture in Oracle Active Data Guard Only Mode
http://docs.oracle.com/goldengate/1212/gg-winux/GIORA/classic_capture.htm#GIORA997

dgmgrl from OS command line

Filed under: 11g,Dataguard,dgmgrl — mdinh @ 3:32 am

Quick and dirty post from what I just learned.

Who said you can’t teach old dogs new tricks!

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawka"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawkb"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.11 MByte/s
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$

Example from 4 Nodes RAC DG Configuration:

Real name and password were replaced. All others are the same.

host=white01/02/03/04
db_name=hawk
db_unique_name=hawka
instance_name=hawk1/2/3/4
ORACLE_SID=hawk1/2/3/4

++++++++++

host=black01/02/03/04
db_name=hawk
db_unique_name=hawkb
instance_name=hawk1/2/3/4
ORACLE_SID=hawk1/2/3/4

$ dgmgrl / "show configuration"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawka - Primary database
    hawkb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

$ dgmgrl / "show database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk1
    hawk2
    hawk3
    hawk4

Database Status:
SUCCESS

$ dgmgrl / "show database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       28 minutes 9 seconds
  Real Time Query: ON
  Instance(s):
    hawk1
    hawk2 (apply instance)
    hawk3
    hawk4

Database Status:
SUCCESS

$ dgmgrl / "show database verbose hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk1
    hawk2
    hawk3
    hawk4

  Properties:
    DGConnectIdentifier             = 'hawka'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1200'
    LogArchiveMaxProcesses          = '16'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

$ dgmgrl / "show database verbose hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       15 minutes 53 seconds
  Real Time Query: ON
  Instance(s):
    hawk1
    hawk2 (apply instance)
    hawk3
    hawk4

  Properties:
    DGConnectIdentifier             = 'hawkb'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '60'
    NetTimeout                      = '180'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = 'hawk2'
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = '16'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '600'
    LogArchiveMaxProcesses          = '16'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = '+DATA/hawka, +DATA/hawkb'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk1 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk1' of database 'hawka'

  Host Name: white01.local
  PFILE:     
  Properties:
    SidName                         = 'hawk1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk2 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk2' of database 'hawka'

  Host Name: white02.local
  PFILE:     
  Properties:
    SidName                         = 'hawk2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk3 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk3' of database 'hawka'

  Host Name: white03.local
  PFILE:     
  Properties:
    SidName                         = 'hawk3'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk4 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk4' of database 'hawka'

  Host Name: white04.local
  PFILE:     
  Properties:
    SidName                         = 'hawk4'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk1 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk1' of database 'hawkb'

  Host Name: black01.local
  PFILE:     
  Properties:
    SidName                         = 'hawk1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk2 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk2' of database 'hawkb'

  Host Name: black02.local
  PFILE:     
  Properties:
    SidName                         = 'hawk2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk3 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk3' of database 'hawkb'

  Host Name: black03.local
  PFILE:     
  Properties:
    SidName                         = 'hawk3'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk4 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk4' of database 'hawkb'

  Host Name: black04.local
  PFILE:     
  Properties:
    SidName                         = 'hawk4'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

Verify connectivity using DGConnectIdentifier

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 9 14:19:51 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

@> connect sys/oracle@hawka as sysdba
Connected.
SQL> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      
db_name                              string      hawk
db_unique_name                       string      hawka

SQL> connect sys/oracle @hawkb as sysdba
Connected.

SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      
db_name                              string      hawk
db_unique_name                       string      hawkb

SQL> exit

Verify connectivity using StaticConnectIdentifier

$ grep -i hawka_DGMGRL test_dg_static_connect_id.sql|grep HOST
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))' as sysdba

$ grep -i hawkb_DGMGRL test_dg_static_connect_id.sql|grep HOST
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))' as sysdba

3 out 4 failed for standby and all passed for primary

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 9 14:44:44 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

@> @test_dg_static_connect_id.sql

"hawkb_DGMGRL hawk1"
Connected.

"hawkb_DGMGRL hawk2"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SP2-0640: Not connected
SP2-0640: Not connected

"hawkb_DGMGRL hawk3"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0640: Not connected
SP2-0640: Not connected

"hawkb_DGMGRL hawk4"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0640: Not connected
SP2-0640: Not connected

"hawka_DGMGRL hawk1"
Connected.
"hawka_DGMGRL hawk2"
Connected.
"hawka_DGMGRL hawk3"
Connected.
"hawka_DGMGRL hawk4"
Connected.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

December 4, 2016

Toys for when You & I are bored

Filed under: oracle — mdinh @ 3:36 am

https://github.com/terrywang/vagrantboxes/blob/master/oraclelinux-7-x86_64.md

https://github.com/yasushiyy/vagrant-oracle11g-rac
https://github.com/yasushiyy/vagrant-oracle12c-rac

https://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home/RAC_Attack_Automation

https://github.com/racattack/vagrantfile

http://oracleprof.blogspot.com/2015/08/automation-for-dba-vagrant-part-1.html
http://oracleprof.blogspot.com/2015/09/automation-for-dba-vagrant-part-2.html

http://vxcompany.com/2016/08/12/vagrant-for-you-rac-test-environment/

https://oravirt.wordpress.com/2014/12/23/racattack-meet-ansible-oracle/

http://myofwexperiments.blogspot.com/2015/10/oracle-12c-database-installation-on.html

https://www.centos.org/download/

Images

November 30, 2016

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Filed under: 11g,Dataguard — mdinh @ 1:37 pm

This is a quick and dirty note to self about Doc ID 1387859.1 which may be incorrect for Single Instance Database with Oracle Restart.

Please share if you have configuration for Single Instance Database with Oracle Restart using DataGuard Broker and listener registered from Grid – TIA.

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)	

+++ DO NOT BELIEVE THIS TO BE CORRECT - FAILED FOR TEST CASE.

Single Instance Database with Oracle Restart

Here there is no cluster, but clusterware has been installed to enable the Oracle Restart feature. 

The local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Grid Infrastructure home. 

In this case the static service registration is:
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name)
    )
  )

As with Single Instance databases, the SID_NAME value sid_name will default to the db_unique_name.

+++ SUCCESS
Data Guard & Oracle Restart in 11gR2 https://uhesse.com/2010/09/06/data-guard-oracle-restart-in-11gr2/

DEMO: Listener configured from DB Home and NOT GI Home which failed.

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ ll
total 0
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 alert_HAWKB.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 drcHAWKB.log

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:03:54
Uptime                    0 days 0 hr. 10 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow2.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKB.localdomain" has 2 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGB.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKB_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ dgmgrl sys/oracle@hawka
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawkb - Primary database
    hawka - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

DGMGRL> switchover to hawka
Performing switchover NOW, please wait...
New primary database "hawka" is opening...
Operation requires startup of instance "HAWKB" on database "hawkb"
Starting instance "HAWKB"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawka"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawka - Primary database
    hawkb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

DGMGRL> exit

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,Readonly,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------
oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ ll
total 0
-rw-r--r--. 1 oracle oinstall 0 Nov 30 05:12 alert_HAWKA.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:12 drcHAWKA.log

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:04:43
Uptime                    0 days 0 hr. 9 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKA.localdomain" has 2 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGB.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKA_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,Readonly,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------
oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$

Next Page »

Blog at WordPress.com.