DBA's Forum, Routine Tasks and Activities
A simple RMAN backup scripts for Windows Env
C:\Users\snojha>rman target=sys/<password>@orcl Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 5 18:13:01 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1312789174) RMAN> connect catalog rman/<password>@rcat connected to recovery catalog database RMAN> configure controlfile autobackup format for device type disk to 'c:/rmanbackup/%F'; RMAN> configure controlfile autobackup on; RMAN> run{ allocate channel d1 type disk; allocate channel d2 type disk; allocate channel d3 type disk; allocate channel d4 type disk; backup tag FULL_DB format 'c:/rmanbackup/db_%t_%s.bk' (database); release channel d1; release channel d2; release channel d3; release channel d4; } Screen output ========================================================================================== allocated channel: d1 channel d1: SID=37 device type=DISK allocated channel: d2 channel d2: SID=193 device type=DISK allocated channel: d3 channel d3: SID=226 device type=DISK allocated channel: d4 channel d4: SID=34 device type=DISK Starting backup at 05-JUN-12 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00006 name=C:\APP\ORACLE\ORADATA\ORCL\APP_DATA01 input datafile file number=00005 name=C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF channel d1: starting piece 1 at 05-JUN-12 channel d2: starting full datafile backup set channel d2: specifying datafile(s) in backup set input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF input datafile file number=00014 name=C:\APP\ORACLE\ORADATA\ORCL\PART_IDX03 input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF channel d2: starting piece 1 at 05-JUN-12 channel d3: starting full datafile backup set channel d3: specifying datafile(s) in backup set input datafile file number=00007 name=C:\APP\ORACLE\ORADATA\ORCL\APP_IDX01 input datafile file number=00010 name=C:\APP\ORACLE\ORADATA\ORCL\PART_DATA03 input datafile file number=00012 name=C:\APP\ORACLE\ORADATA\ORCL\PART_IDX01 input datafile file number=00015 name=C:\APP\ORACLE\ORADATA\ORCL\PART_IDX04 channel d3: starting piece 1 at 05-JUN-12 channel d4: starting full datafile backup set channel d4: specifying datafile(s) in backup set input datafile file number=00008 name=C:\APP\ORACLE\ORADATA\ORCL\PART_DATA01 input datafile file number=00009 name=C:\APP\ORACLE\ORADATA\ORCL\PART_DATA02 input datafile file number=00011 name=C:\APP\ORACLE\ORADATA\ORCL\PART_DATA04 input datafile file number=00013 name=C:\APP\ORACLE\ORADATA\ORCL\PART_IDX02 channel d4: starting piece 1 at 05-JUN-12 channel d3: finished piece 1 at 05-JUN-12 piece handle=C:\RMANBACKUP\DB_785183133_23.BK tag=FULL_DB comment=NONE channel d3: backup set complete, elapsed time: 00:00:20 channel d4: finished piece 1 at 05-JUN-12 piece handle=C:\RMANBACKUP\DB_785183143_24.BK tag=FULL_DB comment=NONE channel d4: backup set complete, elapsed time: 00:00:25 channel d2: finished piece 1 at 05-JUN-12 piece handle=C:\RMANBACKUP\DB_785183130_22.BK tag=FULL_DB comment=NONE channel d2: backup set complete, elapsed time: 00:01:32 channel d1: finished piece 1 at 05-JUN-12 piece handle=C:\RMANBACKUP\DB_785183129_21.BK tag=FULL_DB comment=NONE channel d1: backup set complete, elapsed time: 00:06:54 Finished backup at 05-JUN-12 Starting Control File and SPFILE Autobackup at 05-JUN-12 piece handle=C:\RMANBACKUP\C-1312789174-20120605-01 comment=NONE Finished Control File and SPFILE Autobackup at 05-JUN-12 released channel: d1 released channel: d2 released channel: d3 released channel: d4 RMAN> ========================================================================================== |
Export and Import schema statistics
Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly. SCHEMA NAME – SACHI Create the table to store the statistics- Export schema stats – will be stored in the temp_stat If required import these statistics back to TEST schema. Finally drop the table created to backup the schema stats |
How to find partition keys for partition table
SQL> select * from USER_PART_KEY_COLUMNS; NAME OBJEC ------------------------------ ----- COLUMN_NAME -------------------------------------------------------------------------------- COLUMN_POSITION --------------- F_SALES TABLE SALES_AMT |
Dataguard Broker
The Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. You can use either the Oracle Enterprise Manager graphical user interface (GUI) or command-line interface (CLI) to automate and simplify:
BROKER CONFIGURATION: On both Primary and Standby sites, change the initialization parameter in the spfile to enable the Data guard broker. SQL> Alter system set dg_broker_start=True scope=both; System Altered. On the PRIMARY site, open the ‘cmd’ and start Command Line Interface (CLI) of the Dataguard Broker (DGMGRL). C:\> dgmgrl DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL>_ Now connect to the database through the service you made previously. DGMGRL> connect sys/oracle@to_primary Connected. Create broker configuration. DGMGRL> create configuration ‘broker1’ as > primary database is ‘primary’ > connect identifier is to_primary; (‘to_primary’ in Connect identifier is the service name through which the broker is connected to the PRIMARY database) Add Standby Database to the above configuration. DGMGRL> Add database ‘standby’ as > connect identifier is to_standby > maintained as physical; (‘to_standby’ in Connect identifier is the service name through which the broker is connected to the STANDBY database) Now the configuration has been set up but it is still disabled. You can view the configuration by executing: DGMGRL> show configuration Configuration Name: broker1 Enabled: NO Protection Mode: MaxPerformance Fast-Start Failover: DISABLE Databases: primary - Physical standby database standby - Primary database Current status for "broker1": DISABLE The next step is to ENABLE the configuration ‘broker1’. DGMGRL> enable configuration; Enabled Again view the configuration. DGMGRL> show configuration Configuration Name: broker1 Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLE Databases: primary - Physical standby database standby - Primary database Current status for "broker1": SUCCESS [edit] Switchover: Now we are ready to switch over the PRIMARY database Role to STANDBY database Role. DGMGRL> switchover to ‘Standby’; ….. ….. Primary Database Successfully converted to Physical Standby. You can again switch over the Standby Database to Primary by executing following command. DGMGRL> switchover to ‘Primary’; ….. ….. Standby Database is successfully converted to Primary Database. Failover: Failover can be done through the same configuration without any alteration. You simply need to execute following command: DGMGRL> failover to ‘Standby’; …. Failover to standby succeeded. And also… DGMGRL> failover to ‘Primary’; …. Failover to primary succeeded. |
Ways to manage a Data Guard environment
Oracle provides three ways to manage a Data Guard environment: 1. SQL*Plus and SQL Statements Using SQL*Plus and SQL commands to manage Data Guard environment.The following SQL statement initiates a switchover operation: SQL> alter database commit to switchover to physical standby; 2. Data Guard Broker GUI Interface (Data Guard Manager) Data Guard Manger is a GUI version of Data Guard broker interface that allows you to automate many of the tasks involved in configuring and monitoring a Data Guard environment. 3. Data Guard Broker Command-Line Interface (CLI) It is an alternative interface to using the Data Guard Manger. It is useful if you want to use the broker from batch programs or scripts. You can perform most of the activities required to manage and monitor the Data Guard environment using the CLI. |
Data Guard Services
Oracle Base and Diagnostic Destination Configuration
The directory that you specify when you are prompted for ORACLE_BASE by
Oracle Universal Installer is stored in the Oracle home inventory.
Database Upgrade Assistant uses this value to derive the default
database locations and the DIAGNOSTIC_DEST parameter. The diagnostic
destination location contains all ADR directories (diagnostic files,
such as the alert logs, trace files, and so on). This diagnostic
destination directory is required while upgrading an earlier Oracle
Database release to Oracle Database 11g release of the database. If the
Oracle base directory already exists, then Oracle Database Upgrade
Assistant automatically retrieves this information and populates its
path. Starting with Oracle Database 11g Release 1, the initialization
parameter settings for background dump (BACKGROUND_DUMP_DEST), user dump
(USER_DUMP_DEST), and core dump (CORE_DUMP_DEST) destinations are
replaced by the Diagnostic Destination (DIAGNOSTIC_DEST). |
Use the following script to generate SQL to kill off multiple sessions all waiting for the same library cache pin
SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='value of p1raw' |