How to Create A Batch File for Scheduling Backups in Oracle on Daily, Weekly, Monthly and Yearly basis. 


Folks,  below is the script which will create a batch file to run in Windows scheduler to take backups of databases on daily, weekly, monthly basis.

For your reference, I have also attached a .BAT file screenshot.

Steps :

1) Open notepad  and paste the text provided below. 

2) In this text, database name is SCANA, so you can change your db name accordingly.

3) Change the dpdump location in text.

4) Save the changes and save as .bat file as shown in screenshot.

5) Create a task in windows scheduler and schedule the time for batch file to run.

----------------------------
 
set ORACLE_SID=SCANA
set day=%date:~0,3%
set month=%date:~4,2%
set daydate=%date:~7,2%
set year=%date:~-4%
set hour=%time:~0,1%
set minute=%time:~3,2%
set backup_date_time=%daydate%_%month%_%year%_%hour%_%minute%
expdp system/password@SCANA directory=data_pump_dir dumpfile=SCANA_%backup_date_time%.dmp logfile=SCANA_%backup_date_time%.log FULL=Y
forfiles -p "D:\app\Admin\admin\SCANA\dpdump" -s -m SCANA*.* -d -2 -c "cmd /c del @file"
 

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

Thanks.

 

Viewing Oracle Compilation Errors :

 
When creating procedures, functions, packages, triggers, or package bodies via Oracle, if there are compilation errors, Oracle will store these errors in a system table.

To view these errors, you will need to know the type of object being compiled, such as a procedure, and the name of the object. With this information, the following query can be executed.

select * from SYS.USER_ERRORS where NAME = <object_name> and type = <object_type>

For example, if attempting to create a procedure via the following command:

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







CREATE OR REPLACE PROCEDURE RAISE_SALARY(dept_no number, percentage number) AS
employee_salary EMPLOYEE.SALARY%TYPE;
CURSOR ECursor IS
    SELECT SALARY FROM EMPLOYEE where DNO = dept_no FOR UPDATE;
BEGIN
    OPEN ECursor;
    LOOP
    FETCH ECursor into employee_salary;
    EXIT WHEN ECursor%NOTFOUND;
    UPDATE EMPLOYEE SET SALARY = (employee_salary*percentage)
        WHERE CURRENT OF ECursor;
    END LOOP;
    CLOSE ECursor;
    COMMIT;
END RAISE_SALARY;
/


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

 To view any errors associated with the above procedure, you can use the following query:

select * from SYS.USER_ERRORS where NAME = 'RAISE_SALARY' and type = 'PROCEDURE';

Let's say, for example, that the table EMPLOYEE does not exist. The above query will then return an error such as the following:

PL/SQL: ORA-00942: table or view does not exist

The type column can be types such as the following:

PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY
TRIGGER

Oracle Reinvents Database Protection with Zero Data Loss Recovery Appliance

Worth reading article by Oracle Press

http://www.oracle.com/us/corporate/press/2313883


Cheers!

Snapshot Standby - Step by Step Configuration

Snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

1. Configured flash recovery area, check database flashback enable, must enable it if not.

SQL> alter system set db_recovery_file_dest_size = 2G;
SQL> alter system set db_recovery_file_dest= 'D:\app\db_recov';

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

2. Cancel managed recovery mode on physical standby database

SQL> alter database recover managed standby database cancel;

3. Convert physical standby database to snapshot standby database

SQL> alter database convert to snapshot standby;

4. Recycle the database:

SQL> shutdown immediate
SQL> startup
SQL> select open_mode, database_role from v$database;

You can see database enable for read/write operations

5. Convert snapshot standby database back to physical standby database:

SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby;
SQL> shutdown

Start managed recovery process:

SQL> alter database recover managed standby database disconnect;
SQL> select open_mode, database_role from v$database;

Snapshot Standby - Step by Step Configuration  :

Snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

1. Configured flash recovery area, check database flashback enable, must enable it if not.

