Oracle database

  • How to ensure the Oracle database security?

    Database security has several aspects. First, there is authentication: Who can connect? How does one identify them? Second, there is authorization: What are users allowed to do? How does one restrict their actions? Finally, there is auditing: Given that users can connect and perform certain actions, how do you track what they are doing? These aspects are covered in this short note.

  • How to view Oracle database processes running on the Linux server?

    Problem

    You want to view processes currently running on the Oracle database server (Linux / Solaris).

    Solution

    To view process information, use the ps (process status) utility. If you type the ps command without any parameters, you see all processes that have been started by the user you’re currently logged on as:

  • Implementing a Hot Backup Strategy for Oracle Database 12C

    As discussed previously, RMAN should be your tool of choice for any type of Oracle database 12C backup (either online or offline). RMAN is more efficient than user-managed backups and automates most tasks. Having said that, one of the best ways to gain an understanding of Oracle backup and recovery internals is to make a hot backup and then use that backup to restore and recover your database. Manually issuing the commands involved in a hot backup, followed by a restore and recovery, helps you understand the role of each type of file (control files, data files, archive redo logs, online redo logs) in a restore-and-recovery scenario.

  • Incomplete Recovery for Archivelog Mode Oracle Database 12C

    Incomplete recovery means that you don’t restore all transactions that were committed before the failure. With this type of recovery, you’re recovering to a point in time in the past, and transactions are lost. This is why incomplete recovery is also known as database point-in-time recovery (DBPITR).

  • Isolating GC Buffer Busy Waits in Oracle Database 12c

    Isolating GC Buffer Busy Waits in OracleThe three key tools that every Oracle DBA who has worked on a database since Oracle 10g should know about - Automatic Database Diagnostic Monitor (ADDM), Automatic Workload Repository (AWR) reports, and Active Session History (ASH) reports - are also extremely useful for detecting performance issues related to the gc buffer busy wait event. The next sections explain exactly how to locate crucial information about the statements, user sessions, and database objects that are causing an Oracle database to perform poorly because of high occurrences of the gc buffer busy wait event.

  • Issues with DTP, XA and Rollback Segments in an Oracle Database

    Issues with DTP, XA and Rollback Segments in OracleOracle Database applications that connect to multiple databases via database links or that use XA (extended Architecture) for distributed transaction processing (DTP) may occasionally experience issues with pending transactions. When such issues arise, it’s not unusual to encounter extreme contention, often to the point that the database instance “hangs,” which may cause all database DML operations to halt until the problem is resolved. The following checklist and corresponding scripts will help to ascertain if an Oracle database instance is in danger of hanging due to issues with DTP via XA:

  • Managing Oracle 12C Control Files with examples

    A control file is a small binary file that stores information such as the database name, names and locations of data files, names and locations of online redo log files, current online redo log sequence number, checkpoint information, and names and locations of RMAN backup files (if using). You can query much of the information stored in the control file from data dictionary views. This example displays the types of information stored in the control file by querying V$CONTROLFILE_RECORD_SECTION:

  • Managing Oracle Database 12C Online Redo Logs

    Online redo logs store a record of transactions that have occurred in your Oracle Database 12C. These logs serve the following purposes:

    • Provide a mechanism for recording changes to the database so that in the event of a media failure, you have a method of recovering transactions.
    • Ensure that in the event of total instance failure, committed transactions can be recovered (crash recovery) even if committed data changes have not yet been written to the data files.
    • Allow administrators to inspect historical database transactions through the Oracle LogMiner utility.
    • They are read by Oracle tools such as GoldenGate or Streams to replicate data.
  • Matching Oracle 12c Table Types to Business Requirements

    Selecting Oracle 12c Table TypesProblem

    You’re new to Oracle Database 12c and have read about the various table types available. For example, you can choose between heap-organized tables, index-organized tables, and so forth. You want to build a database application and need to decide which table type to use.

    Solution

    Oracle provides a wide variety of table types. The default table type is heap-organized. For most applications, a heap-organized table is an effective structure for storing and retrieving data. However, there are other table types that you should be aware of, and you should know the situations under which each table type should be implemented. Table 1 describes each table type and its appropriate use.

  • Oracle 12c: a New Cloud Consolidation Era in IT

    Oracle database 12c Cloud Consolidation capabilitiesLet’s start by taking a brief look at the history of database use, before introducing the architecture of the future. As you can see in Figure 1-1, we will not refer to dates, but version numbers, going back to the time when the Oracle Database became predominant. 

    When Oracle Database 8i and 9i were on the market, midrange computers became prevalent in data centers. We were moving from the mainframe era to a client/server era, and Oracle architecture was very well suited for that. Written in the C programming language, it was available on multiple platforms and with all user management contained within its data dictionary. It was ready for the client/server architecture, using the OS only to listen on a TCP/IP port and to store files. Furthermore, the architecture was scalable on minicomputers, thanks to the parallel server feature, which would later become RAC (Real Application Clusters).

     

  • Oracle Database 12C Backup: understanding RMAN

    RMAN is Oracle’s flagship B&R tool. RMAN is provided by default when you install the Oracle software (for both the Oracle Database 12C  Standard Edition and Enterprise Edition). RMAN offers a robust and flexible set of B&R features. The following list highlights some of the most salient qualities:

  • Oracle Database 12C Files in Support of Backup and Recovery Operations

    An Oracle database 12C consists of three types of mandatory files: control files, online redo logs, and data files. This chapter looks at the basics of managing these critical files. My blog also discusses how to implement archiving (which generates archive redo log files) and enabling a fast recovery area (FRA). Understanding how to enable archiving and how to manage archive redo logs is a crucial part of database administration and backup and recovery. It’s also important to understand basic FRA concepts as RMAN (unless otherwise directed) will by default write backup files to the FRA.

  • Oracle Database 12c JVM Features

    The Oracle Database 12c Java Virtual Machine (JVM) has matured. The following new features are available in this release of the database:

    • The internal Oracle JVM is now compatible with Standard Edition of Java 6 by default, and configurable with Java 7.
    • Oracle JVM enhancements include support for the loadjava utility, list-based operations with dropjava, the ability to resolve external class references by using the ojvmmtc utility, and increased functionality in the ojvmjava tool.
    • Database-resident JAR files have been introduced, which means that when you load a JAR file, you now have the option of creating an object representing the JAR file transparently.
    • Oracle Database 12C: implementing Archivelog Mode

      Recall from the discussion earlier in my blog that archive redo logs are created only if your Oracle database 12C is in archivelog mode. If you want to preserve your database transaction history to facilitate point-in-time and other types of recovery, you need to enable that mode.

    • Oracle Database 12C: managing Tablespaces and Data files

      The term tablespace is something of a misnomer, in that it’s not just a space for tables. Rather, a tablespace is a logical container that allows you to manage groups of data files, the physical files on disk that consume space. Once a tablespace is created, you can then create Oracle database objects (tables and indexes) within tablespaces, which results in space allocated on disk in the associated data files.

    • Oracle Database and Instance definition

      Oracle Database and InstanceData is the most critical asset for any organization. As companies have continued to expand, their need for information storage and access has expanded exponentially, and there is no end in sight to the need for data storage and retrieval. By now, most organizations have moved from files and folders to the database. The relative ease of use and efficiency of the database accounts for its success in taking on the majority of companies’ important data. The database offers a placeholder for storing information, but more importantly, when properly organized, it allows for efficient and meaningful information retrieval.

    • Oracle Database and Security: is protection strong?

      Oracle Database SecurityIn June of 1997, Larry Ellison and Robert Miner founded a company called Software Development Labs. Both had worked together at Ampex; Robert had been Larry's supervisor. Together they had a vision, inspired by the work of Edgar Codd. Codd worked as a researcher for IBM and developed ideas for relational database systems. In 1970 he published a paper entitled "Relational Model of Data for Large Shared Data Banks." While IBM was slow to see the potential of Codd's ideas, Larry and Robert were not. They changed their company's name to Relational Software, Inc., in 1979, and not long after that it again underwent a name change—becoming Oracle. "Oracle" had been the code name for a CIA project that both Larry and Robert had worked on while at Ampex. Indeed, by all accounts, in the early years, the biggest consumers of Oracle's software was the CIA and the NSA. Given this, one would assume that security would have been at the top of Oracle's agenda.

    • Oracle Database: обзор базы данных

      Данная статья - это обзор концепций и структур, относящихся к ядру СУБД Oracle Database. Разобравшись в архитектуре сервера Oracle, вы заложите фундамент для понимания остальных обширных средств, предоставляемых базой данных Oracle. СУБД Oracle Database состоит из физических и логических компонентов. 

    • Oracle Performance Tuning Basics

      Oracle Database Performance Tuning BasicsAll of the current practices I’m aware of that are typically used for performance tuning can be grouped into a category that I call a small model approach. The small model approach includes a variety of methods that I also refer to as traditional methods because they represent the way tuning has been accomplished since Oracle began instrumenting and exposing performance metrics. The amount of data available to tuners has increased greatly, but the methods for examining the metrics remain essentially the same.

    • Oracle Performance Tuning: Gathering Problem Information

      Gathering Information for Oracle Database Performance TuningWhen I began tuning Oracle databases and applications back in the mid-1980s, I was using the methods that were state of the practice at that time and then moved to the traditional methods and tools mentioned in this article as they became available. For metrics-based analysis, I looked at data in individual tables/views—first in the v$ views, later the StatsPack views when they were introduced, and finally, beginning with version 10g, I considered metrics persisted in the AWR views. I researched and worked hard to understand the content and meaning of the various metrics. I relied heavily on input from users about when a problem occurred and what the symptoms were to make choices about where to look. Over time and with practice, I was able to improve my ability to quickly hone in on a problem by choosing the right metrics to consider. I was always concerned, however, that there were so many metrics being ignored/left out of the analysis. Although I felt confident in my ability to perform a root-cause analysis for the problems I addressed, I was certain that a more complete analysis would yield a better, more holistic picture of the database and what was going on “under the covers.”

    Page 2 of 5