RMAN Can’t Backup or Copy Active Files In NoArchivelog Mode

Oracle Recovery Manager is the backup solution from Oracle Corporation. Here are the steps I followed this morning to backup my oracle database running in NOARCHIVELOG mode. First connect to the database and issue BACKUP DATABASE command
1) In shell prompt type rman
2) Issue the following command in RMAN prompt to connect to the target database
RMAN>CONNECT TARGET ORACLE_SID
This will ask for target database password. On successfully issuing password, we’ll see connected to target database information
3) Issue BACKUP DATABASE command. This will end up in error as the database is in NOARCHIVELOG mode
RMAN> backup database;
Starting backup at 28-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/28/2010 08:09:04
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-APR-10
channel ORA_DISK_1: finished piece 1 at 28-APR-10
piecehandle=C:APPUsernameFLASH_RECOVERY_AREADatabase_SIDBACKUPSET2010_04_28O1_MF_NCSNF_TAG20100428T080904_5XJ9C258_.BKP tag=TAG20100428T080904co
mment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/28/2010 08:09:04
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
Reason behind ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode:
I tried to create a backup of the tablespace using the oracle rman utility. I’m making use of oracle database enterprise edition. I got the following error
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
This error is self-explanatory. We can backup a database, database storage structures like tablespace only if the database is in ARCHIVELOG mode. We need to bring the database into archivelog mode to overcome this problem. Connect to database using sql*plus as sys user with sysdba privilege.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1375792 bytes
Variable Size 679477712 bytes
Database Buffers 385875968 bytes
Redo Buffers 4603904 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Now try the backup command and it works fine.
RMAN> backup tablespace users;
Starting backup at 27-SEP-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=C:APPorclORCLORCLUSERS01.DBF
channel ORA_DISK_1: starting piece 1 at 27-SEP-10
channel ORA_DISK_1: finished piece 1 at 27-SEP-10
piece handle=C:APPorclFLASH_RECOVERY_AREAORCLBACKUPSET2010_09_27O1_MF
_NNNDF_TAG20100927T172436_6B22WNFM_.BKP tag=TAG20100927T172436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-SEP-10

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner

Oracle 12c SGA Starting Address Parameters

Oracle 12c offers mechanism to get details on starting address of SGA. Two popular parameters in oracle 12c help us determine the starting address of SGA in a 64-bit environment
1) hi_shared_memory_address – This parameter specifies the starting address of SGA at runtime. It specifies high order 32 bit of 64 bit address. If SGA is not used this value is 0
2) shared_memory_address – This value provides details on starting address of SGA. By default it is 0
D:>set oracle_sid=oracle12c
D:>sqlplus sys/****** as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Aug 18 18:30:29 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> show parameter memory

NAME TYPE VALUE
———————————— ———– ——————————
hi_shared_memory_address integer 0
memory_max_target big integer 1632M
memory_target big integer 1632M
shared_memory_address integer 0

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner

Automate AWR Report Generation Using dbms_workload_repository

AWR report popularly called the automated workload repository is the brainchild of Oracle corporation. This simple tool is a one stop solution to get complete picture of Oracle database management system. In addition to DB statistics this tool collects statistics at OS level, I/O performance detail, CPU usage by SQL’s etc
AWR report can be easily generated by running awrrpt.sql script located inside rdbms/admin directory of ORACLE_HOME
It is possible to generate the same output using Oracle supplied PL/SQL package dbms_workload_repository
dbms_workload_repository has functions including awr_report_text, awr_report_html that helps us generate report in text or html format. Snapshot ID, DBID can be supplied as input and necessary output can be generated out of DB server
Note : As is the case with awrrpt.sql script this package fails if there is a DB restart between the supplied snap ID’s

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner

Segment Types Oracle Database 11g

The basic unit of data storage in an Oracle database is Oracle block which is formed by contiguous physical storage blocks. Group of oracle blocks will give rise to extent. Extents when grouped together will form Segments.
Whenever a table object is created, table segment is created in the schema space. Apart from this there are index segmentas,undo segments, clustered segments etc. Here is the query to get details on many different segment types in a database.
SQL> select segment_type,count(1) from dba_segments group by segment_type order by segment_type;
SEGMENT_TYPE         COUNT(1)
—————— ———-
CLUSTER                    10
INDEX                    2367
INDEX PARTITION           360
LOB PARTITION               1
LOBINDEX                  725
LOBSEGMENT                725
NESTED TABLE               33
ROLLBACK                    1
TABLE                    1635
TABLE PARTITION           201
TYPE2 UNDO                 10
11 rows selected.
In this command, count(1) represents the first column in selection list

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner