Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 10 hours 1 min ago

Oracle 12c – Is VKTM always your top process?

Fri, 2017-03-03 05:16

If VKTM is always your top cpu consuming process, then this blog might be something for you. Especially in virtual environments, I have seen often the VKTM process as the top process, even if the VM was idle. So, I am burning CPU without any obvious benefit. So what is the reason for the high CPU consumption? Well … it can a combination of many things like not correctly working NTP, missing VMware Tools, but for and foremost Oracle Bugs. I really don’t know why, but quite a lot of issues have been raised regarding the VKTM process, like the following.

  • Bug 20693049 – 12C VKTM CONSUMING MORE CPU THAN IN 11GR2
  • Bug 20552573 – VKTM PROCESSES FROM ASM AND A CDB DATABASE CONSUME 12% CPU PERMANENTLY.
  • BUG 12883034 – CHANGE THE INTERVAL WHEN VKTM PROCESS WAKES U
  • Bug 20542107 – WARNING: VKTM DETECTED A TIME DRIFT
  • Bug 20138957 – VKTM PROCESS CONSUMING HIGH CPU EVEN AFTER PATCH 18499306
  • Bug 11837095 – “TIME DRIFT DETECTED” APPEARS INTERMITTENTLY IN ALERT LOG, THO’ EVENT 10795 SET.

If you search around in MOS, you probably find even more. Usually VKTM and VKRM issues come together, at least when you are using the resource manager. The VTKM is the Virtual Keeper of Time Process. The VKTM acts as a time publisher for an Oracle instance.  VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements.  The VKTM process is a process that is available in ASM instances and RDBMS instances. So if you see issues with VKTM process, it usually popps up on both. VKTM usage is affected mainly by two hidden parameters _timer_precision and _disable_highres_ticks. So, tuning these parameters can bring down VKTM CPU consumption.

The VKRM process is the Virtual Scheduler for Resource Manager Process, and it serves as centralized scheduler for Resource Manager activity. As there is no resource manager on a ASM instance, you will see this process only on RDBMS instances.

Ok .. my test environment is OEL 6.8 with 12.1.0.2 ASM and a 12.1.0.2 database on a virtual guest. To be more precise, it is a Database with PSU 12.1.0.2.161018 and Grid Infrastructure PSU 12.1.0.2.161018. The patch level plays a quite important role. For 11gR2 database, you might need patch 20531619.

So let’s start fixing the ASM VKTM issue first. It warns me all the time, that is has detected a time drift.

$ cat alert_+ASM.log | grep -i "Warning: VKTM detected a time drift."
Warning: VKTM detected a time drift.
Warning: VKTM detected a time drift.
Warning: VKTM detected a time drift.
Warning: VKTM detected a time drift.
Warning: VKTM detected a time drift.
...
...
Warning: VKTM detected a time drift.

Before continuing, it is important that you really don’t have a time drift. On VMware, you might want to consult the following knowledge base article and the time keeping pdf. Both are very good resources. KB Article: 1006427 Timekeeping best practices for Linux guests and http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/techpaper/Timekeeping-In-VirtualMachines.pdf

Next, check that your ntp is in sync.

$ ntpq -p
     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
+aquila.init7.ne 162.23.41.10     2 u   16   64  377   16.375   25.656   3.873
*ntp0.as34288.ne 85.158.25.74     2 u   17   64  377   12.987   27.874   4.045

If your ntp is not in sync, you should stop already here, and correct it. Because in that case, the warning message from Oracle is correct, that VKTM has detected a time drift. Ok. Let’s continue with checking the ASM instance. The 12.1.0.2 ASM defaults regarding VKTM are the following:

Parameter                           Session_Value  Instance_Value Description
----------------------------------- -------------- -------------- --------------------------------------------
_disable_highres_ticks              FALSE          FALSE          disable high-res tick counter
_high_priority_processes            LMS*           LMS*           High Priority Process Name Mask
_highest_priority_processes         VKTM           VKTM           Highest Priority Process Name Mask
_timer_precision                    10             10             VKTM timer precision in milli-sec
_vkrm_schedule_interval             10             10             VKRM scheduling interval
_vktm_assert_thresh                 30             30             soft assert threshold VKTM timer drift

Because I don’t need high resolution ticks on my ASM instance, I am going to disable it, and besides that I am going to disable the excessiv trace by the VKTM process which is done with the 10795 event.

SQL> alter system set "_disable_highres_ticks"=true scope=spfile;

System altered.

SQL> alter system set event="10795 trace name context forever, level 2" scope=spfile;

System altered.

Unfortunately, these changes can’t be done online, and so I have to bounce my ASM instance.

$ srvctl stop asm -f
$ srvctl start asm

My alert.log does not report time drift issues anymore and the VKTM process from my ASM instance  disappeared  from my top process list. As soon as the ASM VKTM process went away, the one from the database popped up. :-)

oracle@oel001:/home/oracle/ [OCM121] top -c
top - 11:17:18 up  1:21,  2 users,  load average: 0.69, 0.77, 0.98
Tasks: 229 total,   2 running, 227 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.7%us,  0.4%sy,  0.0%ni, 98.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  10021556k total,  3816024k used,  6205532k free,   209916k buffers
Swap:  6160380k total,        0k used,  6160380k free,   672856k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5696 oracle    -2   0 1880m  46m  44m S 22.7  0.5   0:23.49 ora_vktm_OCM121

Ok. Let’s fix that one as well. The RDBMS defaults regarding VKTM with 12.1.0.2 are the same as with ASM.

Parameter                           Session_Value  Instance_Value Description
----------------------------------- -------------- -------------- --------------------------------------------
_disable_highres_ticks              FALSE          FALSE          disable high-res tick counter
_high_priority_processes            LMS*           LMS*           High Priority Process Name Mask
_highest_priority_processes         VKTM           VKTM           Highest Priority Process Name Mask
_timer_precision                    10             10             VKTM timer precision in milli-sec
_vkrm_schedule_interval             10             10             VKRM scheduling interval
_vktm_assert_thresh                 30             30             soft assert threshold VKTM timer drift

Without any changes, the tracing for the VKTM and VKRM background processes are enabled, and quite a lot  of information go into these trace files.

Tracing for the VKRM process can be disabled via the following event:

alter system set events '10720 trace name context forever, level 0x10000000';

Tracing for the VKRM process can be disabled via the following event:

alter system set events '10795 trace name context forever, level 2';

Because I don’t need any of those, I am going to disable both in one shot.

SQL> alter system set event='10720 trace name context forever, level 0x10000000','10795 trace name context forever, level 2' comment='Turn off VKRM tracing and turn off VKTM tracing' scope=spfile;

System altered.

And like on the ASM instance, I don’t need the high-resolution ticks here as well.

SQL> alter system set "_disable_highres_ticks"=true scope=spfile;

System altered.

After the restart of the database, the extensive traceing and cpu usage went away.

oracle@oel001:/home/oracle/ [OCM121] srvctl stop database -d OCM121
oracle@oel001:/home/oracle/ [OCM121] srvctl start database -d OCM121

I am not seeing the VKTM process in my top processes anymore. Beforehand, even on an idle system, the VKTM from the ASM and the one from the RDBMS instance have always been at the top.

oracle@oel001:/home/oracle/ [OCM121] top -c
top - 11:29:06 up  1:33,  2 users,  load average: 0.19, 0.69, 0.69
Tasks: 233 total,   2 running, 231 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.8%us,  0.5%sy,  0.0%ni, 98.7%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  10021556k total,  3839340k used,  6182216k free,   211884k buffers
Swap:  6160380k total,        0k used,  6160380k free,   686380k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3643 grid      20   0 1559m  84m  39m S  1.3  0.9   0:55.64 /u00/app/grid/12.1.0.2/bin/oraagent.bin
 3660 grid      20   0  329m  28m  22m S  1.0  0.3   0:28.15 /u00/app/grid/12.1.0.2/bin/evmd.bin
 3517 grid      20   0 1507m  69m  48m S  0.7  0.7   0:34.74 /u00/app/grid/12.1.0.2/bin/ohasd.bin reboot
 3738 grid      20   0  262m  26m  21m S  0.7  0.3   0:28.03 /u00/app/grid/12.1.0.2/bin/evmlogger.bin -o /u00/app/grid/12.1
 3757 grid      20   0  791m  32m  23m S  0.7  0.3   0:31.30 /u00/app/grid/12.1.0.2/bin/cssdagent
Conclusion

Especially in virtual environment I have seen often a quite high cpu usage by the VKTM process, so take care that your time keeping via NTP is setup correctly. After NTP is running smoothly, you might want to disable the high-resolution ticks and disable the extensive tracing by the VKTM and VKRM processes. Obviously, this is not a general recommendation. You should test it yourself.

 

Cet article Oracle 12c – Is VKTM always your top process? est apparu en premier sur Blog dbi services.

Oracle 12c – How to Recover lost DataGuard Broker Configuration Files

Tue, 2017-02-28 07:53

If you are using RMAN to back up your database, you are already doing the right thing. However, RMAN does not take care of everything. e.g. it is not backing up the following things, to mention just a few.

  • Oracle Home
  • Grid Infrastructure Home
  • Data Guard broker files
  • Password File
  • SQL*Net file like ldap.ora, sqlnet.ora, tnsnames.ora and listener.ora
  • /etc/oratab
  • OS audit files
  • Wallets
  • /etc/sysctl.conf and limits.conf
  • OLR and OCR
  • Voting Disks
  • ASM Metadata
  • passwd, shadow, group
  • RMAN scripts itself ksh/rcv (some exceptions when using the RMAN catalog)
  • crontab

There are for sure many more, but in this article I would like to take a closer look at the Data Guard broker configuraiton files. The DataGuard Broker Configuration Files are quite important files. The contain entries that describe the state and the properties of the DataGuard configuration like the sites and databases that are part of the configuration, the roles and properties of each of the databases, and the state of each of the elements of the configuration. The broker configuration files are very small, in my case they have only 12K. Don’t expect them to grow very big. They usually stay at this size.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 09:27 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 09:34 dr2DBIT121_SITE1.dat

You can have a maximum of two different copies which can be configured via the DG_BROKER_CONFIG_FILEn (where n = 1, 2) parameter. Maybe, maximum is not the correct word in this context, because if you don’t specify the broker file location, Oracle still creates one in the default directory which is OS dependent. On Linux they end up in $ORACLE_HOME/dbs/.

However, not like you might expect, the broker configuration files are not a 1:1 mirror like e.g. redo log members or controlfiles. They are individual copies and they maintain the last known good state of the configuration. You can check it quite easily yourself by editing e.g. the FastStartFailoverThreshold and check the time stamps of the broker files afterwards.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
Property "faststartfailoverthreshold" updated

-- Primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -l dr*
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:27 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:34 dr2DBIT121_SITE1.dat

-- Standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -l dr*
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:34 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:27 dr2DBIT121_SITE2.dat

As you can see here, the broker configuration files have two different time stamps. One with the last know good state at 09:27 and one with new state and 09:34. When the broker is started for the first time, it is expected that you see only one configuration file. But don’t panic, the other will be created by the next updates done by the broker.

The broker configuration file is a binary file, however the readable contents of that file can be extracted via the strings command. It gives an idea of some contents of the broker file. e.g. it shows you that the broker config file is brought to you by the fine folks from NEDC, whoever they are. :-) With 12cR1 is looks like the following.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] file dr2DBIT121_SITE1.dat
dr2DBIT121_SITE1.dat: data

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] strings dr2DBIT121_SITE1.dat
}|{z
DBIT121
DBIT121_SITE1
DBIT121_SITE2
DBIT121_SITE2
Brought to you by the fine folks at NEDC.
TRUETRUEdbidg03ALLDBIT121FALSECONTINUE
DBIT121_SITE1DBIT121_SITE1FALSEFALSEFALSESYNCoptionalDISABLEON1,1948637,931101276,0*AUTO0,0,00,0,0AUTODBIT121_SITE2
dbidg01DBIT121DBIT121(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NA
ME=DBIT121_SITE1_DGMGRL)(INSTANCE_NAME=DBIT121)(SERVER=DEDICATED)))USE_DB_RECOVERY_FILE_DEST%t_%s_%r.dbf4
DBIT121_SITE2DBIT121_SITE2FALSEFALSEFALSESYNCoptionalDISABLEON1,1948637,931101276,0*DBIT121AUTO0,0,00,0,0AUTODBIT121_SITE1
dbidg02DBIT121DBIT121(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT121_SITE2_DGMGRL)(INSTANCE_NAME=DBIT121)(SERVER=DEDICATED)))USE_DB_RECOVERY_FILE_DEST%t_%s_%r.dbf

Things are changing with Oracle 12cR2. The broker config files are still binary files, however the readable content via the strings command is very good now. You can get a lot of very useful information out of it. And even with Oracle 12cR2, it looks like that it is still brought to you by the same fine folks from NEDC. :-) Looks like that the Oracle developers have a sort of humor. :-)

oracle@dbidg01:/u01/app/oracle/admin/DBIT122/pfile/ [DBIT122] file dr2DBIT122_SITE1.dat
dr2DBIT122_SITE1.dat: data
oracle@dbidg01:/u01/app/oracle/admin/DBIT122/pfile/ [DBIT122] strings dr2DBIT122_SITE1.dat
}|{z
DBIT122
DBIT122_SITE1
Brought to you by the fine folks at NEDC.
<?xml version="1.0" encoding="UTF-8"?>
<DRC Version="12.2.0.1.0" Name="DBIT122" CurrentPath="True">
  <PlannedState>OFFLINE</PlannedState>
  <Status>
    <Severity>Success</Severity>
    <Error>0</Error>
  </Status>
  <DefaultState>ONLINE</DefaultState>
  <IntendedState>ONLINE</IntendedState>
  <MIV PropertyID="1">0</MIV>
  <PRIMARY_SITE_ID PropertyID="26">513</PRIMARY_SITE_ID>
  <DRC_UNIQUE_ID PropertyID="4">152596437</DRC_UNIQUE_ID>
  <DRC_UNIQUE_ID_SEQUENCE PropertyID="5">18</DRC_UNIQUE_ID_SEQUENCE>
  <EXT_COND PropertyID="29">7</EXT_COND>
  <OVERALL_PROTECTION_MODE PropertyID="30">2</OVERALL_PROTECTION_MODE>
  <FastStartFailoverMode PropertyID="32">0</FastStartFailoverMode>
  <FSFO_MIV PropertyID="33">11</FSFO_MIV>
  <FastStartFailoverOBID1 PropertyID="119">470173189</FastStartFailoverOBID1>
  <FastStartFailoverOBID2 PropertyID="120">470173183</FastStartFailoverOBID2>
  <FastStartFailoverOBID3 PropertyID="121">470173184</FastStartFailoverOBID3>
  <ObserverVersion1 PropertyID="133">0</ObserverVersion1>
  <Configuration_Name PropertyID="31">DBIT122</Configuration_Name>
  <ObserverName1 PropertyID="129">dbidg03</ObserverName1>
  <ConfigurationWideServiceName PropertyID="132">DBIT122_CFG</ConfigurationWideServiceName>
  <RoleChangeHistory PropertyID="106">
    <RoleChangeRecord>
      <Event>PhysicalFailover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>931098450</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalFailover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>931098812</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalFailover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>932306689</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>932307856</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>932377455</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>932381717</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>932382294</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>932383387</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>934017954</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</New
Primary>
      <Status>0</Status>
      <Timestamp>934018548</Timestamp>
    </RoleChangeRecord>
  </RoleChangeHistory>
  <Member MemberID="1" Name="DBIT122_SITE1" CurrentPath="True" Enabled="True" MultiInstanced="True">
    <PlannedState>STANDBY</PlannedState>
    <StandbyRole>PhysicalStandby</StandbyRole>
    <Status>
      <Severity>Success</Severity>
      <Error>0</Error>
    </Status>
    <DefaultState>PRIMARY</DefaultState>
    <IntendedState>PRIMARY</IntendedState>
    <ResourceType>Database</ResourceType>
    <CurrentState>PRIMARY</CurrentState>
    <Role>
      <ConditionState>PRIMARY</ConditionState>
      <DefaultState>READ-WRITE-XPTON</DefaultState>
      <IntendedState>READ-WRITE-XPTON</IntendedState>
    </Role>
    <Role>
      <ConditionState>STANDBY</ConditionState>
      <DefaultState>PHYSICAL-APPLY-ON</DefaultState>
      <IntendedState>OFFLINE</IntendedState>
    </Role>
    <DB_Unique_Name PropertyID="23">DBIT122_SITE1</DB_Unique_Name>
    <DGConnectIdentifier PropertyID="6">DBIT122_SITE1</DGConnectIdentifier>
    <DbDomain PropertyID="37"/>
    <ClusterDatabase PropertyID="42">FALSE</ClusterDatabase>
    <DbChangeCritical PropertyID="8">FALSE</DbChangeCritical>
    <DbIsCritical PropertyID="9">FALSE</DbIsCritical>
    <LogXptMode PropertyID="40">SYNC</LogXptMode>
    <IncarnationTable PropertyID="57">6,2568637,932306696,5*5,2514031,931098817,4#4,2513489,931098453,2#2,1396169,929894741,1#1,1,924281211,0#</IncarnationTable>
    <SRLStatus PropertyID="58">0</SRLStatus>
    <ActualApplyInstance PropertyID="7"/>
    <StandbyFileManagement PropertyID="72">AUTO</StandbyFileManagement>
    <ArchiveLagTarget PropertyID="73">0</ArchiveLagTarget>
    <LogArchiveMaxProcesses PropertyID="74">4</LogArchiveMaxProcesses>
    <LogArchiveMinSucceedDest PropertyID="75">1</LogArchiveMinSucceedDest>
    <DataGuardSyncLatency PropertyID="138">0</DataGuardSyncLatency>
    <DbFileNameConvert PropertyID="76"/>
    <LogFileNameConvert PropertyID="77"/>
    <FastStartFailoverTarget PropertyID="38">DBIT122_SITE2</FastStartFailoverTarget>
    <ReinstateContextArray PropertyID="39"/>
    <Instance InstanceID="1" Name="DBIT122" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True">
      <PlannedState>OFFLINE</PlannedState>
      <HostName PropertyID="2" Default="True">dbidg01</HostName>
      <SidName PropertyID="3">DBIT122</SidName>
      <InstanceName PropertyID="36">DBIT122</InstanceName>
      <StaticConnectIdentifier PropertyID="25">(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))</StaticConnectIdentifier>
      <StandbyArchiveLocation PropertyID="96" Default="True">USE_DB_RECOVERY_FILE_DEST</StandbyArchiveLocation>
      <LogArchiveTrace PropertyID="98">0</LogArchiveTrace>
      <LogArchiveFormat PropertyID="99">%t_%s_%r.dbf</LogArchiveFormat>
    </Instance>
  </Member>
  <Member MemberID="2" Name="DBIT122_SITE2" CurrentPath="True" Enabled="True" MultiInstanced="True">
    <PlannedState>STANDBY</PlannedState>
    <StandbyRole>PhysicalStandby</StandbyRole>
    <Status>
      <Severity>Success</Severity>
      <Error>0</Error>
    </Status>
    <DefaultState>STANDBY</DefaultState>
    <IntendedState>STANDBY</IntendedState>
    <ResourceType>Database</ResourceType>
    <CurrentState>STANDBY</CurrentState>
    <Role>
      <ConditionState>PRIMARY</ConditionState>
      <DefaultState>READ-WRITE-XPTON</DefaultState>
      <IntendedState>OFFLINE</IntendedState>
    </Role>
    <Role>
      <ConditionState>STANDBY</ConditionState>
      <DefaultState>PHYSICAL-APPLY-ON</DefaultState>
      <IntendedState>PHYSICAL-APPLY-ON</IntendedState>
    </Role>
    <DB_Unique_Name PropertyID="23">DBIT122_SITE2</DB_Unique_Name>
    <DGConnectIdentifier PropertyID="6">DBIT122_SITE2</DGConnectIdentifier>
    <DbDomain PropertyID="37"/>
    <ClusterDatabase PropertyID="42">FALSE</ClusterDatabase>
    <DbChangeCritical PropertyID="8">FALSE</DbChangeCritical>
    <DbIsCritical PropertyID="9">FALSE</DbIsC
ritical>
    <LogXptMode PropertyID="40">SYNC</LogXptMode>
    <IncarnationTable PropertyID="57">8,2568637,932306696,4*4,2514031,931098817,3#3,2513489,931098453,2#2,1396169,929894741,1#1,1,924281211,0#</IncarnationTable>
    <SRLStatus PropertyID="58">0</SRLStatus>
    <ActualApplyInstance PropertyID="7">DBIT122</ActualApplyInstance>
    <StandbyFileManagement PropertyID="72">AUTO</StandbyFileManagement>
    <ArchiveLagTarget PropertyID="73">0</ArchiveLagTarget>
    <LogArchiveMaxProcesses PropertyID="74">4</LogArchiveMaxProcesses>
    <LogArchiveMinSucceedDest PropertyID="75">1</LogArchiveMinSucceedDest>
    <DataGuardSyncLatency PropertyID="138">0</DataGuardSyncLatency>
    <DbFileNameConvert PropertyID="76"/>
    <LogFileNameConvert PropertyID="77"/>
    <FastStartFailoverTarget PropertyID="38">DBIT122_SITE1</FastStartFailoverTarget>
    <ReinstateContextArray PropertyID="39"/>
    <Instance InstanceID="1" Name="DBIT122" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True">
      <PlannedState>OFFLINE</PlannedState>
      <HostName PropertyID="2" Default="True">dbidg02</HostName>
      <SidName PropertyID="3">DBIT122</SidName>
      <InstanceName PropertyID="36">DBIT122</InstanceName>
      <StaticConnectIdentifier PropertyID="25">(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))</StaticConnectIdentifier>
      <StandbyArchiveLocation PropertyID="96" Default="True">USE_DB_RECOVERY_FILE_DEST</StandbyArchiveLocation>
      <LogArchiveTrace PropertyID="98">0</LogArchiveTrace>
      <LogArchiveFormat PropertyID="99">%t_%s_%r.dbf</LogArchiveFormat>
    </Instance>
  </Member>
</DRC>

In 12cR2, Oracle put more information into the broker files and that’s why they are getting bigger. With a standard config they have now 16k instead of 12k like they had before with 12cR1. Ok .. it looks like I am drifting away. Let’s get back to the original question, how do I recover the broker configuration files, in case they get lost? Like always … it depends. You could lose the old copy on the standby, you could lose the new copy on the standby, or you could lose both copies on the standby, and you could lose them while the standby is up and running or while it is shutdown. The same applies to the primary. It might get even more complicated in case you have a far sync database in between, or more standby’s.

