Create a job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST_JOB',
job_type => 'EXECUTABLE',
job_action => '/u01/app/abc.sql',
repeat_interval => 'FREQ=MINUTELY',
enabled => TRUE
);
END;
/
Unlike DBMS_JOB you do not need to commit the job creation for it to be taken into account.
As a corollary, if you want to cancel it, you have to remove or disable it.
****** Remove a job
EXEC DBMS_SCHEDULER.DROP_JOB('TEST_JOB');
****** Run a job now
To force immediate job execution:
EXEC dbms_scheduler.run_job('myjob');
****** Change job attributes
Examples:
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE
('WEEKNIGHT_WINDOW', 'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');
END;
****** Enable / Disable a job
BEGIN
DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
DBMS_SCHEDULER.DISABLE('myjob');
END;
****** Monitoring jobs
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'TEST_JOB';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'TEST_JOB';
****** or checking from JOB owner schema
SELECT * FROM user_scheduler_jobs WHERE job_name = 'TEST_JOB';
SELECT * FROM user_scheduler_job_log WHERE job_name = 'TEST_JOB';
Wednesday, May 27, 2015
Thursday, May 14, 2015
FIND ARCHIVE GAP BETWEEN PRIMARY AND STANDBY
Finding Archive gap between PRIMARY and STANDBY
A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database. When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the log files in standby to resolve the gap.
Use below query on standby Db to see if there is any gap. This will be help full if u have logs shipped and not applied. But in situations when the listener is down or network disturbances. etc the logs wont be shipped so this query might not work.
Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
The primary thread max seq should match standby.
******************************************************************************
----- More Detailed info with max received seqno# & max applied seqno# included -----
******************************************************************************
select MAX_RECEIVED_SEQNO,MAX_APPLIED_SEQNO,(MAX_RECEIVED_SEQNO - MAX_APPLIED_SEQNO) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED_SEQNO,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED_SEQNO
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
Sample output :
SQL> select MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
MAX_RECEIVED MAX_APPLIED Difference
------------ ----------- ----------
26424 26419 5
*********************************************************************************
----- Simple query to just display difference-----
*********************************************************************************
select MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
Sample output :
SQL> select (MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
Difference
----------
3
How to Sync small gap betwen Primary and Standby :
Lets say there is a Gap between the DB's, try to sync them manually if gap is small. Follow below process :
COPY MISSING ARCHIVELOG FILE'S FROM PRIMARY TO STANDBY :
$ scp log_file_1234.arc oracle@standby:/log_location/log_file_1234.arc
NOW REGISTER LOGFILE IN STANDBY DB:
SQL> alter database register logfile ‘/log_location/log_file_n.arc';
logfile registered
Repeat the same process for all the log files which are missing at standby.In case the gap is huge You can use Rman backup to cover the gap.
A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database. When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the log files in standby to resolve the gap.
Use below query on standby Db to see if there is any gap. This will be help full if u have logs shipped and not applied. But in situations when the listener is down or network disturbances. etc the logs wont be shipped so this query might not work.
Run on Primary DB :
Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
The primary thread max seq should match standby.
Run on Standby DB:
******************************************************************************
----- More Detailed info with max received seqno# & max applied seqno# included -----
******************************************************************************
select MAX_RECEIVED_SEQNO,MAX_APPLIED_SEQNO,(MAX_RECEIVED_SEQNO - MAX_APPLIED_SEQNO) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED_SEQNO,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED_SEQNO
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
Sample output :
SQL> select MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
MAX_RECEIVED MAX_APPLIED Difference
------------ ----------- ----------
26424 26419 5
*********************************************************************************
----- Simple query to just display difference-----
*********************************************************************************
select MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
Sample output :
SQL> select (MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
APPLIED_THREAD#, MAX(APPLIED_SEQ#) as MAX_APPLIED
FROM
V$ARCHIVE_DEST_STATUS) ARCH ;
Difference
----------
3
How to Sync small gap betwen Primary and Standby :
Lets say there is a Gap between the DB's, try to sync them manually if gap is small. Follow below process :
COPY MISSING ARCHIVELOG FILE'S FROM PRIMARY TO STANDBY :
$ scp log_file_1234.arc oracle@standby:/log_location/log_file_1234.arc
NOW REGISTER LOGFILE IN STANDBY DB:
SQL> alter database register logfile ‘/log_location/log_file_n.arc';
logfile registered
Repeat the same process for all the log files which are missing at standby.In case the gap is huge You can use Rman backup to cover the gap.
Monday, May 4, 2015
Flashback Database setup in Oracle
Enable Flash back on :
Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected.
[oracle@Linux1 ~]$ sqlplus /"As sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Lets check if Flash back is enabled or not :
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
NO OPEN
Since it is not enabled. Lets turn flash back ON:
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
YES OPEN
Confirm the size of DB_RECOVERY_FILE_DEST_SIZE (Better keep it at least 30GB+ until it is Dev or Test environment)
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 20000M
I want to set it to 30GB:
SQL> alter system set db_recovery_file_dest_size=30G ;
System altered.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 30G
Lets verify the location of recovery area. This is where the flashback logs are stored:
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 30G
You can reset the location by simple command :
alter system set db_recovery_file_dest= '/u01/app/oracle/flash_recovery/oraflash';
Lets confirm the retention period :
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
You can Change the retention period :
sql > alter system set db_flashback_retention_target=2880; -- 2days
Note : This retention period is in minutes so 1440/60 = 24 hours.
If you have a standby Database configured:
Activating flashback logging on the standby database Flashback operations, such as activating or accessing restore points, can only be undertaken in the MOUNT stage of the database. An active recovery will also first have to be canceled.
Manual method First cancel the recovery on the standby database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Then activate Flashback:
ALTER DATABASE FLASHBACK ON;
Then restart the recovery:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Deactivating To deactivate flashback mode, use the command:
ALTER DATABASE FLASHBACK OFF;
Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected.
[oracle@Linux1 ~]$ sqlplus /"As sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Lets check if Flash back is enabled or not :
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
NO OPEN
Since it is not enabled. Lets turn flash back ON:
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
YES OPEN
Confirm the size of DB_RECOVERY_FILE_DEST_SIZE (Better keep it at least 30GB+ until it is Dev or Test environment)
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 20000M
I want to set it to 30GB:
SQL> alter system set db_recovery_file_dest_size=30G ;
System altered.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 30G
Lets verify the location of recovery area. This is where the flashback logs are stored:
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 30G
You can reset the location by simple command :
alter system set db_recovery_file_dest= '/u01/app/oracle/flash_recovery/oraflash';
Lets confirm the retention period :
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
You can Change the retention period :
sql > alter system set db_flashback_retention_target=2880; -- 2days
Note : This retention period is in minutes so 1440/60 = 24 hours.
If you have a standby Database configured:
Activating flashback logging on the standby database Flashback operations, such as activating or accessing restore points, can only be undertaken in the MOUNT stage of the database. An active recovery will also first have to be canceled.
Manual method First cancel the recovery on the standby database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Then activate Flashback:
ALTER DATABASE FLASHBACK ON;
Then restart the recovery:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Deactivating To deactivate flashback mode, use the command:
ALTER DATABASE FLASHBACK OFF;
Subscribe to:
Posts
(
Atom
)