Restore Missing Datafile In Standby Database Oracle 12c

 

Restore Missing Datafile In Standby Database Oracle 12c

In oracle 12c, if datafile is missing is standby database, then it can be restored easily from primary database using RESTORE DATAFILE FROM SERVICE command. This is one of the new feature of dataguard 12c . 

DEMO:

PRIMDB – Primary database

STYDB – Standby database

 

Check status of primary and standby database:

--PRIMARY

 

SQL> select DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE from v$database;

 

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

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

PRIMDB READ WRITE PRIMARY

-- STANDBY

SQL> select name,OPEN_MODE,DATABASE_ROLE from v$database;

 

NAME OPEN_MODE DATABASE_ROLE

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

STYDB READ ONLY WITH APPLY PHYSICAL STANDBY

 

Lets remove one file from standby database:[STYDB]:

 

SQL> select file_name from dba_data_files;

FILE_NAME

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

/archive/PRIMDB/PRIMDB/system01.dbf

/archive/PRIMDB/PRIMDB/sysaux01.dbf

/archive/PRIMDB/PRIMDB/undotbs01.dbf

/archive/PRIMDB/PRIMDB/users01.dbf

mv /archive/PRIMDB/PRIMDB/users01.dbf /archive/PRIMDB/PRIMDB/users01.dbf_bkp

-- Try to query any table

SQL> select file_name from dba_data_files;

select file_name from dba_data_files

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/archive/PRIMDB/PRIMDB/users01.dbf'

ORA-27041: unable to open file

SVR4 Error: 2: No such file or directory

Additional information: 3

CHECK FROM RMAN: 

RMAN> list failure;

using target database control file instead of recovery catalog

Database Role: PHYSICAL STANDBY

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

322 CRITICAL OPEN 18-OCT-16 One or more non-system datafiles are missing

SOLUTION:

Now follow below steps to restore the datafile from primary:

NOTE : All activities need to be done on standby database

1. CANCEL THE RECOVERY( STANDBY)

SQL> recover managed standby database cancel;

Media recovery complete.

2 . START STANDBY IN MOUNT STAGE( IF IN READ-ONLY MODE):

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.3935E+10 bytes

Fixed Size 6002112 bytes

Variable Size 4630514240 bytes

Database Buffers 9193914368 bytes

Redo Buffers 104153088 bytes

Database mounted.

3. RESTORE THE DATAFILE ( STANDBY)

Now connect to RMAN in standby and use restore command.

 

SYNTAX – RESTORE DATAFILE < FILE_ID> FROM SERVICE < PRIMARY DB SERVICE NAME >

rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 18 18:04:15 2016

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

connected to target database: STYDB (DBID=599956155, not open)

RMAN> restore datafile 4 from service PRIMDB;

Starting restore at 18-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=705 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service PRIMDB

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /archive/PRIMDB/PRIMDB/users01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 18-OCT-16

4. START THE RECOVERY ( STANDBY)

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

5. CANCEL RECOVERY AND START DATABASE IN READ ONLY:

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.3935E+10 bytes

Fixed Size 6002112 bytes

Variable Size 4630514240 bytes

Database Buffers 9193914368 bytes

Redo Buffers 104153088 bytes

Database mounted.

Database opened.

6 . START THE RECOVERY AGAIN:

 

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

Now all datafiles are online :

 

SQL> select file_name from dba_data_files;

FILE_NAME

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

/archive/PRIMDB/PRIMDB/system01.dbf

/archive/PRIMDB/PRIMDB/sysaux01.dbf

/archive/PRIMDB/PRIMDB/undotbs01.dbf

/archive/PRIMDB/PRIMDB/users01.dbf

 

 

 

 

New Features in Oracle Database 19c

General

• Multi-model partitioning with Hybrid partitioning allowing some partitions in the database and some as external partitions even in hdfs
• New ALTER SYSTEM statement clause FLUSH PASSWORDFILE_METADATA_CACHE
• Clear flashback logs periodically
• Flush Metadata Cache for Passwords
• Hybrid Partitioned Tables – to integrate internal partitions and external partitions into a single partition table. partitions to reside in both Oracle Database segments and in external files and sources
• Passwords removed from schema accounts (default accounts)

Automatic Indexing