Not making it too complex, in the end, we might end up with 6 different recovery scenarios.

1.) We lose the old copy of the broker config file on either the primary or the standby
2.) We lose both copies of the broker config file on the standby while it is up and running
3.) We lose both copies of the broker config file on the standby while it is shut down
4.) We lose both copies of the broker config file on the primary while it is up and running
5.) We lose both copies of the broker config file on the primary while it is shut down
6.) We lose both copies of the broker contfig files on the primary and the standby (quite unlikely to happen)

My demos are built on a simple primary/standby setup with 12cR1. The primary is on host dbidg01 and the standby is currently on host dbidg02.

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:03 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:04 dr2DBIT121_SITE1.dat

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:04 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:03 dr2DBIT121_SITE2.dat
Scenario 1.) We lose the old copy of the broker config file on either the primary or the standby

There is absolutely not need to panic in this case. I am deleting the old copies on the primary and standby at the same time.

-- primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr1DBIT121_SITE1.dat
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

-- standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr2DBIT121_SITE2.dat
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

The broker does not care at all.

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

The second copy will be created automatically as soon as I am changing a property. e.g. I am setting the FastStartFailoverThreshold to the same value as it had before.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
Property "faststartfailoverthreshold" updated

The broker log has no information about the new created file and also the alert.log does not say anything, but automagically we end up with two broker config files after the update was done. So .. nothing to do here. Oracle handles this itself.

oracle@dbidg01:/u01/app/oracle/diag/rdbms/dbit121_site1/DBIT121/trace/ [DBIT121] tail -20f drcDBIT121.log
...
02/28/2017 11:10:25
EDIT CONFIGURATION SET PROPERTY faststartfailoverthreshold = 40
FSFO threshold value set to 40 seconds

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -l dr*
-rw-r----- 1 oracle oinstall 12288 Feb 28 11:10 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12288 Feb 28 11:04 dr2DBIT121_SITE1.dat

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:04 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:10 dr2DBIT121_SITE2.dat

 

2.) We lose both copies of the broker config file on the standby while it is up and running

But what happens if I lose both copies on the standby while it is up and running.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*.dat
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
ls: cannot access dr*: No such file or directory

I am logging in into the standby database and check the configuration. The broker does not care at all if the files are there or not. It seems like nothing has happend.

oracle@dbidg02:/home/oracle/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 11:17:00.64] Connecting to database using DBIT121_SITE2.
[W000 02/28 11:17:01.72] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:17:01.72] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 8 seconds ago)

The broker is reading the information from memory, maybe because of performance reasons. The current state is also reflected in the x$drc.

SQL> desc x$drc
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 OBJECT_ID                                          NUMBER
 ATTRIBUTE                                          VARCHAR2(30)
 VALUE                                              VARCHAR2(512)
 PARENT_ID                                          VARCHAR2(15)
 STATUS                                             VARCHAR2(30)
 MESSAGE                                            VARCHAR2(256)
 ERRNUM                                             NUMBER
 VALUE_RAW                                          RAW(512)
 ERRTIME                                            NUMBER

So … how do we get the broker files back?  By simply editing any property. You can choose any property you want.

oracle@dbidg02:/home/oracle/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 11:27:58.23] Connecting to database using DBIT121_SITE2.
[W000 02/28 11:27:59.33] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:27:59.33] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
<DO_CONFIGURE_DRC version="12.1"><EDIT_DRC><PROPERTY name="faststartfailoverthreshold" value="40" context="0"/></EDIT_DRC></DO_CONFIGURE_DRC>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
Property "faststartfailoverthreshold" updated

As soon as you have edited your configuration, a new broker file appears on the standby.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:28 dr1DBIT121_SITE2.dat

If you run the same command again, the second one appears as well.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:28 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:30 dr2DBIT121_SITE2.dat

 

3.) We lose both copies of the broker config file on the standby while it is shut down

What happens if we lose both copies while the standby is shut down. Does the standby come up correctly? Is there something in the drc.log or alert.log?

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

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*.dat
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

SQL> startup mount
ORACLE instance started.

Total System Global Area 1325400064 bytes
Fixed Size                  2924112 bytes
Variable Size             352321968 bytes
Database Buffers          956301312 bytes
Redo Buffers               13852672 bytes
Database mounted.

The standby comes up and no entries in the alert.log, however the drc.log shows that the broker files are missing.

02/28/2017 11:35:26
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
      dg_broker_config_file2 = "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat"
2017-02-28 11:35:26.313                      DMON: Attach state object
2017-02-28 11:35:26.314                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat", retrying
2017-02-28 11:35:27.334                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
2017-02-28 11:35:27.334                        ORA-27037: unable to obtain file status
2017-02-28 11:35:27.335                        Linux-x86_64 Error: 2: No such file or directory
2017-02-28 11:35:27.335                        Additional information: 3
2017-02-28 11:35:27.335                      DMON: Error opening "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat", error = ORA-16572
2017-02-28 11:35:27.335                      DMON: Establishing "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat" as the more current file
2017-02-28 11:35:27.335                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat", retrying
2017-02-28 11:35:28.355                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat"
...
2017-02-28 11:35:42.893                      Configuration does not exist, Data Guard broker ready
2017-02-28 11:35:42.894 7fffffff           0 DMON: Entered rfm_release_chief_lock() for CTL_BOOTSTRAP


DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 11:38:50.58] Connecting to database using DBIT121_SITE2.
[W000 02/28 11:38:51.67] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:38:51.68] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL


SQL> select attribute, value from x$drc;
select attribute, value from x$drc
                             *
ERROR at line 1:
ORA-16532: Oracle Data Guard broker configuration does not exist

If we logon to the primary, we can see that the standby DBIT121_SITE2 was disabled.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE1
[W000 02/28 11:45:40.56] Connecting to database using DBIT121_SITE1.
[W000 02/28 11:45:41.68] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:45:41.68] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    DBIT121_SITE2 - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 7 seconds ago)

However, after we enable the standby, we can see in the drc.log that the standby receives the metadata from the primary and creates the broker config file.

DGMGRL> enable database 'DBIT121_SITE2';
<DO_CONTROL version="12.1"><DO_COMMAND type="Enable" object_id="33554432"/></DO_CONTROL>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
Enabled.
DGMGRL>


drc.log

2017-02-28 11:46:03.352                      DRCX: Start receiving metadata file: "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
2017-02-28 11:46:03.356                      DRCX: Receiving block #1 (containing Seq.MIV = 2.20), 2 blocks
2017-02-28 11:46:03.358                      DRCX: End receiving metadata file: opcode CTL_ENABLE (1.1.631286030)
2017-02-28 11:46:03.360                      DMON: Entered rfm_get_chief_lock() for CTL_ENABLE, reason 1
2017-02-28 11:46:03.360 02001000   631286030 DMON: start task execution: for metadata resynchronization
2017-02-28 11:46:03.360 02001000   631286030 DMON: status from posting standby instances for RESYNCH = ORA-00000
2017-02-28 11:46:03.360                      INSV: Received message for inter-instance publication
2017-02-28 11:46:03.361                            req ID 1.1.631286030, opcode CTL_ENABLE, phase RESYNCH, flags 8005
2017-02-28 11:46:03.361 02001000   631286030 DMON: Metadata available (1.1.631286030), loading from "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
2017-02-28 11:46:03.361 02001000   631286030       Opcode = CTL_ENABLE, Chief Instance I_ID = 1
2017-02-28 11:46:03.364                      DMON Registering service DBIT121_SITE2_DGB with listener(s)
2017-02-28 11:46:03.364                      DMON: Executing SQL [ALTER SYSTEM REGISTER]
2017-02-28 11:46:03.365                      SQL [ALTER SYSTEM REGISTER] Executed successfully
02/28/2017 11:46:06
Creating process RSM0


oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:46 dr1DBIT121_SITE2.dat

Again, if you do any property change again, the second broker file will be created.

4.) We lose both copies of the broker config file on the primary while it is up and running

There is not much difference with the scenario we have seen with the standby. The broker just reads from memory and as soon as any update to the config is done, the broker file is created again. There will be no entries in the drc.log or the alert.log. The broker file is just silently recreated.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
ls: cannot access dr*: No such file or directory

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager
[W000 02/28 12:28:45.07] Connecting to database using .
[W000 02/28 12:28:45.10] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 12:28:45.10] Oracle database version is '12.1.0.2.0'
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 9 seconds ago)


DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
<DO_CONFIGURE_DRC version="12.1"><EDIT_DRC><PROPERTY name="faststartfailoverthreshold" value="40" context="0"/></EDIT_DRC></DO_CONFIGURE_DRC>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
Property "faststartfailoverthreshold" updated


oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 12:30 dr2DBIT121_SITE1.dat

 

5.) We lose both copies of the broker config file on the primary while it is shut down

Let’s do the whole thing again while the primary DB is shutdown.

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

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
ls: cannot access dr*: No such file or directory

In the drc.log log file you can immediately see that there is something wrong.

>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat"
      dg_broker_config_file2 = "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat"
2017-02-28 12:34:45.866                      DMON: Attach state object
2017-02-28 12:34:45.866                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat", retrying
2017-02-28 12:34:46.876                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat"
2017-02-28 12:34:46.880                        ORA-27037: unable to obtain file status
2017-02-28 12:34:46.881                        Linux-x86_64 Error: 2: No such file or directory
2017-02-28 12:34:46.881                        Additional information: 3
2017-02-28 12:34:46.881                      DMON: Error opening "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat", error = ORA-16572
2017-02-28 12:34:46.881                      DMON: Establishing "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat" as the more current file
2017-02-28 12:34:46.882                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat", retrying
2017-02-28 12:34:47.899                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat"
...
2017-02-28 12:35:02.058 7fffffff           0 DMON: Entered rfm_release_chief_lock() for CTL_BOOTSTRAP
2017-02-28 12:35:02.424                      Fore: Continuing with primary evaluation, rfmsp.drc_status_rfmp = ORA-16532
2017-02-28 12:35:03.507                      Fore: Initiating post-open tasks
2017-02-28 12:35:09.192                      DMON: Initiating post-open tasks
2017-02-28 12:35:22.242 00000000  1934847279 DMON: GET_FSFO will be retired
2017-02-28 12:35:22.242 00000000  1934847279       severity = ORA-16501, status = ORA-16532
2017-02-28 12:35:22.242 00000000  1934847279 DMON: GET_FSFO operation completed
2017-02-28 12:35:52.250 00000000  1934847280 DMON: GET_FSFO will be retired
2017-02-28 12:35:52.250 00000000  1934847280       severity = ORA-16501, status = ORA-16532
2017-02-28 12:35:52.251 00000000  1934847280 DMON: GET_FSFO operation completed

If you take a look at the alert.log, everything is fine.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE1
[W000 02/28 12:39:01.18] Connecting to database using DBIT121_SITE1.
[W000 02/28 12:39:02.28] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 12:39:02.28] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

But if connected to the standby, you will see a clear error message: ORA-16532: Oracle Data Guard broker configuration does not exist.

DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 12:39:18.81] Connecting to database using DBIT121_SITE2.
[W000 02/28 12:39:19.90] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 12:39:19.90] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    Error: ORA-16532: Oracle Data Guard broker configuration does not exist

    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 0 seconds ago)

Taking a close look at the error message, it does not give you a hint how to correct the issue.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] oerr ora 16532
16532, 00000, "Oracle Data Guard broker configuration does not exist"
// *Cause:  A broker operation was requested that required a broker
//          configuration to already be created.
// *Action: Create a Data Guard broker configuration prior to performing
//          other broker operations. If only one instance of a RAC
//          database is reporting this error, ensure that the
//          DG_BROKER_CONFIG_FILE[1|2] initialization parameters are
//          set to file locations that are shared by all instances of
//          the RAC database.

Let’s try the same trick, as we have done on the standby by simply enabling the database again.

DGMGRL> enable database 'DBIT121_SITE1';
<DO_CONTROL version="12.1"><DO_COMMAND type="Enable" object_id="16777216"/></DO_CONTROL>
<RESULT ><MESSAGE ><FAILURE  error_num="16532" error_prefix="ORA"><ERROR_TEXT >ORA-16532: Oracle Data Guard broker configuration does not exist
</ERROR_TEXT></FAILURE></MESSAGE><MESSAGE ><FAILURE  error_num="16625" error_prefix="ORA" error_tag1="DBIT121_SITE1"><ERROR_TEXT >ORA-16625: cannot reach database &quot;DBIT121_SITE1&quot;
</ERROR_TEXT></FAILURE></MESSAGE></RESULT>
<RESULT ><MESSAGE ><FAILURE  error_num="16532" error_prefix="ORA"><ERROR_TEXT >ORA-16532: Oracle Data Guard broker configuration does not exist
</ERROR_TEXT></FAILURE></MESSAGE><MESSAGE ><FAILURE  error_num="16625" error_prefix="ORA" error_tag1="DBIT121_SITE1"><ERROR_TEXT >ORA-16625: cannot reach database &quot;DBIT121_SITE1&quot;
</ERROR_TEXT></FAILURE></MESSAGE></RESULT>
Failed.

Hmmmm … does not look good. To recovery from that situation, we need to figure out the latest broker file version on the standby, which is dr2DBIT121_SITE2.dat is my case, and copy it over to the primary. Before doing that, we need to stop the broker on the primary.

SQL> alter system set dg_broker_start=false;

System altered.

Now we can copy the latest version over.

-- standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -rlth dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 12:30 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 12:33 dr2DBIT121_SITE2.dat

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] scp -p dr2DBIT121_SITE2.dat oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat
dr2DBIT121_SITE2.dat                                                                          100%   12KB  12.0KB/s   00:00

And optionally create the second broker file on the primary.

-- primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] cp -p dr1DBIT121_SITE1.dat dr2DBIT121_SITE1.dat
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

Last but not least, we need to enable the broker again.

SQL> alter system set dg_broker_start=true;

System altered.

DGMGRL> connect sys/manager@DBIT121_SITE1
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)
6.) We lose both copies of the broker config files on the primary and the standby (quite unlikely to happen)

This scenario is quite unlikely to happen, but if it happens it is good to have your Data Guard configuration as a script in place.

-- shutdown primary
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- shutdown standby
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Remove the broker files on the primary and the standby

-- primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

-- standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

Now, after we have lost everything, meaning all broker config files, the only chance that we have is to create it from scratch. It is quite cool, if you have the configuration is place, because in a blink of an eye you recreate the whole configuration.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] cat broker.cfg
CONNECT SYS/manager@DBIT121_SITE1
REMOVE CONFIGURATION;
sql "alter system set log_archive_dest_2=''";
CREATE CONFIGURATION 'DBIT121' AS
PRIMARY DATABASE IS 'DBIT121_SITE1'
CONNECT IDENTIFIER IS 'DBIT121_SITE1';
ADD DATABASE 'DBIT121_SITE2' AS
CONNECT IDENTIFIER IS 'DBIT121_SITE2';
EDIT DATABASE 'DBIT121_SITE1' SET PROPERTY StandbyFileManagement='AUTO';
EDIT DATABASE 'DBIT121_SITE2' SET PROPERTY StandbyFileManagement='AUTO';
EDIT DATABASE 'DBIT121_SITE1' SET PROPERTY LogXptMode='SYNC';
EDIT DATABASE 'DBIT121_SITE2' SET PROPERTY LogXptMode='SYNC';
ENABLE CONFIGURATION;
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
EDIT DATABASE 'DBIT121_SITE1' SET PROPERTY FastStartFailoverTarget='DBIT121_SITE2';
EDIT DATABASE 'DBIT121_SITE2' SET PROPERTY FastStartFailoverTarget='DBIT121_SITE1';
sql "alter system archive log current";
sql "alter system register";
SHOW CONFIGURATION;

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] dgmgrl -debug -xml < broker.cfg > broker.log 2>&1
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121]

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)
Conclusion

Loosing broker configuration files is not the end of the world. As you have seen in the blog, it makes quite a huge difference if you loose the broker file while the DB is up and running or it is shut down. In case you haven’t lost all of them you can recover them in most of the situations. However, the alert.log is not a good candidate to check if the broker files are missing. So better scan the alert.log and the drc.log with your preferred monitoring tool to get the whole picture. Besides that, it is good practice to adjust your broker.cfg file after you have done some changes because it makes it very easy to rebuild the whole broker config from scratch.

 

Cet article Oracle 12c – How to Recover lost DataGuard Broker Configuration Files est apparu en premier sur Blog dbi services.

Introducing AlwaysOn availability groups on Linux

Tue, 2017-02-28 06:13

A couple of days ago, Microsoft has rolled out the SQL Server vNext CTP 1.3 which includes very nice features and the one that interested me in the supportability of AlwaysOn availability groups on Linux. That is definitely a good news because we may benefit from new ways of architecting high-availability. There are a lot of new cool enhancements, which we’ll try to study when writing new blog posts. In this first blog, let’s learn new available configuration options.

First of all, let’s say that configuring availability groups on Linux is not different than configuring them on Windows in workgroup mode (domainless configuration) from SQL Server perspective.

Basically, the same steps remain as it is (please refer to the Microsoft installation documentation):

  • Creating login and users on each replica
  • Creating certificate and grant authorization to the corresponding user on each replica
  • Creating endpoint for data mirroring and grant permission connected to the corresponding certificate

So let’s just set the scene before moving forward on the other installation steps. I used for my demo two virtual machines on Hyper-V which run on Linux CentOS 7 (LINUX02 and LINUX04) . I also installed two SQL Server instances (CTP 1.3) on each machine which will run on the top of the cluster infrastructure with Pacemaker and Corosync.

blog 119 - 00 - ag linux - demo infra

Obviously this time the NFS server is not part of this infrastructure and this time I used a symmetric storage on each virtual machine which includes two mount points and two ext4 partitions (respectively /SQL_DATA and /SQL_LOG to host my AdventureWorks2012 database files).

[mikedavem@linux02 ~]$ cat /etc/fstab

/dev/mapper/cl-root     /                       xfs     defaults        0 0
UUID=d6eb8d27-35c7-4f0f-b0c1-42e380ab2eca /boot                   xfs     defaults        0 0
/dev/mapper/cl-swap     swap                    swap    defaults        0 0
/dev/sdb1       /sql_data       ext4    defaults 0 0
/dev/sdc1       /sql_log        ext4    defaults 0 0

 

SELECT 
	name AS logical_name,
	physical_name
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks2012')

 

blog 119 - 01 - ag linux - adventurworks2012 files

First step to enable the AlwaysOn feature: we have to use the mssql-conf tool (which replaces the famous SQL Server configuration manager) with the new option set hadrenabled 1

Then if we use the Linux firewall on each machine, we have to open the corresponding TCP endpoint port on the Linux firewall on each Linux machine.

[root@linux02 data]firewall-cmd --zone=public --add-port=5022/tcp --permanent
success
[root@linux02 data]firewall-cmd –reload 
success
[root@linux02 data]firewall-cmd --permanent --zone=public --list-all
public
  target: default
  icmp-block-inversion: no
  interfaces:
  sources:
  services: dhcpv6-client high-availability mountd nfs rpc-bind ssh
  ports: 1433/tcp 5022/tcp
  protocols:
  masquerade: no
  forward-ports:
  sourceports:
  icmp-blocks:
  rich rules:

[root@linux04 ~]firewall-cmd --zone=public --add-port=5022/tcp --permanent
success
[root@linux04 ~]firewall-cmd --reload

 

No really new stuff so far … The most interesting part comes now. After installing SQL Server on Linux and achieving endpoint configurations, it’s time to create the availability group. But wait, at this stage we didn’t install any clustering part right? And in fact, we don’t have to do this. We are now able to create an availability group without any cluster dependencies by using a new T-SQL parameter CLUSTER_TYPE = NONE as follows. Very interesting because we may think about new scenarios where only read-scalability capabilities are considered on DR site. In this case we don’t have to setup additional cluster nodes which may lead to manageability overhead in this case.

We may also use the direct seeding mode feature available since SQL Server 2016 to simplify the process of adding a database in the corresponding availability group (AdventureWorks2012 database in my case).

:CONNECT LINUX02 -U sa -P Xxxxx

