Oracle 11g Logo

Generate AWR – Automatic Workload Repository (AWR) in oracle 11g

Many times DBA need to monitor specific loaded/heavy database related activity in order to fix database performance, for that generating AWR report is perfect solution and he will get all necessary information he needed to tune the database. This article is step by step approach to generate AWR report.

Step-I:

Before loaded activity, issue following command to take snapshot of database as start point of AWR report.

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Step-II: Perform your activity.

Perform your activity.

Step-III: Right after the activity, issue

Right after the activity, issue following command to take again snapshot of database as end point of AWR report.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Note: Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Step-IV:

Generate AWR report: (Follow the instructions and provide input acrrodingly)

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html

Select the appropriate instance:

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
4119037639 1 PRODDB ifsprod localhost.lo
caldomain
4119037639 1 UATDB ifsuat localhost.lo
caldomain
* 4119037639 1 PRODDB ifsprod PR
Enter value for dbid: 4119037639
Enter value for inst_num: 1
Using 1 for instance number

Specify the number of days of snapshots to choose from:

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ifsprod IFSPROD 66276 26 Nov 2016 00:00 1
66277 26 Nov 2016 00:30 1
66278 26 Nov 2016 01:01 1
66279 26 Nov 2016 01:30 1
66280 26 Nov 2016 02:00 1
66281 26 Nov 2016 02:30 1
66282 26 Nov 2016 03:00 1
66283 26 Nov 2016 03:30 1
66284 26 Nov 2016 04:00 1
66285 26 Nov 2016 04:30 1
66286 26 Nov 2016 05:00 1
66287 26 Nov 2016 05:30 1
66288 26 Nov 2016 06:00 1
66289 26 Nov 2016 06:31 1
66290 26 Nov 2016 07:00 1
66291 26 Nov 2016 07:30 1
66292 26 Nov 2016 08:00 1
66293 26 Nov 2016 08:30 1
66294 26 Nov 2016 09:00 1
66295 26 Nov 2016 09:30 1
66296 26 Nov 2016 10:00 1
66297 26 Nov 2016 10:30 1
66298 26 Nov 2016 11:00 1
66299 26 Nov 2016 11:30 1
66300 26 Nov 2016 12:00 1
66301 26 Nov 2016 12:30 1
66302 26 Nov 2016 13:00 1
66303 26 Nov 2016 13:30 1
66304 26 Nov 2016 14:00 1
66305 26 Nov 2016 14:30 1
66306 26 Nov 2016 14:41 1
66307 26 Nov 2016 14:45 1

Specify the Start point and end point for the snapshot, input from above:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66306
Begin Snapshot Id specified: 66306
Enter value for end_snap: 66307
End Snapshot Id specified: 66307

Name the report:

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_66306_66307.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: AWR_between_2_manual_snapshot
.
..
...
Report written to AWR_between_2_manual_snapshot
SQL> exit

search for html format snapshot in current directory.

Thanks. Stay Tune. 🙂

Oracle 11g Logo

How to drop database manually, without using Database Configuration Assistant(DBCA)

Before proceed for entire database deletion, kindly consider full database backup for your future reference, if required.

Connect to target database with sys user, and issue following query to check database mode:

[oracle@testserver ~]$ sqlplus / as sysdba
SQL> select open_mode,name from v$database;
OPEN_MODE            NAME
-------------------- ---------
READ WRITE           BLADE2

Normally shutdown your database:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

startup your database in mount mode with exclusive restrict, as follows:

SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 1.0088E+10 bytes
Fixed Size 2215984 bytes
Variable Size 5704257488 bytes
Database Buffers 4362076160 bytes
Redo Buffers 19640320 bytes
Database mounted.

Database is in mount mode:

SQL> select open_mode,name from v$database;
OPEN_MODE            NAME
-------------------- ---------
MOUNTED              BLADE2

Issue following SQL command in order to drop database, this will drop entire database with datafiles, control files and log files,etc.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Database dropped successfully.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

