Find out the list of users with default passwords in Oracle

From 11g we can quickly identify users with default passwords by querying the  data dictionary view: DBA_USERS_WITH_DEFPWD. This view will list out the users who are currently having the default passwords.

SQL> select username from dba_users_with_defpwd ;

USERNAME
------------------------------
DIP
MDSYS
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS


14 rows selected.

The above list is the users who are currently having the default passwords in the database.

SQL> show user
USER is "SYS"
Now change the password for scott and query the above view that won't show the scott user in the list as you changed the scott user default password.

SQL> alter user scott identified by tiger1;
 
User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
CTXSYS
OLAPSYS
OUTLN
EXFSYS
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA

WMSYS

13 rows selected.

To achieve the security compliance for the database, change the password for the above users and then expire the password then very next time when you are connecting to the user that will ask the new password.

SQL> select 'alter user '||username||' identified by Welcome678 account password expire;'from dba_users_with_defpwd where username not in ('SYS','SYSTEM','DBSNMP');

'ALTERUSER'||USERNAME||'IDENTIFIEDBYRESOURCE09ACCOUNTLOCKPASSWORDEXPIRE;'
---------------------------------------------------------------------------
alter user MDSYS identified by Welcome678 account password expire;
alter user OLAPSYS identified by Welcome678 account password expire;
alter user LBACSYS identified by Welcome678 account password expire;
alter user ORDSYS identified by Welcome678 account password expire;
alter user WMSYS identified by Welcome678 account password expire;
alter user XDB identified by Welcome678 account password expire;
alter user SI_INFORMTN_SCHEMA identified by Welcome678 account password expire;
alter user CTXSYS identified by Welcome678 account password expire;
alter user MDDATA identified by Welcome678 account password expire;
alter user ORDPLUGINS identified by Welcome678 account password expire;
alter user DIP identified by Welcome678 account password expire;
alter user EXFSYS identified by Welcome678 account password expire;
alter user OUTLN identified by Welcome678 account password expire;


13 rows selected.

Once you execute the above sql statements then we can say currently no users in the database is using default password.

SQL> select username from dba_users_with_defpwd ;

no rows selected.

Happy Learning :)

RMAN06820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMARY

RMAN archivelog backup at the standby site is throws the following errors

Logfile Details:
****************
RMAN06820: WARNING: failed to archive current log at primary database 
ORACLE error from target database:
ORA17629: Cannot connect to the remote database server 
ORA-17627: ORA-00942: table or view does not exist

But the rest of the backup actually complete successfully.

CAUSE:
******
11.2.0.4 onward as per 'unpublished' Bug 8740124, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site. This is achieved by forcing a log switch at the primary site. However, the connection to the primary failed when attempting to do so.
This is due to this bug:
Bug 17580082 ACTIVE STANDBY RMAN06820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMAR

Workaround:
***********
Do not use operating system authentication to login with RMAN. Use a username and password.
That is, do not use just the "/" (operating system authentication) connect to the standby database:
$ rman target /
Connecting as 'rman target /'
# it gets the sys user but not the password and so, it does NOT mean it is being explicitly specified to connect as sysdba.
Instead put in the username and password for the SYSDBA user:
$ rman target sys/password@stby
Connecting as 'rman target sysdba_user/password@stby'
Note: The password, within the password file, for the primary and standby should be identical.

For more details please look at the Doc ID: 1616074.1


Happy Learning :) 

EM12c Metric Evaluation Error – Supplied date is in the future

After the  Daylight Saving Time we are getting below metric collection errors from some host targets.

