SQL Server - Get rows and space details for all tables in database

with tempspace as (
SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows)

select SchemaName, TableName, 
sum(RowCounts) as RowsCount, 
sum(TotalSpaceKB)/1024/1024 as TotalGB, 
sum(UsedSpaceKB)/1024/1024 as UsedGB, 
sum(UnusedSpaceKB)/1024/1024 as UnusedGB
from tempspace 
group by SchemaName, TableName
order by TotalGB desc, SchemaName, TableName

SQL Server 2012 - Truncate table partitions

1. Create staging table [staging_TABLE_NAME]. The best way to create it is from SSMS 

ServerName -> Databases -> DatabaseName -> Tables -> TABLE_NAME (Right click) -> Storage -> Manage partition -> Create a staging table for partition switching

In the generated script comment ALTER TABLE lines with constraint on partition limits.

Execute script to create staging table.

Update in truncate partition script staging_TABLE_NAME with generated staging table name.

2. Get details about table partititons

DECLARE @TableName NVARCHAR(200) = N'TABLE_NAME'

SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object]
     , p.partition_number AS [p#]
     , fg.name AS [filegroup]
     , p.rows
     , au.total_pages AS pages
     , CASE boundary_value_on_right
       WHEN 1 THEN 'less than'
       ELSE 'less than or equal to' END as comparison
     , rv.value
     , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
       SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
       CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
       SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
       SUBSTRING (au.first_page, 1, 1))) AS first_page
FROM sys.partitions p
INNER JOIN sys.indexes i
     ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
     ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
     ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
     AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
     ON f.function_id = rv.function_id
     AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
     AND o.object_id = OBJECT_ID(@TableName)
order by 2;

3. Execute truncate script for selected partitions. To execute also the commands un-comment EXEC lines. Test it before execution in ptoduction like environment !!!!

DECLARE @partno nvarchar(5)
DECLARE @cmd1 nvarchar(200)
DECLARE @cmd2 nvarchar(200)
SELECT @cmd2 = 'TRUNCATE TABLE [staging_TABLE_NAME]'

DECLARE table_names CURSOR FOR
SELECT distinct partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number between 20 and 100
order by partition_number

OPEN table_names
FETCH NEXT FROM table_names INTO @partno
WHILE @@fetch_status = 0
BEGIN

IF EXISTS (SELECT partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number = @partno)

BEGIN

SELECT @cmd1 = 'ALTER TABLE [DBNAME].[dbo].[TABLE_NAME] SWITCH PARTITION ' + @partno + ' TO [DBNAME].[dbo].[staging_TABLE_NAME]'
   
PRINT @cmd1
PRINT @cmd2
-- EXEC sp_executeSQL @cmd1;
-- EXEC sp_executeSQL @cmd2;
END

FETCH NEXT FROM table_names INTO @partno
END

CLOSE table_names
DEALLOCATE table_names

SQL Server - Get table partitions details

SELECT    
     p.partition_id                             as PartitionID
    ,t.name                                     as [Table]
    ,ps.name                                    as PartitionScheme
    ,pf.name                                    as PartitionFunction
    ,p.partition_number                         as [Partition]
    ,p.rows                                     as [Rows]
    ,prv.value                                  as Boundary
    --,pf.type_desc                               as BoundaryType
    ,case when pf.boundary_value_on_right = 1
        then 'Right'
        else 'Left'
        end                                     as BoundarySide

FROM
    sys.tables t
    inner join sys.partitions p
        on t.object_id = p.object_id
        and p.index_id = 1
    inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
    inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id
    inner join sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    inner join sys.partition_functions pf on ps.function_id = pf.function_id
    left outer join sys.partition_range_values prv
        on pf.function_id = prv.function_id
        and p.partition_number = prv.boundary_id
WHERE
    is_ms_shipped = 0 and
    t.name = 'TABLE_NAME'  
    and exists (select 1 from sys.partitions p2
                where t.object_id = p2.object_id
                and partition_number > 1)
order by
     t.name
    ,p.partition_number desc

11g - Data Guard - Primary/Standby Windows Server reboot

1. Procedure for Primary Oracle server reboot

1. Create safe backups for Primary and Standby databases

C:\Users\oracle>set oracle_sid=<SID_NAME>

C:\Users\oracle> rman target /

RMAN> backup database current controlfile;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE PLUS ARCHIVELOG;

Check Oracle RMAN status:

SQL> select sid, start_time,(sofar/totalwork) * 100 done,

