Creation of Physical standby DB

alter database create standby controlfile as '/orcl/oradata/PROD2/ctrl_PROD_01.ctl';

$ rman target /
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/backup/%d_%s_%T_CONTROL_STDBY’ ;

Export ORACLE_SID=standby_db

rman target sys/passwd@Primary_db auxiliary /

run {
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
allocate auxiliary channel c5 type disk;
allocate auxiliary channel c6 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY;
}

Top 5 Wait events in AWR

Common Oracle AWR Wait Events Explained

This document provides an overview of frequently observed wait events in Oracle Automatic Workload Repository (AWR) reports, categorized by their primary area of impact. Understanding these wait events is crucial for diagnosing and resolving performance bottlenecks in an Oracle database.

DB FILE Type Waits – Physical I/O

These events indicate waits related to physical I/O operations from datafiles.

1. DB File Sequential Reads

   • Description: The user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. Typically a single-block read.

   • Common Causes: Memory starvation (insufficient buffer cache), non-selective indexes, high concurrency on specific blocks.

2. DB File Scattered Reads

   • Description: Similar to db file sequential reads, but the session is reading multiple data blocks (e.g., from a full table scan) and scattering them into different, non-contiguous buffers in the SGA.

   • Common Causes: Full table scans, insufficient indexing, large sorting operations.

3. Direct Path Writes

   • Description: Occurs when a process is writing buffers directly from the session's Program Global Area (PGA) to datafiles, bypassing the SGA buffer cache. The process waits for the write call to complete.

   • Common Causes: Direct-path INSERT operations (e.g., INSERT /*+ APPEND */), data loads (e.g., SQL*Loader direct path), parallel DML.

4. Direct Path Reads

   • Description: An I/O operation that reads data directly into the session's PGA instead of the SGA.

   • Common Causes: Parallel query execution (parallel slaves retrieving data), sorting to disk, hash joins spilling to disk, temporary tablespace activity.

5. DB File Parallel Writes

   • Description: This wait event is associated with the Database Writer (DBWR) process. DBWR compiles a set of dirty blocks, hands the batch over to the OS for writing to datafiles, and waits for the I/O to complete.

   • Common Causes: High database activity generating many dirty blocks, backup operations, partitioned table usage (where DBWR writes blocks for multiple partitions in parallel).

6. DB File Parallel Reads

   • Description: Occurs during database recovery, where blocks that need to be changed are read in parallel. Can also happen during buffer prefetching as an optimization.

   • Common Causes: Instance recovery, crash recovery, certain optimization paths (e.g., multi-block reads for specific operations).

7. DB File Single Write

   • Description: Used to wait for the writing of single blocks, typically file headers.

   • Common Causes: File header writes (e.g., during checkpointing, file resizing), excessive number of data files.

8. Direct Path Read Temp

   • Description: Reading data directly from temporary files into the PGA.

   • Common Causes: Extensive temporary segment activity due to large sorts, hash joins, temporary table usage, or bitmap operations that spill to disk.

9. Direct Path Write Temp

   • Description: Writing data directly to temporary files from the PGA.

   • Common Causes: Similar to Direct Path Read Temp, indicating heavy use of temporary tablespace for sorts, hashes, or temporary tables.

BUFFER Type Waits – Logical I/O

These events relate to contention or issues within the SGA buffer cache.

1. Free Buffer Waits

   • Description: Occurs when a session needs a free buffer in the SGA but none are immediately available. Oracle waits for a buffer to become available.

   • Common Causes:

     • DBWR not writing dirty buffers fast enough (e.g., slow I/O, undersized DB_WRITER_PROCESSES).

     • A file was read-only and is now read-write, requiring invalidation of existing buffers.

     • Need for a buffer in CR (Consistent Read) or recovery modes.

     • Insufficient DB_CACHE_SIZE.

2. Buffer Busy Wait

   • Description: A session cannot access a needed data block because it is currently being used or modified by another session.

   • Common Causes:

     • Hot blocks (frequently accessed blocks, e.g., index root blocks, segment header blocks).

     • Insufficient free lists for a table (pre-ASSM).

     • Too few rollback segments (pre-Automatic Undo Management).

     • High concurrency on specific data or index blocks.

