User managed recovery of sysaux datafile

User managed recovery of sysaux datafile
******************************************

1)Take bkp of database by putting db in begin backup mode
********************

SQL> alter database begin backup;

Database altered.

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024
------------------------------ ---------------
/home/cybage/app/cybage/oradata/temp/users01.dbf
USERS                                        5

/home/cybage/app/cybage/oradata/temp/undotbs01.dbf
UNDOTBS1                                    40

/home/cybage/app/cybage/oradata/temp/sysaux01.dbf
SYSAUX                                     500
                            ....etc

2)copy to bkp location and end backup
**************
cp * /home/cybage/bkp/

SQL> alter database end backup;

Database altered.


3)To stimulate delete datafile from OS
**************
[cybage@oracle bkp]$ rm /home/cybage/app/cybage/oradata/temp/sysaux01.dbf

4)go to mount state
*******************
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  584568832 bytes
Fixed Size                  2215544 bytes
Variable Size             251658632 bytes
Database Buffers          327155712 bytes
Redo Buffers                3538944 bytes
SQL> alter database mount;

Database altered.


5)Restore database by copying file to orginal location
*************
[cybage@oracle bkp]$ cp sysaux01.dbf /home/cybage/app/cybage/oradata/temp/

SQL> recover database ;
Media recovery complete.

6)open database.
*************
SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEMP      READ WRITE

Transportable Tablespace

Transportable Tablespaces

1)Create tablespace

CREATE TABLESPACE test_data

  DATAFILE '/home/cybage/app/cybage/oradata/orcl/test_data01.dbf'

  SIZE 1M AUTOEXTEND ON NEXT 1M;

2)Create User assign created tablespace name

CREATE USER test_user IDENTIFIED BY test_user

  DEFAULT TABLESPACE test_data

  TEMPORARY TABLESPACE temp

  QUOTA UNLIMITED ON test_data;

3)Provide Grants

grant connect,resource,CREATE SESSION, CREATE TABLE to test_user;

4)Create table from that user and add the data

CREATE TABLE test_tab (

  id          NUMBER,

  description VARCHAR2(50),

  CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

INSERT /*+ APPEND */ INTO test_tab (id, description)

SELECT level,

       'Description for ' || level

FROM   dual

CONNECT BY level <= 10000;

5)to check whether database self contained

CONN / AS SYSDBA

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);

6) ALTER TABLESPACE test_data READ ONLY;

7)Take export

 expdp directory=data_pump_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log

.

8)Export of tablespaces

expdp directory=data_pump_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log

9)Transport dump file and data file that to destination directory

Destination Database

10)create user and provide grants

CREATE USER test_user IDENTIFIED BY test_user;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

11)Transportable datafiles

impdp directory=data_pump_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles=' /home/cybage/app/cybage/oradata/temp/test_data01.dbf'

                                                        

Loss of all redolog file

Scenario:Loss of all redolog file.
**********************************

1)details of logfile
select GROUP#,STATUS,MEMBER,IS_RECOVERY_DEST_FILE,TYPE from v$logfile;


   GROUP# STATUS  MEMBER                                             IS_ TYPE
---------- ------- -------------------------------------------------- --- -------
         3         /home/cybage/app/cybage/oradata/temp/redo03.log    NO  ONLINE
         2         /home/cybage/app/cybage/oradata/temp/redo02.log    NO  ONLINE
         1         /home/cybage/app/cybage/oradata/temp/redo01.log    NO  ONLINE

2)renaming the redo log
[cybage@oracle temp]$ mv redo01.log redo01.log.old
[cybage@oracle temp]$ mv redo02.log redo02.log.old
[cybage@oracle temp]$ mv redo03.log redo03.log.old


3) select * from v$Log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
         1          1         19   52428800        512          1 NO  ACTIVE           1944580 17-JAN-17      1945263 17-JAN-17
         2          1         20   52428800        512          1 NO  ACTIVE           1945263 17-JAN-17      1945269 17-JAN-17
         3          1         21   52428800        512          1 NO  CURRENT          1945269 17-JAN-17   2.8147E+14

