Oracle topics

migrating-databases-from-non-asm-to-asm
recover standby database using incremental backup
Logminer
Auditing FGA

Controlfile Resizing --"kccrsz: expanded controlfile section"

The CONTROL_FILE_RECORD_KEEP_TIME parameter dictates how long reusable control file record slots are kept before they can be overwritten, with a default of 7 days. If all slots are in use and the retention period hasn't passed for the earliest entry, Oracle will automatically expand the control file section to create more slots, up to a maximum size. The message "kccrsz: expanded controlfile section" in the alert log simply indicates this expansion.

The control file itself is a vital binary file containing crucial metadata about the database, including:

• Database information (like RESETLOGS SCN and timestamp, creation date, database name, ID).
• History of archive logs.
• Records for tablespaces and datafiles (filenames, checkpoints, status).
• Redo thread information (current online redo logs).
• Current archive log mode.
• Log records (sequence numbers, SCN ranges).
• RMAN catalog details.
• Database block corruption information.

You can monitor the size, total number, and used record count of control file sections through the V$CONTROLFILE_RECORD_SECTION view.

ADMIN_RESTRICTIONS_listener_name=on

Oracle Listener: ADMIN_RESTRICTIONS Parameter

The ADMIN_RESTRICTIONS_<listener_name> parameter in the listener.ora file is a security feature that controls the ability to modify listener parameters at runtime using the LSNRCTL SET command.

Purpose and Functionality

• Parameter Name: ADMIN_RESTRICTIONS_<listener_name>

• Location: listener.ora file (typically located in $ORACLE_HOME/network/admin/)

• Value: ON or OFF (default is OFF)

When ADMIN_RESTRICTIONS_<listener_name> is set to ON, it disables the runtime modification of parameters within the listener.ora file via the LSNRCTL utility. Specifically:

• The listener will refuse to accept SET commands that attempt to alter its parameters (e.g., LSNRCTL SET LOG_FILE listener.log).

• This includes attempts to change ADMIN_RESTRICTIONS_<listener_name> itself at runtime.

How to Change Parameters with ADMIN_RESTRICTIONS Set to ON

If ADMIN_RESTRICTIONS_<listener_name> is ON, and you need to modify any listener parameter:

1. Manually edit the listener.ora file: Open the listener.ora file with a text editor.

2. Make the desired changes: Modify the parameter values as required.

3. Reload listener parameters: Use the RELOAD command from LSNRCTL to apply the new changes without explicitly stopping and restarting the listener.

   lsnrctl reload <listener_name>
   
   

   (Replace <listener_name> with the actual name of your listener, e.g., LISTENER)

Security Benefit

This parameter is particularly useful as a security measure if the listener is not password-protected.

• Without ADMIN_RESTRICTIONS set to ON and without a password, anyone with access to the server could potentially use LSNRCTL SET commands to alter listener behavior, potentially compromising database security or availability.

• By setting ADMIN_RESTRICTIONS to ON, you add a layer of protection, preventing unauthorized runtime changes even if the listener is not password-secured. It forces all configuration changes to be made directly in the listener.ora file, which typically has more restricted file system permissions.

In summary, ADMIN_RESTRICTIONS_<listener_name>=ON enhances listener security by enforcing that all parameter modifications are done through direct file editing and a RELOAD command, rather than dynamic SET commands, especially when the listener is not password-protected.

Preventing SYSDBA login

Normally SYS logs in as

sqlplus “/ as sysdba”

SQLNET.AUTHENTICATION_SERVICES=(NTS)
• Change SQLNET.ORA file:

SQLNET.AUTHENTICATION_SERVICES=(NONE)

• now SYS has to provide the password

sqlplus sys/ as sysdba

Types Load in Oracle

Online Transaction Processing

• Few reads
• Many writes
• Many small transactions
• Look for redo/undo and sequential read issues

Decision Support/Data Warehouse

• Many reads
• Few writes (other then possible temp)
• Few transactions
• Look for sort/workarea and scattered read issues

 Mixed or Hybrid

PMON to register with the local listener

we don’t want that PMON to register automatically with the local listener.

For doing the same we can set Parameter LOCAL_LISTENER=dummy

Purpose of dummy is that the databases do not register anymore to port 1521.

changing name of database

Traditional Method

alter database backup controlfile to trace

Change all references to the old instance name in all locations for sqlnet.ora, listener.ora, tnsnames.ora and the init.ora

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "orcl" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/orcl/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/orcl/log2a.dbf',
'/u04/oradata/orcl/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/mydatabase.dbf'
;


ALTER DATABASE OPEN RESETLOGS;


ALTER TABLESPACE TEMP ADD TEMPFILE ''/u04/oradata/orcl/log2a.dbf/temp.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;

=================================================================================
DBNEWID Utility(NID)

DBID And DBNAME


SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

C:\orcl\920\bin>nid TARGET=sys/password@orcl DBNAME=oracle
DBNEWID: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, orcl Corporation. All rights reserved.

Connected to database orcl (DBID=1024166118)

Control Files in database:
C:\orcl\ORADATA\orcl\CONTROL01.CTL
C:\orcl\ORADATA\orcl\CONTROL02.CTL
C:\orcl\ORADATA\orcl\CONTROL03.CTL