3. Latch Free Waits

   • Description: Latches are internal Oracle locking mechanisms that protect SGA data structures. This wait occurs if a session needs to acquire a latch that is currently held by another session.

   • Common Causes: High concurrency on shared memory structures (e.g., library cache latch, shared pool latch, cache buffers chains latch), inefficient SQL (e.g., excessive parsing).

Log Type Waits

These events are related to the redo log buffer and online redo log files.

1. Log File Parallel Write

   • Description: The Log Writer (LGWR) process is writing the redo buffer to the online redo log files in parallel and waits for the I/O to complete.

   • Common Causes: Slow I/O subsystem for redo logs, insufficient redo log group members, large redo generation rates.

2. Log Buffer Space

   • Description: A session waits for space to become available in the log buffer because redo information is being generated faster than LGWR can write it to the redo files.

   • Common Causes: Log buffer is too small (LOG_BUFFER), redo log files are on disks with I/O contention, very high transaction rates.

3. Log File Switch (Archiving Needed)

   • Description: A log switch is pending, but the next online redo log file cannot be used because it has not yet been archived.

   • Common Causes: Archiver processes are slow or stopped, archive destination is full or slow, insufficient archiver processes (LOG_ARCHIVE_MAX_PROCESSES).

4. Log File Switch (Checkpoint Incomplete)

   • Description: A log switch is pending, but the checkpoint for the next log file has not completed, preventing LGWR from wrapping into it.

   • Common Causes: Redo log files are sized too small, FAST_START_MTTR_TARGET is set too high, slow I/O for datafiles (impacting checkpoint completion).

5. Log File Switch Completion

   • Description: Sessions are waiting for a log switch operation to fully complete.

   • Common Causes: Slow I/O for redo logs, issues with archiver processes, or slow checkpoint completion.

6. Log File Sync

   • Description: When a user session commits, its redo information must be flushed to the redo logfile. The session waits for LGWR to write the log buffer to disk and then post the user session. The wait time includes both the write and the post.

   • Common Causes: Slow I/O to redo logs (most common), high commit rate, CPU contention affecting LGWR, network latency in RAC environments.

PX – Parallel Query

These events are related to operations performed by parallel execution processes.

• Master Note Parallel Execution Wait Events (Doc ID 1097154.1): Refer to this Oracle Support document for detailed information on parallel execution wait events.

GC – Global Cache (RAC Related)

These events indicate contention or waits in Oracle Real Application Clusters (RAC) environments for accessing data blocks across instances.

• gc current block request: Waiting for a current version of a data block from another instance.

• gc cr block request: Waiting for a consistent read (CR) version of a data block from another instance.

• gc current block 3-way: A more complex scenario for current block requests involving three instances.

• gc current block busy: The requested current block is busy on the holding instance.

• gc cr block grant 2-way: A simpler scenario for consistent read block requests.

Undo or Rollback Segment Related

These events are associated with undo segment management and rollback operations.

• Undo Related Wait Events & Known Issues (Doc ID 1575701.1): Consult this Oracle Support document for comprehensive details on undo-related wait events and common issues.

By analyzing the top wait events in AWR reports and understanding their causes, DBAs can effectively pinpoint and address performance bottlenecks in Oracle databases.

Snapshot Standby Database

Converting a Physical Standby Database into a Snapshot Standby Database

Before starting into the steps it is worth to know what is snapshot standby database.

A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby database.

Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.

Step 01: Stop Redo Apply in Physical Standby Database, if it is active.

Login to any database instance of the Physical standby database if it is RAC. In case of Non-RAC you have only once instance/one database.

- Check status by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

- I have also checked the instance to know in which instance I am performing stop redo log.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

bddipdc1

- Issue stop redo log apply command.

SQL> alter database recover managed standby database cancel;

Database altered.

Step 02: In case of RAC database, shut down all but one instance.

If you are non RAC environment then simply you can ignore this step. In case of RAC environment just you need to shutdown other instances except one. In this case I have 2 node RAC and I am just shutting down bddipdc2 instance.

$ sqlplus sys/oracle@bddipdrs2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 12:48:48 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

SQL>  select instance_name from v$instance;

INSTANCE_NAME

----------------

bddipdc2

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step 03: Ensure that database is mounted and not open. Also ensure that a fast recovery area has been configured.

It is not necessary for flashback database to be enabled but you should enable FLASHBACK database if you want to convert a snapshot standby database back into a physical standby database.

Checking fast recovery area by,

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> show parameter db_reco

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +RECOVERY