SQL> alter system set db_recovery_file_dest_size = 2G;SQL> alter system set db_recovery_file_dest= '/db_recov';

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

2. Cancel managed recovery mode on physical standby database

SQL> alter database recover managed standby database cancel;

3. Convert physical standby database to snapshot standby database

SQL> alter database convert to snapshot standby;

4. Recycle the database:

SQL> shutdown immediate
SQL> startup
SQL> select open_mode, database_role from v$database;

You can see database enable for read/write operations

5. Convert snapshot standby database back to physical standby database:

SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby;
SQL> shutdown

Start managed recovery process:

SQL> alter database recover managed standby database disconnect;
SQL> select open_mode, database_role from v$database;

Cheers!

Oracle Data Guard (Introduction)


Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.

Data Guard maintains these standby databases as transactionally consistent copies of the production database. These standby databases can be located at remote disaster recovery sites thousands of miles away from the production data center, or they may be located in the same city, same campus, or even in the same building. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime associated with the outage, and preventing any data loss.

Available as a feature of the Enterprise Edition of the Oracle Database, Data Guard can be used in combination with other Oracle High Availability (HA) solutions such as Real Application Clusters (RAC), Oracle Flashback and Oracle Recovery Manager (RMAN), to provide a very high level of data protection and data availability that is unprecedented in the industry..

Data Guard Configurations
A Data Guard configuration consists of one production (formally known as primary) database and up to nine/9 standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided that they can communicate with each other. However, for disaster recovery, it is recommended that the standby databases be hosted at sites that are geographically separated from the primary site.

· Primary Database
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications. The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.

· Standby Database
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database. Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.

Standby Database Types
Following are the standby database types:

· Physical Standby Database- A physical standby database provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schemas, including indexes, are the same. The Redo Apply technology applies redo data on the physical standby database using standard Oracle media recovery techniques.
· Active Data Guard
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster.

· Snapshot Standby
A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

· Logical standby database
A logical standby database contains the same logical information as the production database, although the physical organization and structure of the data can be different. The SQL apply technology keeps the logical standby database synchronized with the primary database by transforming the data in the redo logs received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This makes it possible for the logical standby database to be accessed for queries and reporting purposes at the same time the SQL is being applied to it. Thus, a logical standby database can be used concurrently for data protection and reporting and database upgrades.

Data Guard provides two methods to apply this redo data to the standby database and keep it transactionally consistent with the primary, and these methods correspond to the two types of standby databases supported by Data Guard. Redo Apply, used for Physical Standby databases SQL Apply, used for Physical Standby databases

Data Guard Protection Modes
Data Guard provides three modes of data protection to balance cost, availability, performance, and data protection. These modes define the rules that govern the behavior of the Data Guard configuration, and can be set easily using any of the available management interfaces, e.g. using the following simple SQL statement on the primary database:

SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION AVAILABILITY PERFORMANCE};

To determine the appropriate data protection mode, enterprises need to weigh their business requirements for data protection against user demands for system response time. The following table outlines the suitability of each mode from a risk of data loss perspective.

· Maximum Protection—This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.

· Maximum Availability—This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.

· Maximum Performance—This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance

Data Guard Services
The following sections explain how Data Guard manages the transmission of redo data, the application of redo data, and changes to the database roles:

Log Transport Services—Control the automated transfer of redo data from the production database to one or more archival destinations.

Log Apply Services—Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database.

Data Guard provides two methods to apply this redo data to the standby database and keep it transactionally consistent with the primary, and these methods correspond to the two types of standby databases supported by Data Guard.

Redo Apply, used for Physical Standby databases
SQL Apply, used for Physical Standby databases

Role Management Services
Change the role of a database from a standby database to a primary database, or from a primary database to a standby database using either a switchover or a failover operation.

A database can operate in one of the two mutually exclusive roles: primary or standby database.