4)SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/cybage/app/cybage/product/11.2.0/dbhome_3/dbs/arch
Oldest online log sequence     19
Next log sequence to archive   19
Current log sequence           21

5)errors in aleart log
Errors in file /home/cybage/app/cybage/diag/rdbms/temp/temp/trace/temp_lgwr_1157                                             8.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/cybage/app/cybage/oradata/temp/redo01.l                                             og'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /home/cybage/app/cybage/diag/rdbms/temp/temp/trace/temp_lgwr_1157                                             8.trc:

6)To restore and recover using RMAN
run { set until sequence 19; restore database; recover database; alter database open resetlogs; }



executing command: SET until clause

Starting restore at 17-JAN-17
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 datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/cybage/app/cybage/oradata/temp/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/cybage/app/cybage/oradata/temp/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/cybage/app/cybage/oradata/temp/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/cybage/app/cybage/oradata/temp/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/cybage/app/cybage/oradata/temp/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/cybage/app/cybage/oradata/temp/new.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/cybage/app/cybage/oradata/temp/test_data01.dbf
channel ORA_DISK_1: reading from backup piece /home/cybage/app/cybage/product/11.2.0/dbhome_3/dbs/0arq8lia_1_1
channel ORA_DISK_1: piece handle=/home/cybage/app/cybage/product/11.2.0/dbhome_3/dbs/0arq8lia_1_1 tag=TAG20170117T141434
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 17-JAN-17

Starting recover at 17-JAN-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 18 is already on disk as file /home/cybage/app/cybage/product/11.2.0/dbhome_3/dbs/arch1_18_933023555.dbf
archived log file name=/home/cybage/app/cybage/product/11.2.0/dbhome_3/dbs/arch1_18_933023555.dbf thread=1 sequence=18
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-JAN-17

database opened

Creation of Raw partitions

Last login: Thu Jan  5 19:47:20 2017 from 192.168.200.1
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# fdisk -l

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux
[root@localhost ~]#
[root@localhost ~]# cat /etc/sysconfig/rawdevices
# raw device bindings
# format:  <rawdev> <major> <minor>
#          <rawdev> <blockdev>
# example: /dev/raw/raw1 /dev/sda1
#          /dev/raw/raw2 8 5
[root@localhost ~]# fdisk -l

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux
[root@localhost ~]#
[root@localhost ~]# fdisk /dev/sda

The number of cylinders for this disk is set to 4568.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): p

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux

Command (m for help): n
First cylinder (4218-4568, default 4218):
Using default value 4218
Last cylinder or +size or +sizeM or +sizeK (4218-4568, default 4568): +3G
Value out of range.
Last cylinder or +size or +sizeM or +sizeK (4218-4568, default 4568): +1G

Command (m for help): p

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux
/dev/sda7            4218        4340      987966   83  Linux

Command (m for help): n
First cylinder (4341-4568, default 4341):
Using default value 4341
Last cylinder or +size or +sizeM or +sizeK (4341-4568, default 4568): +1G

Command (m for help): p

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux
/dev/sda7            4218        4340      987966   83  Linux
/dev/sda8            4341        4463      987966   83  Linux

Command (m for help): n
First cylinder (4464-4568, default 4464):
Using default value 4464
Last cylinder or +size or +sizeM or +sizeK (4464-4568, default 4568): +1G
Value out of range.
Last cylinder or +size or +sizeM or +sizeK (4464-4568, default 4568): +500M

Command (m for help): p

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux
/dev/sda7            4218        4340      987966   83  Linux
/dev/sda8            4341        4463      987966   83  Linux
/dev/sda9            4464        4525      497983+  83  Linux

Command (m for help): wq
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@localhost ~]# partprobe
Warning: Unable to open /dev/hda read-write (Read-only file system).  /dev/hda has been opened read-only.
Warning: Unable to open /dev/hdb read-write (Read-only file system).  /dev/hdb has been opened read-only.
[root@localhost ~]# fdisk -l