Metric Evaluation Error Details:
Host=oraprod
Target type=Database Instance
Target name=PROD
Categories=Error
Message= Row(1): Supplied date is in the future : now = Wed Nov 10 10:39:01 EST 2015 supplied value = Wed Nov 10 11:38:07 EST 2015
Severity=Critical
Event reported time=Nov 10, 2015 10:39:01 AM EST
Contact=Sivaram
Location=Banalore
Comment=DB archive
Department=CRM
Operating System=Linux
Platform=x86_64
Event Type=Metric Evaluation Error
Event name=wait_bottlenecks
Metric Group=Wait Bottlenecks
Collection Name=wait_bottlenecks_10i
Rule Name=CRM Metric Evaluation Error Any Target
Rule Owner=SYSMAN
Update Details:
Row(1): Supplied date is in the future : now = Wed Nov 10 10:39:01 EST 2015 supplied value = Wed Nov 10 11:38:07 EST 2015

Solution:
***********

Clearing and restarting agents on these hosts will fix the problem and it will disappear.
$ ps -ef|grep pmon
oracle   15676     1  0 Jul28 ?        00:15:26 ora_pmon_prod
oracle   20606 20239  0 04:07 pts/0    00:00:00 grep pmon
$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle

$ ps -ef|grep emagent

oracle    3390 20239  0 04:31 pts/0    00:00:00 grep emagent
oracle    9521     1  0 Jul15 ?        00:02:12 /u01/app/oracle/product/12.1.0.2/core/12.1.0.4.0/perl/bin/perl

$ cd /u01/app/oracle/product/12.1.0.2/core/12.1.0.4.0/bin
$ ./emctl stop agent
$ ./emctl stop agent
$ ./emctl clearstate agent
$ ./emctl start agent
$ ./emctl upload agent

Happy Learning :)

How to modify db_file_name_convert parameter value on standby using DGMGRL

1) Check the value of the db_file_name_convert parameter

SQL> show parameter db_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +P1PROD_DATA/p1prod/datafile/,
    +F1PROD_DATA/f1prod/datafile

2. Change the db_file_name_convert  according to the your requirement in sql prompt
SQL> alter system set db_file_name_convert='+P1PROD_DATA/p1prod/datafile/','+F1PROD_DATA/f1prod/datafile/','+P1PROD_DATA2/p1prod/datafile/','+F1PROD_DATA2/f1prod/datafile/' scope=spfile;

Once you change the parameter value in sql prompt then you need to update the same in DGMGRL prompt other wise  you will get the error for the incosistent properties.

DGMGRL> show database f1prod inconsistentproperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
         f1prod    DbFileNameConvert +P1PROD_DATA/p1prod/datafile/, +F1PROD_DATA/f1prod/datafile

DGMGRL> show configuration

Configuration - p1prod_dg

  Protection Mode: MaxPerformance
  Members:
  p1prod - Primary database
    f1prod - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 37 seconds ago)

DGMGRL> show database f1prod

Database - f1prod

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 886.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    f1prod
      Warning: ORA-16675: database instance restart required for property value modification to take effect
      Warning: ORA-16714: the value of property DbFileNameConvert is inconsistent with the database setting

Database Status:
WARNING

3) Change the parameter value in DGMGRL prompt

DGMGRL> edit DATABASE "f1prod" SET PROPERTY DbFileNameConvert='+P1PROD_DATA/p1prod/datafile/,+F1PROD_DATA/f1prod/datafile/,+P1PROD_DATA2/p1prod/datafile/,+F1PROD_DATA2/f1prod/datafile/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "dbfilenameconvert" updated

Once you changed the parameter value then you need to restart the database in order to take the modified values to effect.

4) Stop and start the database using SRVCTL

$ srvctl stop database -d f1prod
[oracle@prodora02:/home/oracle f1prod]>
$ srvctl start database -d f1prod
You have new mail in /var/spool/mail/oracle
[oracle@prodora02:/home/oracle f1prod]>
$ srvctl status database -d f1prod
Database is running.

5) Now you can see that the inconsistent properties value is cleared as after database restart the parameter will effected with the new value.

DGMGRL> show configuration

Configuration - p1prod_dg

  Protection Mode: MaxPerformance
  Members:
  p1prod - Primary database
    f1prod - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 48 seconds ago)

DGMGRL> show database f1prod inconsistentproperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

Happy Learning :)

Convert Physical Standby to Snapshot Standby and Vice Versa using DGMGRL