· Failover During a failover, one of the standby databases takes the new primary database role, when an unplanned failure occurs on the primary database. This enables recovery time objectives to be achieved by quickly promoting the standby to the primary role instead incurring downtime while the events that impact the primary are diagnosed and resolved.

· Switchover A switchover is used to reduce primary database downtime during planned outages, such as operating system or hardware upgrades, or rolling upgrades of the Oracle database software and patch sets. A switchover operation requires all user sessions to be disconnected from the primary database. Following that, the primary database is transitioned to the standby role, after which the standby database is transitioned to the primary role.For example – the following single Data Guard Broker CLI (DGMGRL) command initiates and completes the switchover to the standby database “Chicago”:


DGMGRL> SWITCHOVER TO STANDBY;

Once initiated, Data Guard automates the actual role transition processes. No data is lost in the process.

· Fast-Start Failure
Fast-Start Failover Fast-Start Failover allows Data Guard to automatically fail over to a previously chosen, standby database without requiring any manual steps to invoke the failover. Further, upon return of the failed primary, it is automatically reinstated into the configuration as a standby of the new primary database. Fast-Start Failover can be used only in a Data Guard Broker configuration and can be configured only through DGMGRL or Enterprise Manager.

DGMGRL> FAILOVER TO STANDBY;

A manual failover operation ensures zero data loss if Data Guard was being run in the Maximum Protection or Maximum Availability and the target standby database was synchronized at the time of the failover.

Primary and standby database can continue to alternate roles. The primary database can switch the role to a standby database; and one of the standby databases can switch roles to become the primary.

The main difference between physical and logical standby databases is the manner in which log apply services apply the archived redo data:

For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database, as shown in Figure.

For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database.

Data Guard Interfaces

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.

The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations.

You can perform all management operations locally or remotely through the broker’s easy-to-use interfaces: the Data Guard web pages of Oracle Enterprise Manager, which is the broker’s graphical user interface (GUI), and the Data Guard command-line interface (CLI) called DGMGRL.


Source : Oracle Corporation.

RMAN Backup & Recovery Step by Step Process for Windows O.S. :

Folks, here are all the steps for RMAN and its step by step for simple understanding. I hope there won't be any confusion while going through the following steps.

RMAN Backup & Recovery Step by Step Process for Windows :

The RMAN ambiance consists of the utilities and databases that comedy a role in abutment up your data. At a minimum, the ambiance for RMAN charge accommodate the following:

The ambition database to be backed up

The RMAN client, which interprets advancement and accretion commands, directs server sessions to assassinate those commands, and annal your advancement and accretion action in the ambition database ascendancy file.

Logical Backup : backup utility that performs backups on the logical components of the database. A logical backup consists of backing up the database at the tablespace level or backing up other logical database components such as a table.

Physical Backup: Backing up the database files such as the datafiles, control files, and redo log file

RMAN Repository and Control Files



The RMAN utility uses two methods of storing information about the target databases that are backed up. Oracle calls the collection of metadata about the target databases the RMAN repository There are two methods of storing data in the RMAN repository.

Recovery Catalog

The first method is by accessing an RMAN catalog of information about backups. The second method is by accessing the necessary information about backups in the target database’s control files. The init.ora or spfile.ora parameter CONTROL_FILE_RECORD_KEEP_TIME determines how long information that can be used by RMAN is kept in the control file. The default value for this parameter is 7 days and can be as many as 365 days.

The recovery catalog is designed to be a central storage place for multiple databases’ RMAN information. This centralizes the location of the RMAN information instead of having this information dispersed in each target database’s control file. Here is a list of the components contained in a recovery catalog:

Backup and recovery information that is logged for long-term use from the target databases
RMAN scripts that can be stored and reused
Backup information about datafiles and archive logs
Information about the physical makeup, or schema, of the target database

RMAN (Configuration ) Using Recovery Catalog

Creating user for RMAN, grant required resources/privileges and register database

