Tag Archives: check

FORCE LOGGING Mode in Oracle

FORCE LOGGING Mode in Oracle

In Force logging mode Oracle database must write the redo records even when NOLOGGING is used with DDL Statements.
It will force the write of REDO records even when no-logging is specified.

If we write the NOLOGGING option with DDLs command then our database will not generate redo for that DDLs but in case of Dataguard or media recovery has negative impact of it. So before implement Dataguard or standby server we need to enable the FORCE LOGGING mode of the Oracle Database.
So every changes should be recorded and updated in standby server while syncing.

Force Logging is enable at tablespace and database level.

Note:
–The FORCE LOGGING mode is a persistent attribute if the database is shut down and restarted, it remains in the same logging mode as configured.
–Re-create the control file, the database is not restarted in the FORCE LOGGING mode unless you specify the FORCE LOGGING clause in the CREATE CONTROL FILE statement.

Check database force logging

select force_logging from v$database;

Enable the Database force logging

ALTER DATABASE FORCE LOGGING;

Disable the Database force logging

ALTER DATABASE NO FORCE LOGGING;

Check tablespace force logging

select force_logging from dba_tablespaces;

Enable tablespace force logging

ALTER TABLESPACE FORCE LOGGING;

Disable tablespace force logging

ALTER TABLESPACE NO FORCE LOGGING;

Advertisements

Manage Sequence in Oracle

Managing Sequence in Oracle

Sequence is the oracle database object used for generating the unique sequence number.
It is may or may not be used as primary key.

Create Sequence

CREATE SEQUENCE emp_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

Note:
Start with: starting value of the Sequence it is anything
CACHE clause: preallocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster.
Note: In case of CACHE enabled, the database might skip sequence numbers.
For example, when an instance abnormally shuts down sequence numbers that have been cached are lost.

Alter Sequence
Alter a sequence to change any of the parameters that define it while creating.

ALTER SEQUENCE emp_seq
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;

Use of Sequence with NEXTVAL or CURRVAL
CURRVAL: current sequence value of your session
NEXTVAL: generate and use a sequence number
Note: Each time you use nextval it always show the next value as defined by INCREENT Clause in Sequence

--Check the current value of SEQUENCE
select emp_seq.CURRVAL from dual;

--Insert current value of Sequence
INSERT INTO Employee (id, empid, name)
VALUES (emp_seq.CURRVAL, 20321, 'RAM');

-- Generate next value from Sequence
SELECT emp_seq.NEXTVAL FROM dual;

-- Insert next value in Sequence
INSERT INTO Employee (id, empid, name)
VALUES (emp_seq.NEXTVAL, 10324, 'ANIL');

Check the Sequence Details

select * from DBA_SEQUENCES;

Making a Sequence Scalable
In Sequence Scalable number 6 digit is added in front of the Sequence number.
scalable sequence number = 6 digit scalable sequence offset number || normal sequence number

6 digit scalable sequence offset number = 3 digit instance offset number || 3 digit session offset number.

SYNTAX:

CREATE | ALTER SEQUENCE sequence_name
...
SCALE [EXTEND | NOEXTEND] | NOSCALE

In this you have two options:
EXTENDED:
In Extended it will choose the sequence number as 6 digit of instance offset and y digit of MAXVALUE digit.
If MAXVALUE defined in sequence creating time is 100 then 3 digit is choose as Y digit.

6 digit scalable sequence offset number || 001
6 digit scalable sequence offset number || 002
6 digit scalable sequence offset number || 003

6 digit scalable sequence offset number || 100

NOEXTENDED:
In No Extended, the number of scalable sequence digits cannot exceed the number of digits specified in the MAXVALUE clause.
Example, it MAXVALUE is 1000000 7 digit then Y digit has only 1 digit left because 6 digit is already taken by instance offset

6 digit scalable sequence offset number || 1
6 digit scalable sequence offset number || 2
6 digit scalable sequence offset number || 3

6 digit scalable sequence offset number || 9

When you tried to add 10 at last then it will generate the following error:

ORA-64603: NEXTVAL cannot be instantiated for SQ. Widen the sequence by 1 digits
or alter sequence with SCALE EXTEND.

Dropping Sequences

DROP SEQUENCE emp_seq;

SQL Plan Management

SQL Plan Management

SQL Plan Management provide the platform to control the execution plan usability for oracle database. With help of SQL Plan management , You can have ability to manual interpretation to choose the best plan for you.