to_char(sysdate + time_remaining/3600/24,'dd-mm-yyyy hh24:mi')

from v$session_longops

where opname not like '%aggregate%' and opname like 'RMAN%' and totalwork > sofar;

2. Stop Oracle Enterprise Manager Console

C:\Users\oracle>set oracle_sid=<SID_NAME>

C:\Users\oracle>emctl status dbconsole

Oracle Enterprise Manager 11g is running.

C:\Users\oracle>emctl stop dbconsole

The OracleDBConsole<DB_NAME> service was stopped successfully.

3. Disable Primary and Standby scheduled backup tasks that might interfere with switchover and reboot activity

Set backup jobs for all databases to Disabled.

4. Switchover databases to Standby

Check gaps for online redo

• on primary :

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

• on standby :

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE APPLIED = 'YES'

AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = 'CURRENT')

GROUP BY THREAD#;

Check the difference (should not be bigger the 1 or 2 according to Oracle documentation)

Check no jobs are running (automatic jobs starting at 22:00 should be finished) :

SQL> SELECT * FROM DBA_JOBS_RUNNING;

Tail alert log files (optional)

• With Power Shell:

PS C:\Users\oracle> Get-Content E:\app\oracle\diag\rdbms\<db_name>\<SID>\trace\alert_<sid>.log -wait

Perform switchover

• Connect to the primary instance and perform the switchover

DGMGRL> CONNECT SYS/password@primary

DGMGRL> SHOW CONFIGURATION

DGMGRL> SWITCHOVER TO <standby database name>;

• Check the new configuration

DGMGRL> SHOW CONFIGURATION

• on the new primary  check on each instance if the services are running:

SQL> SELECT NAME FROM V$SERVICES; 

• on the new standby check on each instance if the services are NOT running:

SQL> SELECT NAME FROM V$SERVICES;

5. Prepare server for reboot

• Connect to observer server

C:\Users\oracle>dgmgrl /@<primary_db_name>

DGMGRL> show configuration;

Fast-Start Failover: ENABLED

DGMGRL> disable fast_start failover;

Disabled.

DGMGRL> show configuration;

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

• Checking New Primary/New standby:

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

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

• New primary:

SQL> show parameter log_archive_dest_state_2;

SQL> alter system set log_archive_dest_state_2=defer scope=both;

SQL> alter system switch logfile;

• New standby:

SQL> shutdown immediate

• Stop Oracle services on New standby

6. REBOOT New standby SERVER

7. Startup Oracle services and instances on New standby server reboot

• Start Oracle services on New standby

• Startup database in MOUNT state

C:\Users\oracle> set oracle_sid=<SID_NAME>

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

SQL> startup mount

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

• Check database and alert.log to see that log_archive_dest_state_2 is set automatically to ENABLE.

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

• Enable fast start failover

C:\Users\oracle> dgmgrl /@<db_name>

DGMGRL> show database verbose <primary database>;

DGMGRL> enable fast_start failover;

DGMGRL> show configuration verbose;

8. Switchover databases to Primary

Check gaps for online redo

• on New primary :

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

• on New standby :

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE APPLIED = 'YES'

AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = 'CURRENT')

GROUP BY THREAD#;

Check the difference (should not be bigger the 1 or 2 according to Oracle documentation)

Check no jobs are running (automatic jobs starting at 22:00 should be finished) :

SQL> SELECT * FROM DBA_JOBS_RUNNING;

Tail alert log files (optional)

• With Power Shell:

PS C:\Users\oracle> Get-Content E:\app\oracle\diag\rdbms\<db_name>\<SID>\trace\alert_<sid>.log -wait

Perform switchover

• Connect to the new primary instance and perform the switchover

DGMGRL> CONNECT SYS/password@New primary

DGMGRL> SHOW CONFIGURATION

DGMGRL> SWITCHOVER TO <Primary database name>;

• Check the new configuration

DGMGRL> SHOW CONFIGURATION

• on the primary check on each instance if the services are running:

SQL> SELECT NAME FROM V$SERVICES; 

• on the new standby check on each instance if the services are NOT running:

SQL> SELECT NAME FROM V$SERVICES;

9. Enable scheduled tasks

Enable Scheduled tasks

2. Procedure for STANDBY Oracle server reboot

1. Create safe backups for Primary and Standby databases

C:\Users\oracle>set oracle_sid=<SID_NAME>

C:\Users\oracle> rman target /