Disk /dev/sda: 37.5 GB, 37580963840 bytes
255 heads, 63 sectors/track, 4568 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1020     8193118+  83  Linux
/dev/sda2            1021        3631    20972857+  83  Linux
/dev/sda3            3632        3892     2096482+  82  Linux swap / Solaris
/dev/sda4            3893        4568     5429970    5  Extended
/dev/sda5            3893        4153     2096451   83  Linux
/dev/sda6            4154        4217      514048+  83  Linux
/dev/sda7            4218        4340      987966   83  Linux
/dev/sda8            4341        4463      987966   83  Linux
/dev/sda9            4464        4525      497983+  83  Linux
[root@localhost ~]#

Data pump parameters

Parameters to use
•    ATTACH, CONTENT,DATA_OPTION,PARALLEL,REMAP_DATA,REMAP_TABLE, REMAP_TABLESPACE ,FROM USER , TO USER
•    PARFILE,QUERY,RESUMABLE,STATISTICS,TRANSPORT_TABLESPACE

1)Remap_table and sqlfile
impdp dumpfile=table.dmp logfile=table.log directory=DATA_PUMP_DIR tables=emp remap_table=emp:emp1 SQLFILE=file.sql
sql file=stores the DDL statment used in the import operation.

2)attach,job_name
expdp full=y dumpfile=full.dmp logfile=full.log job_name=expdp_full directory=data_pump_dir

to view the running jobs
select job_name, state from dba_datapump_jobs;

a)to attach the job
expdp ATTACH=EXPDP_FULL

b)to stop the job
 STOP_JOB=IMMEDIATE

c)to start the job
Export> START_JOB
Export> CONTINUE_CLIENT(to view updates)

d)to kill the job (which you cannot resume)
Export> KILL_JOB

3)DATA_OPTIONS
DATA_OPTIONS = [DISABLE_APPEND_HINT | SKIP_CONSTRAINT_ERRORS | REJECT_ROWS_WITH_REPL_CHAR]

DISABLE_APPEND_HINT=to avoid TM locks and it will not create logs.

SKIP_CONSTRAINT_ERRORS=If constraint violated also it will load the data.

REJECT_ROWS_WITH_REPL_CHAR=If data type to be converted before import and this parameter is set it will not convert and load the data.

4)CONTENT=ALL, DATA_ONLY, and METADATA_ONLY

5)estimate=blocks|statistics
shows the size of data by both method

6)PARFILE
Ex.enlist all the parameter in the file and provide the name in PARFILE parameter

[cybage@oracle dpdump]$ cat parameter.par
 full=y dumpfile=full.dmp logfile=full.log job_name=expdp_full directory=data_pump_dir

[cybage@oracle dpdump]$ expdp PARFILE=parameter.par

why to use parfile..??
-If command line uses'?' symbol in datafile path
-The over head of writing same parameter with same value.
-command is big and using next line.

Data Pump using DB link

1)Create DB link for Data Pump on temp database to access orcl:

CREATE public DATABASE LINK pushpak
CONNECT TO scott IDENTIFIED BY tiger
USING '192.168.200.129:1521/orcl.cybage.com';

2)Expdp for exporting tables

expdp dumpfile=table.dmp logfile=table.log directory=DATA_PUMP_DIR tables=emp
scp to temp db location
impdp dumpfile=table.dmp logfile=table.log directory=DATA_PUMP_DIR tables=emp table_exists_action=replace SQLFILE=file.sql

3)To import tablespace to using db_link

[cybage@oracle dpdump]$ impdp tablespaces=NEW  logfile=new.log network_link=pushpak remap_tablespace=new:users table_exists_action=replace


Import: Release 11.2.0.1.0 - Production on Thu Jan 12 23:37:03 2017

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

Username: scott
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLESPACE_01":  scott/******** tablespaces=NEW logfile=new.log network_link=pushpak remap_tablespace=new:users table_exists_action=replace
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 27 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP_NEW"                               14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLESPACE_01" successfully completed at 23:37:12