Create SQL plan baselines for SQL queries means accepted execution plans for all statements. The SQL plan baselines are stored in a plan history in the SYSAUX tablespace.

In Oracle database 12c the evolution job is done by an automated job as an advisor task called SYS_AUTO_SPM_EVOLVE_TASK, which is triggered by the Job “sql tuning advisor” present in auto task list table. If you can disable the job, it automatically stop the evolution task for sql baseline.

Check the automatic job status:
SELECT * FROM dba_autotask_task;

Automatic capturing of SQL Plan
Automatic capture plan enabled for SQL queries by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.(By default is false)
It’s start capturing the execution plan for SQL queries in SQL baseline. First plan is always has accepted state.

Note: Using SQL Plan Baseline,Parameter OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.(By default is true)

Check the SQL Plan baselines having SQL plan:
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines;

Manually capturing of SQL Plan
We have SCOTT schema as default provided by Oracle. We proceed with an example:

1. Checked the SQL Plan Baseline table is empty or using any plan by your oracle system.

SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines;
no rows selected

2. Execute following query which will proceed for FULL Table Scan

SQL> select * from scott.emp where ename='SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

3. For manual load the SQL plan, Find the SQL ID from V$SQL view.

SELECT sql_id,sql_text FROM v$sql WHERE sql_text LIKE '%select * from scott.emp%';

SQL_ID        SQL_TEXT
------------- -------------------------------------------
b4xzjkzz30q5j select * from scott.emp where ename='SCOTT'

4. Load the Plan manually in SQL baseline with help of package:

SET SERVEROUTPUT ON
DECLARE
l_plans_captured PLS_INTEGER;
BEGIN
l_plans_captured := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'b4xzjkzz30q5j');DBMS_OUTPUT.put_line('Plans captured: ' || l_plans_captured);
END;
/

5. Check again the SQL baselines table for SQL plan

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%'

SQL_HANDLE             PLAN_NAME                      ENA ACC
--------------------- ------------------------------ --- ---
SQL_e523596056c54c6e   SQL_PLAN_fa8utc1bcam3fd8a279cc YES YES

6. Created the index on ename column and checked the execution plan again

Create index scott.emp_name_idx on scott.emp(ename);

set autotrace traceonly
select * from scott.emp where ename=’SCOTT’;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

7. Full scan is used in query,On Check SQL plan baseline table.One more plan but not in accepted state.

select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselinesSELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%emp%';

SQL_HANDLE             PLAN_NAME                      ENA ACC
---------------------- ------------------------------ --- ---
SQL_e523596056c54c6e   SQL_PLAN_fa8utc1bcam3f38fa3838 NO  NO 
SQL_e523596056c54c6e   SQL_PLAN_fa8utc1bcam3fd8a279cc YES NO

 

8. Manually Evolve the SQL Plan in 11g/12c
In 11g for envolve the plan:

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_e523596056c54c6e')
FROM dual;

In 12c envolve the plan:

Step 1: Create task
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_e523596056c54c6e');
DBMS_OUTPUT.put_line('Task Name: ' || l_return);
END;
/
Task Name: TASK_1644

Step 2: Evolve task
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1644’);
DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);
END;
/
Execution Name: EXEC_1591

Step 3: Generate the report of task generated above.

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1644’, execution_name => ‘EXEC_1591’) AS output FROM dual;

OUTPUT
--------------------------------------------------------------------------------GENERAL INFORMATION SECTION
--------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name            : TASK_1644
Task Owner           : SYS
Execution Name       : EXEC_1591
Execution Type       : SPM EVOLVE
Scope                : COMPREHENSIVE
Status               : COMPLETED
Started              : 01/24/2017 00:32:24
Finished             : 01/24/2017 00:32:25
Last Updated         : 01/24/2017 00:32:25
Global Time Limit    : 2147483646
Per-Plan Time Limit  : UNUSED
Number of Errors     : 0
--------------------------------------------------------------------------------
SUMMARY SECTION
--------------------------------------------------------------------------------
  Number of plans processed  : 1
  Number of findings         : 1
  Number of recommendations  : 1
  Number of errors           : 0