CREATE AVAILABILITY GROUP [agLinux]
WITH 
(
	DB_FAILOVER = ON, --> Trigger the failover of the entire AG if one DB fails 
	CLUSTER_TYPE = NONE --> SQL Server is not a member of a Windows Server Failover Cluster 
)
FOR REPLICA ON
N'LINUX02' 
WITH 
(
	ENDPOINT_URL = N'tcp://192.168.5.18:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC, --> Use direct seeding mode
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'LINUX04' 
WITH 
( 
	ENDPOINT_URL = N'tcp://192.168.5.20:5022', 
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
	FAILOVER_MODE = AUTOMATIC,
	SEEDING_MODE = AUTOMATIC, --> Use direct seeding mode
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

ALTER AVAILABILITY GROUP [agLinux] 
GRANT CREATE ANY DATABASE;
GO

:CONNECT LINUX04 -U sa -P Xxxxx
ALTER AVAILABILITY GROUP [agLinux] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [agLinux] GRANT CREATE ANY DATABASE;
GO


:CONNECT LINUX02 -U sa -P Xxxxx
ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL;
GO
BACKUP DATABASE [AdventureWorks2012] TO DISK = 'NUL';
GO
-- Add the AdventureWorks2012 database to the agLinux AG
ALTER AVAILABILITY GROUP [agLinux] ADD DATABASE [AdventureWorks2012];
GO

 

After configuring the agLinux availability group let’s have a look at the different DMVs I usually use in this case:

  • sys.dm_hadr_availability_group_states
  • sys.dm_hadr_availability_replica_states
  • sys.dm_hadr_database_replica_states
-- groups info
SELECT 
	g.name as ag_name,
	rgs.primary_replica, 
	rgs.primary_recovery_health_desc as recovery_health, 
	rgs.synchronization_health_desc as sync_health
FROM sys.dm_hadr_availability_group_states as rgs
JOIN sys.availability_groups AS g
				 ON rgs.group_id = g.group_id

-- replicas info
SELECT 
	g.name as ag_name,
	r.replica_server_name,
	rs.is_local,
	rs.role_desc as role,
	rs.operational_state_desc as op_state,
	rs.connected_state_desc as connect_state,
	rs.synchronization_health_desc as sync_state,
	rs.last_connect_error_number,
	rs.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS r
	ON rs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
	ON g.group_id = r.group_id

-- DB level          
SELECT 
	g.name as ag_name,
	r.replica_server_name,
	DB_NAME(drs.database_id) as [database_name],
	drs.is_local,
	drs.is_primary_replica,
	synchronization_state_desc as sync_state,
	synchronization_health_desc as sync_health,
	database_state_desc as db_state
FROM sys.dm_hadr_database_replica_states AS drs
		 JOIN sys.availability_replicas AS r
		  ON r.replica_id = drs.replica_id
		 JOIN sys.availability_groups AS g
		  ON g.group_id = drs.group_id
ORDER BY g.name, drs.is_primary_replica DESC;
GO

 

Here the corresponding output:

blog 119 - 1 - ag linux - ag status

Ok everything seems to be ok. We have configured an availability group which includes two replicas and synchronous replication so far. But maybe you have already noticed we didn’t create any listener and the reason is pretty obvious: if we refer to previous versions of SQL Server, creating a listener requires to create a corresponding CAP on the WSFC side and in our context, no cluster exists at this stage. So go ahead and let’s do it. You may refer to the Microsoft documentation to add the pacemaker cluster to the existing infrastructure.

I didn’t expect a big change compared to my last installation for SQL Server FCI on Linux for the basics but for the integration of the AG resource it will probably be another story. Indeed, the declaration of the resource (ocf:mssql:ag) is completely different compared to the SQL Server FCI (ocf::sql:fci). In this case, we have to create a multi-state resource which may allow us to start multiples instances of the AG resource and have them on each concerned replica. Multi-state resources are specialized clone resources which may be in one of two operating modes – master and slave. We run exactly on the same concept with availability groups because we consider to use one primary replica (master) and at least one secondary (slave). According to the Microsoft documentation we have to setup meta-properties which limit the number of resource copies (on replicas) as well as the number of master resources (only one primary replica at time in our case).

[mikedavem@linux02 ~]$ sudo pcs resource create sqllinuxaag ocf:mssql:ag ag_name=agLinux --master meta master-max=1 master-node-max=1 clone-max=2 clone-node-max=1

 

Creating a multi-state resource generates two separate resources regarding their role. In my case, I will get respectively sqllinuxaag-master (for the resource enrolled as Master resource) and sqllinuxaag-slave (for resource(s) enrolled as Slave resource(s)). We have to define monitoring for both as follows:

[mikedavem@linux02 ~]$ sudo pcs resource op sqllinuxaag monitor interval=11s timeout=60s role=Master
[mikedavem@linux02 ~]$ sudo pcs resource op sqllinuxaag monitor interval=12s timeout=60s role=Slave

 

Finally, we have to create and associate a virtual IP address as follows. The virtual IP must run at the same location than the master resource in our case (INFINITY is mandatory here)

[mikedavem@linux02 ~]$ sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.5.30
[mikedavem@linux02 ~]$ sudo pcs constraint colocation add virtualip sqllinuxaag-master INFINITY with-rsc-role=Master

 

Here a recap of the existing constraints which concern my availability group resource:

[mikedavem@linux02 ~]$ sudo pcs constraint
Location Constraints:
Ordering Constraints:
  promote sqllinuxaag-master then start virtualip (kind:Mandatory)
Colocation Constraints:
  virtualip with sqllinuxaag-master (score:INFINITY) (with-rsc-role:Master)
Ticket Constraints:

 

We may notice the global state of the resources and their roles (Master/Slave)

[mikedavem@linux02 ~]$ sudo pcs status
Cluster name: clustlinuxag
Stack: corosync
Current DC: linux04.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum
Last updated: Mon Feb 27 06:16:03 2017          Last change: Mon Feb 27 06:14:11 2017 by root via cibadmin on linux02.dbi-services.test
…

Full list of resources:

 Master/Slave Set: sqllinuxaag-master [sqllinuxaag]
     Masters: [ linux02.dbi-services.test ]
     Slaves: [ linux04.dbi-services.test ]
 virtualip      (ocf::heartbeat:IPaddr2):       Started linux02.dbi-services.test

 

So, now let’s perform some failover tests. I used a basic PowerShell script to connect to my availability group by using the sqllinuxaag resource and return a response (OK and the server name of the concerned replica or KO). But let’s say the resource is not considered as a listener from the availability group. No listener exists at this stage.

  • First test:

The first test consisted in switching manually over the sqllinuxaag (master) to the next available node (LINUX02). At this stage, we can’t use neither the wizard nor T-SQL statement to trigger a failover event. This is a limitation (explained by Microsoft) and I expect to see it to disappear in the future. Keeping the control of such action from SQL Server side will make more sense for DBAs.

[mikedavem@linux04 ~]$ sudo pcs resource move sqllinuxaag-master linux02.dbi-services.test --master

 

During the failover event, no way to reach out the resource but the situation went back into normal as expected.

blog 119 - 2 - ag linux - ag failover manual test

  • Second test

The second test consisted in simulating “soft” failure by changing the state of the new active node (LINUX02) to standby in order to trigger a switch over the next available cluster node (LINUX04). But before going on this way, let’s configure stickiness to avoid unexpected failback of the sqllinuxaag-master resource when the situation will go back to normal.

[mikedavem@linux04 ~]$ sudo pcs resource defaults resource-stickiness=INFINITY
[mikedavem@linux04 ~]$ sudo pcs cluster standby linux02.dbi-services.test

[mikedavem@linux04 ~]$ sudo pcs status
Cluster name: clustlinuxag
Stack: corosync
Current DC: linux02.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum
Last updated: Sun Feb 26 18:55:01 2017          Last change: Sun Feb 26 18:54:42 2017 by root via crm_attribute on linux04.dbi-services.test

2 nodes and 3 resources configured

Node linux02.dbi-services.test: standby
Online: [ linux04.dbi-services.test ]

Full list of resources:

 Master/Slave Set: sqllinuxaag-master [sqllinuxaag]
     Masters: [ linux04.dbi-services.test ]
     Stopped: [ linux02.dbi-services.test ]
 virtualip      (ocf::heartbeat:IPaddr2):       Started linux04.dbi-services.test

 

Same result than previously. During the failover event, the resource was unreachable but after the situation went back to normal, the application was able to connect again.

blog 119 - 3 - ag linux - ag failover with standby test

We may also confirm the state of the availability group by using usual DMVs. The replica role has switched between replicas as expected and the synchronization state remained in healthy state.

blog 119 - 4- ag linux - ag failover with standby test

  • Third test

My third test consisted in simulating a network outage between my two cluster nodes and the infrastructure responded well and performed the necessary tasks to recover the situation.

It was a quick introduction to the new capabilities offered by the SQL Server vNext in terms of HA and availability groups. Other scenarios and tests as well will come soon. In any event, availability groups feature is very popular as well as Linux in my area and getting the way to mix the both will probably be a good argument for customer adoption.   We will see in a near future!

See you

 

Cet article Introducing AlwaysOn availability groups on Linux est apparu en premier sur Blog dbi services.

Oracle 12cR2: Online tablespace encryption

Sun, 2017-02-26 13:57

By default, all data is visible in the datafiles. Transparent Tablespace Encryption (TDE) can be used to get them encrypted. It requires Enterprise Edition plus Advanced Security Option. Except in the Oracle Cloud when it is available – and mandatory – in all editions. And we can foresee that security policies will be enforced in the future years, by law or because companies realize their files can be stolen. This means that lot of databases will have to be encrypted, and this may take too long to do it during a maintenance window. In 12.2 we can encrypt online. Online means that we can do it while our application is running, but of course there is a performance overhead on the system.

I’ve run a SLOB workload with reads and writes (PCT_UPDATE=25). Four times the same workload:

  • during the first one, I encrypted the tablespace online
  • the second one is running on the encrypted tablespace
  • during the third one, I decrypted the tablespace online
  • the fourth one is running on the decrypted tablespace

Here is the ASH visualized with Orachrome Lighty:

Online_encryption_ASH_Response_Time

The dark blue is ‘db file sequential read’ is ny 4 SLOB sessions activity. Light blue is all background activity (DBWR, LGWR) and the encrypt/decrypt (db file parallel write).
The green is CPU activity. The brown is free buffer gets: DBWR can’t keep up with the rate of changes we are doing, while encrypting the tablespace.

You may wonder how I was able to have un-encrypted tablespaces on 12.2 which is available only on the Oracle Cloud where encryption is mandatory. This is explained in Oracle Public Cloud 12cR2: TDE is not an option. This means that I created the SLOB database and I have created the wallet.

Configure the TDE keystore

I’ve created the directory
mkdir -p /u01/app/oracle/admin/SLOB/tde_wallet

I declared it in sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/SLOB/tde_wallet)))

I created the wallet
administer key management create keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Opened it
administer key management set keystore open identified by oracle;

Created the master key
administer key management set key identified by oracle with backup;

Optionally created an auto-login wallet
administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Statspack

I’ll show some statistics for the following runs: Inital (when not encrypted), Encryption (when encryption is running concurrently), Encrypted (when tablespace encryption has been completed), Decryption (when decrypt is running concurrently) and Decrypted (once decryption is completed).

Run on non-encrypted tablespace

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.0 0.1 0.02 6.39
DB CPU(s): 0.4 0.0 0.00 0.68
Redo size: 1,064,743.9 18,829.0
Logical reads: 15,635.7 276.5
Block changes: 7,293.4 129.0
Physical reads: 9,451.4 167.1
Physical writes: 3,303.2 58.4

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 817,591 1,000 1 48.9
log file parallel write 12,258 408 33 20.0
db file parallel write 18,284 217 12 10.6
CPU time 128 6.3
db file parallel read 46,263 121 3 5.9

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 26-27
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,201.2 100.0
DB CPU 127.8 10.6
PL/SQL execution elapsed time 1.7 .1
parse time elapsed 0.0 .0
connection management call elapsed 0.0 .0
hard parse elapsed time 0.0 .0
Tablespace encryption elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 1,201.7

Run during tablespace encryption

As soon as I started this SLOB run, I started the encryption:
alter tablespace IOPS encryption encrypt;

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.8 0.1 0.03 8.27
DB CPU(s): 0.4 0.0 0.00 0.72
Redo size: 644,447.5 18,839.1
Logical reads: 9,441.5 276.0
Block changes: 4,412.8 129.0
Physical reads: 5,702.6 166.7
Physical writes: 1,952.0 57.1

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 28-29
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,455.0 99.9
DB CPU 126.9 8.7
Tablespace encryption elapsed time 15.4 1.1
Tablespace encryption cpu time 12.1 .8


Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 546,294 660 1 30.8
free buffer waits 30,704 325 11 15.2
log file parallel write 8,057 304 38 14.2
db file parallel write 9,929 260 26 12.1
db file async I/O submit 6,304 185 29 8.7

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 2,860,788 9,441.5 276.0
consistent gets 2,154,358 7,110.1 207.9
blocks decrypted 850,557 2,807.1 82.1
blocks encrypted 1,042,777 3,441.5 100.6

Run on encrypted tablespace

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.0 0.1 0.02 6.95
DB CPU(s): 0.5 0.0 0.00 0.95
Redo size: 1,057,446.8 18,806.5
Logical reads: 15,534.1 276.3
Block changes: 7,248.4 128.9
Physical reads: 9,415.8 167.5
Physical writes: 3,266.7 58.1

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 30-31
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,201.1 99.9
DB CPU 164.7 13.7
Tablespace encryption elapsed time 19.0 1.6
Tablespace encryption cpu time 10.1 .8

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 824,329 958 1 47.3
log file parallel write 12,207 416 34 20.5
db file parallel write 17,405 202 12 10.0
CPU time 166 8.2
db file parallel read 46,394 113 2 5.6

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 4,706,832 15,534.1 276.3
consistent gets 3,546,519 11,704.7 208.2
blocks decrypted 2,852,666 9,414.7 167.4
blocks encrypted 989,254 3,264.9 58.1

Run during tablespace decryption

As soon as I started this SLOB run, I started the decryption:
alter tablespace IOPS encryption decrypt;

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.9 0.2 0.04 7.56
DB CPU(s): 0.4 0.0 0.00 0.61
Redo size: 606,680.3 19,111.0
Logical reads: 8,817.1 277.8
Block changes: 4,121.4 129.8
Physical reads: 5,294.9 166.8
Physical writes: 1,827.2 57.6

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 515,429 629 1 25.9
free buffer waits 34,335 362 11 14.9
log file parallel write 7,287 293 40 12.1
direct path write 7,703 275 36 11.3
db file parallel write 9,966 270 27 11.1

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 32-33
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,464.6 99.9
DB CPU 117.9 8.0
Tablespace encryption elapsed time 9.4 .6
Tablespace encryption cpu time 4.6 .3

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 2,662,776 8,817.1 277.8
consistent gets 2,001,129 6,626.3 208.7
blocks decrypted 1,026,940 3,400.5 107.1
blocks encrypted 696,105 2,305.0 72.6

Run on decrypted tablespace

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.0 0.1 0.02 6.79
DB CPU(s): 0.4 0.0 0.00 0.72
Redo size: 1,060,856.5 18,876.7
Logical reads: 15,565.8 277.0
Block changes: 7,258.6 129.2
Physical reads: 9,418.8 167.6
Physical writes: 3,330.5 59.3

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 818,717 999 1 42.1
log file parallel write 11,799 421 36 17.8
direct path write 8,887 299 34 12.6
db file parallel write 18,817 222 12 9.4
CPU time 129 5.4

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 34-35
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,201.1 100.0
DB CPU 127.1 10.6
PL/SQL execution elapsed time 1.7 .1
parse time elapsed 0.0 .0
connection management call elapsed 0.0 .0
Tablespace encryption cpu time 0.0 .0
Tablespace encryption elapsed time 0.0 .0

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 4,685,294 15,565.8 277.0
consistent gets 3,528,610 11,723.0 208.6
blocks decrypted 271 0.9 0.0
blocks encrypted 10 0.0 0.0

Observations

During encryption and decryption, we have contention on ‘free buffer waits’. When running a workload that is I/O bound, and with updates, the DBWR cannot keep-up when encryption/decryption is running in parallel. Online encryption works like online datafile move: there is a double write, one the the current file and one to the file encrypted one. Only when completed, the reads and writes are directed to ne new file and the old one is removed.

The statistics ‘blocks decrypted’ and ‘block encrypted’ are related to reads and writes from an encrypted tablespace.

The Time Model ‘Tablespace encryption’ statistics are significant only when the tablespace is encrypted, or during encryption/decryption. But the time is not so significant: 1% of DB Time. I’m not completely sure about how to interpret it and it is not yet documented. From my test, it looks like it measures the overhead of reading from encrypted tablespaces.

But fore sure, having the tablespaces encrypted is not a big overhead, and online encryption can be useful to avoid a large maintenance window (it can take few hours to encrypt hundred of GB) but don’t run it at a time where you have lot of modifications.

 

Cet article Oracle 12cR2: Online tablespace encryption est apparu en premier sur Blog dbi services.

12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard

Sun, 2017-02-26 09:32

You can accept to do NOLOGGING operations on bulk loads or index build according that you do a backup just after, and that your recovery plan mentions how to load the data again in case of media recovery. With a standby database, we usually force logging because we want redo to be generated for all operations in order to ship it and apply it on standby database. 12.2 brings a new solution: do nologging operations, without generating redo, and then ship the blocks to the standby. This is done on the standby by RMAN.

On primary ORCLA

I create the demo table
SQL> create table DEMO tablespace users pctfree 99 as select rownum n from xmltable('1 to 1000');
Table created.

put it in NOLOGGING
SQL> alter table DEMO nologging;
Table altered.

The database is not in force logging:
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO

Here is a direct-path insert
SQL> insert /*+ append */ into DEMO select rownum n from xmltable('1 to 100000');
100000 rows created.
 
SQL> commit;
Commit complete.

My rows are here:
SQL> select count(*) from DEMO;
COUNT(*)
----------
200000

This is a nologging operation. Media recovery is not possible. The datafile needs backup:
RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
7 full or incremental /u01/oradata/ORCLA/users01.dbf

On ADG standby ORCLB

In Active Data Guard, I can query the table, but:
SQL> select count(*) from DEMO
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 16966)
ORA-01110: data file 7: '/u01/oradata/ORCLB/datafile/o1_mf_users_dbvmwdqc_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The blocks were not replicated because redo was not generated by the primary and then not shipped and applied on the standby.

Note that this is not identifed by RMAN on the standby:
RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
 
RMAN>

recover nonlogged blocks

If I try some recovery here, I can’t because I’m still is apply mode, here is the message I get if I try:
ORA-01153: an incompatible media recovery is active

Let’s stop the apply:
DGMGRL> edit database orclb set state=apply-off;
Succeeded.

In 12.1 I can recover the datafile from the primary with ‘recover from service’ but in 12.2 there is no need to ship the whole datafile. The non-logged block list has been shipped to the standby, recorded in the standby controlfile, and we can list them from v$nonlogged_block.

And we can recover them with a simple command: RECOVER DATABASE NONLOGGED BLOCK

RMAN> recover database nonlogged block;
Starting recover at 25-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
 
starting recovery of nonlogged blocks
List of Datafiles
=================
File Status Nonlogged Blocks Blocks Examined Blocks Skipped
---- ------ ---------------- --------------- --------------
1 OK 0 0 104959
2 OK 0 0 18783
3 OK 0 0 62719
4 OK 0 0 8959
7 OK 0 16731 18948
 
Details of nonlogged blocks can be queried from v$nonlogged_block view
 
recovery of nonlogged blocks complete, elapsed time: 00:00:03

Here it is, I can query the table now
SQL> select count(*) from DEMO;
COUNT(*)
----------
200000

I re-enable real-time apply
DGMGRL> edit database orclb set state=apply-on;
Succeeded.

Switchover

Now, what would happen if I do a switchover of failover between the nologging operation and the nonlogged recovery?
I did the same on primary and then:
DGMGRL> switchover to orclb;
Performing switchover NOW, please wait...
Operation requires a connection to database "orclb"
Connecting ...
Connected to "ORCLB"
Connected as SYSDBA.
New primary database "orclb" is opening...
Operation requires start up of instance "ORCLA" on database "orcla"
Starting instance "ORCLA"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "ORCLA"
Switchover succeeded, new primary is "orclb"

I can query the list of nonlogged blocks that was shipped to standby:

SQL> select * from v$nonlogged_block;
 
FILE# BLOCK# BLOCKS NONLOGGED_START_CHANGE# NONLOGGED
---------- ---------- ---------- ----------------------- ---------
NONLOGGED_END_CHANGE# NONLOGGED RESETLOGS_CHANGE# RESETLOGS
--------------------- --------- ----------------- ---------
OBJECT# REASON CON_ID
---------------------------------------- ------- ----------
7 307 16826 2197748
2197825 1396169 22-FEB-17
74006 UNKNOWN 0

But I cannot recover because the database (the old standby that became primary) is opened:
ORA-01126: database must be mounted in this instance and not open in any instance

So what?

This new feature is acceptable if you recover the nonlogged blocks on the standby just after the nologging operation on the primary. This can be used automatically for datawarehouse load, but also manually when doing a reorganization or an application release that touches the data. Just don’t forget the recover on the standby to avoid surprises later. It will not reduce the amount of data that is shipped to the standby, because shipping the blocks is roughly the same as shipping the redo for the direct-path writes. But one the primary you have the performance benefit of nologging operations.

 

Cet article 12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard est apparu en premier sur Blog dbi services.

Oracle 12c – Recreating a Controlfile in a Data Guard environment with noresetlogs

Fri, 2017-02-24 04:44

Sometimes you might run into situations where the controlfile does not represent the backups and archivelogs correctly, because of a mismatch of the control_file_record_keep_time and the RMAN retention. The controlfile has non circular and a circular records. Non circular are e.g. database information, redo threads, datafiles and so on. These non circular records don’t age out, however, they can be reused, e.g. when a tablespace is dropped. The circular records are e.g. the log history, archived logs, backupsets, datafile copies and so on. These records can age out. So, when you have a control_file_record_keep_time of 7 days and a RMAN recovery window of 14 days, then you obviously have a mismatch here. In 11gR2, Oracle stores 37 different record types in the control file, which can be check with:

SELECT type FROM v$controlfile_record_section ORDER BY 1;

12cR1 stores 41 different record types, where the AUXILIARY DATAFILE COPY, MULTI INSTANCE REDO APPLY, PDB RECORD and PDBINC RECORD was added. In 12cR2 there are even more. The TABLESPACE KEY HISTORY record type was added, so you end up with 42 different record types in 12cR2.

If RMAN needs to add new backup set or archive log record to the control file, any records that expired as per the control_file_record_keep_time parameter are overwritten. But coming back to my issue. My controlfile is out of sync with the recovery catalog and in some situation you can’t correct it anymore, even with delete force commands or alike, and you end up with error like the following:

ORA-19633: control file record 8857 is out of sync with recovery catalog

There might be other solutions to fix it, however, I want to have a clean control file and so I am recreating it manually. However, I don’t want to open the DB with resetlogs.

The high level steps to get this done are

  • Disable everything that might interfere with your action e.g. Fast Start Failover, Broker and so on
  • Adjust your control_file_record_keep_time to a higher value
  • Create the controlfile to trace
  • Unregister from RMAN catalog
  • Shutdown immediate and re-create the controlfile
  • Re-catalog your backups and archivelogs
  • Re-register into the RMAN catalog

Ok, let’s get started and disable fast start failover first. We don’t want that the observer to kick in and do any nasty stuff during my action.

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

DGMGRL> disable fast_start failover;
Disabled.

As a next step, I increase the control_file_record_keep_time to a much higher time. The formula is usually CONTROL_FILE_RECORD_KEEP_TIME = retention period + level 0 backup interval + 1. Meaning that with a retention period of 24 days and a weekly level 0 backup, it would be 24+7+1, so at least 32. But I don’t care if my controlfile is 20MB in size 30MB, so I set it directly to 72 days.

-- Primary

SQL> alter system set control_file_record_keep_time=72;

System altered.

-- Standby

SQL> alter system set control_file_record_keep_time=72;

System altered.

The next important step is to create a trace of the controlfile, which can be adjusted manually later on, depending on your needs. Beforehand, I specify a tracefile identifier, so that I easily spot my trace file in the DIAG destination.

SQL> alter session set tracefile_identifier='control';

Session altered.

SQL> alter database backup controlfile to trace noresetlogs;

Database altered.

oracle@dbidg01:/u01/app/oracle/diag/rdbms/dbit121_site1/DBIT121/trace/ [DBIT121] ls -rlt | grep control
-rw-r----- 1 oracle oinstall     101 Feb 24 09:10 DBIT121_ora_25050_control.trm
-rw-r----- 1 oracle oinstall    9398 Feb 24 09:10 DBIT121_ora_25050_control.trc