db_recovery_file_dest_size           big integer 800G

which means fast recovery area is configured as we are using DB_RECOVERY_FILE_DEST as archival destination.

You can check it is mounted but not opened by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

You can check flashback database option by,

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.

SQL> alter database convert to snapshot standby;

Database altered.

Step 05: Open the database and check the database_role and open_mode.

SQL> alter database open;

Database altered.

SQL>  select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

READ WRITE           SNAPSHOT STANDBY BDDIPDC   BDDIPDRS

Now let's see in the alert log to know what happens in the backend.

alter database recover managed standby database cancel

Tue Mar 08 12:17:24 2011

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:

ORA-16037: user requested cancel of managed recovery operation

Recovery interrupted!

Waiting for MRP0 pid 17366 to terminate

Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:

ORA-16037: user requested cancel of managed recovery operation

Tue Mar 08 12:17:24 2011

MRP0: Background Media Recovery process shutdown (bddipdc1)

Managed Standby Recovery Canceled (bddipdc1)

Completed: alter database recover managed standby database cancel

Tue Mar 08 12:17:42 2011

Reconfiguration started (old inc 8, new inc 10)

List of instances:

 1 (myinst: 1)

 Global Resource Directory frozen

 * dead instance detected - domain 0 invalid = TRUE

 Communication channels reestablished

 Master broadcasted resource hash value bitmaps

 Non-local Process blocks cleaned out

Tue Mar 08 12:17:42 2011

 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Tue Mar 08 12:17:42 2011

 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

 Set master node info

 Submitted all remote-enqueue requests

 Dwn-cvts replayed, VALBLKs dubious

 All grantable enqueues granted

 Post SMON to start 1st pass IR

 Submitted all GCS remote-cache requests

 Post SMON to start 1st pass IR

 Fix write in gcs resources

Reconfiguration complete

Tue Mar 08 12:17:53 2011       

alter database convert to snapshot standby

Created guaranteed restore pointSNAPSHOT_STANDBY_REQUIRED_03/08/2011 12:17:53

Tue Mar 08 12:17:53 2011

krsv_proc_kill: Killing 219043332100 processes (all RFS)

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 449582938

Resetting resetlogs activation ID 1523683415 (0x5ad19057)

Online log +DATA/bddipdrs/onlinelog/group_1.351.728933147: Thread 1 Group 1 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_1.256.728933147: Thread 1 Group 1 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_2.356.728933149: Thread 1 Group 2 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_2.326.728933149: Thread 1 Group 2 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_4.379.728933149: Thread 2 Group 4 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_4.366.728933149: Thread 2 Group 4 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_14.374.728933149: Thread 2 Group 14 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_14.354.728933149: Thread 2 Group 14 was previously cleared

Standby became primary SCN: 449582936

Tue Mar 08 12:17:55 2011

Setting recovery target incarnation to 6

CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby

Completed: alter database convert to snapshot standby

alter database open

This instance was first to open

Picked broadcast on commit scheme to generate SCNs

Tue Mar 08 12:18:00 2011

Assigning activation ID 1523975741 (0x5ad6063d)

Thread 1 opened at log sequence 1

  Current log# 1 seq# 1 mem# 0: +DATA/bddipdrs/onlinelog/group_1.351.728933147

  Current log# 1 seq# 1 mem# 1: +DATA/bddipdrs/onlinelog/group_1.256.728933147

Successful open of redo thread 1

Tue Mar 08 12:18:01 2011

ARC4: Becoming the 'no SRL' ARCH

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Tue Mar 08 12:18:01 2011

ARC0: Becoming the 'no SRL' ARCH

Tue Mar 08 12:18:01 2011

SMON: enabling cache recovery

Instance recovery: looking for dead threads

Instance recovery: lock domain invalid but no dead threads

Successfully onlined Undo Tablespace 2.

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

Starting background process GTX0

Tue Mar 08 12:18:02 2011

GTX0 started with pid=43, OS id=24612

Starting background process RCBG

Tue Mar 08 12:18:02 2011

RCBG started with pid=44, OS id=24614

replication_dependency_tracking turned off (no async multimaster replication found)

Redo thread 2 internally disabled at seq 1 (CKPT)

Tue Mar 08 12:18:02 2011

ARC3: Becoming the 'no SRL' ARCH

ARC3: Archiving disabled thread 2 sequence 1