File system to asm migration

File system to asm migration
############################


Check existing details
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/db/orcl/system01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/sysaux01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/undotbs01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/users01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/example01.dbf

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

SQL> show parameter db_recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3882M

SQL> select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/db/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl


SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          7   52428800        512          1 YES
INACTIVE               1011076 23-JAN-17      1011080 23-JAN-17

         2          1          8   52428800        512          1 NO
CURRENT                1011080 23-JAN-17   2.8147E+14

         3          1          6   52428800        512          1 YES
INACTIVE               1011073 23-JAN-17      1011076 23-JAN-17


[oracle@localhost ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576      5740     5514                0            2757              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      1928     1874                0            1874              0             N  FRA/
ASMCMD>



#######################################
Actual migration:
#######################################
1)Change the controlfile parameter
ALTER SYSTEM SET control_files='+DATA','+FRA' scope=spfile;

2)change db create parameter and create pfile
ALTER SYSTEM SET db_create_file_dest='+DATA' SCOPE=spfile;
create pfile='/tmp/pfile_orcl.ora' from spfile;

3)Connect to RMAN and fire

RMAN> backup as copy database format '+DATA';

Starting backup at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/product/11.2.0.4/db/orcl/system01.dbf
output file name=+DATA/orcl/datafile/system.257.934043207 tag=TAG20170123T162643 RECID=2 STAMP=934043339
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/product/11.2.0.4/db/orcl/sysaux01.dbf
output file name=+DATA/orcl/datafile/sysaux.258.934043341 tag=TAG20170123T162643 RECID=3 STAMP=934043449
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:49
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/db/orcl/example01.dbf
output file name=+DATA/orcl/datafile/example.259.934043451 tag=TAG20170123T162643 RECID=4 STAMP=934043468
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/product/11.2.0.4/db/orcl/undotbs01.dbf
output file name=+DATA/orcl/datafile/undotbs1.260.934043477 tag=TAG20170123T162643 RECID=5 STAMP=934043489
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/orcl/controlfile/backup.261.934043493 tag=TAG20170123T162643 RECID=6 STAMP=934043497
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/product/11.2.0.4/db/orcl/users01.dbf
output file name=+DATA/orcl/datafile/users.262.934043501 tag=TAG20170123T162643 RECID=7 STAMP=934043501
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-JAN-17
channel ORA_DISK_1: finished piece 1 at 23-JAN-17
piece handle=+DATA/orcl/backupset/2017_01_23/nnsnf0_tag20170123t162643_0.263.934043501 tag=TAG20170123T162643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-17

RMAN>

4)restart db and go to no mount state
connect to RMAN

RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0.4/db/orcl/control01.ctl';

Starting restore at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.264.934044723
output file name=+FRA/orcl/controlfile/current.256.934044729
Finished restore at 23-JAN-17

--Controlfile restored to new location by using modified parameters

5)to change path in controlfile

RMAN> switch database  to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.934043207"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.258.934043341"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.260.934043477"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.262.934043501"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.259.934043451"

6)Recover database

RMAN> recover database;

Starting recover at 23-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

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

Finished recover at 23-JAN-17

7)Now only redolog file migration is pending
a)change parameter
alter system set db_create_online_log_dest_1='+FRA';

b)add logfile group
alter database add logfile group 4;
alter database add logfile group 5;
alter database add logfile group 6;

c)it will be created on ASM location now drop existing one

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

group 3 will not get drop because file showing status as current
SQL>  select GROUP#,THREAD#,MEMBERS,STATUS from v$log;

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         3          1          1 CURRENT
         4          1          1 UNUSED
         5          1          1 UNUSED
         6          1          1 UNUSED

For that
SQL> alter system switch logfile;

System altered.

still it will show status as active

SQL> alter system checkpoint;

System altered.


and drop the group 3 redolog
alter database drop logfile group 3;




