As RMAN needs a consistent view of the control file it takes a backup of the controlfile by creating a snapshot and during the backup RMAN uses the snapshot of the controlfile. By default the snapshot controlfile is created in $ORACLE_HOME/dbs/snapcf_ORACLE_SID.f which can be changed by setting the following parameter in RMAN
$ rman target /
– display parameter value
RMAN> show all;
..
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/10.2.0.4/TESTDB/snapcf_TESTDB.f'; # default
– set to new path for controlfile snapshot
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/TESTDB/snapcf_TESTDB.f';
When multiple RMAN are running one could run into an issue RMAN-08512 where RMAN is waiting for getting a lock on snapshot controlfile header. To find the session run the following SQL.
SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS')
FROM v$session vs, v$enqueue_lock vel
WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2
The “LEN” function cannot be used to find length of a column of datatype “TEXT” so using function “DATALENGTH” one can find the length of TEXT field.
Eg: SELECT DATALENGTH(textfiled) lentextfile FROM table;
Code that be used to scripts to extract role permissions for a database for SQL authenticated accounts, it is useful when rebuilding a server and after databases are reattached the SQL authenticated users access has to be granted.
DECLARE db_cursor CURSOR FOR
SELECT 'exec sp_addrolemember ''' + g.name + ''', ''' + sl.name + '''', sl.name
FROM sysusers u
, sysusers g
, sysmembers m
, master.dbo.syslogins sl
WHERE g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
AND g.uid = m.groupuid
AND sl.sid = u.sid
AND g.issqlrole = 1
AND sl.isntuser = 0
AND sl.name != 'sa'
AND u.uid = m.memberuid
ORDER BY sl.name
DECLARE @l_sql_stmt VARCHAR(200)
DECLARE @l_user VARCHAR(200), @l_prev_user VARCHAR(200)
SET @l_prev_user = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_prev_user != @l_user
BEGIN
PRINT 'IF EXISTS (SELECT 1 FROM sysusers WHERE [name] = ''' + @l_user + ''')'
PRINT ' EXEC sp_revokedbaccess ''' + @l_user + ''''
PRINT 'EXEC sp_grantdbaccess ''' + @l_user + ''''
SET @l_prev_user = @l_user
END
PRINT @l_sql_stmt
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
END
CLOSE db_cursor
DEALLOCATE db_cursor
Run the following SQL to find permissions granted on individual objects on the database
select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id and o.name = 'object_name'
inner join sysusers objectowner on o.uid = objectowner.uid
order by o.name, u.name