Move database to another Mount point using RMAN

Move database to another Mount point(NOARCHIVE MODE DB).

Easiest:
1. Shutdown the DB.
2. Copy the data file using OS commands.
3. Start database in mount mode.
4. Get RMAN going:

oracle@localhost:/oradata/ALOK$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 27 06:08:23 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ALOK (not mounted)

RMAN> catalog start with '/oradata/ALOK';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 01/27/2016 06:08:34
ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

RMAN> catalog start with '/oradata/ALOK';

searching for all files that match the pattern /oradata/ALOK

List of Files Unknown to the Database
=====================================
File Name: /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf
File Name: /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
File Name: /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
File Name: /oradata/ALOK/o1_mf_temp_cb1x7qrz_.tmp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf
File Name: /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
File Name: /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
File Name: /oradata/ALOK/o1_mf_temp_cb1x7qrz_.tmp
File Name: /oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf"
datafile 3 switched to datafile copy "/oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf"
datafile 4 switched to datafile copy "/oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf"
datafile 6 switched to datafile copy "/oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf"

RMAN> recover database;

Starting recover at 27-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 27-JAN-16

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/27/2016 06:09:40
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open noresetlogs;

Statement processed

RMAN> report schema;

Report of database schema for database with db_unique_name ALOK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
3    710      SYSAUX               ***     /oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf
4    60       UNDOTBS1             ***     /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
6    96       USERS                ***     /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/ALOK/datafile/o1_mf_temp_cb1x7qrz_.tmp

RMAN>

RMAN>                                                                                                        

Note : Left the errors for reference, I started from no mount state. Recovery may not be required if db was gracefully shutdown.

Future protection for file creation:
alter system set db_create_file_dest='/oradata/ALOK';

Dropped the tempfile and restarted the database. That took care of the tempfile..got auto created.

oracle@localhost:/u01/app/oracle/oradata/ALOK$ rm /u01/app/oracle/oradata/ALOK/datafile/o1_mf_temp_cb1x7qrz_.tmp

oracle@localhost:/u01/app/oracle/oradata/ALOK$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 27 06:25:08 2016

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size                  2289016 bytes

Variable Size            1056965256 bytes

Database Buffers          587202560 bytes

Redo Buffers                7061504 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@localhost:/u01/app/oracle/oradata/ALOK$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 27 06:25:28 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ALOK (DBID=3000882446)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ALOK

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    770      SYSTEM               ***     /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf

3    710      SYSAUX               ***     /oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf

4    60       UNDOTBS1             ***     /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf

6    96       USERS                ***     /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    60       TEMP                 32767       /oradata/ALOK/ALOK/datafile/o1_mf_temp_cbjrpjgg_.tmp

RMAN>                          

Oracle Procedure monitoring progress

Solve Oracle PLSQL code monitoring;

Monitor code done via:

DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'Anything/Proc name',  action_name => 'Anything');
DBMS_APPLICATION_INFO.SET_ACTION(action_name => 'Anything/Progress');

See Sample:
Monitoring session as DBA:
08:56:43 SQL> l
  1* select Module,action from v$session where sid=263
08:56:47 SQL> /

MODULE                                                           ACTION
---------------------------------------------------------------- ----------------------------------------------------------------
SQL*Plus

Note: Session id is 263. Note Module is default and action is blank, after executing following code in session 263.

SQL> DECLARE
  CURSOR c1 IS
    SELECT * FROM emp;
BEGIN
  FOR PC IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || PC.ename || ', Job = ' || PC.job);
  END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11
Name = SMITH, Job = CLERK
Name = ALLEN, Job = SALESMAN
Name = WARD, Job = SALESMAN
Name = JONES, Job = MANAGER
Name = MARTIN, Job = SALESMAN
Name = BLAKE, Job = MANAGER
Name = CLARK, Job = MANAGER
Name = SCOTT, Job = ANALYST
Name = KING, Job = PRESIDENT
Name = TURNER, Job = SALESMAN
Name = ADAMS, Job = CLERK
Name = JAMES, Job = CLERK
Name = FORD, Job = ANALYST
Name = MILLER, Job = CLERK

PL/SQL procedure successfully completed.

NOW little modification:

SQL> DECLARE
  CURSOR c1 IS
    SELECT * FROM emp;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'TESTCode',  action_name => 'BeforeCursor');/* Only at start */
  FOR PC IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || PC.ename || ', Job = ' || PC.job);
          DBMS_APPLICATION_INFO.SET_ACTION(action_name => c1%rowcount);
  END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11   12   13
Name = SMITH, Job = CLERK
Name = ALLEN, Job = SALESMAN
Name = WARD, Job = SALESMAN
Name = JONES, Job = MANAGER
Name = MARTIN, Job = SALESMAN
Name = BLAKE, Job = MANAGER
Name = CLARK, Job = MANAGER
Name = SCOTT, Job = ANALYST
Name = KING, Job = PRESIDENT
Name = TURNER, Job = SALESMAN
Name = ADAMS, Job = CLERK
Name = JAMES, Job = CLERK
Name = FORD, Job = ANALYST
Name = MILLER, Job = CLERK

PL/SQL procedure successfully completed.

See session monitor difference:

9:01:38 SQL> /

MODULE                                                           ACTION

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

TESTCode                                                         14

09:01:39 SQL> 

You can use DBMS_APPLICATION_INFO.SET_ACTION(action_name => c1%rowcount);

to display session progress like:

1. Cursor Open next

2. Procession rows

3. Last error

4. Procession complete

5. Anything and everything you fathom......Enjoy!!!

Oracle: Index: Unusable Rebuild ORA-01502

How to deal with errors reporting Unusable indexes(ORA-01502). 

Must resolve as soon as possible to avoid query optimizer going berserk and cause havoc on DB with un-optimized plans:

For Non-partiton indexes:

Find:

 select owner, index_name, tablespace_name from dba_indexes where status = 'UNUSABLE';

Generate fix:

select 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';' from dba_indexes where status = 'UNUSABLE';




For Partitioned indexes:

Find:

select index_owner, index_name, partition_name, tablespace_name from dba_ind_PARTITIONS where status = 'UNUSABLE';

Generate Fix:

select 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' from dba_ind_partitions where status = 'UNUSABLE';


Then just run the fix statements.