Note: Here I am not utilizing defalt rman backup location (db_recovery_dest), I will create new directory named "rman" for holding all rman backups.

Database : Oracle 10g, Oracle 11g

1. Login as sys user, create directory and permit to rman user for reading/writing backups on directory.

2. Login to database , create rman user and grant necessary privileges

SQL> set oracle_sid=orcl

SQL> sqlplus / as sysdba
Connected.

SQL> create user rman identified by rman default tablespace users temporary tablespace temp;
User created.

SQL> grant connect, resource, recovery_catalog_owner to rman;
Grant succeeded.

3. Connect to RMAN, create catalog and register database.

C:\Documents and Settings\user> rman
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 3 18:38:55 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target
connected to target database: ORCL (DBID=1232238763)

RMAN> connect catalog rman/rman@orcl
connected to recovery catalog database

RMAN> create catalog tablespace users;
recovery catalog created

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

4. View default RMAN parameters and change if required

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\RAMTECH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

Note: Here I am changing default parameters - (you can leave it, if want to utilize default )

RMAN> configure device type disk backup type to compressed backupset;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete


RMAN> configure retention policy to recovery window of 30 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup format for device type disk to 'D:\app\Ramtech\flash_recovery_area\%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\Ramtech\flash_recovery_area\%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Creating Backup Sets :

RMAN> connect target

Full Database Backup (archive logs not included)

RMAN> BACKUP DATABASE;

Note: we can also take backup as following:

Starting backup at 03-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=D:\APP\RAMTECH\ORADATA\ORCL\SDE1.DBF
input datafile file number=00001 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00004 name=D:\APP\RAMTECH\ORADATA\ORCL\USERS01.DBF
input datafile file number=00003 name=D:\APP\RAMTECH\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 03-JUL-15
channel ORA_DISK_1: finished piece 1 at 03-JUL-15
piece handle=D:\APP\RAMTECH\PRODUCT\11.2.0\DBHOME_1\DATABASE\08QB4TTH_1_1 tag=TAG20150703T182801 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
Finished backup at 03-JUL-15

Starting Control File and SPFILE Autobackup at 03-JUL-15
piece handle=D:\APP\RAMTECH\FLASH_RECOVERY_AREA\C-1402635108-20150703-01 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JUL-15

RMAN> run
2> { allocate channel c1 type disk;backup database format 'D:\app\Ramtech\flash_recovery_area\%d_%I_%s_%T';
3> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=21 device type=DISK

Starting backup at 03-JUL-15
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=D:\APP\RAMTECH\ORADATA\ORCL\SDE1.DBF
input datafile file number=00001 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00004 name=D:\APP\RAMTECH\ORADATA\ORCL\USERS01.DBF
input datafile file number=00003 name=D:\APP\RAMTECH\ORADATA\ORCL\UNDOTBS01.DBF
channel c1: starting piece 1 at 03-JUL-15
channel c1: finished piece 1 at 03-JUL-15
piece handle=D:\APP\RAMTECH\FLASH_RECOVERY_AREA\ORCL_1402635108_11_20150703 tag=TAG20150703T183319 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:15
Finished backup at 03-JUL-15

Starting Control File and SPFILE Autobackup at 03-JUL-15
piece handle=D:\APP\RAMTECH\FLASH_RECOVERY_AREA\C-1402635108-20150703-02 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JUL-15
released channel: c1


Database and Archive log Backup :

RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s';
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
RMAN>

Note: can user following parameters if required--BACKUP (ARCHIVELOG ALL DELETE INPUT);

Using the TAG command to name the backup monthly backup.

RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s' tag monthly_backup;
backup format 'log_t%t_s%s_p%p' (archivelog all);
}

We can create rman script, and execute when required :

CREATE SCRIPT wholedbbackup
COMMENT "Whole database backup and archive log"
{ ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE CHANNEL C2 TYPE DISK;
BACKUP DATABASE [backup as compressed backupset database TAB COMPRESED_DBBK_082807];
BACKUP FORMAT 'ARC%S_%R.%T' (ARCHIVELOG ALL); }