• Automatic Indexing is one of the great feature included in 19c .It automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload.
• This feature improves database performance by managing indexes automatically in an Oracle Database.
• Oracle has provided new package DBMS_AUTO_INDEX for this

SQL Quarantine

• SQL statements that are terminated by Oracle Database Resource Manager due to their excessive consumption of CPU and I/O resources are automatically quarantined.
• The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.
• This feature protects an Oracle Database from performance degradation by preventing execution of SQL statements that excessively consume CPU and I/O resources.

Pluggable Databases

• ADDM Analysis at PDB Level
• Ability to Create a PDB by Cloning a Remote PDB Using DBCA in Silent
• Mode Ability to relocate a PDB to another CDB Using DBCA in Silent Mode
• Creation Duplicate of an Oracle Database, CreateDuplicateDB command, in DBCA Silent Mode

Data Pump

• Oracle Data Pump Allows Tablespaces to Stay Read-Only During TTS Import
• Oracle Data Pump Test Mode for Transportable Tablespaces (TTS)
• Oracle Data Pump Support for Resource Usage Limitations – new parameter MAX_DATAPUMP_PARALLEL_PER_JOB
• Oracle Data Pump Loads Partitioned Table Data One Operation – GROUP_PARTITION_TABLE_DATA, a new value for the Import DATA_OPTIONS command line parameter
• Oracle Data Pump Import Supports More Object Store Credentials
• Oracle Data Pump Ability to Exclude ENCRYPTION Clause on Import – new transform parameter OMIT_ENCRYPTION_CLAUSE
• Oracle Data Pump Prevents Inadvertent Use of Protected Roles – new ENABLE_SECURE_ROLES parameter is available

 

 

Performance

• SQL Statement Diagnosability with SQL Advisor repair and SQL Test case for procedures
• Realtime statistics for DML Operations – Oracle Database 19c introduces real-time statistics, which extend online support to conventional DML statements
• Workload Capture and Replay in a PDB
• Statistics Collection on custom frequency automatically – From 19c onwards, High-frequency automatic optimizer statistics collection complements the standard statistics collection job
• Automatic Database Diagnostic Monitor (ADDM) Support for Pluggable Databases (PDBs)
• Automatic Indexing – Manage Auto Indexes with Advisory task like Monitor, Capture, Identify, Verify, Decide – to enable auto index mode is below, we have options “Implement”, “Report-Only”, “OFF”.

Data Guard

• DML redirect to standby/ADG for read-mostly applications
• Propagate Restore Points from Primary to Standby site
• Re-creation of broker configuration
• Simplified Data-guard broker parameter configurations
• Re-creation of broker configuration
• Finer Granularity Supplemental Logging for logical standby databases
• Flashback Standby Database when Primary Database is Flashed Back – Oracle 19c onwards, DBA can put the standby database in MOUNT mode without managed recovery and then flashback primary database; the standby will also be reverted, thus keeping it in sync with the primary
• Oracle Data Guard Multi-Instance Redo Apply Works with the In-Memory Column Store
• Finer Granularity Supplemental Logging for logical standby databases

New init parameters

• DATA_GUARD_MAX_IO_TIME
• DATA_GUARD_MAX_LONGIO_TIME
• MAX_DATAPUMP_JOBS_PER_PDB
• aws_pdb_autoflush_enabled (TRUE or FALSE)
• ADG_REDIRECT_DML (TRUE or FALSE)

New tables/views

• dba_sql_quarantine
• V$SQL_TESTCASES
• dba_auto_index_config
• DBA_REGISTRY_BACKPORTS

New packages

• dbms_auto_index_internal
• dbms_auto_index
• DBMS_SQLQ

Queries on OEM Repository to find some valuable information

 

Queries on OEM Repository to find some valuable information

Querying Oracle Management Repository (OEM repository) to dig out for a valuable information.  Here we can see very few examples.


How to find Available Target Types in OEM?

SQL> select distinct target_type,type_display_name 

from mgmt_targets order by 1;


