How to add or drop online-redo logs?
Below are the steps on how to add a new online redo-group. It could be of the same size as other groups or different size.
1) List log groups, their archive status, activity status and size
SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;
GROUP# ARC STATUS BYTES
———- — —————- ———–
1 NO ACTIVE 104857600
2 NO CURRENT 104857600
3 NO INACTIVE 104857600
2) Add a new log group with a different size and query v$log to show the activity status of the new log member is UNUSED
SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;
Database altered.
SQL> select group#, archived, status, bytes from v$log;
GROUP# ARC STATUS BYTES
———- — —————- ———-
1 NO CURRENT 104857600
2 NO INACTIVE 104857600
3 NO INACTIVE 104857600
4 YES UNUSED 209715200
Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”
To drop the online redo log, the files in the group has to be dropped manually.
SQL> select * from v$logfile where group# = 3;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG NO
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG NO
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
Shirking sharepoint database
We had an issue the other day when one of the Sharepoint database logs became extremely large as the server where it was installed didn’t have SQLServer Management Studio so using “sqlcmd” we were able connect to the database and shrink the logfile and it ran on SQLServer lite Edition.
Here are the steps:
1) Connect to the sharepoint database via sqlcmd
C:> sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
2) List the databases
1> select name, database_id from sys.databases;
2> go
name database_id
—————————————————————————
master 1
tempdb 2
model 3
msdb 4
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b 5
SharePoint_AdminContent_b002cd77-a299-4ed7-998c-2fec58619fa5 6
WSS_Search_CALSHP1AP01 7
WSS_Content 8
3) Connect to the database that had the large logfile
1> use [SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b]
2> go
3) Get the current size of the files
1> select name, size from sys.sysfiles;
2> go
name size
——————————————————— —————————-
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b 664
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log 2171808
(2 rows affected)
4) Shrink the logfile
1> dbcc shrinkfile(‘SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log’, 2)
2> go
Cannot shrink log file 2 (SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 1986704 63 1986704 56
(1 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system ad ministrator.
1> BACKUP LOG [SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b] with truncate_only
2> go
1> dbcc shrinkfile(‘SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log’, 2)
2> go
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 256 63 256 56
(1 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How to fix issue when the online redo logs are missing?
If for some reason the online redo log were lost or when cloning the copy of shutdown database you don’t have the on-line redo logs, you would need to open the database with “RESETLOGS” option which will create the online redo logs.
Example:
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 356517584 bytes
Database Buffers 155189248 bytes
Redo Buffers 8052736 bytes
SQL> alter database mount;
Database altered.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
-- shows online redo logs have been recreated
SQL> select member from v$logfile;
MEMBER
—————————————–
E:\ORACLE\DB\TESTDB\ORADATA\REDO01A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO01B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG
6 rows selected.
One has perform “RECOVER DATABASE UNTIL CANCEL” before opening the database, if one doesn’t Oracle will generate the following error when trying to open with resetlogs:
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
Create a database manually on windows in 11g?
-- Step 1) set up environment variables needed (ORACLE_HOME, ORACLE_SID and add ORACLE_HOME\bin to PATH)
set ORACLE_HOME=e:\oracle\11g\product\11.2.0.1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=TESTDB
-- Step 2) Sample pfile (parameter file) for the instance and place it in %ORCALE_HOME%\dbs
db_name='TESTDB'
memory_target=500m
processes=150
audit_file_dest='E:\oracle\DB\TESTDB\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
diagnostic_dest=E:\oracle\DB\TESTDB\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("E:\oracle\DB\TESTDB\oradata\control1.ora", "E:\oracle\DB\TESTDB\oradata\control2.ora")
compatible ='11.1.0'
-- Step 3) Create directories mentioned in the spfile
E:\>mkdir E:\oracle\DB\TESTDB\admin\adump
E:\>mkdir E:\oracle\DB\TESTDB\diagnostic_dest
E:\>mkdir E:\oracle\DB\TESTDB\oradata
-- Step 4) Creation of service for TESTDB and set startup mode to be manual
E:\>oradim -NEW -SID TESTDB -STARTMODE manual
Instance created.
-- Step 5) Start SQL*PLus to create database
E:\> sqlplus "/nolog"
SQL> connect sys/oracle as sysdba
Connected
-- Step 6) Start instance in nomount with the pfile
SQL> startup nomount pfile="E:\oracle\11g\product\11.2.0.1\dbs\initTESTDB.ora"
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes
-- Step 7) Create database
SQL>
CREATE DATABASE TESTDB
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
DATAFILE 'E:\oracle\DB\TESTDB\oradata\SYSTEM01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1000M
SYSAUX DATAFILE 'E:\oracle\DB\TESTDB\oradata\SYSAUX01.DAT' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
DEFAULT TABLESPACE USERS DATAFILE 'E:\oracle\DB\TESTDB\oradata\USERS01.DBF' SIZE 50M AUTOEXTEND ON MAXSIZE 200M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\oracle\DB\TESTDB\oradata\TEMP01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'E:\oracle\DB\TESTDB\oradata\UNDOTBS01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('E:\oracle\DB\TESTDB\oradata\REDO01a.LOG', 'E:\oracle\DB\TESTDB\oradata\REDO01b.LOG') SIZE 100M,
GROUP 2 ('E:\oracle\DB\TESTDB\oradata\REDO02a.LOG', 'E:\oracle\DB\TESTDB\oradata\REDO02b.LOG') SIZE 100M,
GROUP 3 ('E:\oracle\DB\TESTDB\oradata\REDO03a.LOG', 'E:\oracle\DB\TESTDB\oradata\REDO03b.LOG') SIZE 100M
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 1;
-- Step 8) Create catalog data dictionary.
SQL> @?/rdbms/admin/catalog.sql
-- Step 9) Create all structures required for PL/SQL
SQL> @?/rdbms/admin/catproc.sql
-- Step 10) Create spfile from pfile and restart instance
SQL> create spfile from pfile='E:\oracle\11g\product\11.2.0.1\dbs\initTESTDB.ora';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
SQL> show parameter spfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string E:\ORACLE\11G\PRODUCT\11.2.0.1\DATABASE\SPFILETESTDB.ORA
-- Step 11) How to stop/start the services and instance
-- Show service status
E:\>sc query OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0×0)
SERVICE_EXIT_CODE : 0 (0×0)
CHECKPOINT : 0×0
WAIT_HINT : 0×0
-- Stop service, the status of the service changes to STOP_PENDING
E:\>sc stop OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 3 STOP_PENDING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0×0)
SERVICE_EXIT_CODE : 0 (0×0)
CHECKPOINT : 0×1
WAIT_HINT : 0x15f90
-- Check service again after waiting for a bit
E:\>sc query OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 1 STOPPED
(NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN))
WIN32_EXIT_CODE : 0 (0×0)
SERVICE_EXIT_CODE : 0 (0×0)
CHECKPOINT : 0×0
WAIT_HINT : 0×0
-- Start service, the status will change to START_PENDING
E:\>sc start OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 2 START_PENDING
(NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN))
WIN32_EXIT_CODE : 0 (0×0)
SERVICE_EXIT_CODE : 0 (0×0)
CHECKPOINT : 0×0
WAIT_HINT : 0x7d0
PID : 3784
FLAGS :
-- Recheck status of the service
E:\>sc query OracleServiceTESTDB
SERVICE_NAME: OracleServiceTESTDB
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0×0)
SERVICE_EXIT_CODE : 0 (0×0)
CHECKPOINT : 0×0
WAIT_HINT : 0×0
-- Re-login to start the database, if you don't relogin, you will get the ORA-03113: end-of-file on communication channel error message
E:\>sqlplus "/nolog"
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 7 10:01:21 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes
How to drop snapshots?
-- drop snapshot, it will not generate an error if the snap id is out of range
SQL> EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>1, high_snap_id=>10);
PL/SQL procedure successfully completed.
SQL by high physical I/O
-- Shows SQL statements currently in shared pool with high physical I/O, it sorts by the high I/O first
SELECT buffer_gets
, disk_reads
, executions
, buffer_gets/executions bufovex
, sql_text
FROM v$sql
WHERE executions != 0
ORDER BY disk_reads desc;
Using DBMS_ADVISOR to advice about UNDO tablespace from AWR
declare
task_id number;
name varchar2(100);
descr varchar2(100);
obj_id number;
-- get the min and max snap id from AWR
CURSOR l_cur IS
SELECT min_snap_id, begin_interval_time, max_snap_id, end_interval_time
FROM dba_hist_snapshot d,
(select min(snap_id) as min_snap_id, max(snap_id) max_snap_id FROM dba_hist_snapshot) minmaxsnap
WHERE d.snap_id = minmaxsnap.min_snap_id
or d.snap_id = minmaxsnap.max_snap_id
order by begin_interval_time;
l_min_snap l_cur%ROWTYPE;
l_max_snap l_cur%ROWTYPE;
-- Get advisor information
CURSOR l_undo_advisor(p_task_id IN dba_advisor_findings.task_id%TYPE) IS
select type, message, more_info from dba_advisor_findings where task_id = p_task_id;
l_rec_advisor l_undo_advisor%ROWTYPE;
begin
open l_cur;
fetch l_cur INTO l_min_snap;
fetch l_cur INTO l_max_snap;
close l_cur;
-- name of advisor task
name := 'UNDO_TABLESPACE_' || l_min_snap.min_snap_id || '_' || l_max_snap.max_snap_id;
descr := 'Check Undo Tablespace';
dbms_advisor.create_task('Undo Advisor', task_id, name, descr);
dbms_advisor.create_object(name, 'UNDO_TBS', NULL, NULL, NULL, 'null', obj_id);
-- Set parameters
dbms_advisor.set_task_parameter(name, 'TARGET_OBJECTS', obj_id);
dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', 106);
dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', 423);
dbms_advisor.set_task_parameter(name, 'INSTANCE', 1);
dbms_output.put_line('Task Name: ' || name);
dbms_output.put_line('Task Id: ' || task_id);
dbms_advisor.execute_task(name);
open l_undo_advisor(task_id);
fetch l_undo_advisor INTO l_rec_advisor;
IF l_undo_advisor%FOUND THEN
dbms_output.put_line('Type: ' || l_rec_advisor.type);
dbms_output.put_line('Message: ' || l_rec_advisor.message);
dbms_output.put_line('More Info: ' || l_rec_advisor.more_info);
ELSE
dbms_output.put_line('No Information found');
END IF;
close l_undo_advisor;
dbms_advisor.delete_task(name);
end;
/
Output:
Task Name: UNDO_TABLESPACE_106_426
Task Id: 466
Type: PROBLEM
Message: The undo tablespace is OK.
More Info:
Check Parallel Queries, Slaves and Coordinator, RAC instances
REM ————————————————————————–
REM REQUIREMENTS:
REM select access on v$px_session px, v$session s, v$instance i
REM ————————————————————————–
REM PURPOSE:
REM To lists users running a parallel query and their associated slaves.
REM —————————————————————————
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is not
REM supported by Oracle Support Services.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ————————————————————————–
REM Main text of script follows:
col username for a12
col “QC SID” for A6
col SID for A6
col “Inst” form A3
col “QC/Slave” for A10
col “Requested DOP” for 9999
col “Actual DOP” for 9999
col “slave set” for A10
col “Event” for a26
set pages 100
set lines 1200
select
decode(px.qcinst_id,NULL,username,
‘ – ‘||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”,
decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
to_char( px.server_set) “Slave Set”,
to_char(s.sid) “SID”,
to_char(s.inst_id) “Inst”,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”,
px.req_degree “Requested DOP”,
px.degree “Actual DOP” ,
s.event “Event”
from
gv$px_session px,
gv$session s
where
px.sid=s.sid (+) and
px.serial#=s.serial# and
px.inst_id = s.inst_id
order by 6 , 5 asc, 1 desc
/
Resumable timeout explained
One can make Oracle suspend a session if it runs out a free space by enabling resumable timeout. This can be set by as follows and the value is in seconds:
– Set a session timeout to be 1800
SQL> alter session enable resumable timeout 1800;
Session altered.
– Example: Set system wide resuamble timeout to be 1800 sec
SQL> ALTER SYSTEM SET RESUMABLE_TIMEOUT=1800;
System altered.
Demo: Shows a session suspended when resumable timeout is set. In the following example the session was suspended as it ran out of space on a tablespace
SQL> insert into a (select * from a);
Message in the alert_TESTDB.log when a session runs out of space
Mon Aug 01 13:49:55 2011
statement in resumable session ‘User SYS(0), Session 194, Instance 1′ was suspended due to
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
insert into a (select * from a)
*
There is a view that contains information on sessions that are and were suspended, in the example below it shows the STATUS is SUSPENDED and time when it occurred in the above example
SQL> select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;
USER_ID INSTANCE_ID STATUS
———- ———– ———
NAME
——————————————————————————–
ERROR_MSG
——————————————————————————–
SUSPEND_TIME RESUME_TIME
——————– ——————–
0 1 SUSPENDED
User SYS(0), Session 194, Instance 1
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
08/01/11 13:49:54
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
Information in V$SESSION about session waiting for the tablespace to be extended, the status is ACTIVE and event is “statement suspended…”
SQL> select status, state, event FROM v$session where sid = 194;
STATUS
——————-
STATE
——————-
EVENT
—————————————————————-
ACTIVE
WAITING
statement suspended, wait error to be cleared
Also this event is recorded in v$session_event
SQL> select * FROM v$session_event where sid = 194 and EVENT like ‘statement%’;
SID EVENT
———- —————————————————————-
TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
———– ————– ———– ———— ———- —————–
EVENT_ID WAIT_CLASS_ID WAIT_CLASS#
———- ————- ———–
WAIT_CLASS
—————————————————————-
194 statement suspended, wait error to be cleared
254 254 50788 199.95 200 507883018
680822103 3290255840 2
Configuration
If the tablepsace is not fixed within the timeout, the session will error out and the status is TIMEOUT for the session in DBA_RESUMABLE
SQL> select user_id, session_id, instance_id, status, name, error_msg, start_time, suspend_time, resume_time from dba_resumable;
USER_ID SESSION_ID INSTANCE_ID STATUS
———- ———- ———– ———
NAME
——————————————————————————–
ERROR_MSG
——————————————————————————–
START_TIME SUSPEND_TIME RESUME_TIME
——————– ——————– ——————–
0 194 1 TIMEOUT
User SYS(0), Session 194, Instance 1
ORA-01653: unable to extend table SYS.A by 128 in tablespace SYSTEM
08/01/11 13:49:54 08/01/11 13:49:54
And also message gets recorded in the alert.log when the sessinon times out
Mon Aug 01 14:19:55 2011
statement in resumable session ‘User SYS(0), Session 194, Instance 1′ was timed out
If the tablepsace is fixed within the timeout, the session will continue to run the status is NORMAL for the session in DBA_RESUMABLE with resume_time set to indicate when the session start running again
USER_ID SESSION_ID INSTANCE_ID STATUS
———- ———- ———– ———
NAME
——————————————————————————–
ERROR_MSG
——————————————————————————–
START_TIME SUSPEND_TIME RESUME_TIME
——————– ——————– ——————–
0 194 1 NORMAL
User SYS(0), Session 194, Instance 1
08/01/11 15:05:53 08/01/11 15:06:19
And in the alert.log
Mon Aug 01 15:06:16 2011
alter database datafile ‘E:\ORACLE\TESTDB\ORADATA\SYSTEM.DBF’ AUTOEXTEND ON
Completed: alter database datafile ‘E:\ORACLE\TESTDB\ORADATA\SYSTEM.DBF’ AUTOEXTEND ON
Mon Aug 01 15:06:19 2011
statement in resumable session ‘User SYS(0), Session 194, Instance 1′ was resumed