RUN { EXECUTE script wholedbbackup; }

Backup the control file:

RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP CURRENT CONTROLFILE TO 'D:\app\Ramtech\flash_recovery_area\cntrlfile.copy';

Backup tablespace:

RMAN> BACKUP TABLESPACE system, users, tools;

Backup datafiles :

RMAN> BACKUP AS BACKUPSET DATAFILE 'D:\app\Ramtech\oradata\orcl\users01.dbf','D:\app\Ramtech\oradata\ORCL\undotbs01.dbf';

Backup parameter file:

RMAN> BACKUP SPFILE;

Backup Everything Using Backupset Command:

RMAN> BACKUP BACKUPSET ALL;

Database Recovery from RMAN backups (Examples)

Database recovery depend on events, it may requird full database recover or single database file, redolog , controlfile even spfile (parameter file), here some examples for database recover using RMAN backup.

Restore spfile from backup

RMAN> CONNECT TARGET;
RMAN> SET DBID 1232238763
RMAN> STARTUP FORCE NOMOUNT
RMAN> RESTORE SPFILE FROM AUTOBACKUP;


Restoring and recovering a datafile

RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7ONLINE';

Restore and recover a tablespace

RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Restore and recover the whole database

RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Monitoring RMAN Backups

Connect database and find databas dbid

SQL> connect system/manager@orcl
SQL> select dbid from v$database;

SQL> connect rman_user/rman_user@orcl
SQL> select db_key from rc_database where dbid = 1232238763
SQL> select bs_key, backup_type, completion_time
from rc_database_incarnation a, rc_backup_set b
where a.db_key = b.db_key
and a.db_key = b.db_key
and a.current_incarnation = ‘YES’;

RMAN Reporting :

RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> list backupset by file;
RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> REPORT OBSOLETE;
RMAN> REPORT SCHEMA;

Oracle Architecture :

Felt like Sharing this :

Cheers!

ORA 12514 error:TNS listener error :


Probable reasons and solutions :

One of the reasons you get that kind of error is because database instance started before listener did. Listener must always be started first.

    - Check if a database instance up and running.

    - Check the output of the lsnrctl service command and see what services are registered.

    - Check if you has SERVICE_NAME correctly specified when connecting to the instance.

    - If it happens that database instance stared before listener did sometimes you just need to wait a little or you can execute alter system register in order to register the instance.

    - Add the SID entry in TNSNAMES.ORA :

SID_NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SID_NAME)
    )
  )

Cheers!

How do you query an 11g Oracle database to get a description of an error code?



Anonymous : I want to write a procedure which will return the description of an error code I give to it. So when it's written I could call it like this:

select ora_code_desc('ORA-00000')
from dual;

And it would output:

Normal, successful completion.
Cause: An operation has completed normally, having met no exceptions.
Action: No action required.


Solution :  

It's not accessible from SQL but within PL/SQL, you can use the SQLERRM function.

For example

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    dbms_output.put_line( sqlerrm(0) );
  3    dbms_output.put_line( sqlerrm(-1041) );
  4* end;
SQL> /
ORA-0000: normal, successful completion
ORA-01041: internal error. hostdef extension doesn't exist

PL/SQL procedure successfully completed.

You could, of course, build an ora_code_desc function that took in a string, removed the first three characters, passed the resulting number to SQLERRM, and returned the result

SQL> ed
Wrote file afiedt.buf

  1  create or replace function ora_code_desc( p_code in varchar2 )
  2    return varchar2
  3  is
  4    l_str varchar2(1000);
  5  begin
  6    l_str := sqlerrm( substr(p_code, 4 ) );
  7    return l_str;
  8* end;
SQL> /

Function created.

SQL> select ora_code_desc( 'ORA-00000' ) from dual;