****************************Migration Complete to verify*****************************

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.257.934043207
+DATA/orcl/datafile/sysaux.258.934043341
+DATA/orcl/datafile/undotbs1.260.934043477
+DATA/orcl/datafile/users.262.934043501
+DATA/orcl/datafile/example.259.934043451


 SQL> select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.264.934044723
+FRA/orcl/controlfile/current.256.934044729


SQL> select GROUP#,MEMBER from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         4 +FRA/orcl/onlinelog/group_4.257.934045745
         5 +FRA/orcl/onlinelog/group_5.258.934045773
         6 +FRA/orcl/onlinelog/group_6.259.934045781


****************************************************END*************************************************

Switchover standby Manually

Switchover Stimulation

On primary

1)To check error

select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

2)Current status

select message from v$dataguard_status;

3)To switch to standby

SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE=’USER’;

alter database commit to switchover to standby with session shutdown;

shut down immediate;

alter database mount standby database;

4)To verify

alter database recover managed standby database disconnect from session;

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

ORCL      MOUNTED              PHYSICAL STANDBY

SQL> show parameter db_unique

NAME                                 TYPE        VALUE

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

db_unique_name                       string      orcl

On stand by

1)to convert to primary

alter database commit to switchover to primary;

2)Bounce and open the database

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area  217157632 bytes

Fixed Size                  2211928 bytes

Variable Size             159387560 bytes

Database Buffers           50331648 bytes

Redo Buffers                5226496 bytes

Database mounted.

Database opened.

3)To verify

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

ORCL      READ WRITE           PRIMARY

SQL> show parameter db_unique

NAME                                 TYPE        VALUE

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

db_unique_name                       string      stby

Note:

If standby in dgmgrl showing error

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Then start the real time apply on standby.

If dbf file deleted from the primary recovery from standby backup datafile

Primary:orcl

Standby:stby

👇👇👇👇👇👇👇👇👇👇👇👇👇👇
On primary
1)Report Schema
report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               YES     /home/cybage/app/cybage/oradata/orcl/system01.dbf
2    480      SYSAUX               NO      /home/cybage/app/cybage/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             YES     /home/cybage/app/cybage/oradata/orcl/undotbs01.dbf
4    11       USERS                NO      /home/cybage/app/cybage/oradata/orcl/users01.dbf
5    100      EXAMPLE              NO      /home/cybage/app/cybage/oradata/orcl/example01.dbf
6    50       NEW                  NO      /home/cybage/app/cybage/oradata/orcl/new.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/cybage/app/cybage/oradata/orcl/temp01.dbf

RMAN> exit


2)Datafile removed from primary
rm /home/cybage/app/cybage/oradata/orcl/new.dbf

On stand by
3)connect to rman of stand by and auxiliary as pri DB
rman target / auxiliary=sys/oracle@orcl

4)restored from stand by
backup as copy datafile 6 auxiliary format '/home/cybage/app/cybage/oradata/orcl/new.dbf';

on primary
5)rman target /
connect catalog rc/rc

6)to offline the datafile
sql 'alter database datafile 6 offline';
ALTER DATABASE DATAFILE '/home/cybage/app/cybage/oradata/orcl/new.dbf' OFFLINE immediate;

7)to register in catalog
catalog datafilecopy '/home/cybage/app/cybage/oradata/orcl/new.dbf';

8)Switch to datafile so controlfile updated
run {
 set newname for datafile 6 to '/home/cybage/app/cybage/oradata/orcl/new.dbf';
 switch datafile 6;
 }

9)recover datafile for primary
recover datafile 6;

Creration Of recovery Catalog🙋🙋

1)Create user for recovery catalog

 

CREATE USER rc IDENTIFIED BY rc 

      DEFAULT TABLESPACE users 

      QUOTA UNLIMITED ON users;

 

2)Grant permission for the user

 

grant RECOVERY_CATALOG_OWNER TO rc;

3)Connect to Rman and catalog

 

rman target / catalog rc/rc@orcl

4)To register database 

 

register database

5)To verify whether views created for RC user

 

sqlplus

UN:rc

passwd:rc

select * from tab;

😊😊😊😊😊😊😊😊😊😊