Archived Log entry 48206 added for thread 2 sequence 1 ID 0x0 dest 1:

Tue Mar 08 12:18:03 2011

Starting background process QMNC

Tue Mar 08 12:18:03 2011

QMNC started with pid=46, OS id=24618

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open

Note that, when the physical standby is converted to a snapshot standby database a guaranteed restore point is created. In this case we see the name is SNAPSHOT_STANDBY_REQUIRED_03/08/2011. When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion.

If you want to convert the snapshot standby database into physical standby database then you simply need to do,

SQL> shutdown immediate

SQL> startup mount

SQL> alter database convert to physical standby;

AWR snapshot interval change & baseline

@?/rdbms/admin/awrrpt.sql

Display a list of snapshots

set lines 100 pages 999

select snap_id

, snap_level

, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin

from dba_hist_snapshot

order by 1

/

Change the snapshot interval

col snap_interval format a30

col retention format a30

select snap_interval

, retention

from dba_hist_wr_control

/

changes it to 10 minutes

exec dbms_workload_repository.modify_snapshot_settings (interval => 10)

Change the retention period

changes it to 10 days

exec dbms_workload_repository.modify_snapshot_settings (retention => 10*24*60)

AWR BASELINE

Creating a baseline allowed a specified range of snapshots to be retained, regardless of the AWR retention policy

The main purpose of a baseline is to preserve typical runtime statistics in the AWR repository, allowing you to run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR. This allows you to compare current performance to established baseline performance, which can assist in determining database performance problems.

Create a baseline

exec dbms_workload_repository.create_baseline (, ,'')

Remove a baseline