ORA_CODE_DESC('ORA-00000')
--------------------------------------------------------------------------------
ORA-0000: normal, successful completion

 

Oracle also ships a utility on Unix platforms oerr that provides more detail-- particularly the cause and action you're looking for. If you really want that data too, you could write a Java stored procedure that called out to an operating system shell, executed an oerr command, and returned the result. That would give you more data but would, obviously, be much more complex.


Source :

http://stackoverflow.com/questions/11892043/query-oracle-for-ora-code-error-details


Cheers!






Top 40 Most Searched For Oracle Errors :

This is really interesting .

 

Position	Error	Global Monthly Searches*
1	ORA-12154	101,500
2	ORA-00600	40,500
3	ORA-01722	27,100
4	ORA-12560	22,200
5	ORA-03113	18,100
6	ORA-00604	14,800
7	ORA-00936	12,100
8	ORA-01017	12,100
9	ORA-01555	12,100
10	ORA-04031	12,100
11	ORA-00257	12,100
12	ORA-27101	12,100
13	ORA-00911	12,100
14	ORA-00933	9,900
15	ORA-01403	9,900
16	ORA-01422	9,900
17	ORA-04030	9,900
18	ORA-00932	9,900
19	ORA-01031	8,100
20	ORA-20000	8,100
21	ORA-12560	8,100
22	ORA-06508	8,100
23	ORA-01000	6,600
24	ORA-12505	6,600
25	ORA-20001	6,600
26	ORA-12519	6,600
27	ORA-01008	6,600
28	ORA-00054	6,600
29	ORA-01830	6,600
30	ORA-00907	6,600
31	ORA-00984	6,600
32	ORA-01461	5,400
33	ORA-01110	5,400
34	ORA-00001	5,400
35	ORA-02010	5,400
36	ORA-12537	5,400
37	ORA-03135	5,400
38	ORA-01034	5,400
39	ORA-00918	5,400
40	ORA-04063	5,400

*Figures based on Google Adwords Keyword tool global monthly search average. 


Cheers!

Database Error Messages 11G:

For official Oracle site, visit the following site for all Oracle error messages including cause and solution.

http://docs.oracle.com/cd/B28359_01/server.111/b28278/toc.htm


Cheers!

ORA-38029: object statistics are locked
ORA-06512: at "SYS.DBMS_DDL", line 257
ORA-06512: at "SYS.DBMS_UTILITY", line 458
ORA-06512: at line 1

SQL> EXEC DBMS_UTILITY.analyze_schema('SCHEMA_NAME','ESTIMATE', estimate_rows => 100);
BEGIN DBMS_UTILITY.analyze_schema('SCHEMA_NAME','ESTIMATE', estimate_rows => 100); END;

*
ERROR at line 1:

ORA-38029: object statistics are locked
ORA-06512: at "SYS.DBMS_DDL", line 257
ORA-06512: at "SYS.DBMS_UTILITY", line 458
ORA-06512: at line 1


Solution :

Execute the following :