TARGET_TYPE                    TYPE_DISPLAY_NAME
------------------------------ ----------------------------------------
cluster                        Cluster
composite                      Group
has                            Oracle High Availability Service
host                           Host
j2ee_application               Application Deployment
j2ee_application_cluster       Clustered Application Deployment
j2ee_application_domain        Domain Application Deployment
metadata_repository            Metadata Repository
oracle_apache                  Oracle HTTP Server
oracle_beacon                  Beacon
oracle_bi_publisher            Oracle BI Publisher
oracle_cloud                   Cloud
oracle_coherence               Oracle Coherence Cluster
oracle_coherence_cache         Oracle Coherence Cache
oracle_coherence_node          Oracle Coherence Node
oracle_database                Database Instance
oracle_dbsys                   Database System
oracle_em_service              EM Service
oracle_emd                     Agent
oracle_emrep                   OMS and Repository
oracle_emsvrs_sys              EM Servers System
oracle_home                    Oracle Home
oracle_listener                Listener
oracle_oms                     Oracle Management Service
oracle_oms_console             OMS Console
oracle_oms_pbs                 OMS Platform
oracle_pdb                     Pluggable Database
oracle_si_network              Systems Infrastructure Network
oracle_si_server_map           Systems Infrastructure Server
osm_cluster                    Cluster ASM
osm_instance                   Automatic Storage Management
osm_proxy                      Asm Proxy
rac_database                   Cluster Database
weblogic_cluster               Oracle WebLogic Cluster
weblogic_domain                Oracle WebLogic Domain
weblogic_j2eeserver            Oracle WebLogic Server
weblogic_nodemanager           Oracle WebLogic Node Manager


How to find all registered targets in OEM for a particular target type?

So I want to find all the targets registered in OEM repository whose target type is "rac_database"
in other words, basically I want to find all the rac databases registered in my OEM.

SQL> select target_name,target_type,target_guid 

from mgmt_targets where target_type='rac_database';

TARGET_NAME                    TARGET_TYPE
------------------------------ ------------------------------
HCMPRD                     rac_database
HCMSTG                     rac_database


How to find a particular target information registered in OEM?

SQL> select * from mgmt_targets where target_name='HCMPRD';


How to find Important Matrics related to a particular Target?

SQL> select * from mgmt$metric_daily 

where target_name = 'HCMPRD' and trunc(rollup_timestamp) >= trunc(sysdate-2);


How to find the daily growth of a database(last 7 days) from OEM repository?

SQL> select rollup_timestamp, average
from sysman.mgmt$metric_daily
where target_name = 'HCMPRD'
and column_label = 'Used Space(GB)'
and trunc(rollup_timestamp) >= trunc(sysdate-7)
order by rollup_timestamp;

ROLLUP_TIME    AVERAGE
---------   ----------
18-NOV-20    2794.44
19-NOV-20    2794.45
20-NOV-20    2794.50
21-NOV-20    2794.54
22-NOV-20    2794.65
23-NOV-20    2794.74

How to find database user accounts password is going to expire in next 15 days?

select distinct Target_name "Target_Database",host_name,username,profile,expiry_date
from sysman.mgmt$db_users
where expiry_date is not null  and username not like '%SYS%'
and trunc(expiry_date-sysdate) between 0 and 15;

Target_Database              HOST_NAME               USERNAME        PROFILE      EXPIRY_DATE
-------------------- ----------------------------- ----------- ------------  ------------
HCMDE2              hcmdev2.example.com     U_KMPSMST        SECURE       10-DEC-20
HCMDE2              hcmde2.example.com      U_SEBUAU         SECURE       26-NOV-20
HCMST1              hcmtst1.example.com     U_VANSHUI        SECURE       26-NOV-20
HCMST1              hcmtst1.example.com     U_SDHAGAY        SECURE       10-DEC-20


How to find perticular file system usage details from all target hosts:

with FSsize as
(select rollup_timestamp, lower(target_name) hostname, round((average/1024),0) size_gb , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
  and rollup_timestamp > trunc(sysdate-2)
  and metric_column = 'size'),
FSAvailable as
(select rollup_timestamp, lower(target_name) hostname, round((average/1024),0) size_gb , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
  and rollup_timestamp > trunc(sysdate-2)
  and metric_column = 'available'),