oracle@dbidg01:/u01/app/oracle/diag/rdbms/dbit121_site1/DBIT121/trace/ [DBIT121] mv DBIT121_ora_25050_control.trc /u01/app/oracle/admin/DBIT121/create/recreate_controlfile.sql

Let’s take a look at the control file trace which was created. It contains nearly everything that we need. Some parts might have to be adjusted, and some parts do not work at all or have to be done in a different way, but we will see later. But in general it is a very good starting point to get the job done.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] cat recreate_controlfile.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="DBIT121_SITE1"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("DBIT121_SITE2")'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=AUTO
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=DBIT121_SITE2
--
-- LOG_ARCHIVE_DEST_2='SERVICE=DBIT121_SITE2'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR AFFIRM NOVERIFY ASYNC=0'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=DBIT121_SITE2'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBIT121" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnop9_.log',
    '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnq4o_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo42k_.log',
    '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo43q_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppn86_.log',
    '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppngb_.log'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 (
--     '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log',
--     '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t89m_.log'
--   ) SIZE 50M BLOCKSIZE 512,
--   GROUP 5 (
--     '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj3b_.log',
--     '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj8m_.log'
--   ) SIZE 50M BLOCKSIZE 512,
--   GROUP 6 (
--     '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tp52_.log',
--     '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tpb4_.log'
--   ) SIZE 50M BLOCKSIZE 512,
--   GROUP 7 (
--     '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twdq_.log',
--     '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twkt_.log'
--   ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_system_d4fjt03j_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_sysaux_d4fjrlvs_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_undotbs1_d4fjvtd1_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_example_d4fjz1fz_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_users_d4fjvqb1_.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 14 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 32 DAYS');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE1'' CONNECT IDENTIFIER  ''DBIT121_SITE1''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE2'' CONNECT IDENTIFIER  ''DBIT121_SITE2''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS  ''SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u99/backup/DBIT121)''');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u02/oradata/DBIT121_SITE1/changetracking/o1_mf_dbx3wgqg_.chg' REUSE;
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_temp_d4fjxn8l_.tmp'
     SIZE 206569472  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log'
 SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t89m_.log'
                                       TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log';
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj3b_.log'
 SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj8m_.log'
                                       TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj3b_.log';
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tp52_.log'
 SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tpb4_.log'
                                       TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tp52_.log';
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twdq_.log'
 SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twkt_.log'
                                       TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twdq_.log';

I am also stopping the broker to avoid any side effects and afterwards I unregister the database from the RMAN catalog. I will re-create it later on with the clean entries.

-- primary

SQL> alter system set dg_broker_start=false;

System altered.

oracle@dbidg01:/home/oracle/ [DBIT121] rman target sys/manager catalog rman/rman@rman

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 24 09:16:17 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=172831209)
connected to recovery catalog database
recovery catalog schema release 12.02.00.01. is newer than RMAN release

RMAN> unregister database;

database name is "DBIT121" and DBID is 172831209

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

RMAN>

The next step is very important. We need to shutdown the DB cleanly, either with normal or immediate. Afterwards, I create a copy of the current controlfiles. You never know, it is always good to have another fallback.

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

oracle@dbidg01:/home/oracle/ [DBIT121] cd /u02/oradata/DBIT121_SITE1/controlfile/
oracle@dbidg01:/u02/oradata/DBIT121_SITE1/controlfile/ [DBIT121] mv o1_mf_d4fjws55_.ctl o1_mf_d4fjws55_.ctl.old
oracle@dbidg01:/u02/oradata/DBIT121_SITE1/controlfile/ [DBIT121] cd /u03/fast_recovery_area/DBIT121_SITE1/controlfile/
oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/controlfile/ [DBIT121] mv o1_mf_d4fjwsgr_.ctl o1_mf_d4fjwsgr_.ctl.old

Now we can startup nomount, and recreate our control from scratch. It is very important that you specify REUSE and NORESETLOGS here.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1325400064 bytes
Fixed Size                  2924112 bytes
Variable Size             436208048 bytes
Database Buffers          872415232 bytes
Redo Buffers               13852672 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "DBIT121" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnop9_.log',
    '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnq4o_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo42k_.log',
    '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo43q_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppn86_.log',
    '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppngb_.log'
 19    ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_system_d4fjt03j_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_sysaux_d4fjrlvs_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_undotbs1_d4fjvtd1_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_example_d4fjz1fz_.dbf',
  '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_users_d4fjvqb1_.dbf'
CHARACTER SET AL32UTF8
 27  ;

Control file created.

SQL>

Now we can configure the RMAN persistent settings like retention and so on.

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 14 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 32 DAYS');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE1'' CONNECT IDENTIFIER  ''DBIT121_SITE1''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE2'' CONNECT IDENTIFIER  ''DBIT121_SITE2''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS  ''SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u99/backup/DBIT121)''');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');

The next step is to the re-create the incarnation table. This might fail with a recursive SQL error if you use the SQL provided in the trace file. Just use REGISTER PHYSICAL LOGFILE instead of REGISTER LOGFILE and then it works.

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_1_%u_.arc';

SQL> ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_142_dbzv31hq_.arc';
ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_142_dbzv31hq_.arc'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_142_dbzv31hq_.arc';

Database altered.

Because I have shutdown the database cleanly, there is no need to do any recovery and I can continue to enable the block change tracking file, open the database, and add my tempfile back to the database.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/oradata/DBIT121_SITE1/changetracking/o1_mf_dbx3wgqg_.chg' REUSE;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_temp_d4fjxn8l_.tmp'
  2  SIZE 206569472  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

Regarding the Standby Redo logs, the easiest is to remove the old ones, and simply recreate them afterwards, because you can’t add them back as long as they have Oracle managed file names.

SQL> select * from v$standby_log;

no rows selected

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log' SIZE 50M BLOCKSIZE 512 REUSE
*
ERROR at line 1:
ORA-01276: Cannot add file
/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log.  File has an Oracle
Managed Files file name.

-- delete standby redo logs

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] cd /u02/oradata/DBIT121_SITE1/onlinelog/
oracle@dbidg01:/u02/oradata/DBIT121_SITE1/onlinelog/ [DBIT121] ls -l
total 358428
-rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_1_d4fpnop9_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_2_d4fpo42k_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 24 09:47 o1_mf_3_d4fppn86_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_4_dbx3t840_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_5_dbx3tj3b_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_6_dbx3tp52_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_7_dbx3twdq_.log
oracle@dbidg01:/u02/oradata/DBIT121_SITE1/onlinelog/ [DBIT121] rm o1_mf_4_dbx3t840_.log o1_mf_5_dbx3tj3b_.log o1_mf_6_dbx3tp52_.log o1_mf_7_dbx3twdq_.log
oracle@dbidg01:/u02/oradata/DBIT121_SITE1/onlinelog/ [DBIT121] cd /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/
oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/ [DBIT121] ls -l
total 358428
-rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_1_d4fpnq4o_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_2_d4fpo43q_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 24 09:47 o1_mf_3_d4fppngb_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_4_dbx3t89m_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_5_dbx3tj8m_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_6_dbx3tpb4_.log
-rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_7_dbx3twkt_.log
oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/ [DBIT121] rm o1_mf_4_dbx3t89m_.log o1_mf_5_dbx3tj8m_.log o1_mf_6_dbx3tpb4_.log o1_mf_7_dbx3twkt_.log

-- recreate standby redo logs

SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 50M BLOCKSIZE 512;

Database altered.

SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 50M BLOCKSIZE 512;

Database altered.

SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 6 SIZE 50M BLOCKSIZE 512;

Database altered.

SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 7 SIZE 50M BLOCKSIZE 512;

Database altered.

Don’t forget to enable Flashback as well, if your DataGuard is running in Max availability mode.

SQL> alter database flashback on;

Database altered.

Now we need to recatalog all our backups and archivelogs again.

oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/ [DBIT121] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 24 09:50:16 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=172831209)

RMAN> catalog recovery area;

using target database control file instead of recovery catalog
searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_140_dbzswh06_.arc
File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_141_dbzsxpv5_.arc
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx641px_.flb
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx642pf_.flb
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dby398lz_.flb
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbymcg20_.flb
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbyyg1r0_.flb
File Name: /u03/fast_recovery_area/DBIT121_SITE1/controlfile/o1_mf_d4fjwsgr_.ctl.old
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64pz6_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64q0b_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s0z_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s3n_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnsnf_TAG20170223T090856_dbx65kmx_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_ncnnf_TAG20170223T090856_dbx65lnt_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090923_dbx65mto_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypdc_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypfp_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpysqh_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy2f_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy56_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnsnf_TAG20170224T080812_dbzpzqnz_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_ncnnf_TAG20170224T080812_dbzpzqop_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080841_dbzpzskt_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_140_dbzswh06_.arc
File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_141_dbzsxpv5_.arc
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64pz6_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64q0b_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s0z_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s3n_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnsnf_TAG20170223T090856_dbx65kmx_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_ncnnf_TAG20170223T090856_dbx65lnt_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090923_dbx65mto_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypdc_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypfp_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpysqh_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy2f_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy56_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnsnf_TAG20170224T080812_dbzpzqnz_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_ncnnf_TAG20170224T080812_dbzpzqop_.bkp
File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080841_dbzpzskt_.bkp

List of Files Which Were Not Cataloged
=======================================
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx641px_.flb
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx642pf_.flb
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dby398lz_.flb
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbymcg20_.flb
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbyyg1r0_.flb
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u03/fast_recovery_area/DBIT121_SITE1/controlfile/o1_mf_d4fjwsgr_.ctl.old
  RMAN-07519: Reason: Error while cataloging. See alert.log.

List of files in Recovery Area not managed by the database
==========================================================
File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_4_dbzwt72f_.log
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_5_dbzwtgl3_.log
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_6_dbzwtn04_.log
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_7_dbzwtvc7_.log
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter

number of files not managed by recovery area is 4, totaling 200.00MB

RMAN>

We are nearly done. We just need to enable the Data Guard broker again and enable fast start failover.

SQL> alter system set dg_broker_start=true;

System altered.

SQL> alter system archive log current;

System altered.

DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> show configuration;

Configuration - DBIT121

 Protection Mode: MaxAvailability
 Members:
 DBIT121_SITE1 - Primary database
 DBIT121_SITE2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS (status updated 21 seconds ago)

DGMGRL> validate database 'DBIT121_SITE2';

 Database Role: Physical standby database
 Primary Database: DBIT121_SITE1

 Ready for Switchover: Yes
 Ready for Failover: Yes (Primary Running)

Re-register the database into the RMAN catalog.

oracle@dbidg01:/home/oracle/ [DBIT121] rman target sys/manager catalog rman/rman@rman

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 24 09:57:34 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=172831209)
connected to recovery catalog database
recovery catalog schema release 12.02.00.01. is newer than RMAN release

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Ready. That’s it. In case your Standby Controlfile is not correct as well (was not in my case), you can now simply create a new standby controlfile on the primary and move it to the standby like documented in the following MOS note (Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1).

Conclusion

As a last resort, sometimes it is needed to recreate the controlfile manually, but in case you have all the online redo logs and your datafiles in place, you can do it with noresetlogs. And take care that your RMAN retention is always higher than your control file record keep time.

 

Cet article Oracle 12c – Recreating a Controlfile in a Data Guard environment with noresetlogs est apparu en premier sur Blog dbi services.

12c Unified Auditing and AUDIT_TRAIL=DB in mixed mode

Thu, 2017-02-23 13:34

Oracle enables some auditing by default, and if you don’t do anything, the tables where it is stored will grow in SYSAUX. Don’t wait to get an alert when it is too late. Everything that fills something automatically must be managed to archive or purge automatically. If not, one day you will have a problem.

Imagine that you have 5 features doing something similar but in a different way because they were implemented one at a time. You want to stop this and have only 1 unified feature. That’s great. But you are also required to maintain compatibility with previous version, which means that you actually implemented a 5+1=6th feature :(

Unified Auditing

This exactly what happens with Unified Auditing. Because of this compatibility requirement, it is declined in two modes:

  • The ‘mixed mode’ that keeps all compatibility as the 5+1 case in my example
  • The ‘pure mode’ that do not take care of the past and is actually the one that unifies all. The real ‘Unified’ one.

You are in ‘mixed mode’ by default and you see it as if there is nothing new enabled:

SQL> select parameter,value from v$option where parameter='Unified Auditing';
 
PARAMETER VALUE
--------- -----
Unified Auditing FALSE

But there may be something enabled if the old auditing is enabled, because it is actually a mixed mode.

AUDIT_TRAIL=DB

Let me explain. I use the old auditing:

SQL> show parameter audit
NAME TYPE VALUE
---------------------------- ------- --------------------------------
audit_trail string DB

This means that I have the default audits (such as logon, logoff, ALTER/CREATE/DROP/GRANT ANY, and so on.
In addition to that, I enabled the audit of create table:

SQL> audit create table;
Audit succeeded.

I do some of these stuff and I can see info in the old audit trail:
SQL> select action_name,sql_text from dba_audit_trail;
 
ACTION_NAME SQL_TEXT
----------- --------
CREATE TABLE
LOGON
SELECT
LOGON
LOGOFF

If you are in that case, you probably manage this trail. Our recommandation is either to disable audit, or to manage it.

But once upgraded to 12c, did you think about managing the new unified audit trail?

SQL> select audit_type,unified_audit_policies,action_name,return_code,count(*) from unified_audit_trail group by audit_type,unified_audit_policies,action_name,return_code order by 1,2,3;
---- ------ ------------------------------------------------------------------ ---- ------------------- ----- -- --------------------------------------------------------- ----- -- ------
AUDIT_TYPE UNIFIED_AUDIT_POLICIES ACTION_NAME RETURN_CODE COUNT(*)
Standard ORA_LOGON_FAILURES LOGON 0 2
Standard ORA_LOGON_FAILURES LOGON 1017 1
Standard ORA_SECURECONFIG CREATE ROLE 0 1
Standard ORA_SECURECONFIG DROP ROLE 0 1
Standard EXECUTE 0 1

Even with Unified Auditing set to off, some operations are audited when AUDIT_TRAIL=DB. If you don’t want them you have to disable them:

noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

As you see, in mixed mode the new unified auditing is enabled, and AUDIT_TRAIL is not ignored. This is the mode to use until you have migrated all your policies and audit trail queries to the new one. However you can see that in mixed mode, there is no double auditing but only new default policies. The old policies are only logged to to old audit trail.

But if you don’t use auditing, then you don’t want the mixed mode.

uniaud_on

This is done with an instance shutdown, relinking onLinux or renaming a ddl on Windows.


SQL> shutdown immediate;
ORACLE instance shut down.
SQL> host ( cd $ORACLE_HOME/rdbms/lib ; make -f ins_rdbms.mk uniaud_&2 ioracle ORACLE_HOME=$ORACLE_HOME )
/usr/bin/ar d /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/12.2.0/dbhome_1/bin
 
- Linking Oracle
...

And then you are in ‘pure mode':


SQL> select parameter,value from v$option where parameter='Unified Auditing';
 
PARAMETER VALUE
--------- -----
Unified Auditing TRUE

In that mode, AUDIT_TRAIL is ignored and you will never see new rows in the old AUD$:

SQL> select action_name,sql_text from dba_audit_trail;
 
no rows selected

However, as in the mixed mode you will have to manage the new audit trail. My best recommandation is to keep it and add a purge job. One day you may want to have a look at unsuccessful logins of the past few days. But you still have the choice to disable the default polices, and then the only things you will see are the operations done on the trail:

AUDIT_TYPE UNIFIED_AUDIT_POLICIES ACTION_NAME SQL_TEXT
---------- ---------------------- ----------- --------
Standard EXECUTE BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;^@
Standard EXECUTE BEGIN dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.audi
Standard EXECUTE BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;^@

The reason is that if a hacker getting super administrator rights has tried to whipe his traces, then at least this suspect operation remains.

Test it

To validate this blog post, I’ve tested all scenarios on 12.2.0.1 with the combination of:

  • audit_trail=db or audit_trail=none
  • uniaud_on or uniaud_off
  • audit or noaudit policy for ORA_SECURECONFIG and ORA_LOGON_FAILURES

For each combination, I’ve purged both audit trails (AUD$ and AUD$UNIFIED) and run a few statements that are logged by default or by explicit audit.

So what?

Basically, the recommandation is still the same as before: either disable the audit or schedule a purge. There is no purge by default because auditing is different than logging. When your security policy is to audit some operations, they must not be purged before being archived, or processed.

When you upgrade to 12c:

  1. If you want to manage only the old audit, then you should disable ORA_LOGON_FAILURES and ORA_SECURECONFIG.
  2. If you want to manage both, then add a job to purge the unified audit trail (audit_trail_type=>dbms_audit_mgmt.audit_trail_unified).
  3. If you don’t use the old auditing, then enable the ‘pure mode’. But then, AUDIT_TRAIL=NONE is ignored, so:
  4. If you don’t use the new unified auditing, then disable ORA_LOGON_FAILURES and ORA_SECURECONFIG.
  5. Or use the new unified auditing and set a job to purge it regularly.

And control the growth of SYSAUX:

SQL> select occupant_name,schema_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like 'AUD%';
 
OCCUPANT_NAME SCHEMA_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
------------- ----------- ------------- ------------------
AUDSYS AUDSYS AUDSYS schema objects 1280
AUDIT_TABLES SYS DB audit tables 0

SYS ‘DB audit tables’ is the old one, filled in ‘mixed mode’ only. AUDSYS ‘AUDSYS schema objects’ is the new unified one, filled in both modes.

But I have something to add. The default policies do not audit something that you are supposed to do so frequently, it should not fills hundreds of MB before several decades.
If you get this during the last hour:
SQL> select audit_type,unified_audit_policies,action_name,return_code,count(*)
2 from unified_audit_trail where event_timestamp>sysdate-1
3 group by audit_type,unified_audit_policies,action_name,return_code
4 order by count(*);
AUDIT_TYPE UNIFIED_AUDIT_POLICIES ACTION_NAME RETURN_CODE COUNT(*)
---------- ---------------------- ----------- ----------- --------
Standard AUDIT 0 2
Standard EXECUTE 0 4
Standard ORA_SECURECONFIG CREATE ROLE 0 9268
Standard ORA_LOGON_FAILURES LOGON 1017 348

then the problem is not auditing but an attack, either from a hacker of because of your application design connecting for each execution or running DDL all the time.

 

Cet article 12c Unified Auditing and AUDIT_TRAIL=DB in mixed mode est apparu en premier sur Blog dbi services.

BLOG Technoark 2017 Conference – Blockchain, beyond the bitcoin.

Thu, 2017-02-23 07:15

BLOG Technoark 2017 Conference – Blockchain, beyond the bitcoin.
and
How applying Blockchain for Enterprise security and data management.
BlockC1

I post a brief personal impression of the TechnoArk Conference 2017 in TechnoArk center of Sierre /Switzerland)
This TechnoArk Center is based on three essentials parts:

• The Ark Accelerator leverages new technologies and guides them to a commercial application. It is also responsible, if necessary, for patenting them.

• The Ark Incubator offers professional coaching to start-up companies. It strengthens the chances of success and promotes their insertion in the economic fabric.

• The Innovation department offers proactive support in the consolidation of your achievements, but especially in the development of new business potential.

Sum-up of this event :

Born with Bitcoins in 2008 and created by Natoshi Sakamoto, Blockchain is a technologic concept, and not a ”software”. Rather it’s a new techno and new decentralized organization.
Blockchain is in same time a peer2peer network, and a distributed database system, which allows to the historic listing of transactions between 2 parts, to become unfalsifiable.
The aim of this event was to show that we can use blockchain in several domains like aeronautic, health, lawyer, energy, eGovernment, gaming and so on.
One presentation per domain was carried out.
Keynotes were organized around case studies, among special interests:
Fintech : it is seen as a foundation techno but it will take time to be seen as a standard. There’s no real application, all in beta version. Smart contract can be used, they could allow real time transaction, immediate reconciliation between traders and sellers.
eHealth : blockchain can be used with Hyperledger software on follow up for oncology as it is a long term disease that implies multiple people, doctors, pharmacists, hospital executives…
eGov : Mike Gault from Guardtime explains how Estonian gov. already used blockchain for Enterprise security and data management.
Energy : Stephan Tual, (founder of the startup slock.it) has explained how blockchains could be used in the service of renewable energies. Notably with regard to the billing with energy consumed.
Aeronautic : Stephane Cheickh explained how blockchains could be useful in civil aviation supervision of the use of drones by businesses and individuals, but also in the management of baggage.
• Others keynotes belonged to a very particular field (like gaming, the legal one and so on)

What we heard :
Blockchain is more than a Technology; It’s a strategy …
Blockchain is the most disruptive technology I have ever seen…

Technical part:

As it is very difficult to find precise technical information about blockchains, let me give you some interesting elements to have in memory for the future markets and contracts:

Definition:
A blockchain is a ledger of records arranged in data batches called blocks (or blocs) that use cryptographic validation to link themselves together.
Put simply, each block references and identifies the previous block by a hashing function, forming an unbroken chain, hence the name.

BlockC2Source : Blockchain France ©

 

Blocks in details :

Block-d-C3

Source : Blockchain France ©

The operation of a ledger can be described like this:

BlockC4Source : Blockchain France ©

 

• We can immediately see that Blockchain’s ledger doesn’t store “data” itself but fingerprints only.
• The second point proves the existence of an object at precise moment, but not authenticity.

 

What is important to remember is that a blockchain is characterized by the 4 following features:

BlockC5Source : Blockchain France ©

• usually contains financial transactions
• is replicated across a number of systems in almost real-time
• usually exists over a peer-to-peer network
• uses cryptography and digital signatures to prove identity, authenticity and enforce read/write access rights
• can be written by certain participants
• can be read by certain participants, maybe a wider audience, and
• has mechanisms to make it hard to change historical records, or at least make it easy to detect when someone is trying to do so

 

Additionals informations :

A string of blocks is a kind of independent, transparent and permanent database coexisting in several places and shared by a community. That is why it is sometimes called a Mutual Distributed Book (MDL) too.
There is nothing new about MDL, the origins of which date back to the 1976 Diffie-Hellman research paper New Directions in Cryptography. But for a long time they were considered complicated and not quite safe.
It took the Blockchain implementation simpler in Bitcoin to change things. The permanence, security and distributed nature of Bitcoin ensured that it was a currency maintained by a growing community but controlled by absolutely no one and incapable of being manipulated.
Throughout this event, it has been objectively demonstrated that this concept has many advantages.
On the other hand, it is more complicated regarding the Swiss law (and law in general), which is not yet ready to absorb the paradigm shifts induced by this new technology. The impacts on society are also not to be overlooked.

Conclusion:
Today big companies are cautiously launching into the use of this concept because they believe that the first to use the blockchains will be ahead of the competition.
And if this concept was to become dangerous, the money invested would have served to better understand the danger.

Ludovic HAYE

dbi-services consultant

 

 

Cet article BLOG Technoark 2017 Conference – Blockchain, beyond the bitcoin. est apparu en premier sur Blog dbi services.

OGG: Unable to lock file “/xxx/de000000″ (error 11, Resource temporarily unavailable).

Wed, 2017-02-22 00:32

When you see the above message in the GoldenGate logfile there are usually orphan processes that prevent GoldenGate from locking the file (typically when your trail files are on NFS). In a case I had at a customer last week this was not the case. It could be confirmed that there are no other processes sitting on the file by doing an fuser on the file on all nodes of the cluster (This was an 8 node Exadata). What we finally needed to do was:

cd [TRAIL_DIRECTORY]
mv de000000 de000000_bak
cp de000000_bak de000000
rm de000000

Once we did this we could start the extract again and GoldenGate was happy. Hope this helps …

 

Cet article OGG: Unable to lock file “/xxx/de000000″ (error 11, Resource temporarily unavailable). est apparu en premier sur Blog dbi services.

12cR2: lockdown profiles and ORA-01219

Tue, 2017-02-21 14:40

When you cannot open a database, you will get some users unhappy. When you cannot open multitenant database, then the number of unhappy users is multiplied by the number of PDBs. I like to encounter problems in my lab before seeing them in production. Here is a case where I’ve lost a file. I don’t care about the tablespace, but would like to put it offline and at least be able to open the database.

ORA-01113

So, it’s my lab, I dropped a file while the database was down. The file belongs to a PDB but I cannot open the CDB:

SQL> startup
ORACLE instance started.
 
Total System Global Area 1577058304 bytes
Fixed Size 8793208 bytes
Variable Size 1124074376 bytes
Database Buffers 436207616 bytes
Redo Buffers 7983104 bytes
Database mounted.
ORA-01113: file 23 needs media recovery
ORA-01110: data file 23: '/tmp/STATSPACK.dbf'

Yes this is a lab, I like to put datafiles in /tmp (lab only) and I was testing my Statspack scripts for an article to be published soon. I’ve removed the file and have no backup. I recommand to do nasty things on labs, because those things sometimes happen on production systems and better be prepared. This recommandation supposes you cannot mistake your lab prompt with a production one of course.

ORA-01157

The database is in mount. I cannot open it:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 23 - see DBWR trace file
ORA-01110: data file 23: '/tmp/STATSPACK.dbf'

This is annoying. I would like to deal with this datafile later and open the CDB. I accept that the PDB it belongs to (PDB1 here) cannot be opened but I wish I can open the other ones quickly.

ORA-01219

Let’s go to the PDB and take the datafile offline:

SQL> alter session set container=pdb1;
Session altered.
 
SQL> alter database datafile 23 offline for drop;
alter database datafile 23 offline for drop
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

This is quite annoying. I know that the database is not open. I know that the pluggable database is not open. I want to put a datafile offline, and this is an operation that concerns only the controlfile. No need to have the database opened. Actually, I need to put this datafile offline in order to open the CDB.

SQL_TRACE

This is annoying, but you know why Oracle is the best database system: troubleshooting. I have an error produced by recursive SQL (ORA-00604) and I want to know the SQL statement that raised this error:


SQL> alter session set sql_trace=true;
alter session set sql_trace=true;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

Oh yes, I forgot that I cannot issue any SQL statement. But you know why Oracle is the best database system: troubleshooting.


SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
Statement processed.
 
SQL> alter database datafile 23 offline for drop;
alter database datafile 23 offline for drop
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
 
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT OFF;
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_20258.trc

Here is the trace:

*** 2017-02-21T13:36:51.239026+01:00 (PDB1(3))
=====================
PARSING IN CURSOR #140359700679600 len=34 dep=0 uid=0 oct=35 lid=0 tim=198187306591 hv=3069536809 ad='7b8db148' sqlid='dn9z45avgauj9'
alter database datafile 12 offline
END OF STMT
PARSE #140359700679600:c=3000,e=71171,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=198187306590
WAIT #140359700679600: nam='PGA memory operation' ela= 30 p1=327680 p2=1 p3=0 obj#=-1 tim=198187307242
WAIT #140359700679600: nam='control file sequential read' ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=198187307612
WAIT #140359700679600: nam='control file sequential read' ela= 13 file#=0 block#=16 blocks=1 obj#=-1 tim=198187307743
WAIT #140359700679600: nam='control file sequential read' ela= 6 file#=0 block#=18 blocks=1 obj#=-1 tim=198187307796
WAIT #140359700679600: nam='control file sequential read' ela= 9 file#=0 block#=1119 blocks=1 obj#=-1 tim=198187307832

This is expected. I’m in PDB1 (container id 3) and run my statement to put the datafile offline.
And then it switches to CDB$ROOT (container 0):

*** 2017-02-21T13:36:51.241022+01:00 (CDB$ROOT(1))
=====================
PARSING IN CURSOR #140359700655928 len=248 dep=1 uid=0 oct=3 lid=0 tim=198187308584 hv=1954812753 ad='7b67d9c8' sqlid='6qpmyqju884uj'
select ruletyp#, ruleval, status, ltime from lockdown_prof$ where prof#=:1 and level#=:2 order by ltime
END OF STMT
PARSE #140359700655928:c=2000,e=625,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=198187308583
=====================
PARSE ERROR #140359700655928:len=249 dep=1 uid=0 oct=3 lid=0 tim=198187308839 err=1219
select ruletyp#, ruleval, status, ltime from lockdown_prof$ where prof#=:1 and level#=:2 order by ltime
 
*** 2017-02-21T13:36:51.241872+01:00 (PDB1(3))
EXEC #140359700679600:c=4000,e=2684,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=198187309428
ERROR #140359700679600:err=604 tim=198187309511

I have a parse error when reading LOCKDOWN_PROF$ in the root container. It is a table, a dictionary table stored in SYSTEM tablespace. The CDB is not open. It is not accessible, reason for the error message.

Then, I remember that I’ve set a lockdown profile at CDB level. It doesn’t make sense for CDB$ROOT, but I’ve set it to get it as default for all new created PDBs. Any statement that may be disabled by a lockdown profile has to read the lockdown profile rules stored in root. And here I learn that this occurs when parsing the DDL statement, not at execution time.

In my opinion this is a bug. Either I should not set pdb_lockdown at CDB level, or it shouldn’t be checked when the CDB is closed. Because then any DDL will fail. I’m not blocked by the lockdown profile here. Just because the lockdown profile cannot be read.

pdb_lockdown

Now I know how to workaround the problem: unset the lockdown profile, offline my datafile, open the CDB, open the PDB, drop the tablespace.

SQL> alter system set pdb_lockdown='';
System altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database datafile 23 offline for drop;
Database altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter database open;

Lockdown profile is a very nice feature allowing fine grain control on what can be done by users on a PDB, even admins ones. But it is a new mecanism, leading to situations we have never seen before. Don’t forget the power (and fun) of troubleshooting.

 

Cet article 12cR2: lockdown profiles and ORA-01219 est apparu en premier sur Blog dbi services.

Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition

Tue, 2017-02-21 09:13

As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby, without disruption, during the switchover/failover. When the database is reopened as the primary, the suspended sessions resume their operations as if nothing had happened. If the database (or an individual PDB) is not opened in the primary role, the sessions will be terminated.
To enable this feature, the STANDBY_DB_PRESERVE_STATES initialization parameter in the standby side is used. This parameter can have following values:
NONE — No sessions on the standby are retained during a switchover/failover.
SESSION or ALL — User sessions are retained during switchover/failover.
This parameter is only meaningful on a physical standby database that is open in real-time query mode. This needs Active dataguard option
In this blog we are going  to do a demonstration of this new feature. First we present below our configuration

DGMGRL> show configuration;
Configuration - ORCL_DR
Protection Mode: MaxProtection
Members:
ORCL_SITE - Primary database
ORCL_SITE1 - Physical standby database
ORCL_SITE2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 32 seconds ago)
DGMGRL>

