Dear Community !


Today, I just make a test with bash script to backup mysql data. You can take this script to play around with it. Thanks!

================================
#!/bin/bash

TIMESTAMP=$(date +"%F")
BACKUP_DIR="/path/db_mysql/$TIMESTAMP"
MYSQL_USER="msqluser"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="123456789"
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_DIR/mysql"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done


========================
Hope you enjoy!

Monitor Postgres Guide line For postgresql adminstration:

+ To track service of postgres :
    - ps -ef | grep postgres
+ top :
    - top -c -u postgres

+ iostat : Check device status
    - iostat 2 5

+ netstat : information about current network connection
    - netstat -an | grep LISTEN
    - netstat -an | grep LISTEN | grep tcp
    - netstat -an | grep LISTEN | grep PGSQL

+ pg_stat_activilty  : to track number of connections, query activity,...

+ pg_stat_replication : to monitor postgres clustering

+ pg_stat_archvier : to monitor archive files , log files,....

+ pg_stat_bgwriter: checking perform : like check_piont time. ...

+ pg_stat_database : to check status for databases,....

+ pg_stat_all_tables : to check status for tables,....

+ pg_stat_all_indexes : to track indexes,...

+ pg_stat_system_indexes : to check status for indexes,...

+ pg_stat_user_indexes : to track user indexes,....

+ pg_stat_database_conflicts : to check status for conflic like tables,....

**** Useful Queries ****
+ Show all current connections:
    select * from pg_stat_activity;
+ Show all waiting queries:
    select * from pg_stat_activity where waiting='t';
+ Show top 3 longest running queries:
    select datname,usename,now() - query_start as query_runtime from pg_stat_activity order by 3 desc limit 3;