FSpctAvailable as
(select rollup_timestamp, lower(target_name) hostname, round(average,2) pctAvailable , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
  and rollup_timestamp > trunc(sysdate-2)
  and metric_column = 'pctAvailable'
)
select
   a.rollup_timestamp ts#
   , a.hostname
   , a.filesystem "Mounted on"
   , a.size_gb "FS_Size_GB"
   , (a.size_gb-b.size_gb) "FS_Used_GB"
   , b.size_gb "FS_Available_GB"
   , c.pctavailable "Fs_Free_%"
   , 100-c.pctavailable "Fs_Used_%"
from FSsize a, FSAvailable b, FSpctAvailable c
where c.hostname = a.hostname and c.hostname = b.hostname
and c.rollup_timestamp = a.rollup_timestamp and c.rollup_timestamp = b.rollup_timestamp
and c.filesystem = a.filesystem and c.filesystem = b.filesystem
and c.filesystem = '/u01';


How to find CPU Utilization of one host for last 24 hours?

SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a 

WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'host'
AND metric_group_name = 'Load'
AND metric_column_name = 'cpuUtil'
AND a.entity_name like 'oratesthost.example.com' order by a.collection_time;

COLLECTION_TIME          VALUE
---------------------- ----------
24-NOV-2020 16:57:06      4.149
24-NOV-2020 17:02:06      4.317
24-NOV-2020 17:07:06      4.003
24-NOV-2020 17:12:06      3.093
24-NOV-2020 17:17:06      2.897
24-NOV-2020 17:22:06      3.117
24-NOV-2020 17:27:06      2.782
24-NOV-2020 17:32:06      2.546
24-NOV-2020 17:37:06      2.061


How to find Memory Utilization of one host for last 24 hours?

SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'host'
AND metric_group_name = 'Load'
AND metric_column_name = 'memUsedPct'
AND a.entity_name like 'oratesthost.example.com' order by a.collection_time;

COLLECTION_TIME           VALUE
-------------------- ----------
...
24-NOV-2020 23:52:06     27.879
24-NOV-2020 23:57:06     27.841
25-NOV-2020 00:02:06     27.865
25-NOV-2020 00:07:06     27.874
25-NOV-2020 00:12:06     27.877
25-NOV-2020 00:17:06     27.878
25-NOV-2020 00:22:06     27.881
25-NOV-2020 00:27:06     27.876
25-NOV-2020 00:32:06     27.877
..

How to find Requests Per Minute for any java application?

SELECT a.collection_time, a.value  
FROM sysman.gc_metric_values a WHERE  TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'weblogic_j2eeserver'
AND metric_group_name = 'server_servlet_jsp'
AND metric_column_name = 'service.throughput'
AND a.entity_name like '%/TST_WEB_webdomain/testwebdomain/hcmapp1%' order by a.collection_time;

COLLECTION_TIME           VALUE
-------------------- ----------
24-NOV-2020 16:37:03    707.332
24-NOV-2020 16:52:03    468.336
24-NOV-2020 17:07:03    560.999
24-NOV-2020 17:22:03    421.933
24-NOV-2020 17:37:03    385.066
...


How to find Archive log summary for all Oracle databases from oem?

select distinct database_name "DATABASE_NAME",instance_name "INSTANCE_NAME",log_mode "LOG_MODE",host_name "HOST_NAME"
from mgmt$db_dbninstanceinfo order by LOG_MODE;

DATABASE_NAME        INSTANCE_NAME    LOG_MODE         HOST_NAME
-------------------- ---------------- ------------ --------------------------------
EBSPRD                 EBSPRD             ARCHIVELOG   oraprdhost.example.com
EBSVAL                 EBSVAL             ARCHIVELOG   oravalhost.example.com
HCMPRD                 HCMPRD             ARCHIVELOG   oraprdhost.example.com
....

How to find Backout information details?

select blackout_name "BLACKOUT_NAME",created_by "CREATED_BY",start_time "START_TIME",target_name "TARGET_NAME",
target_type "TARGET_TYPE",status "STATUS"
from SYSMAN.MGMT$BLACKOUT_HISTORY where STATUS in('Partial Blackout','Started');

Index Fragmentation Query

 set termout off verify off

define owner='DEMO'         -- table owner

define table='DEMOTABLE'    -- table name

define index='DEMOINDEX'    -- index name

define buckets=10           -- number of buckets

define sample=100           -- 100% scans all the index