RMAN> backup database current controlfile;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE PLUS ARCHIVELOG;

Check Oracle RMAN status:

SQL> select sid, start_time,(sofar/totalwork) * 100 done,

to_char(sysdate + time_remaining/3600/24,'dd-mm-yyyy hh24:mi')

from v$session_longops

where opname not like '%aggregate%' and opname like 'RMAN%' and totalwork > sofar;

2. Disable DCO scheduled backup tasks that might interfere with reboot activity

Set backup jobs for all databases to Disabled.

3. Prepare server for reboot (stop databases and Oracle services)

• Connect to observer server

C:\Users\oracle>dgmgrl /@<primary_db_name>

DGMGRL> show configuration;

Fast-Start Failover: ENABLED

DGMGRL> disable fast_start failover;

Disabled.

DGMGRL> show configuration;

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

• Checking Primary/standby:

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

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

• primary:

SQL> show parameter log_archive_dest_state_2;

SQL> alter system set log_archive_dest_state_2=defer scope=both;

SQL> alter system switch logfile;

• standby:

SQL> shutdown immediate

• Stop Oracle services on standb

4. REBOOT Standby SERVER

5. Startup Oracle services and instances on Standby server after server reboot

• Start Oracle services on standby

• Startup databases in MOUNT state

C:\Users\oracle> set oracle_sid=<SID_NAME>

C:\Users\oracle> sqlplus /nolog

SQL> conn / as sysdba

SQL> startup mount

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

• Check database and alert.log to see that log_archive_dest_state_2 is set automatically to ENABLE.

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

• Enable fast start failover

C:\Users\oracle> dgmgrl /@<db_name>

DGMGRL> show database verbose <primary database>;

DGMGRL> enable fast_start failover;

DGMGRL> show configuration verbose;

• Select the time of action

SQL> set linesize 200

SQL> select (select (to_char(sysdate,'dd - mm - yyyy hh24:mi:ss')) from dual) as TimeofExec, db_unique_name,

database_role, open_mode from v$database;

6. Enable scheduled tasks on Standby server

Enable Scheduled tasks

11g - Reconfigure EM console

C:\Users>set oracle_unqname=orcl

C:\Users>emca -config dbcontrol db -repos recreate -reconfig ports -dbcontrol_http_port 5501

Optional, reconfigure EM console port

C:\Users>emca -reconfig ports -DBCONTROL_HTTP_PORT 5501

Check EM console status

C:\Users>emctl status dbconsole

 

11g - DataGuard change SYS password

PRIMARY WIN-PRYORA01

STANDBY WIN-SECORA01

OBSERVER WIN-OBSORA01

On WIN-OBSORA01 (Check Oracle Wallet)

C:\Users\oracle>mkstore -wrl "E:\app\oracle\product\11.2.0\dbhome_1\BIN\owm\wallets\oracle" -listCredential

Oracle Secret Store Tool : Version 11.2.0.4.0 - Production

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

Enter wallet password:

List credential (index: connect_string username)

2: testaic2 sys

1: testaic sys

C:\Users\oracle>

On WIN-OBSORA01 (Check observer status)

C:\Users\oracle>dgmgrl /@testaic

DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> show configuration verbose

Configuration - testaic

Protection Mode: MaxAvailability

Databases:

testaic - Primary database

testaic2 - (*) Physical standby database

(*) Fast-Start Failover target

Properties:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

ObserverReconnect = '0'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

ObserverOverride = 'FALSE'

ExternalDestination1 = ''

ExternalDestination2 = ''

PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: ENABLED

Threshold: 30 seconds

Target: testaic2

Observer: WIN-OBSORA01

Lag Limit: 30 seconds (not in use)

Shutdown Primary: TRUE

Auto-reinstate: TRUE

Observer Reconnect: (none)

Observer Override: FALSE

Configuration Status:

SUCCESS

C:\Users\oracle>dgmgrl /@testaic

DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> disable fast_start failover;

Disabled.

DGMGRL> exit

On WIN-PRYORA01 (Disable log transport)

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter user sys identified by "NewPassword";

User altered.

Copy PWDtestaic.ORA to WIN-SECORA01, and rename it to PWDtestaic2.ORA

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>

On WIN-OBSORA01 (Enable fast_start failover)

C:\Users\oracle>dgmgrl sys/NewPassword@testaic

DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> enable fast_start failover;

Enabled.

DGMGRL> exit