Change database ID and database name orcl to oracle? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1024166118 to 1317278975
Changing database name from orcl to oracle
Control File C:\orcl\ORADATA\orcl\CONTROL01.CTL - modified
Control File C:\orcl\ORADATA\orcl\CONTROL02.CTL - modified
Control File C:\orcl\ORADATA\orcl\CONTROL03.CTL - modified
Datafile C:\orcl\ORADATA\orcl\SYSTEM01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\CWMLITE01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\DRSYS01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\INDX01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\ODM01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\TOOLS01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\USERS01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\XDB01.DBF - dbid changed, wrote new name
Datafile C:\orcl\ORADATA\orcl\TEMP01.DBF - dbid changed, wrote new name
Control File C:\orcl\ORADATA\orcl\CONTROL01.CTL - dbid changed, wrote new name
Control File C:\orcl\ORADATA\orcl\CONTROL02.CTL - dbid changed, wrote new name
Control File C:\orcl\ORADATA\orcl\CONTROL03.CTL - dbid changed, wrote new name

Database name changed to oracle.
Modify parameter file and generate a new password file before restarting.
Database ID for database oracle changed to 1317278975.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Connect to SQL* Plus and shutdown the database

SQL> SHUDOWN IMMEDIATE;

Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

SQL> STARTUP MOUNT;

SQL > ALTER SYSTEM SET DB_NAME=oracle SCOPE=spfile;

SQL> SHUDOWN IMMEDIATE;

Create a new password file

On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdoracle.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/oracle/dbs/ pwdoracle.ora password=xxxxxxx entries=10


STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

Backup the whole database again



DBID Only

Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances

using the DBID, preventing the original and cloned instance being managed by the same catalog

Backup the database.

Mount the database after a clean shutdown.

SHUTDOWN IMMEDIATE


STARTUP MOUNT

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME.

nid TARGET=sys/password@orcl

Shutdown and open the database with RESETLOGS.

SHUTDOWN IMMEDIATE
STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

Backup the database.


DBNAME Only

Repeat the process as before except use the following command to start the DBNEWID utility.

nid TARGET=sys/password@orcl DBNAME=oracle SETNAME=YES

The SETNAME parameter tells the DBNEWID utility to only alter the database name.

When opening the database the RESETLOGS option is not needed so the database can be started using the STARTUP command.

. oraenv

The oraenv Utility: Setting Oracle Environment Variables

The oraenv (and its C shell counterpart coraenv) is a fundamental and invaluable utility for Oracle Database Administrators (DBAs) and developers working on Unix/Linux operating systems. It simplifies the process of correctly setting up the necessary environment variables to interact with a specific Oracle database instance.

What is oraenv?

oraenv is a shell script provided by Oracle that automates the configuration of essential environment variables required to connect to and manage an Oracle database. Without these variables correctly set, you might encounter errors when trying to use Oracle command-line utilities like sqlplus, rman, expdp, impdp, tnsping, and others.

Why is oraenv important?

While seemingly simple, oraenv is crucial for:

• Consistency: It ensures that your shell environment is always configured correctly for the target Oracle database, reducing common "command not found" or TNS errors.

• Multi-Instance/Multi-Version Environments: On servers hosting multiple Oracle database instances (each with a unique ORACLE_SID) or different Oracle software versions (each in a distinct ORACLE_HOME), oraenv allows you to quickly switch between these environments without manual configuration.

• Ease of Use: It abstracts away the need to manually remember and set multiple environment variables, making daily DBA tasks more efficient.

What Environment Variables Does oraenv Set?

When executed, oraenv typically sets the following key environment variables:

• ORACLE_SID: The System Identifier of the Oracle database instance you want to connect to.

• ORACLE_HOME: The full path to the directory where the Oracle software for the specified ORACLE_SID is installed.

• PATH: It prepends $ORACLE_HOME/bin to your system's PATH variable, allowing you to execute Oracle binaries directly from any directory.

• LD_LIBRARY_PATH (or LIBPATH, SHLIB_PATH depending on the OS): This variable ensures that the operating system can locate the necessary Oracle shared libraries.

How to Use oraenv

The oraenv script must be "sourced" into your current shell environment for its changes to persist. This is done using a leading dot (.) followed by a space.

Basic Usage (Interactive):

. oraenv

Upon execution, oraenv will typically prompt you to enter the ORACLE_SID of the database you wish to configure your environment for:

ORACLE_SID = [your_current_sid] ?

You would then type the desired ORACLE_SID (e.g., PRODDB, TESTDB, or +ASM for an ASM instance) and press Enter. oraenv will then look up the corresponding ORACLE_HOME in the /etc/oratab file (or /var/opt/oracle/oratab on some Linux distributions) and set the environment variables accordingly.

Non-Interactive Usage (for scripting):

To use oraenv within scripts or to avoid the prompt, you can set the ORAENV_ASK environment variable to N before calling oraenv:

ORAENV_ASK=N
ORACLE_SID=PRODDB; export ORACLE_SID
. oraenv

In this example, the environment will be automatically set for the PRODDB instance without any user interaction.

Common Scenarios Where oraenv Helps

• "sqlplus: command not found": This often means $ORACLE_HOME/bin is not in your PATH.

• "ORA-12154: TNS:could not resolve the connect identifier specified": While this can be a TNSNAMES.ORA issue, it can also occur if ORACLE_HOME is not correctly set, preventing Oracle from finding the network/admin directory.

• Switching between databases: Quickly change your shell's context from one database to another.

• Running RMAN or Data Pump: Ensure the correct Oracle binaries and libraries are found.

In essence, oraenv is a simple yet powerful utility that every Oracle DBA and user on Unix/Linux systems should be familiar with, as it streamlines environment setup and prevents many common configuration-related issues.