Now let’s connect to the standby ORCL_SITE1 and let’s note our session’s info (sid, serial#)
SQL>
select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
USERNAME SID SERIAL#
--------------- ---------- ----------
SYSTEM 65 2869


SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL_SITE1


SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

With the default value NONE for the parameter standby_db_preserve_states on ORCL_SITE1 let’s do a switchover to ORCL_SITE1.
SQL>
show parameter standby_db_preserve_states;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_db_preserve_states string NONE
SQL>


DGMGRL> switchover to 'ORCL_SITE1';
Performing switchover NOW, please wait...
Operation requires a connection to database "ORCL_SITE1"
Connecting ...
Connected to "ORCL_SITE1"
Connected as SYSDBA.
New primary database "ORCL_SITE1" is opening...
Operation requires start up of instance "ORCL" on database "ORCL_SITE"
Starting instance "ORCL"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "ORCL_SITE"
Switchover succeeded, new primary is "ORCL_SITE1"

While the switchover going on, let’s start a query on ORCL_SITE1. As expected we get an error, the session was disconnected

SQL> select * from dba_objects;
select * from dba_objects
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1915
Session ID: 65 Serial number: 2869
SQL>

Our new configuration is now like this

DGMGRL> show configuration;
Configuration - ORCL_DR
Protection Mode: MaxProtection
Members:
ORCL_SITE1 - Primary database
ORCL_SITE - Physical standby database
ORCL_SITE2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 58 seconds ago)
DGMGRL>

Now let’s connect to the standby ORCL_SITE with the standby_db_preserve_states set to ALL

SQL> select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
USERNAME SID SERIAL#
--------------- ---------- ----------
SYSTEM 58 58847


SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL_SITE


SQL> show parameter standby_db_preserve_states
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_db_preserve_states string ALL

Now let’s do a switchover back to SITE_ORCL and let’s monitor the connection.

DGMGRL> switchover to 'ORCL_SITE';
Performing switchover NOW, please wait...
Operation requires a connection to database "ORCL_SITE"
Connecting ...
Connected to "ORCL_SITE"
Connected as SYSDBA.
New primary database "ORCL_SITE" is opening...
Operation requires start up of instance "ORCL" on database "ORCL_SITE1"
Starting instance "ORCL"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "ORCL_SITE1"
Switchover succeeded, new primary is "ORCL_SITE"
DGMGRL>

As expected, after the switchover I see that my session is still connected with the same SID and SERIAL#. Indeed user sessions are retained and when the database is reopened as the primary, the suspended sessions resume their operations as if nothing had happened.

SQL> select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
USERNAME SID SERIAL#
--------------- ---------- ----------
SYSTEM 58 58847

Just in the documentation it is mentioned that “Sessions that have long running queries or are using database links will not be retained regardless of the setting of this parameter”.

 

Cet article Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition est apparu en premier sur Blog dbi services.

Oracle 12c – How to correct the error: “RMAN-20005: target database name is ambiguous”

Tue, 2017-02-21 08:09

I do have a Data Guard environment, where I have configured the RMAN DB_UNIQUE_NAME persistent setting for my primary and the standby. With the RMAN DB_UNIQUE_NAME settings I am able to run reports my Oracle Data Guard environment from any database. I could e.g. list all archivelogs for SITE1 from SITE2 or the other ways around.
Or I could show all persistent settings for SITE1 from SITE2 and of course the other way around. The only prerequisite for this feature is the RMAN catalog. In case you are not connected to the RMAN catalog you end up with the following error:

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 02/21/2017 13:58:53
RMAN-05037: FOR DB_UNIQUE_NAME option cannot be used in nocatalog mode

After connecting to the catalog, you can use this feature, e.g. to show the archive deletion policy.

$ rman target sys/welcome1 catalog /@rcat

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 21 14:25:10 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (DBID=644484523)
connected to recovery catalog database

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE1';
RMAN configuration parameters for database with db_unique_name DBIT121_SITE1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK;

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE2';
RMAN configuration parameters for database with db_unique_name DBIT121_SITE2 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

There are quite a lot options which can be combined with the DB_UNIQUE_NAME feature like the following.

LIST ARCHIVELOG ALL FOR DB_UNIQUE_NAME 'DBIT121_SITE2';
REPORT SCHEMA FOR DB_UNIQUE_NAME 'DBIT121_SITE2';
SHOW ALL FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

But getting back to my issue. I was running a resync catalog from my Standby database and ended up with the following error:

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME 'DBIT121_SITE1';

resyncing from database with DB_UNIQUE_NAME DBIT121_SITE1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync from db_unique_name command on default channel at 02/21/2017 13:08:42
RMAN-20005: target database name is ambiguous

RMAN says that the target database name is ambiguous. But what does this mean. Let’s take a look a the RMAN error with the oerr utility. The oerr utility can not only be used with “ORA” error codes like “oerr ora 01555″, but also with “RMAN” error codes.

$ oerr rman 20005
20005, 1, "target database name is ambiguous"
// *Cause: two or more databases in the recovery catalog match this name
// *Action:
//

Ok. This error is much more precise. Looks like that RMAN found more the one database called DBIT121 in the catalog, and so RMAN does not know, on which DBID to perform the requested command. Ok. So let’s connect to the RMAN catalog and check if this is really the case.

SQL> SELECT DB.DB_KEY,DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME
        FROM DB, DBINC
       WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY
         AND DBINC.DB_NAME   = 'DBIT121' ;  2    3    4

    DB_KEY      DB_ID CURR_DBINC_KEY DB_NAME
---------- ---------- -------------- --------
         1  642589239              2 DBIT121
    546780  644484523         546781 DBIT121

Indeed. I do have two different DBID’s pointing to the same DB_NAME. Kinda confusing for RMAN. But which one is the one that have been backed up. We could query the RC_BACKUP_SET and RC_BACKUP_PIECE views to find that out.

SQL> SELECT RBS.DB_KEY
         , RD.NAME
         , RBS.DB_ID
  2    3    4           , RBS.BS_KEY
         , RBS.RECID
         , RBS.STAMP
         , RBS.BACKUP_TYPE
         , RBS.START_TIME, STATUS
  5    6    7    8    9        FROM RC_BACKUP_SET RBS, RC_DATABASE RD
     WHERE RBS.DB_KEY=RD.DB_KEY
       AND RBS.DB_ID=RD.DBID
       AND RD.NAME='DBIT121' ;  10   11   12
...
...

    DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIM S
---------- -------- ---------- ---------- ---------- ---------- - --------- -
    546780 DBIT121   644484523     555608       3070  936496831 I 21-FEB-17 A
    546780 DBIT121   644484523     555609       3071  936496832 I 21-FEB-17 A
    546780 DBIT121   644484523     555610       3072  936496836 D 21-FEB-17 A
    546780 DBIT121   644484523     555611       3073  936496860 D 21-FEB-17 A
    546780 DBIT121   644484523     555612       3074  936496875 D 21-FEB-17 A
    546780 DBIT121   644484523     555613       3075  936496884 D 21-FEB-17 A
    546780 DBIT121   644484523     555614       3076  936496890 D 21-FEB-17 A
    546780 DBIT121   644484523     555615       3077  936496895 L 21-FEB-17 A
    546780 DBIT121   644484523     555616       3078  936496897 L 21-FEB-17 A
    546780 DBIT121   644484523     555617       3079  936496897 L 21-FEB-17 A
    546780 DBIT121   644484523     555618       3080  936496898 D 21-FEB-17 A

    DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIM S
---------- -------- ---------- ---------- ---------- ---------- - --------- -
    546780 DBIT121   644484523     555619       3081  936496900 D 21-FEB-17 A
    546780 DBIT121   644484523     555620       3082  936498788 D 21-FEB-17 A
    546780 DBIT121   644484523     555621       3083  936502389 D 21-FEB-17 A
    546780 DBIT121   644484523     555622       3084  936505991 D 21-FEB-17 A
    546780 DBIT121   644484523     555623       3085  936509589 D 21-FEB-17 A
    546780 DBIT121   644484523     555624       3086  936513189 D 21-FEB-17 A
    546780 DBIT121   644484523     555625       3087  936516788 D 21-FEB-17 A
    546780 DBIT121   644484523     555626       3088  936520387 D 21-FEB-17 A
    546780 DBIT121   644484523     555627       3089  936523988 D 21-FEB-17 A
    546780 DBIT121   644484523     555628       3090  936527608 D 21-FEB-17 A
    546780 DBIT121   644484523     555629       3091  936531188 D 21-FEB-17 A
...
...

After checking the output, I see that DBID 644484523 is the correct one, and DBID 642589239 is the one I want to get rid of.

To do so, we can shutdown the Standby database and start it up with nomount. The reason for that, is that you can’t issue the SET DBID command against a database which is mounted or open.

RMAN> SET DBID=642589239;

executing command: SET DBID
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 02/21/2017 13:15:26
RMAN-06188: cannot use command when connected to a mounted target database

Ok. Let’s go the nomount and execute the “unregister database;” command after the correct DBID is set.

$ rman target sys/welcome1 catalog /@rcat

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 21 14:25:10 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBIT121 (not mounted)
connected to recovery catalog database

RMAN> SET DBID=642589239;

executing command: SET DBID
database name is "DBIT121" and DBID is 642589239

RMAN> unregister database;

database name is "DBIT121" and DBID is 642589239

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

RMAN>

Let’s check the RMAN catalog again.

SQL> SELECT DB.DB_KEY, DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME
        FROM DB, DBINC
       WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY
         AND DBINC.DB_NAME   = 'DBIT121' ;  2    3    4

    DB_KEY      DB_ID CURR_DBINC_KEY DB_NAME
---------- ---------- -------------- --------
    556718  644484523         556719 DBIT121

Cool. Looks much better. :-) Now my resync catalog from SITE1 issued from SITE2 works again.

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
556718  DBIT121  644484523        PRIMARY          DBIT121_SITE1
556718  DBIT121  644484523        STANDBY          DBIT121_SITE2

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME 'DBIT121_SITE1';

resyncing from database with DB_UNIQUE_NAME DBIT121_SITE1
starting full resync of recovery catalog
full resync complete
Conclusion

The RMAN DB_UNIQUE_NAME persistent setting is a quite cool feature. This is something I would really recommend when working with RMAN and Data Guard. It allows you to do actions on primary from the standby or the standby from the primary. It doesn’t matter. But take care that you don’t have multiple DBID’s pointing to the same DB in your RMAN catalog.

 

Cet article Oracle 12c – How to correct the error: “RMAN-20005: target database name is ambiguous” est apparu en premier sur Blog dbi services.

Converting a column from one data type to another in PostgreSQL

Mon, 2017-02-20 11:15

Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string. When we want to convert this column to be a real date data type in Oracle we can use dbms_redefinition. How can we do that in PostgreSQL? The first answer that came to me mind is: Add a new column (of data type date) and populate it with the converted string from the source column, drop the source column and rename the new column to the name of the dropped column. This for sure will work but it is not the most convenient way: What happens if the application is somehow dependent on the order of the columns? Shouldn’t be the case if the application is written well, but you never know.

To start with lets generate some test data:

drop table if exists t1;
create table t1 ( a varchar(20) );
insert into t1 (a) values ('01012017');
insert into t1 (a) values ('02012017');
insert into t1 (a) values ('03012017');
insert into t1 (a) values ('04012017');
insert into t1 (a) values ('05012017');
insert into t1 (a) values ('06012017');
insert into t1 (a) values ('07012017');
insert into t1 (a) values ('08012017');
insert into t1 (a) values ('09012017');
insert into t1 (a) values ('10012017');
insert into t1 (a) values ('11012017');
(postgres@[local]:5440) [postgres] > select * from t1;
    a     
----------
 01012017
 02012017
 03012017
 04012017
 05012017
 06012017
 07012017
 08012017
 09012017
 10012017
 11012017
(11 rows)

We now have a varchar column holding the date values as a string. When you look at the “alter table” command in PostgtreSQL you’ll notice something like this:

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

What will really help here is the “using” keyword because you can do things like this:

(postgres@[local]:5440) [postgres] > alter table t1 alter column a type date using to_date(a,'DDMMYYYY');
ALTER TABLE
(postgres@[local]:5440) [postgres] > \d t1
     Table "public.t1"
 Column | Type | Modifiers 
--------+------+-----------
 a      | date | 

That’s really cool: You can pass a function (this mean a self written function as well) to the alter command to specify on how you want to do the conversion.

When there is an index on the column, what happens to the index?

(postgres@[local]:5440) [postgres] > \d t1
             Table "public.t1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | character varying(20) | 
Indexes:
    "i1" btree (a)
(postgres@[local]:5440) [postgres] > alter table t1 alter column a type date using to_date(a,'DDMMYYYY');
ALTER TABLE
Time: 5.931 ms
(postgres@[local]:5440) [postgres] > \d t1
     Table "public.t1"
 Column | Type | Modifiers 
--------+------+-----------
 a      | date | 
Indexes:
    "i1" btree (a)
(postgres@[local]:5440) [postgres] > select indisvalid,indisready,indislive from pg_index where indexrelid = 'i1'::regclass; 
 indisvalid | indisready | indislive 
------------+------------+-----------
 t          | t          | t
(1 row)

Looks fine as well, lets do a quick test if the index is really usable:

Time: 0.453 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 11
Time: 2.373 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 22
Time: 39.653 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 44
Time: 1.110 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 88
Time: 1.072 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 176
Time: 1.455 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 352
Time: 1.432 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 704
Time: 3.344 ms
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 1408
Time: 20.972 ms
(postgres@[local]:5440) [postgres] > explain analyze select a from t1 where a = to_date('01012017','dd.mm.yyyy');
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Index Only Scan using i1 on t1  (cost=0.27..8.29 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)
   Index Cond: (a = to_date('01012017'::text, 'dd.mm.yyyy'::text))
   Heap Fetches: 0
 Planning time: 0.062 ms
 Execution time: 0.029 ms
(5 rows)

Perfect. The remaining question is: Does this operation block others from reading the table while it is being executed? Lets generate some more date to make the select operation a bit longer and then “watch” the statement in a separate session while we execute the conversion:

Time: 0.453 ms
drop table if exists t1;
create table t1 ( a varchar(20) );
insert into t1 (a) values ('01012017');
insert into t1 (a) values ('02012017');
insert into t1 (a) values ('03012017');
insert into t1 (a) values ('04012017');
insert into t1 (a) values ('05012017');
insert into t1 (a) values ('06012017');
insert into t1 (a) values ('07012017');
insert into t1 (a) values ('08012017');
insert into t1 (a) values ('09012017');
insert into t1 (a) values ('10012017');
insert into t1 (a) values ('11012017');
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 11
-- and so on and so on ...
(postgres@[local]:5440) [postgres] > insert into t1 select * from t1;
INSERT 0 360448