exec dbms_workload_repository.drop_baseline(''')

11G Enhancement

Time-expired baselines - start and end times.

Procedures and functions allowing baselines to be created using start and end times, which are used to estimate the relevant snapshot IDs. The functions have the same parameter lists as the procedures, but return the baseline ID. By default baselines are kept forever, but the new expiration parameter allows them to be automatically expired after specified number of days

The Moving Window Baseline

Uses the system_moving_window to incorporate adaptive thresholds, whereby the bases changes as does the typical workload.  The system_moving_window corresponds to the retention tine window for the existing baselines, and the total time span is controlled by the dbms_workload_repository.modify_baseline_window_size procedure that allows you to create adaptive threshold baselines for longer periods (like monthly workload cycles).

Poor performance reported by user

we should follow the below steps.


- Check alert log, for any errors
- Check for archive log destination/file system full
- Check for database locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates/changes as been done from the Application team

How To Check Whether Physical Standby is in Sync with the Primary or Not?

A. On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Output:

Thread     Last Sequence Generated

---------- -----------------------

B. On Physical Standby
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
 
Output:
Thread     Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------



C. On Physical Standby






  
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 

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.

Data Guard Switchover & Failover (manually)

Data Guard Switchover

On the primary

select database_role,switchover_status from v$database;
alter database commit to switchover to physical standby;


On the standby

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
On the primary
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;


On the standby

Oracle Database 10g release 1
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;


Oracle Database 10g release 2 onwards
SQL>ALTER DATABASE OPEN;


On the primary
alter database recover managed standby database disconnect from session;


Data Guard Failover


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;


If the physical standby database has been opened in read-only mode since the last time it was started, shut down the target standby database and restart it:


SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Kernel Parameters

Parameter	Command
semmsl, semmns, semopm, and semmni	# /sbin/sysctl -a | grep semThis command displays the value of the semaphore parameters in the order listed.
shmall, shmmax, and shmmni	# /sbin/sysctl -a | grep shm
file-max	# /sbin/sysctl -a | grep file-max
ip_local_port_range	# /sbin/sysctl -a | grep ip_local_port_range



/etc/sysctl.conf 

/sbin/sysctl -p

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

standby_archive_dest and log_archive_dest

The  standby_archive_dest & log_archive_dest were both deprecated in 11g in favor of log_archive_dest_n.

if both standby_archive_dest and log_archive_dest_n are specified in 11g, then standby_archive_dest will be accepted.  

However, it is sufficient to simply specifylog_archive_dest_n to replace standby_archive_dest and log_archive_dest.

archive log gap in physical standby database

one of the cause
ORA-12154:TNS:could not resolve the connect identifier specified

>>>alert_.log

search for GAP keyword in alertlog.

sequence=8171 thread=1;

I had to restore that on the primary side

rman target / catalog rman/rman@abc

RMAN>restore archivelog sequence=8171 thread=1;

:):):)

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.

Added Datafile on wrong mountpoint in Dataguard

Oracle Data Guard: Datafile Management in Logical and Physical Standby

This document outlines the procedures for managing datafiles in Oracle Data Guard environments, covering both Logical Standby and Physical Standby databases. It addresses common scenarios such as moving a datafile and registering newly added or moved datafiles.

1. Moving a Datafile in a Logical Standby Database

When you need to move a datafile to a new location on a Logical Standby database, you must temporarily stop the logical standby apply process, move the file at the OS level, and then rename the datafile within the database.

Steps:

1. Stop Logical Standby Apply: Before making any changes to datafiles, you must stop the logical standby apply process to ensure consistency.

   ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   
   

2. Take the Tablespace Offline (Optional but Recommended): Taking the tablespace offline ensures that no operations attempt to access the datafiles during the move.

   ALTER TABLESPACE your_tablespace_name OFFLINE NORMAL; -- Replace your_tablespace_name
   
   

3. Copy the Datafile at the Operating System Level: Use your operating system's copy command to move the datafile from the old (incorrect) location to the new (correct) location.

   cp /wrong/mount/point/old.dbf /right/mount/point/new.dbf
   
   

   (Replace /wrong/mount/point/old.dbf and /right/mount/point/new.dbf with your actual file paths.)

4. Rename the Datafile within the Database: Inform the Oracle database about the new location of the datafile.

   ALTER TABLESPACE your_tablespace_name RENAME DATAFILE '/wrong/mount/point/old.dbf' TO '/right/mount/point/new.dbf'; -- Replace paths
   
   

5. Bring the Tablespace Online: Once the datafile is moved and renamed, bring the tablespace back online.

   ALTER TABLESPACE your_tablespace_name ONLINE;
   
   

6. Start Logical Standby Apply: Finally, restart the logical standby apply process.

   ALTER DATABASE START LOGICAL STANDBY APPLY;
   
   

2. Registering a Datafile in a Physical Standby Database (Addressing ORA-01111/ORA-01157)

When a new datafile is added on the primary database, or an existing datafile is moved, a Physical Standby database might encounter errors like ORA-01111: name for data file is unknown or ORA-01157: cannot identify/lock data file if STANDBY_FILE_MANAGEMENT is set to MANUAL or if the file was not automatically created.

Scenario: You observe errors in the alert log indicating an unknown or unidentifiable datafile, such as:

ORA-01111: name for data file 65 is unknown - rename to correct file
ORA-01110: data file 65: '/UNNAMED00065'
ORA-01157: cannot identify/lock data file 65 - see DBWR trace file

Steps to Resolve and Register the Datafile:

1. Cancel Managed Recovery on Standby: You need to stop the redo apply process to make changes to the standby database.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   
   

2. Start Managed Recovery (Disconnected Session) (Optional, if you want to keep it running but need to switch to MANUAL first): If you need to ensure the apply process is running in the background but want to switch to manual file management, you might issue this. However, for the CREATE DATAFILE step, STANDBY_FILE_MANAGEMENT must be MANUAL.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   
   

3. Set STANDBY_FILE_MANAGEMENT to MANUAL: This parameter allows you to manually add or rename datafiles on the standby database.

   ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
   
   

4. Create/Register the Datafile: Use the ALTER DATABASE CREATE DATAFILE command to register the unknown datafile with its correct path on the standby.

   ALTER DATABASE CREATE DATAFILE '/UNNAMED00065' AS '/new/path/to/datafile.dbf'; -- Replace with your actual path
   
   

   (Note: The /UNNAMED00065 is the temporary name Oracle assigns when it can't find the file. The AS '/new/path/to/datafile.dbf' is the actual correct path on your standby system.)

5. Set STANDBY_FILE_MANAGEMENT back to AUTO: It is highly recommended to set this back to AUTO after manual operations to ensure that future datafiles added on the primary are automatically created on the standby.

   ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
   
   

6. Restart Managed Recovery: Resume the redo apply process on the physical standby.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
   
   

   (For background recovery, you can add DISCONNECT FROM SESSION.)

By following these steps, you can effectively manage datafile locations and resolve common datafile-related errors in your Oracle Data Guard configurations.

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.