--------------------------------------------------------------------------------
DETAILS SECTION
--------------------------------------------------------------------------------
 Object ID          : 2
 Test Plan Name     : SQL_PLAN_fa8utc1bcam3f38fa3838
 Base Plan Name     : SQL_PLAN_fa8utc1bcam3fd8a279cc
 SQL Handle         : SQL_e523596056c54c6e
 Parsing Schema     : SYS
 Test Plan Creator  : SYS
 SQL Text           : select * from scott.emp where ename='SCOTT'
Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan
                    ----------------------------  ------------------
 Elapsed Time (s):  .000009                       .000007
 CPU Time (s):      0                             0
 Buffer Gets:       0                             0
 Optimizer Cost:    3                             2
 Disk Reads:        0                             0
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        10                            10

FINDINGS SECTION
--------------------------------------------------------------------------------
Findings (1):
-----------------------------
 1. The plan was verified in 0.04600 seconds. It passed the benefit criterion
    because its verified performance was 3.50000 times better than that of the
    baseline plan.

Recommendation:
-----------------------------
 Consider accepting the plan. Execute
 dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1644', object_id => 2,
 task_owner => 'SYS');

EXPLAIN PLANS SECTION
--------------------------------------------------------------------------------
Baseline Plan
-----------------------------
 Plan Id          : 101
 Plan Hash Value  : 3634526668

---------------------------------------------------------------------
| Id  | Operation           | Name | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    1 |    38 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | EMP  |    1 |    38 |    3 | 00:00:01 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ENAME"='SCOTT')

Test Plan
-----------------------------
 Plan Id          : 102
 Plan Hash Value  : 955922488
--------------------------------------------------------------------------------
| Id | Operation                            | Name         | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |              |    1 |    38 |    2 | 00:00:01 |
|  1 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |    1 |    38 |    2 | 00:00:01 |
| *2 |    INDEX RANGE SCAN                  | EMP_NAME_IDX |    1 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ENAME"='SCOTT')
--------------------------------------------------------------------------------

Step 4: Accept the plan in oracle 12c
exec dbms_spm.accept_sql_plan_baseline(task_name => ‘TASK_1644’, object_id => 2, task_owner => ‘SYS’);
OR
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_21’);
DBMS_OUTPUT.put_line(‘Plans Accepted:’ || l_return);
END;
/

9. Check the status in sql plan baselines view, it show execution plan in accepted state

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines ;

SQL_HANDLE                PLAN_NAME                           ENA ACC
------------------------- ----------------------------------- --- ---
SQL_e523596056c54c6e      SQL_PLAN_fa8utc1bcam3f38fa3838      YES YES
SQL_e523596056c54c6e      SQL_PLAN_fa8utc1bcam3fd8a279cc      YES YES

10.Test the SQL Queries above used and found index is used. Now both the plan is in accepted state.

select * from scott.emp where ename=’SCOTT’;

Execution Plan
----------------------------------------------------------
Plan hash value: 2092603121
-----------------------------------------------------------------------------
| Id | Operation                         | Name       |Rows|Bytes |Cost(%CPU)
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT                  |            | 1  |   38 |    2 (0)|
|  1 |TABLE ACCESS BY INDEX ROWID BATCHED| EMP        | 1  |   38 |    2 (0)|
|*  2    INDEX RANGE SCAN                |EMP_NAME_IDX| 1  |      |    1 (0)|
-----------------------------------------------------------------------------

11. You can modified it by changing attribute of SQL plan baseline. Suppose you don’t want to use the new plan and switch to old plan.
You can modified the enabled parameter to No. So optimizer not able to use the new plan at all. you can alter attributes with ALTER_SQL_PLAN_BASELINE function.

enabled (YES/NO) : YES : plan is available for the optimizer to use.
fixed (YES/NO) : YES : Fixed plans got more priority than non-fixed plans.

Disable the latest plan as below:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_e523596056c54c6e',plan_name => 'SQL_PLAN_fa8utc1bcam3f38fa3838',
attribute_name => 'ENABLED', attribute_value => 'NO');
end;
/

12. Verify again the sql query, its not using index and switch to old plan.

SQL>
SQL>Select * from scott.emp where ename='SCOTT';

Execution Plan
--------------------------
Plan hash value: 3956160932
------------------------------------------------------------------
| Id | Operation         | Name| Rows | Bytes| Cost(%CPU)| Time  |
------------------------------------------------------------------
|  0| SELECT STATEMENT  |     |     1|    38|     3 (0)| 00:00:01|
|* 1|  TABLE ACCESS FULL| EMP |     1|    38|     3 (0)| 00:00:01|
------------------------------------------------------------------ 

