We have a large number of production databases that are being used as the source for Materialized Views.
Often the developers just add an MV Log to a new table on the chance that it might be used. As we all know MV Logs or MLogs are used to record changed rows within a table, so that the MV based upon that source table, can perform a fast refresh.
I needed a way to locate all MLOGs and work out if they are still or have even been used. The solution to this is quite simple.
The object SYS.MLOG$ contains various details of the MLOGs usage. The information is better at 11g but ok at 10g.
The following query lists all tables who's name starts with MLOG$, and then added the SYS.MLOG$ details. I've used an outer join to still show tables that have never been used as MV Logs. The v$database is only there because I was doing a lot.
select db.name, tabs.owner, tabs.table_name, mlog.master, mlog.log, mlog.youngest
from sys.mlog$ mlog, v$database db, dba_tables tabs
where tabs.owner = mlog.mowner(+) and tabs.table_name = mlog.log(+)
and tabs.table_name like 'MLOG$%' and tabs.table_name not in ('MLOG$','MLOG_REF$')
order by 2,3;
Let me know what you think of my solution.
Happyjohn
Wednesday, October 22, 2014
Sunday, October 19, 2014
Resumable Timeouts caused by ORA-01652 but Tablespace is not full
* 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
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
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
Subscribe to:
Posts (Atom)