* when you have a resumable timeout set so that SQL waiting for tablespace resources can pause until that space becomes available.
I had this interesting problem the other day, and while the solution is reasonably obvious, it didn't occur to me immediately at the time.
You can detect resumable pauses with this statement...
select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;
This then shows one or more sessions where the resumable wait has cleared or an error is display showing a wait in progress. At my company we monitor for these waits extending to long and then report them to call-out.
In this case, as always, this is the error message shown. There were 2 sessions waiting on a large data warehouse.
ORA-01652: unable to extend temp segment by string in tablespace cma_ts1
Earlier in the day, I had got the same message and when I checked, the tablespace was indeed almost completely full. It was on ASM and the single file in the tablespace was 1.5Tb. Because it's a production database I didn't hang around and increased the datafile to 2Tb.
ALTER DATABASE DATAFILE '+DATA_DG/dgb_tsm1/datafile/cma_ts1.1145.851597989' AUTOEXTEND ON MAXSIZE 2000G
database datafile '+DATA_DG/DGB_TSM1/DATAFILE/CMA_TS1.1145.851597989' altered.
So with an additional 500Gb, I figured we'd be ok for some time. However 3 hours later I was called out again. Same error.
However now, only 91% of the data file was being used. This equates to 180Gb still free.
Of course what I didn't immediately spot was that just checking the size of the free space isn't enough. You must also consider the amount of space being requested by running processes.
This statement gives you the tablespace usage details.
select file_name, file_id, round(user_bytes/1024/1024,2) "Used(Mb)", round(user_bytes/1024/1024/1024,2) "Used(Gb)", status, autoextensible
from dba_data_files c
where tablespace_name = 'CMA_TS1' order by file_id desc;
And this the percent used. (There are other ways).
select tablespace_name, used_percent, tablespace_size, used_space, (tablespace_size - used_space) free
from dba_tablespace_usage_metrics
where tablespace_name = 'CMA_TS1';
Having obtained the space used figures, take a look at the amount of space currently being requested by the running processes.
select segment_name, bytes/1048576/1024 from dba_segments where tablespace_name='CMA_TS1'
order by 2 desc;
If the segment name is a number rather than an object name, then this represents those temporary segments we're looking for. In my case there were 2 (as expected), and these were sized at 337Gb and 240Gb. Clearly neither of these next extent requirements would fit into the free space available.
I could have added more diskspace, but the prudent thing to do was to locate the session owners and have a quiet word with them. Having done this and explained the realities of initial and next extents, we killed the processes and the scripts were rewritten.
HappyJohn
Sunday, October 19, 2014
Tuesday, September 16, 2014
ORA-30036 and no free UNDO space
We have a database with a large amount (10Gb) of redo that has never need to be increased since the database was built. One morning we find lots of sessions suspended because someone has taken all of the undo.
Firstly, I identified the session that was causing the problem.
SELECT a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC;
The result of this query showed one huge amount of undo held by a single session. In my case it was SID 177. So next I display details about the session.
SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and sid = 177;
I've not only displayed the sid and serial# I need to kill the session but also the SPID so I can kill it from unix if necessary.
I also located the SQL for that session and found it was a massive DELETE statement with no commits.
Having got agreement from the user that I could end the session, I killed it.
alter system kill session '177,13989' immediate;
Once the session was killed, I increased the size of the tablespace to allow some sessions to run while the roll back occurred.
This statement shows how much undo is still being used.
select status,
round(sum_bytes / (1024*1024), 0) as MB,
round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
select status, sum(bytes) sum_bytes
from dba_undo_extents
group by status
),
(
select sum(a.bytes) undo_size
from dba_tablespaces c
join v$tablespace b on b.name = c.tablespace_name
join v$datafile a on a.ts# = b.ts#
where c.contents = 'UNDO'
and c.status = 'ONLINE'
);
STATUS MB PERC
--------- ---------- ----------
ACTIVE 10276 96
EXPIRED 1 0
UNEXPIRED 396 4
I continued to monitor the recovery of the database thus.
select * from v$fast_start_transactions;
Note: UNDOBLOCKSDONE increases up to a fixed batch which is then removed from UNDOBLOCKSTOTAL.
Once UNDOBLOCKSTOTAL reached 0, the available bocks in undo went back to normal.
Once the recovery has completed, the above query returns this result.
STATUS MB PERC
--------- ---------- ----------
ACTIVE 81 1
EXPIRED 2 0
UNEXPIRED 10590 99
The quantity of unexpired block is now ok.
HappyJohn.
Firstly, I identified the session that was causing the problem.
SELECT a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC;
The result of this query showed one huge amount of undo held by a single session. In my case it was SID 177. So next I display details about the session.
SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and sid = 177;
I've not only displayed the sid and serial# I need to kill the session but also the SPID so I can kill it from unix if necessary.
I also located the SQL for that session and found it was a massive DELETE statement with no commits.
Having got agreement from the user that I could end the session, I killed it.
alter system kill session '177,13989' immediate;
Once the session was killed, I increased the size of the tablespace to allow some sessions to run while the roll back occurred.
This statement shows how much undo is still being used.
select status,
round(sum_bytes / (1024*1024), 0) as MB,
round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
select status, sum(bytes) sum_bytes
from dba_undo_extents
group by status
),
(
select sum(a.bytes) undo_size
from dba_tablespaces c
join v$tablespace b on b.name = c.tablespace_name
join v$datafile a on a.ts# = b.ts#
where c.contents = 'UNDO'
and c.status = 'ONLINE'
);
STATUS MB PERC
--------- ---------- ----------
ACTIVE 10276 96
EXPIRED 1 0
UNEXPIRED 396 4
I continued to monitor the recovery of the database thus.
select * from v$fast_start_transactions;
Note: UNDOBLOCKSDONE increases up to a fixed batch which is then removed from UNDOBLOCKSTOTAL.
Once UNDOBLOCKSTOTAL reached 0, the available bocks in undo went back to normal.
Once the recovery has completed, the above query returns this result.
STATUS MB PERC
--------- ---------- ----------
ACTIVE 81 1
EXPIRED 2 0
UNEXPIRED 10590 99
The quantity of unexpired block is now ok.
HappyJohn.
Friday, August 1, 2014
ADDM & Reports
The Automatic Database Diagnostic Monitor (ADDM) analyses data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken.
The ADDM analysis includes the following.
CPU load
Memory usage
I/O usage
Resource intensive SQL
Resource intensive PL/SQL and Java
RAC issues
Application issues
Database configuration issues
Concurrency issues
Object contention
Note:
The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microseconds. The parameter can be set using the following.
The ADDM analysis includes the following.
CPU load
Memory usage
I/O usage
Resource intensive SQL
Resource intensive PL/SQL and Java
RAC issues
Application issues
Database configuration issues
Concurrency issues
Object contention
Note:
The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microseconds. The parameter can be set using the following.
To activate:
alter system set statistics_level = typical;
alter system set statistics_level = all;
To De-active:
alter system set statistics_level = basic;
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus.
-- Windows
@C:\app\oracle\product\12.1.0\dbhome_2\rdbms\admin\addmrpt.sql
(the script asks for a range of snapshots (without a restart in between), adn the output report filename. The report is created in the local directory unless you state otherwise).
The basic SQL for this report is listed below, just with a few parameters and a spool slapped around it.
BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '455_478_AWR_SNAPSHOT',
task_desc => 'Advisor for snapshots 455 to 478.');
-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '455_478_AWR_SNAPSHOT',
parameter => 'START_SNAPSHOT',
value => 455);
DBMS_ADVISOR.set_task_parameter (
task_name => '455_478_AWR_SNAPSHOT',
parameter => 'END_SNAPSHOT',
value => 478);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name => '455_478_AWR_SNAPSHOT');
END;
/
-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('455_478_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24
The snapshots are controlled by dba_hist_wr_control. This seems to be hourly.
col snap_interval for a20
col retention for a20
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- -------------------- -------------------- ---------- ----------
3043802518 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 1
To change the collection settings (interval is in minutes, retention in seconds)
exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200);
To display snapshots
col begin_interval_time for a25
col end_interval_time for a25
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
Create a snapshot : exec dbms_workload_repository.create_snapshot;
Delete snapshots : exec dbms_workload_repository.drop_snapshot_range (low_snap_id=>455, high_snap_id=>478);
The report itself.....
-------------------------------
Analysis Period
---------------
AWR snapshot range from 455 to 478.
Time period starts at 31-JUL-14 11.04.16
Time period ends at 01-AUG-14 10.00.34
Analysis Target
---------------
Database 'JHCDB' with DB ID 3043802518.
Database version 12.1.0.1.0.
ADDM performed an analysis of instance jhcdb, numbered 1 and hosted at
Serverxxx
Activity During the Analysis Period
-----------------------------------
Total database time was 742 seconds.
The average number of active sessions was .01.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
--------------------- ------------------- ---------------
1 Top SQL Statements .01 | 61.11 2
2 "User I/O" wait Class 0 | 7.69 0
3 Hard Parse 0 | 4.3 0
4 Soft Parse 0 | 2.45 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .01 active sessions, 61.11% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 31.94% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"bj08huw9tc01y".
Related Object
SQL statement with SQL_ID bj08huw9tc01y.
select /*+ no_monitor */ CON_ID, OBJOID, CLSOID, RUNTIME, PRI,
JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select
...
DESC, OBJOID
Action
Use bigger fetch arrays while fetching results from the SELECT statement
with SQL_ID "bj08huw9tc01y".
Related Object
SQL statement with SQL_ID bj08huw9tc01y.
select /*+ no_monitor */ CON_ID, OBJOID, CLSOID, RUNTIME, PRI,
...
DESC, OBJOID
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "bj08huw9tc01y" was executed 60 times and had
an average elapsed time of 5.5 seconds.
Rationale
At least one execution of the statement ran in parallel.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 29.17% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"bj08huw9tc01y".
Related Object
SQL statement with SQL_ID bj08huw9tc01y.
select /*+ no_monitor */ CON_ID, OBJOID, CLSOID, RUNTIME, PRI,
JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select
...
and ik.logins = 'ALLOWED') order by RUNTIME, JOBTYPE, CLSOID, PRI, WT
DESC, OBJOID
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Finding 2: "User I/O" wait Class
Impact is 0 active sessions, 7.69% of total activity.
-----------------------------------------------------
Wait class "User I/O" was consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than expected.
The Oracle instance memory (SGA and PGA) was adequately sized.
No recommendations are available.
Finding 3: Hard Parse
Impact is 0 active sessions, 4.3% of total activity.
----------------------------------------------------
Hard parsing of SQL statements was consuming significant database time.
Hard parses due to cursor environment mismatch were not consuming significant
database time.
Hard parsing SQL statements that encountered parse errors was not consuming
significant database time.
Hard parses due to literal usage and cursor invalidation were not consuming
significant database time.
The Oracle instance memory (SGA and PGA) was adequately sized.
No recommendations are available.
Finding 4: Soft Parse
Impact is 0 active sessions, 2.45% of total activity.
-----------------------------------------------------
Soft parsing of SQL statements was consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.45% of total activity.
----------------------------------------------------------------
Action
Investigate application logic to keep open the frequently used cursors.
Note that cursors are closed by both cursor close calls and session
disconnects.
Recommendation 2: Database Configuration
Estimated benefit is 0 active sessions, 2.45% of total activity.
----------------------------------------------------------------
Action
Consider increasing the session cursor cache size by increasing the
value of parameter "session_cached_cursors".
Rationale
The value of parameter "session_cached_cursors" was "50" during the
analysis period.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The database's maintenance windows were active during 17% of the analysis
period.
Happyjohn
Thursday, July 31, 2014
Setting up Dataguard in 12c (inc active data guard)
See Document http://docs.oracle.com/database/121/SBYDB/create_ps.htm#SBYDB5017
NOTE: My Primary is jhcdb and my new standby will be jhstby
I think the hardest part of this is setting everything back if it goes wrong.
1. enable force logging on primary (jhcdb).
SQL> ALTER DATABASE FORCE LOGGING;
2. Set the primary to receive redo (this will alow the existing database to switch roles).
Make sure the file size matches existing redo logs (select members, bytes/1024/1024 from v$log;)
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('C:\app\oracle\oradata\jhcdb\slog01.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('C:\app\oracle\oradata\jhcdb\slog02.rdo') SIZE 50M;
3. Define additional init params for redo transport services on the existing primary
-- already set
DB_NAME=jhcdb
DB_UNIQUE_NAME=jhcdb
CONTROL_FILES='C:\APP\ORACLE\ORADATA\JHCDB\CONTROL01.CTL', 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHCDB\CONTROL02.CTL'
-- Require setting
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(jhcdb,jhstby)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jhcdb' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=jhstby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jhstby' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
-- Standby role parameters
alter system set FAL_SERVER=jhstby scope=spfile;
alter system set DB_FILE_NAME_CONVERT='\jhcdb\','\jhstby\' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='\jhcdb\','\jhstby\' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
4. Enable archiving, though this should already be done. But its worth at least shutting down
because you've likely changed the LOG_ARCHIVE_DEST_1
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
5. For the creation of the database the following steps are required and the book mark listed is from the oracle doc (see top).
reference below task database
Section 3.2.1 - Create a Backup Copy of the Primary Database Data Files Primary
Section 3.2.2 6 Create a Control File for the Standby Database Primary
Section 3.2.3 7,8 Create a Parameter File for the Standby Database Primary
Section 3.2.4 9 Copy Files from the Primary System to the Standby System Primary
Section 3.2.5 Set Up the Environment to Support the Standby Database Standby
Section 3.2.6 Start the Physical Standby Database Standby
Section 3.2.7 Verify the Physical Standby Database Is Performing Properly Standby
6. Create a backup copy of the control file. (jhcdb - Primary)
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\app\tmp\jhcdb.ctl';
7. Create a control file for the standby database
SQL> CREATE PFILE='c:\app\tmp\initjhstby.ora' FROM SPFILE;
8. Amend the necessary parameters to make the init file suitable for the new standby database
DB_NAME='jhcdb'
DB_UNIQUE_NAME='jhstby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jhcdb,jhstby)'
CONTROL_FILES='C:\APP\ORACLE\ORADATA\JHSTBY\CONTROL01.CTL', 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHSTBY\CONTROL02.CTL'
DB_FILE_NAME_CONVERT='\jhcdb\','\jhstby\'
LOG_FILE_NAME_CONVERT='\jhcdb\','\jhstby\'
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jhstby'
LOG_ARCHIVE_DEST_2='SERVICE=jhcdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jhcdb'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=jhcdb
Not sure what to do about the other parameters, I did change the audit dest.
9. Shutdown jhcdb and copy the files
SQL> shutdown immediate
create C:\app\oracle\audit\jhstby
copy C:\app\oracle\oradata\jhcdb to C:\app\oracle\oradata\jhstby
**** copy the orapwd file from jhcdb to jhstby (when I created one, the redo log transfer kept failing with ORA-01017 in the arc1 trace file).
set oracle_sid=jhstby
copy the control file create earlier to both locations.
run orapwd file=PWDjhstby.ora password=oracle entries=10
SQL-JHSTBY> STARTUP MOUNT
SQL-JHSTBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
10. Startup the primary
SQL> startup
SQL> select max(sequence#) from v$loghist;
11. Check
SQL-JHSTBY> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
SQL-JHCDB> SELECT MAX(SEQUENCE#) FROM V$LOGHIST;
12. Making it active data guard.
SQL-JHSTBY> recover managed standby database cancel;
SQL-JHSTBY> alter database open read only;
SQL-JHSTBY> recover managed standby database disconnect using current log file;
And hey presto, it's open in read only mode. Try this
On jhcdb, create table jh1 (adate date);
Then on JHSTBY, desc jh1 ..... And you should get n immediate result.
But remember active data guard is separately licenceable .
Happyjohn
NOTE: My Primary is jhcdb and my new standby will be jhstby
I think the hardest part of this is setting everything back if it goes wrong.
1. enable force logging on primary (jhcdb).
SQL> ALTER DATABASE FORCE LOGGING;
2. Set the primary to receive redo (this will alow the existing database to switch roles).
Make sure the file size matches existing redo logs (select members, bytes/1024/1024 from v$log;)
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('C:\app\oracle\oradata\jhcdb\slog01.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('C:\app\oracle\oradata\jhcdb\slog02.rdo') SIZE 50M;
3. Define additional init params for redo transport services on the existing primary
-- already set
DB_NAME=jhcdb
DB_UNIQUE_NAME=jhcdb
CONTROL_FILES='C:\APP\ORACLE\ORADATA\JHCDB\CONTROL01.CTL', 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHCDB\CONTROL02.CTL'
-- Require setting
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(jhcdb,jhstby)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jhcdb' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=jhstby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jhstby' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
-- Standby role parameters
alter system set FAL_SERVER=jhstby scope=spfile;
alter system set DB_FILE_NAME_CONVERT='\jhcdb\','\jhstby\' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='\jhcdb\','\jhstby\' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
4. Enable archiving, though this should already be done. But its worth at least shutting down
because you've likely changed the LOG_ARCHIVE_DEST_1
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
5. For the creation of the database the following steps are required and the book mark listed is from the oracle doc (see top).
reference below task database
Section 3.2.1 - Create a Backup Copy of the Primary Database Data Files Primary
Section 3.2.2 6 Create a Control File for the Standby Database Primary
Section 3.2.3 7,8 Create a Parameter File for the Standby Database Primary
Section 3.2.4 9 Copy Files from the Primary System to the Standby System Primary
Section 3.2.5 Set Up the Environment to Support the Standby Database Standby
Section 3.2.6 Start the Physical Standby Database Standby
Section 3.2.7 Verify the Physical Standby Database Is Performing Properly Standby
6. Create a backup copy of the control file. (jhcdb - Primary)
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\app\tmp\jhcdb.ctl';
7. Create a control file for the standby database
SQL> CREATE PFILE='c:\app\tmp\initjhstby.ora' FROM SPFILE;
8. Amend the necessary parameters to make the init file suitable for the new standby database
DB_NAME='jhcdb'
DB_UNIQUE_NAME='jhstby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jhcdb,jhstby)'
CONTROL_FILES='C:\APP\ORACLE\ORADATA\JHSTBY\CONTROL01.CTL', 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHSTBY\CONTROL02.CTL'
DB_FILE_NAME_CONVERT='\jhcdb\','\jhstby\'
LOG_FILE_NAME_CONVERT='\jhcdb\','\jhstby\'
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jhstby'
LOG_ARCHIVE_DEST_2='SERVICE=jhcdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jhcdb'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=jhcdb
Not sure what to do about the other parameters, I did change the audit dest.
9. Shutdown jhcdb and copy the files
SQL> shutdown immediate
create C:\app\oracle\audit\jhstby
copy C:\app\oracle\oradata\jhcdb to C:\app\oracle\oradata\jhstby
**** copy the orapwd file from jhcdb to jhstby (when I created one, the redo log transfer kept failing with ORA-01017 in the arc1 trace file).
set oracle_sid=jhstby
copy the control file create earlier to both locations.
run orapwd file=PWDjhstby.ora password=oracle entries=10
SQL-JHSTBY> STARTUP MOUNT
SQL-JHSTBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
10. Startup the primary
SQL> startup
SQL> select max(sequence#) from v$loghist;
11. Check
SQL-JHSTBY> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
SQL-JHCDB> SELECT MAX(SEQUENCE#) FROM V$LOGHIST;
12. Making it active data guard.
SQL-JHSTBY> recover managed standby database cancel;
SQL-JHSTBY> alter database open read only;
SQL-JHSTBY> recover managed standby database disconnect using current log file;
And hey presto, it's open in read only mode. Try this
On jhcdb, create table jh1 (adate date);
Then on JHSTBY, desc jh1 ..... And you should get n immediate result.
But remember active data guard is separately licenceable .
Happyjohn
Wednesday, July 30, 2014
RMAN - Automatic backup in windows 7
1. Create a simple backup script
In C:\app\rman_scripts create a file backup1.bat containing this...
cd C:\app\rman_scripts
set oracle_home=C:\app\oracle\product\12.1.0\dbhome_2
set oracle_sid=jhcdb
rman target / @backup1.bck log log\log1.log
set t=%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%.%time:~3,2%.%time:~6,2%
set t=%t: =0%
ren log\log1.log log-%t%.log
And create a log sub-directory in c:\app\rman_scripts
Then create a another file called backup1.bck containing this...
run
{
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
backup incremental level 0 database tag 'Level 0' plus archivelog delete input tag 'Level 0 - Arch';
backup archivelog all delete input;
delete force noprompt obsolete;
}
list backup summary;
exit;
Notes:
a. The 1st set command in thebat file sets a date/time string. The 2nd set changes hours before 10am to include a leading zero.
b. The configures are the default settings, but best to include them to prevent changes to the defaults adversely affecting your backup.
c. The plus archivelog should do enough, but I've included a catchall backup afterwards. Not sure if this is needed.
d. Just to keep things tidy, I'm removing the obsolete backups.
2. Test it.
3. Setup an automatic schedule for the job. I typically leave my PC on overnight so I'm running the backup at 6am. Of course you must be sure the database is running too.
Go to the Control Panel, then Administration Tasks and select Task Scheduler.
Click 'Create a Basic Task'
Enter name and description, click next.
Trigger is Daily, next.
Set a start date and time, tomorrow, 6am, reoccur every day.
Action is 'Start a Program', next
Enter script name C:\app\rman_scripts\backup1, next, then Finish.
Click on 'Task Scheduler Library' (left) to see your tasks.
Note: Make sure your default environment works for rman to run. Obvious perhaps, but important.
Also, if you want an email of the results log, change the bat file from rename to copy, thus keeping log1.log, and then in the scheduler, after the run of the script add an email and make log1.log the attachment. You'll have to set the other details yourself.
Happyjohn
In C:\app\rman_scripts create a file backup1.bat containing this...
cd C:\app\rman_scripts
set oracle_home=C:\app\oracle\product\12.1.0\dbhome_2
set oracle_sid=jhcdb
rman target / @backup1.bck log log\log1.log
set t=%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%.%time:~3,2%.%time:~6,2%
set t=%t: =0%
ren log\log1.log log-%t%.log
And create a log sub-directory in c:\app\rman_scripts
Then create a another file called backup1.bck containing this...
run
{
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
backup incremental level 0 database tag 'Level 0' plus archivelog delete input tag 'Level 0 - Arch';
backup archivelog all delete input;
delete force noprompt obsolete;
}
list backup summary;
exit;
Notes:
a. The 1st set command in thebat file sets a date/time string. The 2nd set changes hours before 10am to include a leading zero.
b. The configures are the default settings, but best to include them to prevent changes to the defaults adversely affecting your backup.
c. The plus archivelog should do enough, but I've included a catchall backup afterwards. Not sure if this is needed.
d. Just to keep things tidy, I'm removing the obsolete backups.
2. Test it.
3. Setup an automatic schedule for the job. I typically leave my PC on overnight so I'm running the backup at 6am. Of course you must be sure the database is running too.
Go to the Control Panel, then Administration Tasks and select Task Scheduler.
Click 'Create a Basic Task'
Enter name and description, click next.
Trigger is Daily, next.
Set a start date and time, tomorrow, 6am, reoccur every day.
Action is 'Start a Program', next
Enter script name C:\app\rman_scripts\backup1, next, then Finish.
Click on 'Task Scheduler Library' (left) to see your tasks.
Note: Make sure your default environment works for rman to run. Obvious perhaps, but important.
Also, if you want an email of the results log, change the bat file from rename to copy, thus keeping log1.log, and then in the scheduler, after the run of the script add an email and make log1.log the attachment. You'll have to set the other details yourself.
Happyjohn
Tuesday, July 29, 2014
Further notes on Sessions and Blocking
-- The following are just a few commands that are useful.
-- take particular note of the blocking locks statement that doesn't use the lock table. It's just different.
-- Show all my sessions
select *
from v$session sess
where sess.osuser = 'j36563';
-- Show specific details for my sessions
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id
from v$session sess
where sess.osuser = 'j36563';
-- Show sql details for my sessions.
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,
stxt.sql_text
from v$session sess,
V$sqltext stxt
where sess.osuser = 'j36563'
and sess.sql_id = stxt.sql_id;
-- Show v$accesss locks for a given object
SELECT * FROM v$access WHERE lower(object) = 'pkg_rege_loss_objection';
-- Show detailed information regarding access locks for a given package
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,
'alter system kill session '''||sid||','||serial#||''' immediate;' comm
from v$session sess where sid in (
SELECT sid FROM v$access WHERE lower(object) = 'pkg_xml_schema_util');
-- show blocking locks
select sess1.username || ' on ' || sess1.machine || ' (' || sess1.sid || '/' || sess1.serial# || ') ' ||
'Is blocking ' ||
sess2.username || ' on ' || sess2.machine || ' (' || sess2.sid || '/' || sess2.serial# || ') ' ||
' => ' || dobj1.owner || '.' || dobj1.object_name || ' (' || dobj1.object_type || ')' "object"
from v$lock lock1,
v$session sess1,
v$lock lock2,
v$session sess2,
v$locked_object lobj1,
dba_objects dobj1
where sess1.sid=lock1.sid and sess2.sid=lock2.sid
and lock1.BLOCK=1 and lock2.request > 0
and lock1.id1 = lock2.id1
and lock2.id2 = lock2.id2
and sess1.sid = lobj1.session_id
and lobj1.object_id = dobj1.object_id
/
-- Another way to view blocking locks
select sid, serial#, username, program, blocking_session_status, blocking_instance, blocking_session, p2text, p2, sql_exec_start,
'alter system kill session '''||sid||','||serial#||''' immediate;' comm
from v$session sess
where blocking_session_status = 'VALID';
--where (sess.osuser = 'j36563' or sid = 20);
-- or
select sess2.username || ' on ' || sess2.machine || ' (' || sess.blocking_session || '/' || sess2.serial# || ') ' ||
'Is blocking ' ||
sess.username || ' on ' || sess.machine || ' (' || sess.sid || '/' || sess.serial# || ') '
from v$session sess, v$session sess2
where sess.blocking_session_status = 'VALID'
and sess.blocking_session = sess2.sid;
-- Kill a session (change the sid,serial# for values)
alter system kill session 'sid,serial#' immediate;
happyjohn
-- take particular note of the blocking locks statement that doesn't use the lock table. It's just different.
-- Show all my sessions
select *
from v$session sess
where sess.osuser = 'j36563';
-- Show specific details for my sessions
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id
from v$session sess
where sess.osuser = 'j36563';
-- Show sql details for my sessions.
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,
stxt.sql_text
from v$session sess,
V$sqltext stxt
where sess.osuser = 'j36563'
and sess.sql_id = stxt.sql_id;
-- Show v$accesss locks for a given object
SELECT * FROM v$access WHERE lower(object) = 'pkg_rege_loss_objection';
-- Show detailed information regarding access locks for a given package
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,
'alter system kill session '''||sid||','||serial#||''' immediate;' comm
from v$session sess where sid in (
SELECT sid FROM v$access WHERE lower(object) = 'pkg_xml_schema_util');
-- show blocking locks
select sess1.username || ' on ' || sess1.machine || ' (' || sess1.sid || '/' || sess1.serial# || ') ' ||
'Is blocking ' ||
sess2.username || ' on ' || sess2.machine || ' (' || sess2.sid || '/' || sess2.serial# || ') ' ||
' => ' || dobj1.owner || '.' || dobj1.object_name || ' (' || dobj1.object_type || ')' "object"
from v$lock lock1,
v$session sess1,
v$lock lock2,
v$session sess2,
v$locked_object lobj1,
dba_objects dobj1
where sess1.sid=lock1.sid and sess2.sid=lock2.sid
and lock1.BLOCK=1 and lock2.request > 0
and lock1.id1 = lock2.id1
and lock2.id2 = lock2.id2
and sess1.sid = lobj1.session_id
and lobj1.object_id = dobj1.object_id
/
-- Another way to view blocking locks
select sid, serial#, username, program, blocking_session_status, blocking_instance, blocking_session, p2text, p2, sql_exec_start,
'alter system kill session '''||sid||','||serial#||''' immediate;' comm
from v$session sess
where blocking_session_status = 'VALID';
--where (sess.osuser = 'j36563' or sid = 20);
-- or
select sess2.username || ' on ' || sess2.machine || ' (' || sess.blocking_session || '/' || sess2.serial# || ') ' ||
'Is blocking ' ||
sess.username || ' on ' || sess.machine || ' (' || sess.sid || '/' || sess.serial# || ') '
from v$session sess, v$session sess2
where sess.blocking_session_status = 'VALID'
and sess.blocking_session = sess2.sid;
-- Kill a session (change the sid,serial# for values)
alter system kill session 'sid,serial#' immediate;
happyjohn
Monday, July 28, 2014
Oracle Database Startup Problems
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2403304 bytes
Variable Size 989856792 bytes
Database Buffers 654311424 bytes
Redo Buffers 6946816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5712
Session ID: 242 Serial number: 3
What could this be? So I looked at the alert log.
Errors in file C:\APP\ORACLE\diag\rdbms\jhcdb\jhcdb\trace\jhcdb_ora_5768.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 12884901888 bytes is 100.00% used, and has 0 remaining bytes available.
Mon Jul 28 09:59:40 2014
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHCDB\ARCHIVELOG\2014_07_28\O1_MF_1_408_%U_.ARC'
Mon Jul 28 09:59:40 2014
Errors in file C:\APP\ORACLE\diag\rdbms\jhcdb\jhcdb\trace\jhcdb_ora_5768.trc:
ORA-16038: log 3 sequence# 408 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'C:\APP\ORACLE\ORADATA\JHCDB\REDO03.LOG'
I think that says it all.
Of course I cannot get into rman without he database running, so I must increase the size of the db_recovery_file_dest_size so I can get the database running so I can tidy up.
sqlplus / as sysdba
SQL> startup mount
SQL> alter system set db_recovery_file_dest_size = 20G;
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2403304 bytes
Variable Size 989856792 bytes
Database Buffers 654311424 bytes
Redo Buffers 6946816 bytes
Database mounted.
Database opened.
SQL>
Now I can go into RMAN and sort things out.
Happyjohn.
Subscribe to:
Posts (Atom)