!!!!! The observer still connects with old SYS password (no password set for password file).

C:\Users\oracle>dgmgrl /@testaic2

DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL>

On WIN-OBSORA01 (Reconfigure users in wallet)

C:\Users\oracle>mkstore -wrl "E:\app\oracle\product\11.2.0\dbhome_1\BIN\owm\wallets\oracle" -deleteCredential testaic

Oracle Secret Store Tool : Version 11.2.0.4.0 - Production

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

Enter wallet password: 

Delete credential

Delete 1

C:\Users\oracle>mkstore -wrl "E:\app\oracle\product\11.2.0\dbhome_1\BIN\owm\wallets\oracle" -createCredential testaic sys NewPassword

Oracle Secret Store Tool : Version 11.2.0.4.0 - Production

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

Enter wallet password: 

Create credential oracle.security.client.connect_string1

Same steps for testaic2

Changing password for SYSTEM user

On WIN-PRYORA01 (Disable log transport)

SQL> alter user system identified by "NewPassword";

User altered.

SQL> conn system/1qazxsw2@testaic

Connected.

SQL> conn system/1qazxsw2@testaic2

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.

Testing new password on secondary

On WIN-OBSORA01 (only for test)

C:\Users\oracle>dgmgrl /@testaic

DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> switchover to testaic2

Performing switchover NOW, please wait...

Operation requires a connection to instance "testaic2" on database "testaic2"

Connecting to instance "testaic2"...

Connected.

New primary database "testaic2" is opening...

Operation requires startup of instance "testaic" on database "testaic"

Starting instance "testaic"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "testaic2"

DGMGRL>

C:\Users\oracle>sqlplus system/NewPassword@testaic2

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 11:29:51 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

C:\Users\oracle>sqlplus system/NewPassword@testaic

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 11:29:58 2016

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

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0 Serial number: 0

Enter user-name:

C:\Users\oracle>

12c Steps to restore database from full backup in case of DR

This is a an restore exercise for a CDB database with CDB name.

1. Create INITcdb.ora file with content:

db_name='cdb'

2. Startup database in nomount (it will read the init file and will start the instace)

SQL> startup nomount
ORACLE instance started.

Total System Global Area  234881024 bytes
Fixed Size                  3044152 bytes
Variable Size             176164040 bytes
Database Buffers           50331648 bytes
Redo Buffers                5341184 bytes

3.  Restore SPFILE

In situations requiring the recovery of your SPFILE or control file from autobackup, such as disaster recovery when you have lost all database files, you will need to use your DBID, or you will hit following errors:

RMAN> restore spfile from autobackup;

Starting restore at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=174 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:41:26
RMAN-06495: must explicitly specify DBID with SET DBID command

If you do not have a record of the DBID of your database, there are two places you can find it without opening your database.

• The DBID is used in forming the filename for the control file autobackup. Locate that file and check DBID (IIIIIIIIII) from filename c-IIIIIIIIII-YYYYMMDD-QQ.
  
• If you have any text files that preserve the output from an RMAN session, the DBID is displayed by the RMAN client when it starts up and connects to your database.

 RMAN> set dbid=1992057407;

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150701
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150630
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150629
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150628
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150627
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150626
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150625
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:48:53
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACKUP\20 15_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3047720 bytes
Variable Size               1493176024 bytes
Database Buffers             637534208 bytes
Redo Buffers                  13725696 bytes


4. Restore CONTROLFILES



RMAN> restore controlfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACK UP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

Starting restore at 01-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:55:26
ORA-19870: error while restoring backup piece E:\APP\ORACLE12C\FAST_RECOVERY_ARE
A\CDB\AUTOBACKUP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP
ORA-19504: failed to create file "E:\APP\ORACLE12C\ORADATA\CDB\CONTROL01.CTL"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

NOTE: Create necessary folders before restoring controlfiles


RMAN> restore controlfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACK UP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

Starting restore at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\APP\ORACLE12C\ORADATA\CDB\CONTROL01.CTL
output file name=E:\APP\ORACLE12C\FAST_RECOVERY_AREA\CDB\CONTROL02.CTL
Finished restore at 01-JUL-15

RMAN> alter database mount;

Statement processed

5. Check backups catalogued in controlfiles

RMAN> list backup summary;

6. Restore database (folders of datafiles must be created prior restoring)

RMAN> restore database;

7. Recover database

RMAN> recover database;

8. Open database with resetlogs

RMAN> alter database open resetlogs;