Transfer the SQL plan in between two database/backup of sql plan before patch/upgrade.
1. Create a staging table for transfer the stats

BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT',
tablespace_name => 'USERS');
END;
/

2. Load the plan baseline to staging table

SET SERVEROUTPUT ON
DECLARE
var_plans_loaded PLS_INTEGER;
BEGIN
var_plans_loaded := DBMS_SPM.pack_stgtab_baseline(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT');DBMS_OUTPUT.put_line('Plans loaded: ' || var_plans_loaded);
END;
/
Plans loaded: 2

3. Take an export/EXPDP backup of staging table

4. Move to another server

5. Import/IMPDP the staging table

6. Unload the execution plan to database server:

SET SERVEROUTPUT ON
DECLARE
l_plans_unloaded PLS_INTEGER;
BEGIN
l_plans_unloaded := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'scott_Plan_backup',
table_owner => 'SCOTT');DBMS_OUTPUT.put_line('Plans Unloaded: ' || l_plans_unloaded);
END;
/
Plans Unloaded: 2

Drop the SQL plan
Specify only the sql_handle, it will drop all the plan related to SQLhandle.

SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_e523596056c54c6e');
DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/

OR
Drop the specific plan present in SQL handle

SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => NULL,
plan_name => 'SQL_PLAN_fa8utc1bcam3f38fa3838');
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

For displaying the SQL plan baselines plan

SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( sql_handle=>'SYS_SQL_209d10fabbedc741',format=>'basic'));

For verification purpose
Check whether oracle is using SQL baseline by joining v$SQL view:

Select s.sql_text, b.plan_name, b.origin, b.accepted
From dba_sql_plan_baselines b, v$sql s
Where s.exact_matching_signature = b.signature
And s.SQL_PLAN_BASELINE = b.plan_name;

Reference from
https://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95121
https://oracle-base.com/articles/12c/adaptive-sql-plan-management-12cr1
https://oracle-base.com/articles/11g/sql-plan-management-11gr1

Create and delete Histogram in Oracle

Histogram in Oracle

Histogram used by SQL Query optimizer to make better execution plan.

A Histogram is a column statistic that provides more detailed information about the data distribution in a table column.

By Default optimizer use the uniform distribution of rows across the distinct value of columns.

Suppose if table has 3000 rows and one column Country has India has 2500 rows, USA has 300 rows and England has 200 rows. By default without histogram, Optimizer considers 1000 rows each for India, USA and England. To make and provide more relevant data to optimizer for choose better plan for SQL Queries we use histogram.

Col column_name for a30
Select column_name , notes , histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

COLUMN_NAME     NOTES           HISTOGRAM
------------    -------------   -------------
OWNER                           NONE
Elapsed: 00:00:00.01


For Creation of histogram on column you need to gather the stats as follows

Gather Stats with histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','K1',OPTIONS=>'GATHER AUTO');

Select column_name , notes , histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

COLUMN_NAME      NOTES          HISTOGRAM
------------     -----------    -------------
OWNER                           FREQUENCY

 
Delete the Histogram:

Exec  dbms_stats.delete_column_stats(ownname=>'',
tabname=>'K1',colname=>'OWNER',col_stat_type=>'HISTOGRAM')

Select column_name,notes,histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

COLUMN_NAME        NOTES       HISTOGRAM
---------------    ---------   -------------
OWNER                          NONE

Secure password in Scripts using Wallet feature of Oracle

Secure password in Scripts using Wallet feature of Oracle

1. Configure the Wallet and set the password for wallet:

Mkstore –wrl "wallet location" -create

Example:

Mkstore  -wrl E:\oracle\wallet –create

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:

 2. Secure the password of user which you want to use in script in wallet.

mkstore –wrl "wallet location" -createCredential

db_connection_string is an entry on your tnsnames.ora or any service name to connect to the desired database.
 
Example:

mkstore –wrl     E:\oracle\wallet  -createCredential  prod   username   password

Note:prod is an entry on your tnsnames.ora file

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -createCredential prod sys
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
Create credential oracle.security.client.connect_string1

 3. Adding the following lines in sqlnet.ora for mentioned the location of wallet.

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
(SOURCE=
(METHOD= FILE)
(METHOD_DATA = (DIRECTORY=E:\oracle\wallet)
))

 4. You can list the credential you set in wallet.