Then, in session 1 I am doing this:

Time: 0.453 ms
(postgres@[local]:5440) [postgres] > select count(*) from t1 where a = '01012017';
 count  
--------
 131072
(1 row)
(postgres@[local]:5440) [postgres] > \watch 0.1

In session 2 I am doing the conversion:

Time: 0.453 ms
(postgres@[local]:5440) [postgres] > alter table t1 alter column a type date using to_date(a,'DDMMYYYY');
ALTER TABLE

In session 1 you’ll notice that the statement is blocked and finally you get this:

Time: 0.453 ms
ERROR:  date/time field value out of range: "01012017"
LINE 1: select count(*) from t1 where a = '01012017';
                                          ^
HINT:  Perhaps you need a different "datestyle" setting.

Conclusion: Converting a column from one data type to another is nothing you want to do when you system is live. When you can afford some downtime the “using” syntax is what you want to do and it is working quite fast. Hope this helps …

 

Cet article Converting a column from one data type to another in PostgreSQL est apparu en premier sur Blog dbi services.

12cR2 real-time materialized view (on query computation)

Fri, 2017-02-17 15:49

Materialized views is a very old feature (you may remember that it was called snapshots a long time ago). It has all advantages of a view, as you can define any select statement that joins, filters, aggregates, and see it as one table. It has all advantages of a table, as it is stored in one segment, can be indexed, partitioned, have constraints, be compressed, etc. It looks like indexes as it stores data redundantly, in a different physical way, more focused on the way it will be queried rather than on the way data is entered. Like indexes, they can be used transparently (with query rewrite) but unlike indexes, they are not maintained synchronously but have to be refreshed. It has some advantages of replication because it can capture the changes done on source tables, into materialized view logs, so that refresh can be incremental (fast refresh).
Oracle Database 12.2 goes a step further being able to deliver fresh result even when the materialized is stale. This is an amazing feature called real-time materialized view, that does on-query computation of fresh result from the stale one, joined with the materialized view log.

I create my DEMO table on Oracle Exdata Express Cloud Service

SQL> create table DEMO (id primary key,a,b) as select rownum,round(log(10,rownum)) a, rownum b from xmltable('1 to 100000');
Table created.

I plan to create a materialized view to aggregate the count and sum of B grouped by A. And DBMS_MVIEW can tell me what I need to be able to fast refresh it.

Explain Materialized View

The goal is to have real-time materialized view with frequent refreshes, which means that we need fast refresh to be possible after any kind of modification.


SQL> exec dbms_mview.explain_mview('select a,count(b),sum(b),count(*) from DEMO group by a');
PL/SQL procedure successfully completed.
 
SQL> select distinct capability_name||' '||msgtxt||' '||related_text from mv_capabilities_table where capability_name like 'REFRESH_FAST%' and possible='N';
 
CAPABILITY_NAME||''||MSGTXT||''||RELATED_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
REFRESH_FAST_AFTER_ONETAB_DML COUNT(*) is not present in the select list
REFRESH_FAST
REFRESH_FAST_AFTER_INSERT the detail table does not have a materialized view log PDB_ADMIN.DEMO
REFRESH_FAST_AFTER_ANY_DML see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT PCT is not possible on any of the detail tables in the materialized view
REFRESH_FAST_AFTER_ONETAB_DML see the reason why REFRESH_FAST_AFTER_INSERT is disabled

Here is what I have to do in order to have a materialized view that can be fast refreshed: COUNT(*) in the select, and create a materialized view log.

Materialized view log


SQL> create materialized view log on DEMO;
Materialized view log created.

Let’s check if it is ok now, with he additional count(*):

SQL> delete from mv_capabilities_table;
15 rows deleted.
 
SQL> exec dbms_mview.explain_mview('select a,count(b),sum(b),count(*) from DEMO group by a');
PL/SQL procedure successfully completed.
 
SQL> select distinct capability_name||' '||msgtxt||' '||related_text from mv_capabilities_table where capability_name like 'REFRESH_FAST%' and possible='N';
 
CAPABILITY_NAME||''||MSGTXT||''||RELATED_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
REFRESH_FAST
REFRESH_FAST_AFTER_ANY_DML see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_INSERT mv log must have ROWID PDB_ADMIN.DEMO
REFRESH_FAST_AFTER_INSERT mv log must have new values PDB_ADMIN.DEMO
REFRESH_FAST_AFTER_INSERT mv log does not have all necessary columns PDB_ADMIN.DEMO
REFRESH_FAST_PCT PCT is not possible on any of the detail tables in the materialized view
REFRESH_FAST_AFTER_ONETAB_DML see the reason why REFRESH_FAST_AFTER_INSERT is disabled

I must add ROWID, used columns and NEW VALUES


SQL> drop materialized view log on DEMO;
Materialized view log dropped.
 
SQL> create materialized view log on DEMO with sequence, rowid (a,b) including new values;
Materialized view log created.

You can see that I’ve added the sequence, that was not mentioned by the explain_mview. I’ll come back on that later and probably in another post.


SQL> delete from mv_capabilities_table;
16 rows deleted.
SQL> exec dbms_mview.explain_mview('select a,count(b),sum(b),count(*) from DEMO group by a');
PL/SQL procedure successfully completed.
SQL> select distinct capability_name||' '||msgtxt||' '||related_text from mv_capabilities_table where capability_name like 'REFRESH_FAST%' and possible='N';
 
CAPABILITY_NAME||''||MSGTXT||''||RELATED_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
REFRESH_FAST_PCT PCT is not possible on any of the detail tables in the materialized view

Ok, now I’m ready to create the materialized view. The only remaining message is for partitioned tables.


SQL> create materialized view DEMO_MV refresh fast on demand as select a,count(b),sum(b),count(*) from DEMO group by a;
Materialized view created.

Aggregate query on the source table

I’m running a simple query that can get its result from the source table or from the materialized view


SQL> select sum(b) from DEMO where a=3;
 
SUM(B)
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brdc1qcbc2npk, child number 0
-------------------------------------
select sum(b) from DEMO where a=3
 
Plan hash value: 2180342005
 
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 262 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 262 |
|* 2 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 16667 | 2846 |00:00:00.01 | 262 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("A"=3)
filter("A"=3)

The query has read the source table. I need to enable query rewrite to get the CBO transparently transforming to a query on the materialized view.

Query Rewrite


SQL> alter materialized view DEMO_MV enable query rewrite;
Materialized view altered.

I also need the query_rewrite_integrity to be set. It is by default:

SQL> show parameter query_rewrite
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

Now, the rewrite can occur:

SQL> select sum(b) from DEMO where a=3;
 
SUM(B)
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brdc1qcbc2npk, child number 0
-------------------------------------
select sum(b) from DEMO where a=3
 
Plan hash value: 2792196921
 
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 9 |
|* 2 | MAT_VIEW REWRITE ACCESS STORAGE FULL| DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("DEMO_MV"."A"=3)
filter("DEMO_MV"."A"=3)

This query is optimized: 9 blocks read from the materialized view instead of 262 ones from the source table.

You can note that it’s not a new child cursor: the previous cursor has been invalidated when I altered the materialized view.

This rewrite can occur only because the materialized view has been refreshed and the source table had no modifications on it.

Stale MVIEW

Let’s do some DML on the source table.


SQL> insert into DEMO values(0,0,0);
1 row created.

and query again


SQL> select sum(b) from DEMO where a=3;
 
SUM(B)
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brdc1qcbc2npk, child number 1
-------------------------------------
select sum(b) from DEMO where a=3
 
Plan hash value: 2180342005
 
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 270 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 270 |
|* 2 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 16667 | 2846 |00:00:00.01 | 270 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("A"=3)
filter("A"=3)

Now, the materialized view is stale. We cannot get the same result from it, so the rewrite didn’t happen.

You can see that I have a new child cursor. The previous one cannot be shared because the previous one was only for non-stale materialized view.

Stale tolerated

If I want to keep using the materialized view, I have the option to accept stale results:


SQL> alter session set query_rewrite_integrity=stale_tolerated;
Session altered.

Now, the rewrite can occur even when the source table has changed since the last refresh.


SQL> select sum(b) from DEMO where a=3;
 
SUM(B)
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brdc1qcbc2npk, child number 2
-------------------------------------
select sum(b) from DEMO where a=3
 
Plan hash value: 2792196921
 
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 9 |
|* 2 | MAT_VIEW REWRITE ACCESS STORAGE FULL| DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("DEMO_MV"."A"=3)
filter("DEMO_MV"."A"=3)

Of course, here you can’t see that the result is stale, because I inserted a row with value 0 which do not change the sum. Let’s do a count the rows, which is something that is also aggregated in my materialized view. I have the option to disable the rewrite and query the source table:


SQL> select /*+ no_rewrite */ count(b) from DEMO;
 
COUNT(B)
----------
100001

This is the accurate result, but with access to full table.

The rewrite can also be forced by hint (because it is a cost decision)


SQL> select /*+ rewrite */ count(b) from DEMO;
 
COUNT(B)
----------
100000

Stale result here: I don’t see the latest modifications.

Frequent refresh

In order to limit the gap between fresh data and stale result, you can refresh the materialized view frequently. It’s not too expensive thanks to the materialized view log: fast refresh is incremental.

Here I don’t want stale result:

SQL> alter session set query_rewrite_integrity=enforced;
Session altered.

and I refresh the materialized view


SQL> exec dbms_mview.refresh('DEMO_MV','f');
PL/SQL procedure successfully completed.

Then I can expect, for the time until the next updates, to get results from he materialized view.


SQL> select sum(b) from DEMO where a=3;
 
SUM(B)
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brdc1qcbc2npk, child number 1
-------------------------------------
select sum(b) from DEMO where a=3
 
Plan hash value: 2180342005
 
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 270 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 270 |
|* 2 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 16667 | 2846 |00:00:00.01 | 270 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("A"=3)
filter("A"=3)

Unfortunately I re-used the same cursor here. When you refresh, the cursors are not invalidated.

I’m running another statement now to get it parsed again:

SQL> select sum(b) this_is_another_cursor from DEMO where a=3;
 
THIS_IS_ANOTHER_CURSOR
----------------------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 27xfg0qjcf7ff, child number 0
-------------------------------------
select sum(b) this_is_another_cursor from DEMO where a=3
 
Plan hash value: 2792196921
 
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 9 |
|* 2 | MAT_VIEW REWRITE ACCESS STORAGE FULL| DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("DEMO_MV"."A"=3)
filter("DEMO_MV"."A"=3)

So, we now read the materialized view but this will last only while there is no updates on the table. So the idea is to trigger a refresh as soon as there are modifications. Ideally it should be like indexes, which are maintained automatically. But indexes are much simple: it’s a simple value to rowid mapping entry to maintain. And rowids do not change. Materialized views have joins, aggregates and contains all columns.

Refresh on commit

So the idea is to defer the maintenance of the materialized view to commit time. This is the latest point where we are required to do it as we want other sessions to never see stale results. And materialized view logs are there to store the incremental changes even if the transaction is very long. Of course, we need to be aware of it because in general the commit is an immediate and simple operation.

Let’s define the materialized view to refresh on commit instead of on-demand


SQL> alter materialized view DEMO_MV refresh on commit;
Materialized view altered.

I do some modifications


SQL> delete from DEMO where id=0;
1 row deleted.

And I run my query


SQL> select sum(b) this_is_a_third_cursor from DEMO where a=3;
 
THIS_IS_A_THIRD_CURSOR
----------------------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dfs068dgbwvd, child number 0
-------------------------------------
select sum(b) this_is_a_third_cursor from DEMO where a=3
 
Plan hash value: 2180342005
 
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 270 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 270 |
|* 2 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 16667 | 2846 |00:00:00.01 | 270 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("A"=3)
filter("A"=3)

Rewrite cannot happen here because the materialized view is stale. I didn’t commit yet. Of course, other sessions can query from the view because they must not see my modification.


SQL> commit;
Commit complete.

The commit has triggered the fast refresh of the materialized view


SQL> select sum(b) this_is_a_fourth_cursor from DEMO where a=3;
 
THIS_IS_A_FOURTH_CURSOR
-----------------------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0075r0yzqt90a, child number 0
-------------------------------------
select sum(b) this_is_a_fourth_cursor from DEMO where a=3
 
Plan hash value: 2792196921
 
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 9 |
|* 2 | MAT_VIEW REWRITE ACCESS STORAGE FULL| DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("DEMO_MV"."A"=3)
filter("DEMO_MV"."A"=3)

With on commit refresh, the materialized view is never stale. The problem is that it can slow down the transactions: in addition to fill the materialized view logs, the commit has the overhead to apply them. In 12.1 this is the only way to have a query on the materialized view that is always fresh. But there’s something new in 12.2.

Real-time materialized views

Even when the materialized view is stale, we can get fresh result without querying the source tables. We have the stale values in the materialized view and we have all changes logged into the materialized view log. Easy or not, merging that can be computed to get fresh result. We still need fast refresh but we don’t need refresh on commit anymore:


SQL> alter materialized view DEMO_MV refresh on demand;
Materialized view altered.

And in order to use this new feature we have to enable it a materialized view level:


SQL> alter materialized view DEMO_MV enable on query computation;
Materialized view altered.

Then let the magic happen:


SQL> select sum(b) from DEMO where a=3;
 
SUM(B)
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brdc1qcbc2npk, child number 0
-------------------------------------
select sum(b) from DEMO where a=3
 
Plan hash value: 2792196921
 
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 9 |
|* 2 | MAT_VIEW REWRITE ACCESS STORAGE FULL| DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - storage("DEMO_MV"."A"=3)
filter("DEMO_MV"."A"=3)

Here my materialized view is not stale, so nothing special happened. Here is a some modification:

SQL> insert into DEMO values(0,0,0);
1 row created.

and…

SQL> select sum(b) try_again from DEMO where a=3;
&npsp;
TRY_AGAIN
----------
4950617
&npsp;
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
&npsp;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dtmhccwr0v7r5, child number 0
-------------------------------------
select sum(b) try_again from DEMO where a=3
&npsp;
Plan hash value: 2180342005
&npsp;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 270 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 270 |
|* 2 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 16667 | 2846 |00:00:00.01 | 270 |
---------------------------------------------------------------------------------------------
&npsp;
Predicate Information (identified by operation id):
---------------------------------------------------
&npsp;
2 - storage("A"=3)
filter("A"=3)

Still no magic here. For the session that did the modifications, it seems that query rewrite cannot happen. All changes are in the materialized view log, but applying the uncommited ones for my session seems to be impossible here. Well, let’s commit my changes.


SQL> commit;
Commit complete.

and see the magic:


SQL> select sum(b) try_again from DEMO where a=3;
&nbps;
SUM(B)
----------
4950617
&nbps;
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
&nbps;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dtmhccwr0v7r5, child number 0
-------------------------------------
select sum(b) try_again from DEMO where a=3
&nbps;
Plan hash value: 2180342005
&nbps;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 270 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 270 |
|* 2 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 16667 | 2846 |00:00:00.01 | 270 |
---------------------------------------------------------------------------------------------
&nbps;
Predicate Information (identified by operation id):
---------------------------------------------------
&nbps;
2 - storage("A"=3)
filter("A"=3)

Oh… that’s my previous cursor. No invalidation occurs. I have to parse a different statement.


SQL> select sum(b) here_I_am from DEMO where a=3;
 
HERE_I_AM
----------
4950617
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 34fqrktpthuk7, child number 1
-------------------------------------
select sum(b) here_I_am from DEMO where a=3
 
Plan hash value: 1240257898
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 25 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 25 | | | |
| 2 | VIEW | | 1 | 705 | 1 |00:00:00.01 | 25 | | | |
| 3 | UNION-ALL | | 1 | | 1 |00:00:00.01 | 25 | | | |
|* 4 | FILTER | | 1 | | 1 |00:00:00.01 | 16 | | | |
|* 5 | HASH JOIN OUTER | | 1 | 100 | 1 |00:00:00.01 | 16 | 3843K| 3843K| 1699K (0)|
|* 6 | MAT_VIEW ACCESS STORAGE FULL | DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 | 1025K| 1025K| |
| 7 | VIEW | | 1 | 100 | 1 |00:00:00.01 | 7 | | | |
| 8 | HASH GROUP BY | | 1 | | 1 |00:00:00.01 | 7 | 1956K| 1956K| 2324K (0)|
| 9 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 10 | RESULT CACHE | 6jf9k1y2wt8xc5b00gv9px6ww0 | 1 | | 1 |00:00:00.01 | 7 | | | |
|* 11 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 12 | WINDOW SORT | | 1 | 1 | 1 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
|* 13 | TABLE ACCESS STORAGE FULL | MLOG$_DEMO | 1 | 1 | 1 |00:00:00.01 | 7 | 1025K| 1025K| |
| 14 | VIEW | | 1 | 605 | 0 |00:00:00.01 | 9 | | | |
| 15 | UNION-ALL | | 1 | | 0 |00:00:00.01 | 9 | | | |
|* 16 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 17 | NESTED LOOPS OUTER | | 1 | 600 | 0 |00:00:00.01 | 0 | | | |
| 18 | VIEW | | 1 | 100 | 0 |00:00:00.01 | 0 | | | |
|* 19 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 20 | HASH GROUP BY | | 1 | | 0 |00:00:00.01 | 0 | 2982K| 2982K| |
|* 21 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
| 22 | RESULT CACHE | 6jf9k1y2wt8xc5b00gv9px6ww0 | 1 | | 1 |00:00:00.01 | 0 | | | |
|* 23 | VIEW | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 24 | WINDOW SORT | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 25 | TABLE ACCESS STORAGE FULL| MLOG$_DEMO | 0 | 1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 26 | INDEX UNIQUE SCAN | I_SNAP$_DEMO_MV | 0 | 6 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 27 | HASH JOIN | | 1 | 5 | 0 |00:00:00.01 | 9 | 3043K| 3043K| 1702K (0)|
|* 28 | MAT_VIEW ACCESS STORAGE FULL | DEMO_MV | 1 | 1 | 1 |00:00:00.01 | 9 | 1025K| 1025K| |
| 29 | VIEW | | 1 | 100 | 1 |00:00:00.01 | 0 | | | |
| 30 | HASH GROUP BY | | 1 | | 1 |00:00:00.01 | 0 | 1956K| 1956K| 2319K (0)|
| 31 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 32 | RESULT CACHE | 6jf9k1y2wt8xc5b00gv9px6ww0 | 1 | | 1 |00:00:00.01 | 0 | | | |
|* 33 | VIEW | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 34 | WINDOW SORT | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 35 | TABLE ACCESS STORAGE FULL | MLOG$_DEMO | 0 | 1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

We got it. All the magic. The materialized view is read. The materialized view log is read. But we don’t need the source tables. All this is merged by outer join and union all. The plan is harder to read but it requires only 25 logical reads to get fresh results instead of 270 from the source table. The bigger the tables are, the more complex the query is, the more benefit you get as long as you don’t have too many changes since the last refresh. And this without any overhead on other transactions commits. That’s the beauty of 12cR2 Enterprise Edition. Can you imagine you have to code this yourself? For any query? For any modifications on source tables?

FRESH_MV

This was query rewrite: query the source table and have the CBO transform the query to query the materialized (given that the CBO costing estimates that it is cheaper). But you can also query the materialized view and ask to get fresh result by joining materialized view log to the stale result. And this can be used also in Standard Edition (only query rewrite is limited to Enterprise Edition). On-query computation when querying the materialized vue is enabled by the FRESH_MV hint:


SQL> select /*+ fresh_mv */ * from DEMO_MV;
 
A COUNT(B) SUM(B) COUNT(*)
---------- ---------- ---------- ----------
5 68378 4500058747 68378
2 285 49590 285
3 2846 4950617 2846
1 28 490 28
4 28460 494990550 28460
0 4 6 4
 
6 rows selected.
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +alias'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gyar0v20qcksu, child number 0
-------------------------------------
select /*+ fresh_mv */ * from DEMO_MV
 
Plan hash value: 2169890143
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 12 | | | |
| 1 | VIEW | | 1 | 730 | 6 |00:00:00.01 | 12 | | | |
| 2 | UNION-ALL | | 1 | | 6 |00:00:00.01 | 12 | | | |
|* 3 | VIEW | VW_FOJ_0 | 1 | 100 | 5 |00:00:00.01 | 9 | | | |
|* 4 | HASH JOIN FULL OUTER | | 1 | 100 | 6 |00:00:00.01 | 9 | 2897K| 2897K| 3217K (0)|
| 5 | VIEW | | 1 | 6 | 6 |00:00:00.01 | 9 | | | |
| 6 | MAT_VIEW ACCESS STORAGE FULL | DEMO_MV | 1 | 6 | 6 |00:00:00.01 | 9 | 1025K| 1025K| |
| 7 | VIEW | | 1 | 100 | 1 |00:00:00.01 | 0 | | | |
| 8 | HASH GROUP BY | | 1 | | 1 |00:00:00.01 | 0 | 1956K| 1956K| 2268K (0)|
| 9 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 10 | RESULT CACHE | 6jf9k1y2wt8xc5b00gv9px6ww0 | 1 | | 1 |00:00:00.01 | 0 | | | |
|* 11 | VIEW | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 12 | WINDOW SORT | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 13 | TABLE ACCESS STORAGE FULL | MLOG$_DEMO | 0 | 1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 14 | VIEW | | 1 | 630 | 1 |00:00:00.01 | 3 | | | |
| 15 | UNION-ALL | | 1 | | 1 |00:00:00.01 | 3 | | | |
|* 16 | FILTER | | 1 | | 0 |00:00:00.01 | 1 | | | |
| 17 | NESTED LOOPS OUTER | | 1 | 600 | 1 |00:00:00.01 | 1 | | | |
| 18 | VIEW | | 1 | 100 | 1 |00:00:00.01 | 0 | | | |
|* 19 | FILTER | | 1 | | 1 |00:00:00.01 | 0 | | | |
| 20 | HASH GROUP BY | | 1 | | 1 |00:00:00.01 | 0 | 1956K| 1956K| 2304K (0)|
| 21 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 22 | RESULT CACHE | 6jf9k1y2wt8xc5b00gv9px6ww0 | 1 | | 1 |00:00:00.01 | 0 | | | |
|* 23 | VIEW | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 24 | WINDOW SORT | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 25 | TABLE ACCESS STORAGE FULL| MLOG$_DEMO | 0 | 1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 26 | INDEX UNIQUE SCAN | I_SNAP$_DEMO_MV | 1 | 6 | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 27 | MERGE JOIN | | 1 | 30 | 1 |00:00:00.01 | 2 | | | |
| 28 | MAT_VIEW ACCESS BY INDEX ROWID | DEMO_MV | 1 | 6 | 6 |00:00:00.01 | 2 | | | |
| 29 | INDEX FULL SCAN | I_SNAP$_DEMO_MV | 1 | 6 | 6 |00:00:00.01 | 1 | 1025K| 1025K| |
|* 30 | FILTER | | 6 | | 1 |00:00:00.01 | 0 | | | |
|* 31 | SORT JOIN | | 6 | 100 | 1 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)|
| 32 | VIEW | | 1 | 100 | 1 |00:00:00.01 | 0 | | | |
| 33 | SORT GROUP BY | | 1 | | 1 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)|
| 34 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 35 | RESULT CACHE | 6jf9k1y2wt8xc5b00gv9px6ww0 | 1 | | 1 |00:00:00.01 | 0 | | | |
|* 36 | VIEW | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 37 | WINDOW SORT | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 38 | TABLE ACCESS STORAGE FULL| MLOG$_DEMO | 0 | 1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Have you seen that we need even less logical reads (12) than before (25). There is an optimization here with RESULT CACHE. You get this when you have the sequence in the materialized view log, and you can see that the sequence is used in the predicates:


Predicate Information (identified by operation id):
---------------------------------------------------
 
3 - filter("AV$0"."OJ_MARK" IS NULL)
4 - access(SYS_OP_MAP_NONNULL("SNA$0"."A")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
11 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND
"MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
13 - storage("MAS$"."SNAPTIME$$">TO_DATE(' 2017-02-16 20:31:08', 'syyyy-mm-dd hh24:mi:ss'))
filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-02-16 20:31:08', 'syyyy-mm-dd hh24:mi:ss'))
16 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)
19 - filter(SUM(1)>0)
23 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND
"MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
25 - storage("MAS$"."SNAPTIME$$">TO_DATE(' 2017-02-16 20:31:08', 'syyyy-mm-dd hh24:mi:ss'))
filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-02-16 20:31:08', 'syyyy-mm-dd hh24:mi:ss'))
26 - access("DEMO_MV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
30 - filter("DEMO_MV"."COUNT(*)"+"AV$0"."D0">0)
31 - access("DEMO_MV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
filter("DEMO_MV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
36 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND
"MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
38 - storage("MAS$"."SNAPTIME$$">TO_DATE(' 2017-02-16 20:31:08', 'syyyy-mm-dd hh24:mi:ss'))
filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-02-16 20:31:08', 'syyyy-mm-dd hh24:mi:ss'))

Of course, you also see a predicate with the staleness timestamp (here 2017-02-16 20:31:08) of the materialized view.

This result cache is interesting because the materialized view log is read several times in the execution plan and this is a way to actually read it only once.

SQL> select type,column_count,row_count,cache_id,name from v$result_cache_objects;
 
TYPE COLUMN_COUNT ROW_COUNT CACHE_ID NAME
---------- ------------ ---------- ------------------------------ ------------------------------
Dependency 0 0 PDB_ADMIN.MLOG$_DEMO PDB_ADMIN.MLOG$_DEMO
Result 7 1 6jf9k1y2wt8xc5b00gv9px6ww0 DMLTYPES:MLOG$_DEMO

The result cache has a dependency on the materialized view log, to be aware of additional changes, and when tracing the transformed query, we can see a lifetime of session for this result cache. /*+ RESULT_CACHE(LIFETIME=SESSION, NAME=”DMLTYPES:MLOG$_DEMO”) */. Note that I included the sequence in the materialized view log, but this is not required. I’ll show in a future post that the execution plan is different then, and not using result cache.

So what?

This is an amazing feature. You can optimize your queries transparently by creating materialized views, get fresh result, and minimize the refresh overhead. And depending on the size of the tables and the rate of modifications. You can choose the right refresh frequency with the goal to limit the materialized view logs to apply on each query. You have real-time result and bulk refresh at the same time. Oracle Database has always been a database for mixed workloads, where readers don’t block writers. And once again we have a feature to optimize queries by pre-calculating them, with minimal impact on source.

It is transparent, but after this first test, I have a few questions that raise and that I’ll try to answer in future posts: Is it always better to have the sequence in the materialized view log? Is the default result cache size still sufficient? How can it use a 1 seconds only precision timestamp and not a SCN? What happens with this at winter Daylight Saving Time clock change? Can we get query rewrite when our own transaction has made the modifications? Do we need to invalidate cursors that read the source table? How accurate are the cardinality estimations on the very volatile materialized view? When full materialized view log is read, can it trigger a complete refresh?

 

Cet article 12cR2 real-time materialized view (on query computation) est apparu en premier sur Blog dbi services.

Oracle 12c – Combining Flashback Drop and Flashback Query

Fri, 2017-02-17 09:09

If you think that Flashback Drop feature just brings back your table, then this is only half of the story. It does much more than that. Besides undropping the table, it also brings back your constraints, your indexes, your trigger, your grants and the statistics as well.

The ugly part is, that the flashback drop brings back some strange object names e.g. your indexes and constraints with names like “BIN$…” or alike. Maybe something you don’t want. So why not combining the Flashback Drop with a Flashback Query on the Dictionary to get the old constraint and index names.

Let’s setup a few objects in the SCOTT schema. But before we do that, we need to grant the user SCOTT some extra privileges.

SQL> grant execute on dbms_flashback to scott;

Grant succeeded.

SQL> grant flashback on user_indexes to scott;

Grant succeeded.

SQL> grant flashback on user_constraints to scott;

Grant succeeded.

SQL> grant flashback on user_triggers to scott;

Grant succeeded.

Now we can setup our objects for this test. I will create 2 tables, and few grants, a trigger and statistics. The goal is to have after the flashback to before drop, exactly the same object names afterwards for the table the index, the constraints and the trigger.

SQL> connect scott/tiger
Connected.

SQL> create table dbi_t
  2  ( x int, constraint t_pk primary key(x),
  3   y int, constraint check_x check(x>0)
  4  );

Table created.

SQL> insert into dbi_t values (1,1);

1 row created.

SQL> insert into dbi_t values (2,2);

1 row created.

SQL> insert into dbi_t values (3,3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> create table dbi_audit
  2  (x int, x_before int, y int, y_before int, z varchar2(10));

Table created.


SQL> CREATE OR REPLACE TRIGGER dbi_after_update
  2  AFTER INSERT OR UPDATE
  3     ON DBI_T
  4     FOR EACH ROW
  5  DECLARE
  6     v_z varchar2(10);
  7  BEGIN
  8     SELECT user INTO v_z FROM dual;
  9     -- Insert record into audit table
 10     INSERT INTO dbi_audit
 11     ( x,
 12       x_before,
 13       y,
 14       y_before,
 15       z)
 16     VALUES
 17     ( :new.x,
 18       :old.x,
 19       :new.y,
 20       :old.y,
 21       v_z );
 22* END;
 /

Trigger created.


SQL> insert into dbi_t values (4,4);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into dbi_t values (5,5);

1 row created.

SQL> commit;

Commit complete.

SQL> update dbi_t set x=6 where y=5;

1 row updated.

SQL> commit;

Commit complete.


SQL> select * from dbi_t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3
         4          4
         6          5

SQL> select * from dbi_audit;

         X   X_BEFORE          Y   Y_BEFORE Z
---------- ---------- ---------- ---------- ----------
         4                     4            SCOTT
         5                     5            SCOTT
         6          5          5          5 SCOTT

		 


SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => '"SCOTT"',
  4  tabname => '"DBI_T"',
  5  estimate_percent => 100
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => '"SCOTT"',
  4  tabname => '"DBI_AUDIT"',
  5  estimate_percent => 100
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.


SQL> grant select on dbi_t to hr;

Grant succeeded.

SQL> grant select on dbi_audit to hr;

Grant succeeded.

Ok. So let’s take a look how is the current situation is right now.

SQL> select TABLE_NAME, LAST_ANALYZED
  2  from user_tables
  3  where TABLE_NAME in ('DBI_T','DBI_AUDIT');

TABLE_NAME   LAST_ANALYZED
------------ --------------------
DBI_AUDIT    17-FEB-17
DBI_T        17-FEB-17

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'DBI_T';

CONSTRAINT_NAME                      C
------------------------------------ -
CHECK_X                              C
T_PK                                 P

SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
T_PK

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from user_tab_privs
  2  where table_name in ('DBI_T','DBI_AUDIT');

GRANTEE        OWNER          TABLE_NAME           GRANTOR        PRIVILEGE
-------------- -------------- -------------------- -------------- --------------------
HR             SCOTT          DBI_AUDIT            SCOTT          SELECT
HR             SCOTT          DBI_T                SCOTT          SELECT

SQL> select TRIGGER_NAME, TABLE_NAME, STATUS from user_triggers;

TRIGGER_NAME             TABLE_NA STATUS
------------------------ -------- --------
DBI_AFTER_UPDATE         DBI_T    ENABLED

Everything looks good. Up to date statistics, trigger is enabled and no objects with “BIN$xx” or something. The next step is a quite important one for this demo. I am just saving the SCN number before the “drop table” into a variable. In the real world, you need to find the SCN number yourself, e.g. with the TIMESTAMP_TO_SCN function.

SQL> column SCN new_val S
SQL> select dbms_flashback.get_system_change_number SCN from dual;

       SCN
----------
   1056212

After we got the SCN, we can drop the table and undrop it afterwards.

SQL> drop table dbi_t;

Table dropped.

SQL> flashback table dbi_t to before drop;

Flashback complete.

Let’s take a look how our constraints and index names look right now. Exactly like expected. They have this ugly “BIN$xxx” names, but we want the old names back.

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'DBI_T';

CONSTRAINT_NAME                      C
------------------------------------ -
BIN$SLt7vMNFZNbgU8k4qMDm6g==$0       C
BIN$SLt7vMNGZNbgU8k4qMDm6g==$0       P

SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
BIN$SLt7vMNHZNbgU8k4qMDm6g==$0

The trick is now to invoke a Flashback Query on the dictionary. Flashback query on the dictionary is not 100% supported, but it works. I just save the current index name into the variable “I” and the old name into variable “OI”.

SQL> column index_name new_val I
SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
BIN$SLt7vMNHZNbgU8k4qMDm6g==$0

SQL> column index_name new_val OI
SQL> select index_name from user_indexes as of scn &S
  2  where table_name = 'DBI_T';
old   1: select index_name from user_indexes as of scn &S
new   1: select index_name from user_indexes as of scn    1056212

INDEX_NAME
------------------------------------
T_PK

After I have the current and the old name in place, I can do an alter index and get my old name back.

SQL> alter index "&I" rename to "&OI";
old   1: alter index "&I" rename to "&OI"
new   1: alter index "BIN$SLt7vMNHZNbgU8k4qMDm6g==$0" rename to "T_PK"

Index altered.

SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
T_PK

 

I will do now exactly the same for the constraints and the trigger.

SQL> column constraint_name new_val CC
SQL> select constraint_name from user_constraints where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C';

CONSTRAINT_NAME
------------------------------------
BIN$SLt7vMNFZNbgU8k4qMDm6g==$0

SQL> column constraint_name new_val OCC
SQL> select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C';
old   1: select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C'
new   1: select constraint_name from user_constraints as of scn    1056212 where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'C'

CONSTRAINT_NAME
------------------------------------
CHECK_X


SQL> alter table DBI_T RENAME CONSTRAINT "&CC" TO "&OCC";
old   1: alter table DBI_T RENAME CONSTRAINT "&CC" TO "&OCC"
new   1: alter table DBI_T RENAME CONSTRAINT "BIN$SLt7vMNFZNbgU8k4qMDm6g==$0" TO "CHECK_X"

Table altered.

SQL> column constraint_name new_val PC
SQL> select constraint_name from user_constraints where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P';

CONSTRAINT_NAME
------------------------------------
BIN$SLt7vMNGZNbgU8k4qMDm6g==$0

SQL> column constraint_name new_val OPC
SQL> select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P';
old   1: select constraint_name from user_constraints as of scn &S where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P'
new   1: select constraint_name from user_constraints as of scn    1056212 where table_name = 'DBI_T' and CONSTRAINT_TYPE = 'P'

CONSTRAINT_NAME
------------------------------------
T_PK


SQL> alter table DBI_T RENAME CONSTRAINT "&PC" TO "&OPC";
old   1: alter table DBI_T RENAME CONSTRAINT "&PC" TO "&OPC"
new   1: alter table DBI_T RENAME CONSTRAINT "BIN$SLt7vMNGZNbgU8k4qMDm6g==$0" TO "T_PK"

Table altered.

SQL> col CONSTRAINT_NAME format a36
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'DBI_T';

CONSTRAINT_NAME                      C
------------------------------------ -
CHECK_X                              C
T_PK                                 P

SQL> col INDEX_NAME format a36
SQL> select index_name from user_indexes where table_name = 'DBI_T';

INDEX_NAME
------------------------------------
T_PK


SQL> select TRIGGER_NAME, TABLE_NAME,STATUS from user_triggers;

TRIGGER_NAME                     TABLE_NAME                       STATUS
-------------------------------- -------------------------------- --------
BIN$SLt7vMNIZNbgU8k4qMDm6g==$0   DBI_T                            ENABLED

SQL> column trigger_name new_val T
SQL> select trigger_name from user_triggers where table_name = 'DBI_T';

TRIGGER_NAME
--------------------------------
BIN$SLt7vMNIZNbgU8k4qMDm6g==$0

SQL> column trigger_name new_val OT
SQL> select trigger_name from user_triggers as of scn &S where table_name = 'DBI_T';
old   1: select trigger_name from user_triggers as of scn &S where table_name = 'DBI_T'
new   1: select trigger_name from user_triggers as of scn    1056212 where table_name = 'DBI_T'

TRIGGER_NAME
--------------------------------
DBI_AFTER_UPDATE

SQL> alter trigger "&T" RENAME TO "&OT";
old   1: alter trigger "&T" RENAME TO "&OT"
new   1: alter trigger "BIN$SLt7vMNIZNbgU8k4qMDm6g==$0" RENAME TO "DBI_AFTER_UPDATE"

Trigger altered.


SQL> select TRIGGER_NAME, TABLE_NAME, STATUS from user_triggers;

TRIGGER_NAME             TABLE_NAME             STATUS
------------------------ ---------------------- --------
DBI_AFTER_UPDATE         DBI_T                  ENABLED

The stats and the grants do come back automatically.

SQL> select TABLE_NAME, LAST_ANALYZED
  2  from user_tables
  3  where TABLE_NAME in ('DBI_T','DBI_AUDIT');

TABLE_NAME   LAST_ANALYZED
------------ --------------------
DBI_AUDIT    17-FEB-17
DBI_T        17-FEB-17


SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from user_tab_privs
  2  where table_name in ('DBI_T','DBI_AUDIT');

GRANTEE        OWNER          TABLE_NAME           GRANTOR        PRIVILEGE
-------------- -------------- -------------------- -------------- --------------------
HR             SCOTT          DBI_AUDIT            SCOTT          SELECT
HR             SCOTT          DBI_T                SCOTT          SELECT

 

Conclusion

The Flashback Drop feature does not just bring back your table. It does much more, it brings back your grants, the trigger, the statistics, the indexes and the constraints as well. If you are lucky, you can even combine it with the Flashback Query to retrieve your old names for the indexes, constraints and triggers.

 

Cet article Oracle 12c – Combining Flashback Drop and Flashback Query est apparu en premier sur Blog dbi services.

OEL 7 – How to disable IPv6 on Oracle Linux 7 – Follow Up

Fri, 2017-02-17 02:56

This is a follow up to the Blog were I explained how to disable IPv6 on Oracle Linux 7.

If you have done all the steps which I have explained here http://blog.dbi-services.com/oel-7-how-to-disable-ipv6-on-oracle-linux-7/  then you have already IPv6 successfully disabled. However, some tools require some special attention afterwards if you want to avoid some ugly warning or error messages. There are so many tools that can use IPv4 and IPv6, but it is impossible to mention all of them. I will just dig a little deeper into the following 4.

  • Postfix
  • Oracle
  • NFS
  • rsyslogd
Postfix

Let’s start with Postfix. This might be one of the first warning messages you see, in case you have disabled IPv6 on your system. If you receive the following warning message when you try to send an email, then you need to adjust your /etc/postfix/main.cf file.

$ mailx -s "Test" xxx.xxx@xxx.com
Test
.
EOT
$ send-mail: warning: inet_protocols: IPv6 support is disabled: Address family not supported by protocol
send-mail: warning: inet_protocols: configuring for IPv4 support only
postdrop: warning: inet_protocols: IPv6 support is disabled: Address family not supported by protocol
postdrop: warning: inet_protocols: configuring for IPv4 support only

The solution is to configure your /etc/postfix/main.cf file to allow only the ipv4 protocol.

[root@SVPCHODAC01 sbin]# /usr/sbin/postconf | grep inet_protocols
inet_protocols = all
/usr/sbin/postconf: warning: inet_protocols: IPv6 support is disabled: Address family not supported by protocol
/usr/sbin/postconf: warning: inet_protocols: configuring for IPv4 support only

[root@SVPCHODAC01 sbin]# cd /etc/postfix/
[root@SVPCHODAC01 postfix]# cp main.cf main.cf.20170203a
[root@SVPCHODAC01 postfix]# vi main.cf

Change “inet_protocols = all”  to “inet_protocols = ipv4″ and then restart PostFix.

[root@SVPCHODAC01 postfix]# /etc/init.d/postfix restart
Shutting down postfix: [ OK ]
Starting postfix: [ OK ]

[root@SVPCHODAC01 postfix]# /usr/sbin/postconf | grep inet_protocols
inet_protocols = ipv4

That’s it. Now the ugly Postfix warning messages disappear.

Oracle

The next candidate is the Oracle Listener. In some situations,  you might see the following error message in your listener.log file when working with Cloud Control 12c.

TNS-01189: The listener could not authenticate the user

This is related to an Oracle bug, to be more precise, it is “BUG 16054202 – TNLIN EXTRACTS WRONG SUBNETMASK FOR IPV6 ADDRESSES”. The bug can be fixed by configuring the Oracle Listener to work with IPv4 only. This is done via the listener.ora IP parameter, which knows the following options.

IP=FIRST

Listen on the first IP address returned by the DNS resolution of the host name.
If the user wants the listener to listen on the first IP to which the specified host name resolves,
then the address must be qualified with (IP=first).

IP=V4_ONLY

Listen only on IPv4 addresses.

IP=V6_ONLY

Listen only on IPv6 addresses.

Simply put the (IP=V4_ONLY) after your PORT setting, and then restart the listener like shown in the following example.

-- listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbidg03)(PORT = 1521)(IP=V4_ONLY))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

-- restart

$ lsnrctl stop LISTENER; lsnrctl start LISTENER

Now the messages “TNS-01189: The listener could not authenticate the user” in the listener.log should disappear.

 

NFS

Under normal circumstances, no changes should be required for NFS unless you had proto=tcp6 configured for your mount options. If so, then your mount will not work anymore.

[root@dbidg02 etc]# mount /u99
mount.nfs: an incorrect mount option was specified

And you will see the following error in the /var/log/messages file.

Feb 14 10:26:48 dbidg02 kernel: NFS: server address does not match proto= option

Now you could either remove the proto option or change it to proto=tcp.

For NFS version 4 you have the following options:

proto=netid The netid determines the transport that is used to communicate with the NFS server. Supported options are tcp, tcp6, and rdma. tcp6 use IPv6 addresses and is only available if support for TI-RPC is built in. Both others use IPv4 addresses.

In my case, I have added the proto=tcp option to my NFS mount table in the /etc/fstab

#-- NFS mounts
dbidg03:/u99   /u99  nfs  vers=4.1,proto=tcp,rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,timeo=600    

-- And now the mount works perfectly again.

[root@dbidg02 etc]# mount /u99
[root@dbidg02 etc]#
[root@dbidg02 etc]# mount | grep nfs
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw,relatime)
nfsd on /proc/fs/nfsd type nfsd (rw,relatime)
dbidg03:/u99 on /u99 type nfs4 (rw,relatime,vers=4.1,rsize=32768,wsize=32768,namlen=255,hard,proto=tcp,port=0,timeo=600,retrans=2,sec=sys,clientaddr=192.168.56.202,local_lock=none,addr=192.168.56.203)

Now the NFS mount works again.

rsyslogd

Almost the same applies to the rsyslogd. In case you have not specified “-6″ in your syslogd options, you are fine. If not, you need to either remove the option or replace it with “-4″

oracle@dbidg03:/etc/sysconfig/ [oms13c] rpm -qa  | grep rsyslog
rsyslog-7.4.7-16.0.1.el7.x86_64

-- from the doc
 -4  Causes rsyslogd to listen to IPv4 addresses only.  If neither -4 nor -6 is given, rsyslogd listens to all configured addresses of the system.
[root@dbidg03 sysconfig]# cat rsyslog
# Options for rsyslogd
# Syslogd options are deprecated since rsyslog v3.
# If you want to use them, switch to compatibility mode 2 by "-c 2"
# See rsyslogd(8) for more details
SYSLOGD_OPTIONS="-4"

[root@dbidg03 sysconfig]# systemctl restart rsyslog
[root@dbidg03 sysconfig]#
Conclusion

There might be some tools on your system that requires special attention after you have disable IPv6 on your system.

 

 

Cet article OEL 7 – How to disable IPv6 on Oracle Linux 7 – Follow Up est apparu en premier sur Blog dbi services.

SQL Server Failover Cluster on Linux and synchronizing uids/gids across nodes

Thu, 2017-02-16 13:13

In my previous blog post, I talked about SQL Server on Linux and high availability. During my test, I used a NFS server to share disk resources between my cluster nodes as described in the Microsoft documentation. A couple of days ago, I decided to add a fourth node (LINUX04) to my cluster infrastructure and I expected to do this work easily. But no chance, I faced a problem I never had before on this infrastructure.

blog 117 - 01 - sqlfcionlinux - archi

Switching over this last node led to a failed SQL Server FCI resource. After digging into the problem, I found out the root from the SQL Server error log as shown below:

[mikedavem@linux04 ~]$ sudo cat /var/opt/mssql/log/errorlog
2017-02-12 18:55:15.89 Server      Microsoft SQL Server vNext (CTP1.2) - 14.0.200.24 (X64)
        Jan 10 2017 19:15:28
        Copyright (C) 2016 Microsoft Corporation. All rights reserved.
        on Linux (CentOS Linux 7 (Core))
2017-02-12 18:55:15.89 Server      UTC adjustment: 0:00
2017-02-12 18:55:15.89 Server      (c) Microsoft Corporation.
2017-02-12 18:55:15.89 Server      All rights reserved.
2017-02-12 18:55:15.89 Server      Server process ID is 4116.
2017-02-12 18:55:15.89 Server      Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-02-12 18:55:15.89 Server      Registry startup parameters:
         -d C:\var\opt\mssql\data\master.mdf
         -l C:\var\opt\mssql\data\mastlog.ldf
         -e C:\var\opt\mssql\log\errorlog
2017-02-12 18:55:15.91 Server      Error: 17113, Severity: 16, State: 1.
2017-02-12 18:55:15.91 Server      Error 2(The system cannot find the file specified.) occurred while opening file 'C:\var\opt\mssql\data\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2017-02-12 18:55:15.91 Server      SQL Server shutdown has been initiated

 

Well, the error speaks for itself and it seems I’m concerned by a file access permission in my case. My first reflex was to check the corresponding permissions on the corresponding NFS folder.

[mikedavem@linux04 ~]$ sudo ls -lu /var/opt/mssql/data
To.al 53320
drwxr-----. 2 995 993     4096 Feb 14 23:12 lost+found
-rwxr-----. 1 995 993  4194304 Feb 14 23:19 master.mdf
-rwxr-----. 1 995 993  2097152 Feb 14 23:19 mastlog.ldf
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 modellog.ldf
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 model.mdf
-rwxr-----. 1 995 993 13959168 Feb 14 23:19 msdbdata.mdf
-rwxr-----. 1 995 993   786432 Feb 14 23:19 msdblog.ldf
drwxr-----. 2 995 993     4096 Feb 14 23:08 sqllinuxfci
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 tempdb.mdf
-rwxr-----. 1 995 993  8388608 Feb 14 23:19 templog.ldf

 

According to the output above we may claim this is a mismatch issue between uids/guids of the mssql user across the cluster nodes. At this stage, I remembered performing some tests including creating some linux users before adding my fourth node leading to create a mismatch for the mssql user’s uids/gids. Just keep in mind that the SQL Server installation creates a mssql user by default with the next available uid/gid. In my case uid and guid.

Let’s compare mssql user uid/gid from other existing nodes LINUX01 / LINUX02 and LINUX03:

[mikedavem@linux04 ~]$ id mssql
uid=997(mssql) gid=995(mssql) groups=995(mssql)
[mikedavem@linux04 ~]$ ssh linux01 id mssql
…
[root@linux04 ~]# ssh linux01 id mssql
uid=995(mssql) gid=993(mssql) groups=993(mssql)
…
[root@linux04 ~]# ssh linux02 id mssql
uid=995(mssql) gid=993(mssql) groups=993(mssql)
…
[root@linux04 ~]# ssh linux03 id mssql
uid=995(mssql) gid=993(mssql) groups=993(mssql)

 

Ok this explains why I faced this permission issue. After investing some times to figure out how to get rid of this issue without changing the mssql user’s uid/guid, I read some discussions about using NFS4 which is intended to fix this uids/gids mapping issue. It seems to be perfect in my case! But firstly let’s just confirm I’m using the correct NFS version

[mikedavem@linux04 ~]$ mount -v | grep nfs
nfsd on /proc/fs/nfsd type nfsd (rw,relatime)
192.168.5.14:/mnt/sql_data_nfs on /var/opt/mssql/data type nfs (rw,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.5.14,mountvers=3,mountport=20048,mountproto=udp,local_lock=none,addr=192.168.5.14)

 

Well, my current configuration is not ready to leverage NFS4 yet and some configuration changes seem to be required to address it.

Firstly, let’s change fstype parameter of my FS resource to nfs4 to mount the NFS share with NFS4.

[mikedavem@linux04 ~]$ sudo pcs resource show FS
 Resource: FS (class=ocf provider=heartbeat type=Filesystem)
  Attributes: device=192.168.5.14:/mnt/sql_data_nfs directory=/var/opt/mssql/data fstype=nfs
  Operations: start interval=0s timeout=60 (FS-start-interval-0s)
              stop interval=0s timeout=60 (FS-stop-interval-0s)
              monitor interval=20 timeout=40 (FS-monitor-interval-20)

[mikedavem@linux04 ~]$ sudo pcs resource update FS fstype=nfs4
[mikedavem@linux04 ~]$ sudo pcs resource restart FS
FS successfully restarted

 

Then I had to perform some updates to my idmap configuration on both sides (NFS server and client as well) to make the mapping working correctly. The main steps were as follows:

  • Enabling idmap with NFS4 (disabled by default in my case)
  • Changing some parameters inside the /etc/idmap.conf
  • Verifying idmap is running correctly.
[root@nfs sql_data_nfs]# echo N >  /sys/module/nfsd/parameters/nfs4_disable_idmapping
…
[root@nfs sql_data_nfs]# grep ^[^#\;] /etc/idmapd.conf
[General]
Domain = dbi-services.test
[Mapping]
Nobody-User = nobody
Nobody-Group = nobody
[Translation]
Method = static

[Static]
mssql@dbi-services.test = mssql
testp@dbi-services.test = testp
…
[root@nfs sql_data_nfs]# systemctl status nfs-idmap
. nfs-idmapd.service - NFSv4 ID-name mapping service
   Loaded: loaded (/usr/lib/systemd/system/nfs-idmapd.service; static; vendor preset: disabled)
   Active: active (running) since Wed 2017-02-15 20:29:57 CET; 1h 39min ago
  Process: 3362 ExecStart=/usr/sbin/rpc.idmapd $RPCIDMAPDARGS (code=exited, status=0/SUCCESS)
 Main PID: 3364 (rpc.idmapd)
   CGroup: /system.slice/nfs-idmapd.service
           └─3364 /usr/sbin/rpc.idmapd

 

At this point, listening user permissions shows nobody/nobody meaning translation is not performed yet.

[root@linux04 ~]# ls -lu /var/opt/mssql
total 16
drwxr-----. 2 nobody nobody 4096 Feb 15 19:00 data
…

 

I forgot to create a corresponding mssql user on the NFS service side. Let’s do it:

 [root@nfs sql_data_nfs]# groupadd mssql -g 993
[root@nfs sql_data_nfs]# useradd -u 995 -g 993 mssql –M

 

After remounting the NFS share, I finally got the expected output as shown below:

[root@linux04 ~]# mount -o remount -t nfs4 192.168.5.14:/mnt/sql_data_nfs/sqllinuxfci /mnt/testp/
…
[root@linux04 ~]# ls -lu /var/opt/mssql
total 16
drwxr-----. 2 mssql mssql 4096 Feb 15 19:00 data
…
[root@linux04 ~]# ls -lu /var/opt/mssql/data/*
-rwxr-----. 1 mssql mssql  4194304 Feb 15 19:53 /var/opt/mssql/data/master.mdf
-rwxr-----. 1 mssql mssql  2097152 Feb 15 19:53 /var/opt/mssql/data/mastlog.ldf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/modellog.ldf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/model.mdf
-rwxr-----. 1 mssql mssql 13959168 Feb 15 19:53 /var/opt/mssql/data/msdbdata.mdf
-rwxr-----. 1 mssql mssql   786432 Feb 15 19:53 /var/opt/mssql/data/msdblog.ldf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/tempdb.mdf
-rwxr-----. 1 mssql mssql  8388608 Feb 15 19:53 /var/opt/mssql/data/templog.ldf

 

This time the translation is effective but let’s perform another test by running the previous command as the mssql user

[root@linux04 ~]# runuser -l mssql -c 'ls -lu /var/opt/mssql/data/*'
ls: cannot access /var/opt/mssql/data/*: Permission denied

The problem starts when I try to access the database files despite the correct mapping configuration. I spent some time to understand that some misconceptions about how NFSv4 and magic mismatch uids/gids fix subsist. I admit the main documentation is not clear about it but please, feel free to comment if it is not the case. After digging into further pointers, I was able to understand that NFS itself doesn’t achieve authentication but delegates it down to the RPC mechanism. If we take a look down at the RPC’s security, we may notice it hasn’t been updated to support such matching. Basically, it continues to use the historic authentication called AUTH_SYS meaning sending uids/gis over the network. Translation work comes later through the idmap service. The only way to get rid of this issue would be to prefer another protocol like RPCSEC_GSS which includes authentication based on LDAP or Kerberos for example.

 The bottom line here is that SQL Server on Linux is not an exception of course. If we want to continue using basic Unix authentication, keeping synchronizing uids and guids across my cluster nodes seems to be a good way to go. Using Kerberos authentication in this case? This is another story that I will try to tell in another blog post!

Happy clustering on Linux!

 

 

 

 

Cet article SQL Server Failover Cluster on Linux and synchronizing uids/gids across nodes est apparu en premier sur Blog dbi services.

Windows Server 2016 RTM and Docker

Thu, 2017-02-16 08:56

I wrote some months ago several blog posts about the new Container feature of Windows Server 2016. Here is the list:

Windows Server 2016: Containers
Windows Containers: Migration
Windows Container: installation and utilization

Today, I will install the Container feature, install Docker and deploy a container.
First of all, I need to enable the Container and the Hyper-V feature, take care if you use VirtualBox because after having enable Hyper-V this won’t work anymore.
Windows Server 2016 support only Hyper-V container and no more Windows Container. So I will check if those features are already enable on my server and if it is not the case enable its, don’t forget to map the Windows Server 2016 iso file to your Virtual Machine.
To do it just run this PowerShell cmdlet:
Docker1
docker2
docker3

Now, both features are installed in my VM and I can install Docker.
To do so I will download the Docker Engine and Client from the Docker project library here to the folder c:\Temp and unzip the file into c:\ProgramFiles:
docker4
docker5

I have my Docker folder with Docker executable files and binaries. Dockerd.exe for the Docker engine and docker.exe for the client:
docker6
I add also the Docker path to the path environment variable:
docker7
Optionally, we could add it forever:
docker8
I will install Docker as a Service and start it:
docker9
Docker is installed and started, I’m now able to use it. I don’t have any images for the moment but the Microsoft/nanoserver image is available in the Docker Hub:
docker10
Let’s download this Nano Server base OS image from the Hub:
docker11
Now that I have my Docker OS image, I will start an interactive session with this image:
docker12
The container starts and we I accessed to the command prompt where I could check the processes running in my container like PowerShell, cmd…:
docker13
I will create a PowerShell script in my container to write a Welcome Container message and exit from my container:
docker14
I can now see my new container with the following command:
docker15
I will now create a new image from my container’s changes named mywelcomecontainer (it’s not possible to use Upper case for the new container name otherwise you will receive this error “repository name must be lowercase”):
docker16
I can finally run my container, it means that a Hyper-V container will be created from my new image named mywelcomecontainer and my PowerShell script will be executed from my container:
docker17
As I avoid the Docker run option –rm, I still have my container available if I run a Docker ps –a command. In order to delete my container I can run a Docker rm <containerid>, rmi will delete images if needed.

To conclude, it looks very easy to create images and Hyper-V containers with Docker in Windows Server 2016. The power of Docker is now available in the Windows World and for sure will be used more and more commonly.

 

Cet article Windows Server 2016 RTM and Docker est apparu en premier sur Blog dbi services.

Oracle 12c – Issues with the HEATMAP Segment even if the heat map feature is not used

Mon, 2017-02-13 04:55

When I don’t need I feature, I don’t turn it on, or do not use it because it reduces the possibility to run into issues. Most of the times this is true, however, during the preparation for an RMAN workshop, the RMAN list failure command showed me the following dictionary issue.

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected        Summary
---------- -------- --------- -------------------- -------
2          CRITICAL OPEN      13-FEB-2017 10:12:26 SQL dictionary health check: seg$.type# 31 on object SEG$ failed

I thought first, that it might be related to some incorrect errors shown by the health check (DBMS_HM), because there used to be some issues with that tool. But even after applying the following patch, nothing changed and the error still appears.

19543595: INCORRECT HEALTHCHECK ERRORS FROM DBMS_HM – FALSE ERRORS ON TS$ , FILE$ OR USER

So I started a manual health check again to get some more details.

SQL> BEGIN
  2  DBMS_HM.RUN_CHECK (check_name => 'Dictionary Integrity Check',
  3  run_name => 'WilliamsDICTrun002',
  4  input_params => 'CHECK_MASK=ALL');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_HM.GET_RUN_REPORT('WilliamsDICTrun002') from dual;

DBMS_HM.GET_RUN_REPORT('WILLIAMSDICTRUN002')
---------------------------------------------------------------------
Basic Run Information
 Run Name                     : WilliamsDICTrun002
 Run Id                       : 61
 Check Name                   : Dictionary Integrity Check
 Mode                         : MANUAL
 Status                       : COMPLETED
 Start Time                   : 2017-02-13 10:56:58.250100 +01:00
 End Time                     : 2017-02-13 10:56:58.689301 +01:00
 Error Encountered            : 0
 Source Incident Id           : 0
 Number of Incidents Created  : 0

Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL

Run Findings And Recommendations
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 62
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: seg$.type# 31 on object SEG$
               failed
 Message       : Damaged rowid is AAAAAIAABAAAK+RAAc - description: Ts# 1
               File# 2 Block# 28032 is referenced

Now I do have the ROWID, the file number and the block number of the affecting object. Let’s see what it is.

SQL> select FILE#, BLOCK#, TYPE#, TS#, BLOCKS from seg$ where rowid='AAAAAIAABAAAK+RAAc';

     FILE#     BLOCK#      TYPE#        TS#     BLOCKS
---------- ---------- ---------- ---------- ----------
         2      28032         11          1       1024
		 

SQL> SELECT segment_name, segment_type, block_id, blocks
  2  FROM   dba_extents
  3  WHERE
  4  file_id = 2
  5  AND
  6  ( 28032 BETWEEN block_id AND ( block_id + blocks ) );

SEGMENT_NAME               SEGMENT_TYPE               BLOCK_ID     BLOCKS
-------------------------- ------------------------ ---------- ----------
HEATMAP                    SYSTEM STATISTICS             28032       1024

Really strange. It is related to the HEATMAP segment, but I am not using the heat map feature, or used it in the past.

SQL> show parameter heat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF

SQL> select name, DETECTED_USAGES from DBA_FEATURE_USAGE_STATISTICS where name like 'Heat%';

NAME                     DETECTED_USAGES
------------------------ ---------------
Heat Map                               0

But how can I get this fixed now? You could either ignore this issue, create a SR at Oracle, or you can drop the statistics segment, in case you are not using the heatmap feature.

In my case, I decided to the drop the statistics segment by issuing the following command. Dropping the statistics segment works by setting the underscore parameter “_drop_stat_segment” to 1.

SQL> select SEGMENT_NAME, SEGMENT_TYPE from dba_extents where SEGMENT_TYPE = 'SYSTEM STATISTICS';

SEGMENT_NAME               SEGMENT_TYPE
-------------------------- ------------------------
HEATMAP                    SYSTEM STATISTICS

SQL> ALTER SYSTEM SET "_drop_stat_segment"=1 scope=memory;

System altered.

SQL> select SEGMENT_NAME, SEGMENT_TYPE from dba_extents where SEGMENT_TYPE = 'SYSTEM STATISTICS';

no rows selected

The heat map table is gone now. Let’s run the dictionary check again.

SQL> BEGIN
  2  DBMS_HM.RUN_CHECK (check_name => 'Dictionary Integrity Check',
  3  run_name => 'WilliamsDICTrun003',
  4  input_params => 'CHECK_MASK=ALL');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_HM.GET_RUN_REPORT('WilliamsDICTrun003') from dual;

DBMS_HM.GET_RUN_REPORT('WILLIAMSDICTRUN003')
---------------------------------------------------------------------
Basic Run Information
 Run Name                     : WilliamsDICTrun003
 Run Id                       : 81
 Check Name                   : Dictionary Integrity Check
 Mode                         : MANUAL
 Status                       : COMPLETED
 Start Time                   : 2017-02-13 11:17:15.190873 +01:00
 End Time                     : 2017-02-13 11:17:15.642501 +01:00
 Error Encountered            : 0
 Source Incident Id           : 0
 Number of Incidents Created  : 0

Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL

Run Findings And Recommendations


RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

no failures found that match specification

 

Looks much better now.

Conclusion

Even if you are not using some features, you can still have trouble with them. :-)

 

Cet article Oracle 12c – Issues with the HEATMAP Segment even if the heat map feature is not used est apparu en premier sur Blog dbi services.

12cR2 DBCA can create a standby database

Sun, 2017-02-12 15:33

Do you like DBCA to create a database from command line, with -silent -createDatabase? On a simple command line you can provision a database, with oratab, tnsnames.ora directory creation and any setting you want. And you can even call a custom script to customize further. But if you want to put it in Data Guard, you have to do the duplicate manually with RMAN. This evolves in 12.2 with a new option in DBCA to do that: dbca -silent -createDuplicateDB -createAsStandby

Limitations

I’ve tried in the Oracle Public Cloud where I just created a RAC database. But unfortunately, this new feature is only for Single Instance:

[FATAL] [DBT-16056] Specified primary database is not a Single Instance (SI) database.
CAUSE: Duplicate database operation is supported only for SI databases.

Ok. RAC is complex enough anyway, so you don’t need that quick command line to create the standby. So I tried with a single instance database:

[FATAL] [DBT-16057] Specified primary database is a container database (CDB).
CAUSE: Duplicate database operation is supported only for non container databases.

Ok. That a bit surprising to have a new feature in 12.2 that works only on the architecture that is deprecated in 12.1 but if we think about it, DBCA is for fast provisioning. In multitenant you create a CDB once, put it in Data Guard, and the fast provisioning comes with the ‘create pluggable database’. And deprecated doesn’t mean that we do not use it, and it is good to have a simple command line tools for easy provisioning in non-CDB.

Then, I tried on a non-CDB that I’ve created in 12.2

I’m a big fan of EZCONNECT but I had a few problems with it. What’s worth to know is that there is no ‘impossible to connect’ message. When it cannot connect, the following message is raised:

[FATAL] [DBT-16051] Archive log mode is not enabled in the primary database.
ACTION: Primary database should be configured with archive log mode for creating a duplicate or standby database.

just because this is the first thing that DBCA checks and this is where it fails when connections is not ok.

But you can also use a tnsnames.ora network service name. This is what I’ll use for -primaryDBConnectionString

$ tnsping ORCLA
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 11-FEB-2017 22:28:35
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MAA.compute-usslash.oraclecloud.internal)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcla.compute-usslash.oraclecloud.internal)))
OK (0 msec)

-createDuplicateDB -createAsStandby

Here is an example:

dbca -silent -createDuplicateDB -gdbName ORCLB.compute-usslash.oraclecloud.internal -sid ORCLB -sysPassword "Ach1z0#d" -primaryDBConnectionString ORCLA -createAsStandby -dbUniquename ORCLB

This will connect RMAN to the target (here called ‘primary’), with the connect string ORCLA and run a duplicate to create ORCLB as specified.

It starts to create a temporary listener (which is still there in listener.ora even after completion), create the auxiliary instance and run RMAN:
Listener config step
33% complete
Auxiliary instance creation
66% complete
RMAN duplicate
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCLB/orcla.log" for further details.

Through RMAN API, the file names are set:


run {
set newname for datafile 1 to '/u01/app/oracle/oradata/orclb/system01.dbf' ;
set newname for datafile 3 to '/u01/app/oracle/oradata/orclb/sysaux01.dbf' ;
set newname for datafile 4 to '/u01/app/oracle/oradata/orclb/undotbs01.dbf' ;
set newname for datafile 7 to '/u01/app/oracle/oradata/orclb/users01.dbf' ;
set newname for tempfile 1 to '/u01/app/oracle/oradata/orclb/temp01.dbf' ;

and the DUPLICATE FOR STANDBY FROM ACTIVE is run:

duplicate target database
for standby
from active database
dorecover
spfile
set 'db_recovery_file_dest_size'='8405385216'
set 'compatible'='12.2.0'
set 'undo_tablespace'='UNDOTBS1'
set 'dispatchers'='(PROTOCOL=TCP) (SERVICE=ORCLAXDB)'
set 'db_name'='orcla'
set 'db_unique_name'='ORCLB'
set 'sga_target'='2281701376'
set 'diagnostic_dest'='/u01/app/oracle'
set 'audit_file_dest'='/u01/app/oracle/audit'
set 'open_cursors'='300'
set 'processes'='300'
set 'nls_language'='AMERICAN'
set 'pga_aggregate_target'='757071872'
set 'db_recovery_file_dest'='/u01/app/oracle/fast_recovery_area/orcla'
set 'db_block_size'='8192'
set 'log_archive_format'='%t_%s_%r.dbf'
set 'nls_territory'='AMERICA'
set 'control_files'="/u01/app/oracle/oradata/orclb/control01.ctl", "/u01/app/oracle/fast_recovery_area/orcla/ORCLB/control02.ctl"
set 'audit_trail'='DB'
set 'db_domain'='compute-usslash.oraclecloud.internal'
set 'remote_login_passwordfile'='EXCLUSIVE'
reset 'local_listener'
reset 'db_file_name_convert'
set 'log_archive_dest_1'='location=/u01/app/oracle/fast_recovery_area/orcla'
reset 'event'
reset 'remote_listener'
nofilenamecheck;
}

The parameters are coming from the ‘primary’ and adapted for the new database. Be careful. This is where I prefer to review the parameters before. For example, when you duplicate to clone the primary (without the -createAsStandby) you probably don’t want to keep the same log_archive_dest that was set in a Data Guard configuration. I’ll have to post a blog about that.

At the end, the standby database is opened read-only, so be careful to close it before starting the apply of redo if you don’t have the Active Data Guard option.

Data Guard

DBCA doesn’t go beyond the DUPLICATE. And you can use it also in Standard Edition to setup the manual standby.

I hope that one day we will have an option to create the Data Guard configuration in the same process, but here you have to do it yourself:

  • No tnsnames.ora entry is added for the standby
  • The static listener entries are not added in listener.ora
  • No Data Guard configuration is there
  • The Data Guard Broker is not started except if it was set in advance to true on primary
  • No standby redo logs are created (except when they were present on primary)

You can set dg_broker_start=true and create the standby redo logs on a post-script that you call with the -customScripts argument. However, the best way is to do it in advance on the primary, and then the duplicate will do the same on the standby.

So what?

You don’t need this new feature because it is easy to automate it yourself. It’s just a copy of spfile parameters, with a few change, and a RMAN duplicate command. But your scripts will be specialized for your environment. Generic scripts are more complex to maintain. The big advantage to have this integrated on DBCA is that is designed for all configurations, and is maintained through versions.

 

Cet article 12cR2 DBCA can create a standby database est apparu en premier sur Blog dbi services.

Pages