select 'exec DBMS_STATS.UNLOCK_TABLE_STATS ('''|| owner ||''','''|| table_name ||''');' from dba_tab_statistics where owner = 'SCHEMA_NAME' and stattype_locked is not null;


And then :

exec DBMS_STATS.UNLOCK_TABLE_STATS('SCHEMA_NAME','Object_NAME');


Cheers!

Rebuild All Indexes for specific User/Schema :




select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;' from dba_indexes where owner=upper('SCHEMA_NAME');

Spool File With Date/Time Name :


Way to spool  automatically :

    SET TERMOUT OFF
    COLUMN today_ddmmyyyy_col NEW_VALUE today_ddmmyyyy
    SELECT TO_CHAR ( SYSDATE, 'DDMMYYYY') AS today_ddmmyyyy_col
    FROM    dual;
    SET TERMOUT ON
    SPOOL log_&today_ddmmyyyy..log

Check Database Features :

Check Database Features :

Small topics but Useful to know which Feature you have on your database Using View :


For 9i ---> V$OPTION


For 10g and above --->  DBA_FEATURE_USAGE_STATISTICS

Cheers!

How to Check FRA Size :

The Below query To check FRA Used Space and Free Space its very Useful Query :


    set lines 100
    col name format a60
    select name
    , floor(space_limit / 1024 / 1024) "Size MB"
    , ceil(space_used  / 1024 / 1024) "Used MB"
    from v$recovery_file_dest
    order by name
    /

AWR vs ADDM vs ASH :


AWR : automatic workload repository

The AWR is used to collect performance statistics including:

    Wait events used to identify performance problems.
    Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
    Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
    Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
    Object usage statistics.
    Resource intensive SQL statements.


 ADDM : automatic database diagnostic monitor


Analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.and we use it for the following cases :

         CPU bottlenecks
         Undersized memory structures
         I/O capacity issues
         High load SQL statements
         RAC specific issues
         Database configuration issues
         Also provides recommendations on hardware changes, database configuration & schema changes.

Generate ADDM  :

    Login to SQL
    @$ORACLE_HOME/rdbms/admin/addmrpt.sql
     enter system password when you asked for .
    Specify a begin_snap from the list and press Enter.
    Specify the end_snap from the list and press Enter.
     Report Name

ASH : Active Session History

statistics from the in-memory performance monitoring tables also used to track session activity and simplify performance tuning.

ASH reports Give the following information :

    Top User Events (frequent wait events)
    Details to the wait events
    Top Queries
    Top Sessions
    Top Blocking Sessions
    Top DB Object.
    Activity Over Time

 Generate ASH reports :

The Best way to do that using OEM.  (Enterprise manager).

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed :


ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed



Cause:
Missing Space Issue

Solution
log_archive_dest_1 = 'LOCATION=D:\app\oracle\primary\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=primary'

Should be

log_archive_dest_1 = 'LOCATION=D:\app\oracle\primary\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode :

SQL> shutdown immediate ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
SQL> alter database archivelog ;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> shutdown immediate ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL>

SQL> SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

Enabling Automatic Memory Management (AMM) :


SQL> show parameter target


    NAME                                               TYPE        VALUE
    ------------------------------------ -----------       -------
    archive_lag_target                                     integer     0
    db_flashback_retention_target                  integer     1440
    fast_start_io_target                                   integer     0
    fast_start_mttr_target                                integer     0
    memory_max_target                                 big integer 26048M
    memory_target                                         big integer 0
    parallel_servers_target                              integer     2048
    pga_aggregate_target                               big integer 6502M
    sga_target                                                big integer 19520M

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET = 20000M scope=spfile ;

SQL > SHUTDOWN IMMEDIATE;
SQL > STARTUP ;

SQL> ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile ;
System altered.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 scope=spfile ;
System altered.

SQL> ALTER SYSTEM SET MEMORY_TARGET=12288M scope=spfile ;
System altered.

ORA-01940: cannot drop a user that is currently connected :


    SQL> drop user ANDY cascade ;
    drop user ANDY cascade
    *
    ERROR at line 1:
    ORA-01940: cannot drop a user that is currently connected


    SQL> select sid,serial# ,username from v$session where username like '%AND%';
           SID    SERIAL# USERNAME
    ---------- ---------- ------------------------------
           745        821 ANDY

    SQL> alter system kill session '745,821' immediate ;
    System altered.


    SQL> drop user ANDY ;
    drop user ANDY
    *
    ERROR at line 1:
    ORA-01940: cannot drop a user that is currently connected

Solution is :

    SQL> startup force restrict;
    ORACLE instance started.
    Total System Global Area 7686086656 bytes
    Fixed Size                  2228032 bytes
    Variable Size            1811939520 bytes
    Database Buffers         5729419264 bytes
    Redo Buffers              142499840 bytes
    Database mounted.
    Database opened.


     SQL> drop user ANDY cascade ;
    User dropped.

 Logical Standby Vs Physical Standby :


What is the difference between these two Data Guard configuration , When To Use them :

Physical Standby:

1- Physical Standby Database Its exactly same As Primary Database.
2-  In Physical Data Guard The archivelog Applied directly after transfer from primary database (FTP)

Properties of Physical Standby

1- Maintain Is Easy.
2- Creation is Easy .
3- Copy Of your Primary Database ( Disaster Recovery Solution ).

Usage :

High availability solutions Or disaster recovery Solution.

Logical Standby

1 - Opposite Of standby Database , Which is not Match primary Database .
2 - This Kind Of Configuration can be Opened in Read Only Mode .
3 - can have additional materialized views and indexes added for faster performance
4 - LogMiner Techniques to transfer Archivelog.

Properties Of Logical Database :

1 - Open In Read only Mode .
2 - Sometimes its used as RollBack Solution In Upgrade ,

Usage:

1 - reporting Database to avoid overhead in primary database.
2 - Query Database .


How they Works :

Regarding to Oracle documentation  :

LNS (log-write network-server) and ARCH (archiver) processes running on the primary database select archived redo logs and send them to the standby database, where the RFS (remote file server) background process within the Oracle instance performs the task of receiving archived redo-logs originating from the primary database.

Alternatively, a supplementary mechanism may transfer the archived redo logs. On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).

Once the archived redo logs have arrived, other processes (such as an ARCH (Archiver process), an MRP (Managed Recovery Process), and/or an LSP (Logical Standby Process)) may set about applying the log contents to the standby database.

 Conclusion :

The difference between physical and logical standby is in the way the changes from the primary are applied. Both created as an exact image of the primary database. Both receive redo logs from the primary database.

 Refernce :
 Oracle DataGuard architecture

Steps to convert Standby Database to Logical Standby Database :

Step #1 :

In this step logminer Dictionary Must be Built into redo data. ( Build LogMiner Tables for Dictionary)

On Primary

    SQL> begin
      2  dbms_logstdby.build;
      3  end ;
      4  /



OR

     SQL> execute dbms_logstdby.build;


Step #2:

Now we have stop Managed recovery between primary and standby :

On Standby :


    SQL> alter database recover managed standby database cancel ;

    Database altered.


Step #3 :

Convert Physical Standby to Logical Standby .

On Standby :


    SQL> alter database recover to logical standby stdby ;

    Database altered.


Step #4:

On Standby ( New Logical)  we open it in Resetlog mode .


On New Logical ( Standby) :

    SQL> shutdown immediate ;
    ORA-01507: database not mounted


    ORACLE instance shut down.

    SQL> startup mount ;
    ORACLE instance started.

    Total System Global Area  839282688 bytes
    Fixed Size                  2217992 bytes
    Variable Size             532678648 bytes
    Database Buffers          301989888 bytes
    Redo Buffers                2396160 bytes
    Database mounted.

    SQL>
    SQL>
    SQL> alter database open resetlogs ;

    Database altered.


Step #5:

Start Apply Service On logical


    SQL> alter database start logical standby apply ;

    Database altered.


OR


    SQL> alter database start logical standby apply immediate ;
    Database altered.


    Check if everything Goes Fine :

    SQL> select database_role , Name from v$database ;

    DATABASE_ROLE    NAME
    ---------------- ---------
    LOGICAL STANDBY  STDBY



Cheers!

 Startup/Shutdown Logical Standby Database :


Simple Steps to do that


Shutdown Steps :

On Primary do the following :

    SQL > alter system switch logfile ;
    SQL > alter system archive log current ;


Go to Standby :

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
    SQL> shutdown immediate;

Primary Database :

    SQL> shutdown immediate;


Startup Steps :

Primary Database


    SQL>startup;

 Standby Database:

    SQL > startup
    SQL > alter database start logical standby apply immediate;

Cheers!