column "free" format A5

variable c refcursor;

declare

 o all_indexes.owner%TYPE:='&owner';

 t all_indexes.table_name%TYPE:='&table';

 i all_indexes.table_name%TYPE:='&index';

 oid all_objects.object_id%TYPE;

 hsz varchar2(2000);

 n number:=&buckets;

 p number:=&sample;

 s varchar2(2000):='';

 k_min varchar2(2000);

 k_max varchar2(2000);

 k_lst varchar2(2000);

 k_nul varchar2(2000);

 k_vsz varchar2(2000);

 p_sam varchar2(2000):='';

 cursor cols is select i.column_name,i.column_position,case when data_type in ('VARCHAR2','RAW') then 3 else 1 end length_bytes

  from dba_ind_columns i join dba_tab_columns t 

  on (t.owner=i.table_owner and t.table_name=i.table_name and t.column_name=i.column_name)

  where i.table_owner=o and i.table_name=t and i.index_name=i order by column_position;

 procedure add(l in varchar2,i number default 0) is begin s:=s||chr(10)||rpad(' ',i)||l; end;

begin

 select object_id into oid from dba_objects where object_type='INDEX' and owner=o and object_name=i;

 /* Note:10640.1: block header size = fixed header (113 bytes) + variable transaction header (23*initrans) */

 select nvl(to_char(block_size - 113 - ini_trans*23),'null') header_size into hsz 

  from dba_indexes left outer join dba_tablespaces using (tablespace_name) where owner=o and index_name=i;

 for c in cols loop

  if ( c.column_position > 1 ) then k_lst:=k_lst||',' ; k_min:=k_min||',';k_max:=k_max||','; k_nul:=k_nul||' and ' ; k_vsz:=k_vsz||'+' ; end if;

  k_lst:=k_lst||c.column_name;

  k_nul:=k_nul||c.column_name|| ' is not null';

  k_min:=k_min||'min('||c.column_name||') '||c.column_name;

  k_max:=k_max||'max('||c.column_name||') '||c.column_name;

  k_vsz:=k_vsz||'nvl(vsize('||c.column_name||'),1)+'||c.length_bytes;

 end loop;

 if p != 100 then p_sam:='sample block('||p||')'; end if;

 add('with leaf_blocks as (',0);

 add('select /* cursor_sharing_exact dynamic_sampling(0) no_monitoring',1);

 add(' no_expand index_ffs('||t||','||i||') noparallel_index('||t||','||i||') */',10);

 add(k_min||','||1/(p/100)||'*count(rowid) num_rows',1);

 add(','||1/(p/100)||'*sum(1+vsize(rowid)+'||k_vsz||') vsize',1);

 add('from '||o||'.'||t||' '||p_sam||' '||t,1);

 add('where '||k_nul,1);

 add('group by sys_op_lbid('||oid||',''L'',rowid)',1);

 add('),keys as (',0);

 add('select ntile('||n||') over (order by '||k_lst||') bucket,',1);

 add(k_min||',',2);

 add('count(*) leaf_blocks, count(*)*'||hsz||' tsize,',2);

 add('sum(num_rows) num_rows,sum(vsize) vsize',2);

 add('from leaf_blocks group by '||k_lst,1);

 add(')',0);

 add('select '||k_min||',''->'' "->",'||k_max||',round(sum(num_rows)/sum(leaf_blocks)) "rows/block"',0);

 add(',round(sum(vsize)/sum(leaf_blocks)) "bytes/block",',1);

 add('case when sum(vsize)<=sum(tsize) then 100*round(1- sum(vsize) / (sum(tsize)),2) else null end "%free space",',1);

 add(' sum(leaf_blocks) "blocks",');

 --add('case when sum(vsize)<=sum(tsize)/2 then substr(rpad(''o'',5*round(1- sum(vsize) / (sum(tsize)),2),''o''),1,5) end "free"',1);

 add('substr(rpad(''o'',5*round(1- sum(vsize) / (sum(tsize)),2),''o''),1,5) "free"',1);

 add('from keys group by bucket order by bucket',0);

 begin open :c for s ; exception when others then dbms_output.put_line(s); raise; end ;

 dbms_output.put_line(s);

end;

/

set termout on 

print c