Check the below commands output before proceeding at standby server.
$ srvctl status database -d stand
$ srvctl status service -d stand
$ srvctl config database -d stand
$ ./crsctl status res -t

Connect to primary server and perform the below steps this will convert the physical standby to snapshot standby

DGMGRL> show configuration;

Configuration - DG-prod

  Protection Mode: MaxPerformance
  Members:
  prod   - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 23 seconds ago)


DGMGRL> convert database stand to snapshot standby;
Converting database "stand" to a Snapshot Standby database, please wait...
Database "stand" converted successfully
DGMGRL> show configuration;

Configuration - DG-prod

  Protection Mode: MaxPerformance
  Members:
  prod   - Primary database
    stand - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

DGMGRL> show database stand;

Database - stand

  Role:               SNAPSHOT STANDBY
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          1 minute 18 seconds (computed 0 seconds ago)
  Instance(s):
    stand

Database Status:
SUCCESS

Make sure that you have the enough space for the FRA.
The below query will give the FRA usagae details.
SQL> select name, (space_limit/1024/1024/1024) ||'GB' as Space_Limit, (space_used/1024/1024/1024)||'GB' as Space_Used from v$recovery_file_dest;
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

Do your tests on the snapshot standby at this time you can open in read-write mode. Once your work done then convert back to snapshot standby to physical standby.

DGMGRL> convert database stand to physical standby;
Converting database "stand" to a Physical Standby database, please wait...
Database "stand" converted successfully
DGMGRL> show configuration;

Configuration - DG-prod

  Protection Mode: MaxPerformance
  Members:
  prod   - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 23 seconds ago)

Once done please crosscheck the sync status between primary and standby.

*********************Check Sync Status**************************

Primary:
*******
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        127056

Standby:
*******
SQL> select max(sequence#) from V$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
        127056

Also check the all the oracle services are running as before the snapshot convert.

$ srvctl status database -d stand
$ srvctl status service -d stand
$ srvctl config database -d stand
$ ./crsctl status res -t

The outputs of the above commands should match with the previous one. If not we need to troubleshoot it.

Happy Learning :)

How to tune a SQL statement

Please follow the below steps for tuning a SQL statement.

Take the sql_id before going to start.

1. Create Tuning Task:

SQL> set serveroutput on
declare
sql_tune_task_id  varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => '03h94xbcu25c3',            //give your sql_id
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tune_03h',
                          description => 'tuning task for statement 03h94xbcu25c3');
  dbms_output.put_line('sql_tune_task_id: ' || sql_tune_task_id);
end;
/

2. Execute Tuning Task:
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_03h');

3. TO GET SUMMARY INFORMATION:

SQL> set long 100000
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('sql_tune_03h') as recommendations from dual;

The above query result gives the recommendations if you want to follow you can. After completion of your work drop the tuning task which you created earlier.

4. Drop the tuning task:

SQL> begin
DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tune_03h');
end;
 /

To know the status of the tuning task
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = '<OWNER_NAME>';

You can tune the sql statement by using the below script provided by the Oracle
script location: ORACLE_HOME/rdbms/admin/sqltrpt.sql
It will prompt for the sql_id, provide the sql_id.

Happy Learning :)

Standby Creation step by step using RMAN Active database duplicate

Standby creation for prod by RMAN duplicate concept
Details of servers are given below.
Primary server details:-
Server name: oracleserver01.prod.com
IP Address: 192.168.1.67
SID: prod

Standby server details:-
Server name : oracleserver02.prod-standby.com
IP Address: 192.168.1.68

Note:
******
Before performing standby creation hash out cron for archive deletion from production.
Steps to be followed to create standby for prod.

1. Add standby logfile in production database server(online logfile + 1)

SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
    GROUP# MEMBER                                                             A.BYTES/1024/1024    THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
6 rows selected.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL

SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

SQL> show parameter standby
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string      +FLASH
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter system set db_create_online_log_dest_2='';
System altered.