+ du -hcs data/* : Disk space usage

+ Huge Pages
    # grep ^Huge /proc/meminfo

Install Oracle in silent mode 11G Release 2 (11.2) on OEL6

Silent mode installation allows to configure necessary Oracle components without using graphical interface nor any interaction with end user. It’s very useful method especially when you want to prepare standard installation using shell scripts.


Reference Link:  http://dbaora.com/install-oracle-in-silent-mode-11g-release-2-11-2/

Lynis - Security Tool for Audit and Hardening Linux / Unix Systems

System administrator must have known about the importance of security. Maintaining servers are not just make the server is up and running but it should be secure too. Security is not coming by default. We need to tune-up the systems to fit our needs. Since security has a wide coverage, sometimes its easy for us to miss something. Because of that, there is a security tool that can remind system administrators what should they do to secure and harden their systems.


Reference Link :http://linoxide.com/how-tos/lynis-security-tool-audit-hardening-linux/

Securing and Hardening Red Hat Linux Production Systems

Focus of this Article

This Linux Security HOWTO is intended for a technical audience, Linux system administrators, and security people in corporations and organizations that have to use commercial Linux distributions for their production environment. If you are a Linux expert then you may find familiar material here, but you will have difficulties to find documentation on various topics like restricting su access to system and shared accounts only as covered in this article, see Restricting su Access to System and Shared Accounts. 
If you need to make Linux production systems compliant with various audit requirements, then this article should offer a good baseline and starting point. The main objective of this Linux Security guide is to discuss basic Linux security requirements including account policies for production systems that are being audited. This document covers various system services like SSH which are usually enabled and required on all Linux production servers. But it does not cover services or applications like Apache, Samba etc., since these applications/services are usually not needed across all Linux servers and should therefore not be installed on all systems. In fact, these applications warrant their own security HOWTO. Also, this article does not cover security features that require kernel patching. This is not an option for most companies due to vendor support issues.

Reference Link: http://www.puschitz.com/SecuringLinux.shtml

Documenting security best practices - XCCDF introduction

When I have some free time, I try to work on a Gentoo Security Benchmark which not only documents security best practices (loosely based on the Gentoo Security Handbook which hasn't seen much updates in the last few years) but also uses the SCAP protocols. This set of protocols allows security administrators to automate and document many of their tasks, and a security best practices guide is almost a must-have in any organization. So I decided to do a few write-ups about these SCAP protocols and how I hope to be using them more in the future.

In this post, I'm going to focus on a very simple matter: documenting. SCAP goes much, much beyond documenting, but I'll discuss those various features in subsequent posts. The end goal of the series is to have a best practice document for PostgreSQL.

To document the secure state of a component, it is important to first have an idea about what you are going to document. Some people might want to document best practices across many technologies so that there is a coherent, single document explaining the security best practices for the entire organization. In my opinion, that is not manageable in the long term. We tried that with the Gentoo Security Handbook, but you quickly start wrestling with the order of chapters, style concerns and what not. Also, some technologies will be much more discussed in depth than others, making the book look "unfinished".

Personally, I rather focus on a specific technology. For instance: Hardening OpenSSH (very much work in progress - the rules are generated automatically for now and will be rewritten in the near future). It talks about a single component (OpenSSH) allowing the freedom for the author to focus on what matters. By providing security best practices on these component levels, you'll create a set of security best practices that can often be reused. This is what the Center for Internet Security is doing with its benchmarks: popular technologies are described in detail on how to configure them to be more secure.

Once you know what technology you want to describe, we need to consider how this technology is used. Some technologies are very flexible in their setup, and might have different security setups depending on their use. For instance, an OpenLDAP server can be used internally as a public address book, or disclosed on the Internet in a multi-replicated setup with authentication data in it. The security best practices for these deployments will vary. The XCCDF (Extensible Configuration Checklist Description Format)standard allows authors to write a single guide, while taking into account the different deployment approaches through the use ofProfile settings.

In XCCDF, Profiles allow for selectively enabling or disabling document fragments (called Groups) and checks (called Rules - I will post about checks later) or even change values (like the minimum password length) depending on the profile. A document can then describe settings with different profiles depending on the use and deployment of the technology. Profiles can also inherit from each other, so you can have a base (default) security conscious setup, and enhance it through other profiles.

Next to the "how", we also need to consider the structure we want for such a best practice:

• We will have rules in place for the deployment of PostgreSQL itself. These rules range from making sure a stable, patched version is used, to the proper rights on the software files, partitioning and file system rules and operating system level requirements (such as particular kernel parameters).
• We will also have rules for each instance. We could plan on running multiple PostgreSQL instances next to each other, so these rules are distinct from the deployment rules. These rules include settings on instance level, process ownership (in case of running PostgreSQL as different service user), etc.
• We might even have rules for databases and users (roles) in the database.

It might make sense to split the best practices in separate documents, such as one for PostgreSQL infrastructure (which is database-agnostic) and one for PostgreSQL databases (and users). I would start with one document for the technology if I was responsible for the entire definition, but if this responsibility is not with one person (or team), it makes sense to use different documents. Also, as we will see later, XCCDF documents can be "played" against a target. If the target is different (for infrastructure, the target usually is the host on which PostgreSQL is installed, whereas for the database settings the target is probably the PostgreSQL instance itself) I would definitely have the definitions through separate profiles, but that does not mean the document needs to be split either.

Finally, documenting a secure best practice also involves keeping track of the references. It is not only about documenting something and why you think this is the best approach, but also about referring readers to more information and other resources that collaborate your story. These can be generic control objectives (such as those provided by the open security architecture) or specific best practices of the vendor itself or third parties.

At the end, for a PostgreSQL security guide, we would probably start with:

Hardening PostgreSQL
+- Basic setup
+- Instance level configuration
|  +- Pre-startup configuration
|  `- PostgreSQL internal configuration
+- Database recommendations
`- User definitions

Profile-wise, I probably would need an infrastructure profile, an instance profile, a user profile and a database profile. I might even have profiles for the different roles (functional account, administrator and end user profiles which inherit from the user profile) as they will have different rules assigned to them.

Performance Tuning PostgreSQL

It's a broad topic, so here's lots of stuff for you to read up on:

• EXPLAIN and EXPLAIN ANALYZE is extremely useful for understanding what's going on in your db-engine
• Make sure relevant columns are indexed
• Make sure irrelevant columns are not indexed (insert/update-performance can go down the drain if too many indexes must be updated)
• Make sure your postgres.conf is tuned properly
• Know what work_mem is, and how it affects your queries (mostly useful for larger queries)
• Make sure your database is properly normalized
• VACUUM for clearing out old data
• ANALYZE for updating statistics (statistics target for amount of statistics)
• Persistent connections (you could use a connection manager like pgpool or pgbouncer)
• Understand how queries are constructed (joins, sub-selects, cursors)
• Caching of data (i.e. memcached) is an option

And when you've exhausted those options: add more memory, faster disk-subsystem etc. Hardware matters, especially on larger datasets.
And of course, read all the other threads on postgres/databases.

PostgreSQL cursors

Question:

I am trying to learn basic cursors using PostgreSQL. This is my script:

DECLARE cur_employees CURSOR FOR
  SELECT *
  FROM   employee

CLOSE cur_employees 

I want to traverse the list and output those which are active. Where should I start?
 

 

Answer:

 

You very rarely want to explicitly use cursors at all in PostgreSQL, even when processing query results in plpgsql. This is a marked contrast from many other SQL databases where they are used almost all the time.
In plpgsql you could simply write something like:

DECLARE
  employee%rowtype emp;
BEGIN
  FOR emp IN SELECT * FROM employee LOOP
    IF emp.active THEN
      RAISE INFO 'Active: %', emp.employee_id
    END IF;
  END LOOP;
END

In the above, the plpgsql language handler deals with opening, binding, fetching and closing itself.
With PostgreSQL from 9.0, you can simply drop into executing plpgsql using a "DO" block. For prior versions, you need to create a function and select it. If you're looking for the PostgreSQL equivalent of, for example, iterating through a result with a cursor on SQL Server, that's what it is. Note that iteration etc. are not part of the SQL dialect, only part of plpgsql (or any of the other emebedded languages).
The "DECLARE CURSOR xxx" syntax at the SQL level can be used like this:

DECLARE cur_employees CURSOR FOR SELECT * FROM employee;
FETCH NEXT FROM cur_employees;
// etc..
CLOSE cur_employees;

This can be used to carefully get just part of the query result set. However, it's unusual to use these, since usually your client driver will provide some sort of functionality to do this (e.g. scrollable result sets in JDBC). You can also return cursors from functions similar to Oracle, although again that's a comparatively rare use case.

how to do a SQL insert with a value with an apostrophe in it

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
             here  

PostgreSQL Error 42501: Permission Denied for Schema

I appear to have gotten it working. I looked back over all of the permission settings on the schema, except that this time I went back through all of the "Default Privileges" as well. I set my "systemusers" group to have SELECT on tables and sequences, EXECUTE on functions and USAGE on types. When I tested again, my stored procedure ran correctly and created my profile record in the database as expected.
So, apparently I DID overlook something rather important. Even with all of the individual permission settings I had assigned on the specific objects, those default privileges were still not set the way they needed to be. Lesson learned

Postgres INSERT ERROR: permission denied for schema public

2 down vote

Assuming the username is testing, you probably want to do: GRANT ALL ON schema public TO testing; Note about granting ALL PRIVILEGES: you don't say on what this GRANT command was applied. Assuming it was ON DATABASE..., it just means CONNECT, CREATE and TEMP privileges, nothing about the public schema or any other contained object, which is why it "doesn't work". EDIT: when that's not sufficient If the tables referenced by the foreign keys are not owned by testing, their owner needs also to have the USAGE privilege on the schema in order to look up the referenced tables. It's not obvious from the result of \dp (the result of \d would tell for sure) but if category is owned by super and that user also has no privilege on the schema, you'd need to assign it with: GRANT USAGE ON schema public TO super;

Permission denied in PostgreSQL

You've granted CREATE, CONNECT, and TEMPORARY privileges on the database to myuser but you haven't granted SELECT and INSERT table privileges yet. You'll need something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from sequences. Generally, the USAGE privilege is be enough for INSERT operations to work, but since you asked to "give all rights":

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

You will probably want to grant DEFAULT PRIVILEGES, too. So your administrator can access future objects automatically, too.
Can be done per schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO administrator; If you omit the schema, it applies to the whole database:
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO administrator; Only applies to objects created by the role specified (defaulting to the role that executes this command):
ALTER DEFAULT PRIVILEGES FOR staff GRANT IN SCHEMA public ... ; Available since PostgreSQL 9.0.
Don't forget to GRANT privileges on SEQUENCES in addition if you have any. (For instance as source for default values in a serial column.)

ERROR: permission denied for sequence using PostgreSQL

Since PostgreSQL 8.2 you have to use:

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO user;

GRANT USAGE - For sequences, this privilege allows the use of the currval and nextval functions.

Granting access to all tables for a user on PostgreSQL Database

First, you have to be able to connect to the database in order to run queries. This can be achieved by

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name 
TO user_name;

The REVOKE is necessary because

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

If you really want to restrict your user to DML statements, then you have a little more to do:

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO user_name;

These assume that you will have only one schema (which is named 'public' by default).

Configuring MS SQL Server for Remote Access

Problem:

A Microsoft SQL instance cannot be accessed remotely through ODBC, Visual Studio, or SQL Server Management Studio connection.

Resolution:

(applies to MS SQL 2005, 2008, 2008 R2, and 2012)
The Windows firewall is usually the culprit in these scenarios. Open TCP port 1433 for the service itself, and 1434 if you need to use the SQL Browser service. Read this article to learn how to Open an Inbound Custom Allow Rule in Windows Firewall.

1. Open cliconfg from a RUN prompt and make sure TCP/IP is an enabled protocol. For SQL 2005/2008/2008 R2: Check the Services tool, Start > Administrative Tools > Services, to see that the service named SQL Server (MSSQLSERVER) is started.
   For MS SQL 2012: Use the Windows key or hover over the left lower corner of the desktop and select Administrative Tools, then Services to see that the service named SQL Server (MSSQLSERVER) is started.
   
2. Ensure that you are using the correct credentials to authenticate. The default SQL administrator account is named sa and if you built the server from one of our server images with MSSQL pre-installed, the password will be in a text file on the root of the C partition.
3. Use netstat –an from the command prompt to verify that the server is listening for SQL traffic on the correct ports.
4. If the server is not listening for SQL traffic on the correct ports, use SQL Server Configuration Manager to change the ports.
   
   • For MS SQL 2005/2008/2008 R2, go to Start > All Programs > Microsoft SQL Server 2005 (or 2008/2008 R2) > Configuration Tools > SQL Server Configuration Manager. For MS SQL 2012: Use the Windows key or hover over the left lower corner of the desktop and select All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager.
     
   • Open the + next to SQL Server Network Configuration.
   • Right-click TCP/IP and select Properties.
   • Select IP Addresses.
   • All TCP ports mentioned on all interfaces should be 1433. Change this to reflect the correct port number and restart the SQL services.
5. If you are using named instances when installing SQL,  giving you the ability to host multiple SQL versions or service types, you will have to specify the name of the SQL instance when connecting rather than just using the server’s name or IP.  If you have created a named instance, you will need to access it by appending the name to the server’s name or IP, following a backslash (e.g. 12.34.56.78\SQLINSTANCENAME or SQLSERVERNAME\SQLINSTANCENAME).

DBMS_LOGMNR On Oracle

The DBMS_LOGMNR package, one of a set of LogMiner packages, contains the subprograms you use to initialize the LogMiner tool and to begin and end a LogMiner session.

See Also:

Oracle Database Utilities for information regarding LogMiner.

This chapter contains the following topics:

• Using DBMS_LOGMNR
  • Overview
  • Security Model
  • Constants
  • Views
  • Operational Notes
• Summary of DBMS_LOGMNR Subprograms

---

Using DBMS_LOGMNR

This section contains the following topics, which relate to using the DBMS_LOGMNR package:

• Overview
• Security Model
• Constants
• Views
• Operational Notes

---

Overview

Oracle LogMiner, which is part of Oracle Database, enables you to query online and archived redo log files through a SQL interface. The DBMS_LOGMNR package provides the majority of the tools needed to start and stop LogMiner and specify the redo log files of interest.

All changes made to user data or to the database dictionary are recorded in the Oracle redo log files so that database recovery operations can be performed. You can take advantage of the data recorded in the redo log files to accomplish other tasks, such as:

• Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun
• Determining what actions you would have to take to perform fine-grained recovery at the transaction level.
• Performance tuning and capacity planning through trend analysis.
• Track any data manipulation language (DML) and data definition language (DDL) statements executed on the database, the order in which they were executed, and who executed them.

See Also:

Chapter 55, "DBMS_LOGMNR_D" for information on the package subprograms that extract a LogMiner dictionary and re-create LogMiner tables in alternate tablespaces

---

Security Model

You must have the EXECUTE_CATALOG_ROLE role to use the DBMS_LOGMNR package.

---

Constants

The DBMS_LOGMNR package defines several enumerated constants for specifying parameter values. Enumerated constants must be prefixed with the package name, for example, DBMS_LOGMNR.NEW.

Table 54-1 describes the constants for the ADD_LOGFILE options flag in the DBMS_LOGMNR package.

Table 54-1 Constants for ADD_LOGFILE Options Flag

Constant	Description
NEW	Implicitly calls the DBMS_LOGMNR.END_LOGMNR procedure to end the current LogMiner session and then creates a new session. The new session starts a new list of redo log files to be analyzed, beginning with the redo log file you specify.
ADDFILE	Adds the specified redo log file to the list of redo log files to be analyzed. Any attempt to add a duplicate file raises an exception (ORA-01289). This is the default if no options flag is specified.

Table 54-2 describes the constants for the START_LOGMNR options flag in the DBMS_LOGMNR package.

Table 54-2 Constants for START_LOGMNR Options Flag

Constant	Description
COMMITTED_DATA_ONLY	If set, DML statements corresponding to committed transactions are returned. DML statements corresponding to a committed transaction are grouped together. Transactions are returned in their commit order. Transactions that are rolled back or in-progress are filtered out, as are internal redo records (those related to index operations, management, and so on). If this option is not set, all rows for all transactions (committed, rolled back, and in-progress) are returned in the order in which they are found in the redo logs (in order of SCN values).
SKIP_CORRUPTION	Directs a select operation on the V$LOGMNR_CONTENTS view to skip any corruptions in the redo log file being analyzed and continue processing. This option works only when a block in the redo log file (and not the header of the redo log file) is corrupt. You should check the INFO column in the V$LOGMNR_CONTENTS view to determine the corrupt blocks skipped by LogMiner. When a corruption in the redo log file is skipped, the OPERATION column contains the value CORRUPTED_BLOCKS, and the STATUScolumn contains the value 1343.
DDL_DICT_TRACKING	If the LogMiner dictionary in use is a flat file or in the redo log files, LogMiner updates its internal dictionary if a DDL event occurs. This ensures that correct SQL_REDO and SQL_UNDO information is maintained for objects that are modified after the LogMiner internal dictionary is built. The database to which LogMiner is connected must be open. This option cannot be used in conjunction with the DICT_FROM_ONLINE_CATALOG option and cannot be used when the LogMiner dictionary being used is one that was extracted to a flat file prior to Oracle9i.
DICT_FROM_ONLINE_CATALOG	Directs LogMiner to use the current online database dictionary rather than a LogMiner dictionary contained in a flat file or in the redo log files being analyzed. This option cannot be used in conjunction with the DDL_DICT_TRACKING option. The database to which LogMiner is connected must be the same one that generated the redo log files. Expect to see a value of 2 in the STATUS column of the V$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.
DICT_FROM_REDO_LOGS	If set, LogMiner expects to find a LogMiner dictionary in the redo log files that were specified. The redo log files are specified with the DBMS_LOGMNR.ADD_LOGFILE procedure or with the DBMS_LOGMNR.START_LOGMNR procedure with theCONTINUOUS_MINE option.
NO_SQL_DELIMITER	If set, the SQL delimiter (a semicolon) is not placed at the end of reconstructed SQL statements. This is helpful for applications that open a cursor and then execute the reconstructed statements.
NO_ROWID_IN_STMT	If set, the ROWID clause is not included in the reconstructed SQL statements. The redo log file may already contain logically unique identifiers for modified rows if supplemental logging is enabled. When using this option, you must be sure that supplemental logging was enabled in the source database at the appropriate level and that no duplicate rows exist in the tables of interest. LogMiner does not make any guarantee regarding the uniqueness of logical row identifiers.
PRINT_PRETTY_SQL	If set, LogMiner formats the reconstructed SQL statements for ease of reading. These reconstructed SQL statements are not executable.
CONTINUOUS_MINE	Directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You only need to specify the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. You are not required to specify any redo log files explicitly. LogMiner automatically adds and mines the (archived and online) redo log files for the data of interest. This option requires that LogMiner is connected to the same database instance that is generating the redo log files. It also requires that the database be mounted and that archiving be enabled. Beginning with Oracle Database release 10.1, the CONTINUOUS_MINE options is supported for use in an Oracle Real Application Clusters environment.

---

Views

The DBMS_LOGMNR package uses the views listed in the section on Accessing LogMiner Operational Information in Views in Oracle Database Utilities.

---

Operational Notes

A LogMiner session begins with a call to DBMS_LOGMNR.ADD_LOGFILE or DBMS_LOGMNR.START_LOGMNR (the former if you plan to specify log files explicitly; the latter if you plan to use continuous mining). The session ends with a call to DBMS_LOGMNR.END_LOGMNR. Within a LogMiner session, you can specify the redo log files to be analyzed and the SCN or time range of interest; then you can issue SQL SELECT statements against the V$LOGMNR_CONTENTS view to retrieve the data of interest.

---

Summary of DBMS_LOGMNR Subprograms

Table 54-3 DBMS_LOGMNR Package Subprograms

Subprogram	Description
ADD_LOGFILE Procedure	Adds a redo log file to the existing or newly created list of redo log files for LogMiner to process, so that if a new list is created, this marks the beginning of a LogMiner session
COLUMN_PRESENT Function	Call this function for any row returned from the V$LOGMNR_CONTENTS view to determine if undo or redo column values exist for the column specified by the column_name input parameter to this function
END_LOGMNR Procedure	Finishes a LogMiner session
MINE_VALUE Function	Call this function for any row returned from the V$LOGMNR_CONTENTS view to retrieve the undo or redo column value of the column specified by the column_name input parameter to this function
REMOVE_LOGFILE Procedure	Removes a redo log file from the list of redo log files for LogMiner to process
START_LOGMNR Procedure	Initializes the LogMiner utility and starts LogMiner (unless the session was already started with a call to DBMS_LOGMNR.ADD_LOGFILE)

---

ADD_LOGFILE Procedure

This procedure adds a file to an existing or newly created list of log files for LogMiner to process.

Syntax

DBMS_LOGMNR.ADD_LOGFILE ( 
   LogFileName     IN VARCHAR2,
   options         IN BINARY_INTEGER default ADDFILE );

Parameters

Table 54-4 ADD_LOGFILE Procedure Parameters

Parameter	Description
LogFileName	Specifies the name of the redo log file to add to the list of redo log files to be analyzed during this session.
options	Does one of the following: Starts a new LogMiner session and a new list of redo log files for analysis (DBMS_LOGMNR.NEW) Adds a file to an existing list of redo log files for analysis (DBMS_LOGMNR.ADDFILE) See Table 54-1, "Constants for ADD_LOGFILE Options Flag".

Exceptions

Table 54-5 ADD_LOGFILE Procedure Exceptions

Exception	Description
ORA-01284	Specified file cannot be opened.
ORA-01287	Specified file is from a different database incarnation.
ORA-01289	Specified file has already been added to the list. Duplicate redo log files cannot be added.
ORA-01290	Specified file is not in the current list and therefore cannot be removed from the list.
ORA-01324	Specified file cannot be added to the list because there is a DB_ID mismatch.

Usage Notes

• Before querying the V$LOGMNR_CONTENTS view, you must make a successful call to the DBMS_LOGMNR.START_LOGMNR procedure (within the current LogMiner session).
• Unless you specify the CONTINUOUS_MINE option, the LogMiner session must be set up with a list of redo log files to be analyzed. Use the ADD_LOGFILEprocedure to specify the list of redo log files to analyze.
• If you are not using the CONTINUOUS_MINE option and you want to analyze more than one redo log file, you must call the ADD_LOGFILE procedure separately for each redo log file. The redo log files do not need to be registered in any particular order.
• Both archived and online redo log files can be mined.
• After you have added the first redo log file to the list, each additional redo log file that you add to the list must be associated with the same database and database RESETLOGS SCN as the first redo log file. (The database RESETLOGS SCN uniquely identifies each execution of an ALTER DATABASE OPENRESETLOGS statement. When the online redo logs are reset, Oracle creates a new and unique incarnation of the database.)
• To analyze the redo log files from a different database (or a database incarnation with a different database RESETLOGS SCN) than that with which the current list of redo log files is associated, use the END_LOGMNR procedure to end the current LogMiner session, and then build a new list using theADD_LOGFILE procedure.
• LogMiner matches redo log files by the log sequence number. Thus, two redo log files with different names but with the same log sequence number will return the ORA-01289 exception. For instance, the online counterpart of an archived redo log file has a different name from the archived redo log file, but attempting to register it with LogMiner after registering the archived counterpart will result in the ORA-01289 exception being returned.

---

COLUMN_PRESENT Function

This function is designed to be used in conjunction with the MINE_VALUE function.

If the MINE_VALUE function returns a NULL value, it can mean either:

• The specified column is not present in the redo or undo portion of the data.
• The specified column is present and has a NULL value.

To distinguish between these two cases, use the COLUMN_PRESENT function, which returns a 1 if the column is present in the redo or undo portion of the data. Otherwise, it returns a 0.

Syntax

DBMS_LOGMNR.COLUMN_PRESENT (
     sql_redo_undo      IN  RAW,
     column_name        IN  VARCHAR2 default '') RETURN NUMBER;

Parameters

Table 54-6 COLUMN_PRESENT Function Parameters

Parameter	Description
sql_redo_undo	Specifies either the REDO_VALUE or the UNDO_VALUE column in the V$LOGMNR_CONTENTS view from which to extract data values. See the Usage Notes for more information.
column_name	Specifies the fully qualified name (schema.table.column) of the column for which this function will return information.

Return Values

Table 54-7 describes the return values for the COLUMN_PRESENT function. The COLUMN_PRESENT function returns 1 if the self-describing record (the first parameter) contains the column specified in the second parameter. This can be used to determine the meaning of NULL values returned by theDBMS_LOGMNR.MINE_VALUE function.

Table 54-7 Return Values for COLUMN_PRESENT Function

Return	Description
0	Specified column is not present in this row of V$LOGMNR_CONTENTS.
1	Column is present in this row of V$LOGMNR_CONTENTS.

Exceptions

Table 54-8 COLUMN_PRESENT Function Exceptions

Exception	Description
ORA-01323	Currently, a LogMiner dictionary is not associated with the LogMiner session. You must specify a LogMiner dictionary for the LogMiner session.
ORA-00904	Value specified for the column_name parameter is not a fully qualified column name.

Usage Notes

• To use the COLUMN_PRESENT function, you must have successfully started LogMiner.
• The COLUMN_PRESENT function must be invoked in the context of a select operation on the V$LOGMNR_CONTENTS view.
• The COLUMN_PRESENT function does not support LONG, LOB, ADT, or COLLECTION datatypes.
• The value for the sql_redo_undo parameter depends on the operation performed and the data of interest:
  • If an update operation was performed and you want to know what the value was prior to the update operation, specify UNDO_VALUE.
  • If an update operation was performed and you want to know what the value is after the update operation, specify REDO_VALUE.
  • If an insert operation was performed, typically you would specify REDO_VALUE (because the value of a column prior to an insert operation will always be NULL).
  • If a delete operation was performed, typically you would specify UNDO_VALUE (because the value of a column after a delete operation will always be NULL).

---

END_LOGMNR Procedure

This procedure finishes a LogMiner session. Because this procedure performs cleanup operations that may not otherwise be done, you must use it to properly end a LogMiner session. This procedure is called automatically when you log out of a database session or when you call DBMS_LOGMNR.ADD_LOGFILE and specify the NEW option.

Syntax

DBMS_LOGMNR.END_LOGMNR;

Exceptions

Table 54-9 END_LOGMNR Procedure Exception

Exception	Description
ORA-01307	No LogMiner session is currently active. The END_LOGMNR procedure was called without adding any log files or before the START_LOGMNRprocedure was called

---

MINE_VALUE Function

This function facilitates queries based on a column's data value. This function takes two arguments. The first one specifies whether to mine the redo (REDO_VALUE) or undo (UNDO_VALUE) portion of the data. The second argument is a string that specifies the fully qualified name of the column to be mined. The MINE_VALUE function always returns a string that can be converted back to the original datatype.

Syntax

DBMS_LOGMNR.MINE_VALUE (
     sql_redo_undo      IN  RAW,
     column_name        IN  VARCHAR2 default '') RETURN VARCHAR2;

Parameters

Table 54-10 MINE_VALUE Function Parameters

Parameter	Description
sql_redo_undo	Specifies either the REDO_VALUE or the UNDO_VALUE column in the V$LOGMNR_CONTENTS view from which to extract data values. See the Usage Notes for more information.
column_name	Specifies the fully qualified name (schema.table.column) of the column for which this function will return information.

Return Values

Table 54-11 Return Values for MINE_VALUE Function

Return	Description
NULL	The column is not contained within the self-describing record, or the column value is NULL. To distinguish between the two different null possibilities, use the DBMS_LOGMNR.COLUMN_PRESENT function.
NON-NULL	The column is contained within the self-describing record; the value is returned in string format.

Exceptions

Table 54-12 MINE_VALUE Function Exceptions

Exception	Description
ORA-01323	Invalid state. Currently, a LogMiner dictionary is not associated with the LogMiner session. You must specify a LogMiner dictionary for the LogMiner session.
ORA-00904	Invalid identifier. The value specified for the column_name parameter was not a fully qualified column name.

Usage Notes

• To use the MINE_VALUE function, you must have successfully started LogMiner.
• The MINE_VALUE function must be invoked in the context of a select operation from the V$LOGMNR_CONTENTS view.
• The MINE_VALUE function does not support LONG, LOB, ADT, or COLLECTION datatypes.
• The value for the sql_redo_undo parameter depends on the operation performed and the data of interest:
  • If an update operation was performed and you want to know what the value was prior to the update operation, specify UNDO_VALUE.
  • If an update operation was performed and you want to know what the value is after the update operation, specify REDO_VALUE.
  • If an insert operation was performed, typically you would specify REDO_VALUE (because the value of a column prior to an insert operation will always be null).
  • If a delete operation was performed, typically you would specify UNDO_VALUE (because the value of a column after a delete operation will always be null).

---

REMOVE_LOGFILE Procedure

This procedure removes a redo log file from an existing list of redo log files for LogMiner to process.

Note:

This procedure replaces the REMOVEFILE constant that was an option on the ADD_LOGFILE procedure prior to Oracle Database 10g.

Syntax

DBMS_LOGMNR.REMOVE_LOGFILE ( 
   LogFileName     IN VARCHAR2);

Parameters

Table 54-13 REMOVE_LOGFILE Procedure Parameters

Parameter	Description
LogFileName	Specifies the name of the redo log file to be removed from the list of redo log files to be analyzed during this session.

Exceptions

Table 54-14 REMOVE_LOGFILE Procedure Exception

Exception	Description
ORA-01290	Cannot remove unlisted log file

Usage Notes

• Before querying the V$LOGMNR_CONTENTS view, you must make a successful call to the DBMS_LOGMNR.START_LOGMNR procedure (within the current LogMiner session).
• You can use this procedure to remove a redo log file from the list of redo log files for LogMiner to process if you know that redo log file does not contain any data of interest.
• Multiple redo log files can be removed by calling this procedure repeatedly.
• The redo log files do not need to be removed in any particular order.
• To start a new list of redo log files for analysis, use the END_LOGMNR procedure to end the current LogMiner session, and then build a new list using theADD_LOGFILE procedure.
• Even if you remove all redo log files from the list, any subsequent calls you make to the ADD_LOGFILE procedure must match the database ID andRESETLOGS SCN of the removed redo log files. Therefore, to analyze the redo log files from a different database (or a database incarnation with a different database RESETLOGS SCN) than that with which the current list of redo log files is associated, use the END_LOGMNR procedure to end the current LogMiner session, and then build a new list using the ADD_LOGFILE procedure.

---

START_LOGMNR Procedure

This procedure starts LogMiner by loading the dictionary that LogMiner will use to translate internal schema object identifiers to names.

Syntax

DBMS_LOGMNR.START_LOGMNR ( 
   startScn           IN NUMBER default 0,
   endScn             IN NUMBER default 0,
   startTime          IN DATE default '01-jan-1988',
   endTime            IN DATE default '31-dec-2110',
   DictFileName       IN VARCHAR2 default '',
   Options            IN BINARY_INTEGER default 0 );

Parameters

Table 54-15 START_LOGMNR Procedure Parameters

Parameter	Description
startScn	Directs LogMiner to return only redo records with an SCN greater than or equal to the startScn specified. This fails if there is no redo log file containing the specified startScn value. (You can query the FILENAME, LOW_SCN, and NEXT_SCN columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of SCN values contained in each redo log file.)
endScn	Directs LogMiner to return only redo records with an SCN less than or equal to the endScn specified. If you specify an endScn value that is beyond the value in any redo log file, then LogMiner will use the greatest endScn value in the redo log file that contains the most recent changes. (You can query the FILENAME, LOW_SCN, and NEXT_SCN columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of SCN values contained in each redo log file.)
startTime	Directs LogMiner to return only redo records with a timestamp greater than or equal to the startTime specified. This fails if there is no redo log file containing the specified startTime value. (You can query the FILENAME, LOW_TIME, and HIGH_TIME columns in the V$LOGMNR_LOGSview for each redo log file to determine the range of time covered in each redo log file.) This parameter is ignored if startScn is specified. See the Usage Notes for additional information.
endTime	Directs LogMiner to return only redo records with a timestamp less than or equal to the endTime specified. If you specify an endTime value that is beyond the value in any redo log file, then LogMiner will use the greatest endTime in the redo log file that contains the most recent changes. You can query the FILENAME, LOW_TIME, and HIGH_TIME columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of time covered in each redo log file.) This parameter is ignored if endScn is specified. See the Usage Notes for additional information.
DictFileName	Specifies the flat file that contains the LogMiner dictionary. It is used to reconstruct SQL_REDO and SQL_UNDO columns in V$LOGMNR_CONTENTS, as well as to fully translate SEG_NAME, SEG_OWNER, SEG_TYPE_NAME, TABLE_NAME, and TABLE_SPACE columns. The fully qualified path name for the LogMiner dictionary file must be specified. (This file must have been created previously through the DBMS_LOGMNR_D.BUILD procedure.) You need to specify this parameter only if neither DICT_FROM_REDO_LOGS nor DICT_FROM_ONLINE_CATALOG is specified.
options	See Table 54-2, "Constants for START_LOGMNR Options Flag".

Exceptions

Table 54-16 START_LOGMNR Procedure Exceptions

Exception	Description
ORA-01280	Internal error encountered.
ORA-01281	startScn or endScn parameter value is not a valid SCN, or endScn is less than startScn.
ORA-01282	value for the startTime parameter was greater than the value specified for the endTime parameter, or there was no redo log file that was compatible with the date range specified with the startTime and endTime parameters.
ORA-01283	Options parameter specified is invalid.
ORA-01284	LogMiner dictionary file specified in the DictFileName parameter has a full path length greater than 256 characters, or the file cannot be opened.
ORA-01285	Error reading specified file.
ORA-01291	Redo log files that are needed to satisfy the user's requested SCN or time range are missing.
ORA-01292	No log file has been specified for the current LogMiner session.
ORA-01293	Mounted database required for specified LogMiner options.
ORA-01294	Error occurred while processing information in the specified dictionary file, possible corruption.
ORA-01295	Specified LogMiner dictionary does not correspond to the database that produced the log files being analyzed.
ORA-01296	Character set mismatch between specified LogMiner dictionary and log files.
ORA-01297	Redo version mismatch between LogMiner dictionary and log files.
ORA-01299	Specified LogMiner dictionary corresponds to a different database incarnation.
ORA-01300	Writable database required for specified LogMiner options.

Usage Notes

• LogMiner can use a dictionary that you previously extracted to the redo log files or to a flat file, or you can specify that LogMiner use the online catalog if LogMiner is mining data from the source system. See Oracle Database Utilities and Chapter 55, "DBMS_LOGMNR_D" in this manual for more information about the LogMiner dictionary.
• After executing the START_LOGMNR procedure, you can query the following views:
  • V$LOGMNR_CONTENTS - contains history of information in redo log files
  • V$LOGMNR_DICTIONARY - contains current information about the LogMiner dictionary file extracted to a flat file
  • V$LOGMNR_PARAMETERS - contains information about the LogMiner session
  (You can query the V$LOGMNR_LOGS view after a redo log file list has been added to the list of files that LogMiner is to mine.)
• Parameters and options are not persistent across calls to DBMS_LOGMNR.START_LOGMNR. You must specify all desired parameters and options (including SCN and time ranges) each time you call DBMS_LOGMNR.START_LOGMNR
• Be aware that specifying redo log files using a timestamp is not precise.
• The CONTINUOUS_MINE option directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You need to specify only the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. Keep the following in mind when using theCONTINUOUS_MINE option:
  • The database control file will hold information about a limited number of archived redo log files, although the number of entries can be quite large. Query the V$ARCHIVED_LOGS view to determine which redo log file entries will be found by LogMiner.
    Even if an entry is listed in the database control file (and the V$ARCHIVED_LOGS view), the archived redo log file may not be accessible by LogMiner for various reasons. For example, the archived redo log file may have been deleted or moved from its location (maybe because of a backup operation to tape), or the directory where it resides may not be not available.
  • If you specify the CONTINUOUS_MINE option and an ending time or SCN that will occur in the future (or you do not specify an end time or SCN), a query of the V$LOGMNR_CONTENTS view will not finish until the database has generated redo log files beyond the specified time or SCN. In this scenario, LogMiner will automatically add archived redo log files to the LogMiner redo log file list as they are generated. In addition, in this scenario only, LogMiner may automatically remove redo log files from the list to keep it at 50 processed redo files. This is to save PGA memory as LogMiner automatically adds redo log files to the list. If LogMiner did not perform automated removal, memory could eventually be exhausted.
  • LogMiner can mine online redo logs. However, if the CONTINUOUS_MINE option is not specified, it is possible that the database is writing to the online redo log file at the same time that LogMiner is reading the online redo log file. If a log switch occurs while LogMiner is reading an online redo log file, the database will overwrite what LogMiner is attempting to read. The data that LogMiner returns if the file it is trying to read gets overwritten by the database is unpredictable.
• Keep the following in mind regarding starting and ending times or SCN ranges:
  • If you specify neither a startTime nor a startScn parameter, LogMiner will set the startScn parameter to use the lowest SCN value from the redo log file that contains the oldest changes.
  • If you specify both time and SCN values, LogMiner uses the SCN value or values and ignores the time values.
  • If you specify starting and ending time or SCN values and they are found in the LogMiner redo log file list, then LogMiner mines the logs indicated by those values.
  • If you specify starting and ending times or SCN values that are not in the LogMiner redo log file list, and you specifyDBMS_LOGMNR.START_LOGMNR without the CONTINUOUS_MINE option, and you specify:
    • 0 for the startTime or startScn value, then the lowest SCN in the LogMiner redo log file list will be used as the startScn
    • A nonzero number for the startTime or startScn value, then an error is returned
    • 0 or a nonzero number for the endTime or endScn value, then the highest SCN in the LogMiner redo log file list will be used as theendScn
  • If you specify starting and ending times or SCN values and they are not found in the LogMiner redo log file list, and you specifyDBMS_LOGMNR.START_LOGMNR with the CONTINUOUS_MINE option, and you specify:
    • 0 for the startTime or startScn value, then an error is returned.
    • A startTime or startScn value that is greater than any value in the database's archived redo log files, then LogMiner starts mining in the online redo log file. LogMiner will continue to process the online redo log file until it finds a change at, or beyond, the requested starting point before it returns rows from the V$LOGMNR_CONTENTS view.
    • An endTime or endScn parameter value that indicates a time or SCN in the future, then LogMiner includes the online redo log files when it mines. When you query the V$LOGMNR_CONTENTS view, rows will be returned from this view as changes are made to the database, and will not stop until LogMiner sees a change beyond the requested ending point.
    • 0 for the endTime or endScn parameter value, then LogMiner includes the online redo log files when it mines. When you query theV$LOGMNR_CONTENTS view, rows will be returned from this view as changes are made to the database, and will not stop until you enter CTL+C or you terminate the PL/SQL cursor.