rename/relocate SYSTEM, SYSAUX or User Tablespace datafiles

This is traditional approach to move/rename SYSTEM, SYSAUX or users tablespace. In this method, we need database downtime as we can’t take SYSTEM or SYSAUX tablespace offline.

I recommend full cold backup before performing mentioned activity.

Consider scenario where I want to move SYSTEM and SYSAUX tablespace to new location.

Step I:

After considering full cold backup of your database, shutdown database with normal or immediate mode only.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step II:

After shut down database normally, I am going to copy datafiles from original location to its new location.

Following query will provide tablespace name and its datafiles details.

SQL> select file_name,tablespace_name from dba_data_files;

Copying datafiles:

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/app/oracle/oradata/RTS_NEW/system01.dbf
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf

After copy, verify size of datafiles on both locations to check whether copy successful or not.

Step III:

Start database in mount mode:

[oracle@PR oradata]$ sqlplus / as sysdba 
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.

Step IV:

After successful mount, rename the datafiles to its locations as below:

SQL> alter database rename file '/u01/app/oracle/oradata/RTS/system01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/system01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/RTS/sysaux01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf';
Database altered.

Step V:

Open database:

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Verify that system and sysaux datafiles are moved/renamed successful to new location.

SQL> select file_name,tablespace_name from dba_data_files;
/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/RTS_NEW/system01.dbf SYSTEM

SYSTEM and SYSAUX datafiles relocated successfully to new location.

This can be done with the help of RMAN, but only non-system tablespaces can be relocated. Click me to know more about it.

OR

You can relocate datafiles without getting offline in oracle 12c.

Thanks, Stay Tune. 🙂

Oracle 11g Logo

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

In my Previous article we have covered How to configure oracle 11g Manual Data Guard, Now we will look into switch-over and switch-back activity.

Switch-over Pre-requisites:

One:

Check listener status on primary and standby database.

Primary Database Listener:

[oracle@PR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:49:31
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:44:06
Uptime 0 days 0 hr. 5 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
Service "RTSXDB" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Standby Database Listener:

[oracle@DR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:51:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:45:18
Uptime 0 days 0 hr. 6 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DR.localdomain)(PORT=1521)))
Services Summary...
Service "RTSDR" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Two:

Check database mode on primary and standby database.

Primary Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Standby Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS MOUNTED

Three:

Check database role on primary and standby database.

Primary Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PRIMARY

Standby Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PHYSICAL STANDBY

Four:

No datafiles should be in recovery mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('RECOVER');
no rows selected

Five:

No datafiles should be in offline mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('OFFLINE');
no rows selected

Six:

No datafiles should be in backup mode on Primary and Standby database.

SQL> select file# from v$backup where status='ACTIVE';
no rows selected

Switch-over steps:

Step-A:

Shutdown primary database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step-B:

Copy control file to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/prtemp/
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/prtemp/

Step-C:

Copy SPFile to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/prtemp/

Step-D:

Copy Redo log files to temporary location on primary database.

[oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS/
[oracle@PR RTS]$ ll *.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:08 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo03.log
[oracle@PR RTS]$ cp *.log /home/oracle/prtemp/

Step-E:

Copy needed archive log to DR site.

[oracle@PR RTS]$ cd /home/oracle/archdir/
[oracle@PR archdir]$ scp * oracle@DR:/home/oracle/archdir/

Step-F:

Get primary database to READ ONLY mode.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.
SQL> alter database open read only;
Database altered.

Step-G:

Apply all needed archive logs to standby database.

SQL> recover standby database until cancel;
ORA-00279: change 957836 generated at 04/26/2016 02:49:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_15_910140016.dbf
ORA-00280: change 957836 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 967269 generated at 04/26/2016 23:34:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_16_910140016.dbf
ORA-00280: change 967269 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/archdir/1_15_910140016.dbf' no longer needed for this recovery
ORA-00279: change 967272 generated at 04/26/2016 23:34:53 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_17_910140016.dbf
ORA-00280: change 967272 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/archdir/1_16_910140016.dbf' no longer needed for this recovery
.
..
...
ORA-00279: change 969653 generated at 05/09/2016 23:48:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_29_910140016.dbf
ORA-00280: change 969653 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/archdir/1_28_910140016.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/archdir/1_29_910140016.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Step-H:

Shutdown standby database.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Step-I:

Copy control file to temporary location on standby database.

[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/drtemp/
[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/drtemp/

Step-J:

Copy SPFile to temporary location on standby database.

[oracle@DR dbs]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/drtemp/

Step-K:

On Standby database, copy all files(from /home/oracle/drtemp/ directory) to primary database server(to /home/oracle/from_dr directory).

[oracle@DR ~]$ cd /home/oracle/drtemp/
[oracle@DR drtemp]$ ll
total 19044
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control02.ctl
-rw-r-----. 1 oracle oinstall 2560 May 11 23:27 spfileRTS.ora
[oracle@DR drtemp]$ scp * oracle@PR:/home/oracle/from_dr

Step-L:

On Primary database, copy all files(from /home/oracle/prtemp/ directory) to standby database server(to /home/oracle/from_pr directory).

[oracle@PR ~]$ cd /home/oracle/prtemp/
[oracle@PR prtemp]$ ll
total 172656
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control02.ctl
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo03.log
-rw-r-----. 1 oracle oinstall 2560 May 11 23:10 spfileRTS.ora
[oracle@PR prtemp]$ scp * oracle@DR:/home/oracle/from_pr

Step-M:

On Standby database, replace copied files(source directory: /home/oracle/prtemp/) to its appropriate locations:

[oracle@DR from_pr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@DR from_pr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@DR from_pr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora
[oracle@DR from_pr]$ cp *.log /u01/app/oracle/oradata/RTS/

Step-N:

Perform role switching on old standby database: (old standby database to new Primary database)

one:

Startup mount old standby database.

[oracle@DR ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.

Two:

Recover database.

SQL> recover database;
Media recovery complete.

Three:

Drop tempfiles(From temporary tablespace) from old standby database. (Note: If this is your first switch over then you may skip this step)

Four:

Open database:

SQL> alter database open;
Database altered.

Five:

Create new tempfile:

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/RTS/temp02.dbf' size 100m autoextend on maxsize unlimited;
Tablespace altered.

On Primary database, replace copied files(source directory: /home/oracle/drtemp/) to its appropriate locations:

[oracle@PR from_dr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR from_dr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@PR from_dr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora

Shutdown old primary database.

SQL> shut immediate;

Step-O:

Perform role switching on old primary database: (old primary database to new standby database)

One:

Start database with nomount state:

SQL> startup nomount;

Two:

Alter database to mount state with standby database:

SQL> alter database mount standby database;
Database altered.

Cheers!! Switch over activity successfully carried out, now lets perform post checks on both the database servers:

Post checks:

Check database mode and role on new primary database, it shall be READ WRITE and Primary.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Check database mode and role on new standby database, it shall be MOUNTED and PHYSICAL STANDBY.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

We are done with switch over activity.

Switch-back activity steps:

There is no difference in switch-over and switch-back steps. Use above steps to do it.
Cheers!!

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on How to open Standby Database in Read ONLY mode.

Thank you. 🙂

Oracle 12c Logo

How to Configure Flashback Database in oracle 12c

Issue the following steps to configure your database with flashback recovery:

// Ensure flashback recovery settings by querying ‘v$database’ dictionary view, as follows:
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO

// Set db_recovery_file_dest system parameter for flashback recovery file destination:
[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> alter system set db_recovery_file_dest=’/u01/flash_recovery’ scope=both; Continue reading

Oracle 12c Logo

How to configure oracle 12c database in archivelog mode

Connect database as sys user to configure it in archivelog mode.

[oracle@12c ~]$ sqlplus “/ as sysdba”

// Archive log list: Database is in ‘No Archive Mode’.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 140
Current log sequence 142

// Shutdown database:

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

// Startup your database in mount state:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size 2288584 bytes
Variable Size 905970744 bytes
Database Buffers 587202560 bytes
Redo Buffers 7737344 bytes
Database mounted.

// Configure your database in archivelog mode by issue the following:

SQL> alter database archivelog;

Database altered.

// Alter database in open mode:

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

// Database has been successfully configured in archive log mode. Issue the following:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 142
Next log sequence to archive 144
Current log sequence 144

 

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Enabling Archive log mode in RAC – Oracle 11g

In Database environment, set following database parameter as sysdba:

ALTER SYSTEM SET log_archive_format=’arch_%t_%s_%r.arc’ SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/MYSID/archive/’ SCOPE=spfile;

In Grid environment, stop the entire clustered database by following command as oracle user.
$ srvctl stop database -d SID
Note:
By above command, database will be shutdown.
SID: Global database name.

In Database environment, issue the following command to enable archive log mode:
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
SHUTDOWN IMMEDIATE;

In Grid environment, start the entire clustered database by following command as oracle user:
$ srvctl start database -d MYSID
By above method you will alter your RAC database to archive log mode.
************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Enabling Archive log mode in RAC – Oracle 10g

In Database environment, set following database parameter as sysdba:

ALTER SYSTEM SET log_archive_format=’arch_%t_%s_%r.arc’ SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1=’location=Path_of_archive_dir’ SCOPE=spfile;

Mount your database in exlusive mode by following ‘alter system’ command:
ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;
Note:
Database is not accessible to any application or user, being in exclusive mode.

In Grid environment, stop the entire clustered database by following command as oracle user.
$ srvctl stop database -d SID
Note:
By above command, database will be shutdown.

SID: Global database name.

 

In Database environment, issue the following command to enable archive log mode:
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE;

Note:
CLUSTER_DATABASE parameter has been changed to its orignal value.

In Grid environment, start the entire clustered database by following command as oracle user:
$ srvctl start database -d MYSID

In Database environment, Ensure changes by following SQL command:
SQL> archive log list;

 

By above method you will alter your RAC database to archive log mode.

************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

How to extract current Oracle user name and schema name?

Issue following query to extract current user name ( Session name ):

Syntax:
SELECT
sys_context(‘USERENV’,’SESSION_USER’) as “USER NAME”, 
sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) as “CURRENT SCHEMA” 
FROM dual;

sys_context() : This function returns the value of parameter associated with the context namespace. Continue reading

Oracle 11g Logo

How to extract the Oracle table definition (Data Defining Language)

How to extract the Oracle table creation definition (Data Defining Language) from an Oracle database without digging dictionary views?

With the help of GET_DDL() function of DBMS_METADATA metadata package.

Syntax:

select DBMS_METADATA.GET_DDL(‘TABLE’,’TABLE_NAME’) from DUAL;

SET LONG 1000

SET PAGESIZE 0 Continue reading

Oracle 11g Logo

How to fetch last inserted row from any oracle table

This is easily done with the date datatype or timestamp datatype.

Ex:

select empno,ename,to_char(hiredate,’dd mon yyyy’) as hiredate
from emp
where hiredate =( select max(hiredate) from emp );

EMPNO      ENAME      HIREDATE
———- ———- ———–
7876       ADAMS      23 may 1987 Continue reading

Oracle 11g Logo Oracle 11g Logo

How to check RMAN backup status and timings

This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.

Login as sysdba and issue the following script:

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

Above script will give you RMAN backup status along with start and stop timing.

OR

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 18        29         1          9115569    19258880   47.33

Above script will give you SID, Total Work, Sofar & % of completion.

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS
----------- ------------- ---------- ------------------- ------------------- --------
 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19
 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01
 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00
 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05
 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03
 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03
 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04
 . .. ...

*****

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have an easy life ahead.