SQL> show parameter db_create
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.

SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b;
    GROUP# MEMBER                                                             A.BYTES/1024/1024    THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1

30 rows selected.

2. Enable force logging on production.
SQL> alter database force logging;

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

3. In the standby database home, create and start a listener that offers a static SID entry for the standby database.
[oracle@oracleserver02 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
          (SID_LIST =
            (SID_DESC =
              (GLOBAL_NAME = standby_dgmgrl)
              (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
              (SID_NAME = standby)
            )
          (SID_DESC =
              (GLOBAL_NAME = standby)
              (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
              (SID_NAME = standby)
            )
          )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Tnsnames.ora
***********
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_prod =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))


standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

LISTENER_standby =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))

4. In the database home on the primary node, create an Oracle Net alias to connect to the listener created in the above step.
prod listener:
**************
[oracle@oracleserver01 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prod)
      (ORACLE_HOME =/u01/app/oracle/product/12.1.0.2/db_1)
    )
  )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Tnsnames.ora:
************
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
LISTENER_standby =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_prod =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))

5. Register the new listener with new grid home(12.1*)

$ srvctl modify listener –l listener –o /u01/app/12.1.0.2/grid

[oracle@oracleserver02 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:43
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
The command completed successfully

[oracle@oracleserver02 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:53
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                16-MAY-2015 03:22:03
Uptime                    24 days 10 hr. 32 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracleserver02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "standby_DGB" has 1 instance(s).
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 2 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standbyXDB" has 1 instance(s).
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Check for network connectivity from primary to standby database.

6. Create a password file on the standby host that where the auxiliary instance will be started. Copy password file from primary to standby in $ORACLE_HOME/dbs location.
[oracle@oracleserver02 dbs]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/dbs
[oracle@oracleserver02 dbs]$ orapwd file=orapwstandby password=yas_3

7.  On the standby host in the ORACLE_HOME/dbs directory create a pfile (initstandby.ora) with the following parameters:
[oracle@oracleserver02 dbs]$ more initstandby.ora
db_name=prod
db_unique_name=standby
sga_target=2g
db_create_online_log_dest_1='+DATA'
db_create_file_dest='+DATA'
db_recovery_file_dest_size=5g
db_recovery_file_dest='+FLASH'
control_files='+DATA'
compitable=12.1.0.2

8.create audit directory on for standby database on standby server
mkdir –p /u01/app/oracle/admin/standby/adump

9. On the standby host set the SID to the same as standby and startup nomount the standby/auxiliary instance. And add the the sid in oratab file too.

export ORACLE_SID=standby
sqlplus / as sysdba
startup nomount
[oracle@oracleserver02 admin]$cat /etc/oratab
standby: /u01/app/oracle/product/12.1.0.2/db_1/:N

10. On the primary host run an rman script that duplicates the primary database using the “duplicate target database for standby from active database” command

[oracle@oracleserver01 dba]$ more rman_standby_standby.ksh
!/bin/ksh

export ORAENV_ASK="NO"
export ORACLE_SID=prod
. oraenv
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

rman target sys/yas_3@prod auxiliary sys/yas_3@standby debug trace=rman_trace.log log=standby_attempt.log<<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stdby1 type disk;
allocate auxiliary channel stdby2 type disk;

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='standby'
set db_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','prod','standby'
set log_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','+DMNO_REDO/prod','+DMNO_REDO/standby','prod','standby'
set control_files='+DATA/standby/controlfile/control01.ctl','+FLASH/standby/controlfile/control02.ctl'
set  DB_CREATE_FILE_DEST='+DATA'
set db_recovery_file_dest='+FLASH'
set db_recovery_file_dest_size='200G'
set audit_file_dest='/u01/app/oracle/admin/standby/adump'
Nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel stdby1;
release channel stdby2;
}
EOF

A brief explanation of the individual clauses is shown below.

• FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
• FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
• DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
• SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
• NOFILENAMECHECK: Destination file locations are not checked.

10. create Softlink for network files.

ln -s /u01/app/12.1.0.2/grid/network/admin/listener.ora listener.ora
ln -s /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora tnsnames.ora

12. Edit the parameter log_archive_dest_2 in primary database.
Primary:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.

SQL> alter system set log_archive_dest_2= ‘service="standby" ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(online_logfile,all_roles)’;

13. Set below parameters on standby.

Standby:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.

13. The standby database will get mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;

DGMGRL Configuration:
*********************
1. Login into primary database (prod) server and standby database (standby) server.
2. Check if both the production and standby database are in sync.
On PRIMARY:
? Select max(sequence#) from v$archived_log;
On STANDBY:
? Select max(sequence#) from v$log_history;
3. Check if output of both these matches with each other. If not kindly bring the database in sync, check if there are any errors transfering archives on standby database.
4. Check ‘ERROR’ column in v$managed_standby for any issue related to log transfer
? Select dest_id,error from v$managed_standby;
5. If everything is fine, then proceed with dataguard broker configuration.
6. Start the Dataguard broker DMON process.
      On Primary and Standby,

SQL> alter system set dg_broker_start=true sid='*';
SQL> alter system set dg_broker_config_file1=’<DIR_PATH/file_name.dat>’
SQL> alter system set dg_broker_config_file2=’<DIR_PATH/file_name.dat>’

7. Connect to dataguard broker.
  Connect to DGMGRL

  Primary,
$dgmgrl
DGMGRL>connect /

8. Create configuration.
DGMGRL> CREATE CONFIGURATION 'DG-PRIM' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod;

Before performing the above kindly make sure no log_archive_dest_n is set on both Primary and Standby database.
9. Verify configuration.
DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  boston - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Add Standby
DGMGRL> add database standby as connect identifier is standby;

DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  prod  - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

10. Enable Configuration
DGMGRL> enable configuration;

11. Verify configuration
DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  prod  - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
DGMGRL> show database prod;

Database – prod

  Role:              PRIMARY
  Intended State:    TRANSPORT-ON
  Instance(s):
    prod

Database Status:
SUCCESS

DGMGRL>  show database standby;

Database – standby

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     0 seconds (computed 1 second ago)
  Apply Lag:         0 seconds (computed 1 second ago)
  Apply Rate:        2.54 MByte/s
  Real Time Query:   ON
  Instance(s):
    standby
Database Status:
SUCCESS

Note:
******
You can find above steps in Oracle DOC ID: 1583588.1
For prerequisites follow oracle doc ID:  1305019.1

Metrics Average Users Waiting Count is at x for event class Application

If you receive this alert check the sessions which are in waiting state and what is reason behind for the waiting state. The below query will give the list of sessions which are in waiting state. So based on the output you can easily find out.

col LOGONDATE for a15 trunc
col MODULE for a30 trunc
col OSUSER for a8
col USERNAME for a8
set pages 10000
col sql_cost for 9999999 trunc
col sid for 99999999
col sql_CNT for 9999999
col machine for a10
col lc_et for 999999
col spid form a6
col app_id form a6
col event for a30
col wait_mins for 99999 trunc
select /*a.sid,a.serial#,*/ b.spid,a.sql_id,(select OPTIMIZER_COST from v$sqlarea where sql_id=a.sql_id) sql_cost,
(select count(1) from v$session where SQL_HASH_VALUE=a.sql_hash_value) sql_CNT,
a.PROCESS App_ID, a.username,
to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.event, a.osuser, a.module,a.machine,round(a.last_call_et/60) wait_mins
from v$session a, v$process b where a.paddr = b.addr
and a.status = 'ACTIVE' and a.username is not null and a.sql_id is not null
order by 8,3,11 desc;

Happy Learning :)

How to drop a disk from the diskgroup

SQL> select name,group_number,state,type,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup; 
NAME                           GROUP_NUMBER STATE       TYPE   Totalspace(GB) Freespace(GB)   PCT_FREE
------------------------------ ------------ ----------- ------ -------------- ------------- ----------
PROD_RECO1                                3 MOUNTED     EXTERN           1600    1305.19922 81.5749512

SQL> Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=3;

PATH                  DISK_NUMBER NAME               MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024
--------------------- ----------- ------------------ ------- ------------ ---------- ------------- ------------
ORCL:ASM_PROD_133               0 ASM_PROD_133       CACHED  MEMBER              400           400   326.292969
ORCL:ASM_PROD_134               5 ASM_PROD_134       CACHED  MEMBER              400           400   326.273438
ORCL:ASM_PROD_135               6 ASM_PROD_135       CACHED  MEMBER              400           400     326.3125
ORCL:ASM_PROD_136               7 ASM_PROD_136       CACHED  MEMBER              400           400   326.320313

SQL> alter diskgroup PROD_RECO1 drop disk ASM_PROD_133;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           3 REBAL RUN           1          1       1046      31956       5837           5
  
SQL> select name,group_number,state,type,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup;

NAME                           GROUP_NUMBER STATE       TYPE   Totalspace(GB) Freespace(GB)   PCT_FREE
------------------------------ ------------ ----------- ------ -------------- ------------- ----------
PROD_RECO1                                3 MOUNTED     EXTERN           1200    901.734375 75.1445313
   
SQL> Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=3;

PATH                      DISK_NUMBER NAME           MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024
------------------------- ----------- -------------- ------- ------------ ---------- ------------- ------------
ORCL:ASM_PROD_134                   5 ASM_PROD_134   CACHED  MEMBER              400           400   301.148438
ORCL:ASM_PROD_135                   6 ASM_PROD_135   CACHED  MEMBER              400           400   301.167969
ORCL:ASM_PROD_136                   7 ASM_PROD_136   CACHED  MEMBER              400           400   301.164063


Happy Learning :)

How to move a datafile from filesystem to ASM

We can move the datafile from filesystem to ASM by bringing the corresponding tablespace offline or the only datafile which you needs to be move. We can see these 2 scenarios.

Here we have added the datafile wrongly due to that datafile is not created in a specified location.

SQL> alter tablespace TCB_TSQ01 add datafile 'CRM_DBF01' size 100m autoextend on next 20m maxsize 32g;

Tablespace altered.


 
I) Tablespace offline:
******************

