Infrastructure at your Service

dbi services BLOG

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

Franck Pachot

Oracle 12cR2: Online tablespace encryption

By | Database Administration & Monitoring | One Comment
 

 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…

Read More
Franck Pachot

12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard

By | Database Administration & Monitoring | No Comments
 

 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,…

Read More
William Sescu

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

By | Database Administration & Monitoring, Database management | No Comments
 

 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,…

Read More

BLOG Technoark 2017 Conference – Blockchain, beyond the bitcoin.

By | Database Administration & Monitoring | No Comments
 

 BLOG Technoark 2017 Conference – Blockchain, beyond the bitcoin. and How applying Blockchain for Enterprise security and data management. 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…

Read More
Daniel Westermann

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

By | Database Administration & Monitoring | No Comments
 

 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…

Read More
Franck Pachot

12cR2: lockdown profiles and ORA-01219

By | Database Administration & Monitoring | One Comment
 

 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.

Read More
Mouhamadou Diaw

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

By | Database Administration & Monitoring | No Comments
 

 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…

Read More
William Sescu

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

By | Database Administration & Monitoring, Database management | No Comments
 

 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…

Read More
Daniel Westermann

Converting a column from one data type to another in PostgreSQL

By | Database Administration & Monitoring | 2 Comments
 

 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…

Read More
Franck Pachot

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

By | Database Administration & Monitoring | 2 Comments
 

 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,…

Read More
William Sescu

Oracle 12c – Combining Flashback Drop and Flashback Query

By | Database Administration & Monitoring, Database management | No Comments
 

 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…

Read More