Mkstore –wrl E:\oracle\wallet -listcredential

Example:

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -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:   l
List credential (index: connect_string username)
1: prod sys

5. Delete the added credential from the wallet

Mkstore –wrl "wallet location" –deleteCredential

Example:

mkstore -wrl E:\oracle\wallet -deleteCredential prod

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -deleteCredential prod
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

 6. Delete the wallet created for use

mkstore -wrl “wallet location”  -delete

Example

mkstore -wrl E:\oracle\wallet –delete

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -delete
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:

 7. Check wallet is deleted or not

mkstore -wrl E:\oracle\wallet –list
Use the wallet user in scripts as follows
Sqlplus /@prod

Script for Complete Status of Data Guard

Script for Complete information about the Data Guard

From the following script you will get the complete details of dataguard including db broker parameter, sync status between two servers.

Status Script for Data guard as follows:

Script:

spool F:\dataguard.txt
set line 999 pages 999
col value for a90
col name for a50
col open_mode for a20
col database_role for a50
PROMPT ====================================
PROMPT ==GET DATA GUARD INFORMATION========
PROMPT ====================================
PROMPT

PROMPT Check archive mode, logging Mode, protection Mode,
PROMPT ==============================================================
PROMPT
col name for a10
col open_mode for a10
col databaes_role for a10
col log_mode for a15
col protection_mode for a25
select name,open_mode,database_role,log_mode,protection_mode from v$database;
PROMPT Check the value of parameters
PROMPT ==============================
PROMPT

col name for a26
col value for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management');

PROMPT Check the dbbroker service configured
PROMPT ===================================
PROMPT
select name,value from v$parameter where name in ('dg_broker_start','dg_broker_config_file1','dg_broker_config_file2');

PROMPT Check the archive gap
PROMPT ======================
PROMPT
col thread# for a20
col low_sequence# for a20
col high_sequence# for a20
select * from v$archive_gap;

PROMPT Check the standby manged process
PROMPT ================================
PROMPT
select process, client_process, sequence#, status from v$managed_standby;

PROMPT list the archive applied
PROMPT =========================
PROMPT
select sequence#, first_time, next_time, applied from v$archived_log;

PROMPT Check the archive or applied thread
PROMPT ====================================
PROMPT
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
spool off

Script for List the Drives with Size in Windows as Batch script

Script for list all the drives with size information in windows platform

Use steps as follows:

1. Save the below script as batch file with any name like diskinfo.bat

2. Go to the location of script placed:
 Cd D:\scripts

3. Open the command prompt windows and execute it with following command:
D:\scripts> diskinfo.bat localhost
 
4.  Output as follows:

Drive: C:
=========================
Total space: 266 GB
Free space : 221 GBDrive: D:
=========================
Total space: 482 GB
Free space : 482 GB

Script as follows:

@ECHO OFF
IF "%~1"=="" goto help
@SETLOCAL ENABLEEXTENSIONS
@SETLOCAL ENABLEDELAYEDEXPANSION

@FOR /F "skip=1 tokens=1" %%x IN ('"WMIC /node:"%1" LOGICALDISK GET Name " ') DO (
REM @ECHO %%x

@FOR /F "tokens=1-3" %%n IN ('"WMIC /node:"%1" LOGICALDISK GET Name,Size,FreeSpace | find /i "%%x""') DO ( @SET FreeBytes=%%n & @SET TotalBytes=%%p

SET TotalSpace=!TotalBytes:~0,-9!
SET FreeSpace=!FreeBytes:~0,-10!

SET /A TotalUsed=!TotalSpace! - !FreeSpace!

REM IF !TotalSpace! LSS 0 goto error

@echo.
@echo.
@echo Drive: %%x
@ECHO ===========================
@ECHO Total space: !TotalSpace! GB
@ECHO Free space : !FreeSpace! GB

REM @SET TotalSpace=
REM @SET FreeSpace=
REM @SET TotalUsed=
REM goto end
)
)
goto end
:error
echo.
echo *** Invalid server or drive specified ***
echo.
goto help

:help
echo.
echo diskfree.cmd
echo.
echo Queries remote server for free disk space.
echo Specify a MACHINENAME and a drive letter to be queried
echo.
echo Example:   diskfree.cmd MACHINENAME c:
echo.
goto end

:end