1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                  TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

2. Take the corresponding tablespace offline. 

SQL> alter tablespace TCB_TSQ01 offline;

Tablespace altered.

3. Copy the datafile to the desired location using RMAN

-bash-4.1$ rman target /

RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';

Starting backup at 20-SEP-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01

output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-SEP-15

Starting Control File and SPFILE Autobackup at 20-SEP-15

piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE

Finished Control File and SPFILE Autobackup at 20-SEP-15

RMAN> exit

Recovery Manager complete.

4. Update the controfile with the new location of the datafile.

SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

Database altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01

6 rows selected.

5. Bring the tablespace to the online.

SQL> alter tablespace TCB_TSQ01 online;

Tablespace altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

II) Datafile Offline:
****************
1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------
 /u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                          TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

2. Take the datafile offline.

SQL> alter database datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' offline;

Database altered.

3. Copy the datafile to the desired location using RMAN

-bash-4.1$ rman target /

RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';

Starting backup at 20-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01
output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-SEP-15

Starting Control File and SPFILE Autobackup at 20-SEP-15
piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE
Finished Control File and SPFILE Autobackup at 20-SEP-15

RMAN> exit

Recovery Manager complete.

4. Update the controlfile with the new location of the datafile.

SQL> set pages 200 lines 200
SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

Database altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------
+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                               
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

5. Recover the datafile using RMAN.

RMAN> recover datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

6. Bring the datafile to the online.

SQL> alter database datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477' online;

Tablespace altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- -------------
+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

Note:

*****

If your oracle version is 12c you can execute the below statement is enough to move the datafile. No need to perform all the above steps.

SQL>  alter database move datafile
'/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to ' +CRM_DBF01';

Happy Learning :)