Wednesday, November 19, 2014

How to increase the SMB space budget

When the message below shows up in the database alert.log, you should increase the SMB space budget.

Message:
SPM: SMB space usage (13523353600) exceeds 10.000000% of SYSAUX size (15728640000).
Command:
exec dbms_spm.configure('Space_budget_percent', 30);
Posted by at No comments:
Labels:

Monday, November 3, 2014

How to create a tarball - short description

To create a tarball, for example of an Oracle Home, do the following:
# become root
su - 
# cd to the directory in which you keep your files you'd like to tar up:
cd /u01/oracle/product/11204
# create the tarball, place it in the parent directory:
tar cvf ../oracle11204.tar *

You could also add compression at the same time:
tar -cvzf ../oracle11204.tar.tgz .
To create a tarball of the directory /tmp/dbscr and any file(s) in it: 
cd /tmp
tar cvf mytarball.tar dbscr
dbscr/
dbscr/file1.txt
dbscr/file2.rsp
dbscr/file3.ora
Posted by at No comments:
Labels:

Saturday, November 1, 2014

Step by step: how to switchover from primary to standby with Oracle 10g

Based on the guide found in the official Oracle 10g documents found here

This is a classic Oracle 10g two-node Physical standby Data Guard setup:

Primary database name: proddb01
Standby database name: stby01

STEP 1: Verify that it is possible to fail over.
On primary database (proddb01):
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database is ready to be switched to the standby role.
In my case, the output was SESSIONS ACTIVE. In this case, check if this is because of a) active SQL sessions or b) active user sessions.
The section "Problems switching over to a standby database, section A.4.2 and A.4.3" gives good examples on how to clean up your primary before attempting to switch over.

It seems like you can still successfully perform a switchover by appending the WITH SESSION SHUTDOWN clause to the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement, and that is exactly what I did:

STEP 2: Instruct the primary database to take the role as the standby database.

On the primary (proddb01):
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

From primary (proddb01) alert log:

idle dispatcher 'D000' terminated, pid = (16, 1)
Tue Oct 28 19:13:39 2014
Switchover: Complete - Database shutdown required (proddb01)
Tue Oct 28 19:13:39 2014
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

From the standby (stby01) alert log:
-- Connected User is Valid
RFS[40]: Assigned to RFS process 7078566
RFS[40]: Identified database type as 'physical standby'
RFS[40]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log
Tue Oct 28 19:10:57 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75389_681750063.arc
Media Recovery Waiting for thread 1 sequence 75390
Tue Oct 28 19:13:31 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[41]: Assigned to RFS process 34669144
RFS[41]: Identified database type as 'physical standby'
RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc'
Tue Oct 28 19:13:32 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc
Identified End-Of-Redo for thread 1 sequence 75390
Tue Oct 28 19:13:32 2014
Media Recovery End-Of-Redo indicator encountered
Tue Oct 28 19:13:32 2014
Media Recovery Applied until change 142570810888
Tue Oct 28 19:13:32 2014
MRP0: Media Recovery Complete: End-Of-REDO (stby01)
Resetting standby activation ID 1529144623 (0x5b24e52f)
Tue Oct 28 19:13:35 2014
MRP0: Background Media Recovery process shutdown (stby01)

Step 3: shutdown the database and start it in mounted mode.
On primary (proddb01):
shutdown abort
startup mount
From the primary (proddb01) alert log:
Tue Oct 28 19:15:37 2014
Successful mount of redo thread 1, with mount id 1709512659
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
        handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE MOUNT


Step 4: verify the current status.

On the standby (stby01):
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

Step 5: Instruct the standby database to take on the role as a primary database.
On the standby (stby01):
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
From the standby (stby01) alert log:
-- Connected User is Valid
RFS[41]: Assigned to RFS process 34669144
RFS[41]: Identified database type as 'physical standby'
RFS[41]: Archived Log: '/u04/oradata/stby01/archive/log1_75390_681750063.arc'
Tue Oct 28 19:13:32 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75390_681750063.arc
Identified End-Of-Redo for thread 1 sequence 75390
Tue Oct 28 19:13:32 2014
Media Recovery End-Of-Redo indicator encountered
Tue Oct 28 19:13:32 2014
Media Recovery Applied until change 142570810888
Tue Oct 28 19:13:32 2014
MRP0: Media Recovery Complete: End-Of-REDO (stby01)
Resetting standby activation ID 1529144623 (0x5b24e52f)
Tue Oct 28 19:13:35 2014
MRP0: Background Media Recovery process shutdown (stby01)
Tue Oct 28 19:18:43 2014
ALTER DATABASE SWITCHOVER TO PRIMARY (stby01)
Tue Oct 28 19:18:43 2014
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 142570810888
Online log /u02/oradata/stby01/redo1.log: Thread 1 Group 4 was previously cleared
Online log /u02/oradata/stby01/redo2.log: Thread 1 Group 5 was previously cleared
Online log /u02/oradata/stby01/redo3.log: Thread 1 Group 6 was previously cleared
Standby became primary SCN: 142570810886
Converting standby mount to primary mount.
Tue Oct 28 19:18:43 2014
Switchover: Complete - Database mounted as primary (stby01)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Tue Oct 28 19:18:43 2014
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC2 started with pid=15, OS id=8847906
Tue Oct 28 19:19:41 2014
ALTER DATABASE OPEN
The primary database (proddb01) is from now on considering itself to be the physical standby database.


From the old primary (proddb01) database alert log:
Tue Oct 28 19:15:37 2014
Physical Standby Database mounted.
Starting control autobackup
Control autobackup written to DISK device
        handle '/u01/oracle/product/ora10g/dbs/c-1529124399-20141028-12'
Completed: ALTER DATABASE   MOUNT
Tue Oct 28 19:26:10 2014
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oracle/admin/proddb01/archive/
Tue Oct 28 19:26:19 2014
Starting control autobackup
Control autobackup written to SBT_TAPE device
        comment 'API Version 2.0,MMS Version 5.3.3.0',
        media '1362'
        handle 'c-1529124399-20141028-13'

Step 6: Verify that the former standby database now has the primary role.
On stby01:
SQL> SELECT DB_UNIQUE_NAME,NAME,DATABASE_ROLE FROM V$DATABASE;

DB_UNIQUE_NAME                 NAME           DATABASE_ROLE
------------------------------ -------------- ----------------
stby01                          proddb01           PRIMARY


After switchover, Oracle complained about some temporary files:

ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/stby01/temp01.dbf'



SQL> 
  1  select tf.file#,tf.ts#,tf.status,tf.enabled, tf.bytes/1024/1024,tf.name "file_name",ts.name "ts_name"
  2  from v$tempfile tf, v$tablespace ts
  3* where tf.ts# = ts.ts#

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                              ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
         1          3 ONLINE  READ WRITE              32767 /u02/oradata/stby01/temp01.dbf          TEMP
         2          8 ONLINE  READ WRITE                100 /u02/oradata/stby01/EDPROD_iastemp.dbf  EDPROD_IAS_TEMP
         3          3 ONLINE  READ WRITE               9900 /u02/oradata/stby01/temp02.dbf          TEMP

SQL> save temp_tabspc_overview.sql
Created file temp_tabspc_overview.sql

SQL> drop tablespace EDPROD_IAS_TEMP including contents and datafiles;

Tablespace dropped.

SQL> @temp_tabspc_overview.sql

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                      ts_name
---------- ---------- ------- ---------- ------------------ ------------------------------ --------------------
         1          3 ONLINE  READ WRITE              32767 /u02/oradata/stby01/temp01.dbf  TEMP
         3          3 ONLINE  READ WRITE               9900 /u02/oradata/stby01/temp02.dbf  TEMP


SQL> create temporary tablespace EDPROD_IAS_TEMP tempfile '/u02/oradata/stby01/EDPROD_iastemp.dbf' size 128M reuse autoextend on next 32M maxsize unlimited

Tablespace created.

SQL>  @temp_tabspc_overview.sql

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                              ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
         1          3 ONLINE  READ WRITE              32767 /u02/oradata/stby01/temp01.dbf          TEMP
         2          8 ONLINE  READ WRITE                128 /u02/oradata/stby01/EDPROD_iastemp.dbf  EDPROD_IAS_TEMP
         3          3 ONLINE  READ WRITE               9900 /u02/oradata/stby01/temp02.dbf          TEMP


SQL> alter database default temporary tablespace EDPROD_IAS_TEMP;

Database altered.

SQL>  drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMP tempfile '/u02/oradata/stby01/temp01.dbf' size 128M reuse autoextend on next 32M maxsize unlimited;

Tablespace created.

SQL> @temp_tabspc_overview.sql

     FILE#        TS# STATUS  ENABLED    TF.BYTES/1024/1024 file_name                              ts_name
---------- ---------- ------- ---------- ------------------ -------------------------------------- --------------------
         1          3 ONLINE  READ WRITE                128 /u02/oradata/stby01/temp01.dbf          TEMP
         2          8 ONLINE  READ WRITE                128 /u02/oradata/stby01/EDPROD_iastemp.dbf  EDPROD_IAS_TEMP
Posted by at No comments:
Labels: ,

Wednesday, October 22, 2014

Step by step: how to create a physical standby database with Oracle 10g

I recently had to create an Oracle 10g standby database to support a simultaneous move and upgrade of the database.

Below are the steps I followed to set it up.

Prerequisites for these steps are
* you have a working tape backup in place, and that you can perform redirected restores.
* you have the same software installed on both servers, in my case it was Oracle EE version 10.2.0.4

By "redirected restores" I mean that you are able to restore database backups through rman to a host different from the one where the backup was taken.
I will not explain the details around details such as standby redo log files, log transportation methods etc; there are plenty of good sources for this to be found on the internet.


1. prepare the primary database parameters:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(proddb01,stby01)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/admin/proddb01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddb01'
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
STANDBY_FILE_MANAGEMENT=AUTO

# The following parameters will only be applicable when the primary is turned into a standby
# We put them there to maker life easier in case of such event
FAL_SERVER=stby01
FAL_CLIENT=proddb01
DB_FILE_NAME_CONVERT='stby01','proddb01'
LOG_FILE_NAME_CONVERT='/u04/oradata/stby01/archive/','/u01/oracle/admin/proddb01/archive/'

2. create standby redo log files on primary database:

First, check the number of redo log groups currently in use:
SQL> select group# ,members, status from v$log;

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------
         4          1 ACTIVE
         5          1 ACTIVE
         6          1 CURRENT
Oracle recommends to add standby redo log file groups with the same size as the largest member in any group. They also recommend that the number of standby redo log groups is the same as the number of redo log groups + 1.
In my case, this means that I will add 4 standby redo log groups of 512M size each:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oradata/proddb01/stb_redo7.log') size 512M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oradata/proddb01/stb_redo8.log') size 512M;

SQL> select group# ,members, status from v$log;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u02/oradata/proddb01/stb_redo9.log') size 512M;

Database altered.

SQL>  ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u02/oradata/proddb01/stb_redo10.log') size 512M;

Database altered.

Verify that they were created:

SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         7          0          0 YES UNASSIGNED
         8          0          0 YES UNASSIGNED
         9          0          0 YES UNASSIGNED
        10          0          0 YES UNASSIGNED

3. enable force logging:

ALTER DATABASE FORCE LOGGING;

4. restart primary database

STARTUP MOUNT

Assuming that you have a valid tape backup solution in place, backup your current control file "for standby" using rman:

rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP CURRENT CONTROLFILE FOR STANDBY;
release channel c1;

5. Open the database, and switch the current logfile 

This must be done so that the last log file is indeed older than your backup controfile that you created in the previous step:

SQL> ALTER DATABASE OPEN;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable

6. Finally, backup the latest archived redo log to tape:

rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
release channel c1;


7. Set up SQL*net connection
...by adding an entry in the primary database's tnsnames.ora:

stby01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = psu0erdb01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby01)
    )
  )

8. Prepare the standby database parameters. 

# The db_name must from now on match your primary database db_name
db_name='proddb01'
# The db_unique_name is the name to which you will typically refer to the standby database
db_unique_name='stby01'
log_archive_config='DG_CONFIG=(proddb01,stby01)'
log_archive_format=log%t_%s_%r.arc
log_archive_dest_1='LOCATION=/u04/oradata/ stby01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= stby01'
log_archive_dest_2='SERVICE=proddb01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=auto
fal_server=proddb01
fal_client=stby01
db_file_name_convert='proddb01','stby01'
log_file_name_convert='proddb01','stby01'

 
9. shutdown the to-become standby database, open it in nomount-mode:

shutdown immediate
startup nomount pfile='your_pfile.ora'

Your standby database is now ready to be cloned from your primary. 

10. Create an rman script:

in file duplicate_for_standby.sh

export ORACLE_SID=stby01
export ORACLE_HOME=/u01/oracle/product/10204
export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
export NLS_LANG=AMERICAN_NORWAY.WE8ISO8859P15
rman target sys/password@proddb01 catalog uid/pwd@rmancat auxiliary / cmdfile='duplicate_for_standby.cmd' log='duplicate_for_standby.log' trace='duplicate_for_standby.trc'
exit

In file duplicate_for_standby.cmd:

run {
ALLOCATE AUXILIARY CHANNEL c1 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c2 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c3 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c4 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c5 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c6 TYPE 'SBT_TAPE';
duplicate target database for standby
dorecover;
}
exit

chmod 755 duplicate_for_standby.sh

11. Start the cloning script in the background:

nohup ./duplicate_for_standby.sh &

Follow the progress with 

tail -f duplicate_for_standby.log

As the directive "dorecover" indicates, rman will create a clone from the primary database, recover it, and leave the recovered database in mounted state.

12. Enable log transport services on the primary database:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

As a recap, this will instruct Oracle to ship logs to LOG_ARCHIVE_DEST_2, which is defined as a service that you have made known to your primary server through an entry in tnsnames.ora:

LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'


13. Check that your standby is applying logs. 
You can use the script found here

Pay particularly attention that the following section looks like this:

Is the MRP process running?
PROCESS   STATUS
--------- ------------

MRP0      WAIT_FOR_LOG

Crosscheck that with a look at the current archived log status:

set lines 200
col name format a60
alter session set nls_language='american';
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
SELECT SEQUENCE#,NAME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# ASC
/

The primary and standby should give similar views of the archived log situation:
Standby:

SEQUENCE# NAME                                                      STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------

75072 /u04/oradata/stby01/archive/log1_75072_681750063.arc          NO  YES YES NO  A 22.10.2014 12:26:07
75073 /u04/oradata/stby01/archive/log1_75073_681750063.arc          NO  YES YES NO  A 22.10.2014 12:26:37

Primary. Notice how the archived logs are sent to two destinations at every log switch:

SEQUENCE# NAME                                                      STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------

75072 stby01                                                        YES YES YES NO  A 22.10.2014 12:26:06
75072 /u01/oracle/admin/proddb01/archive/1_75072_681750063.arc      NO  YES NO  NO  A 22.10.2014 12:26:08
75073 stby01                                                        YES YES YES NO  A 22.10.2014 12:26:37
75073 /u01/oracle/admin/proddb01/archive/1_75073_681750063.arc      NO  YES NO  NO  A 22.10.2014 12:26:37

From the standby database's alert log, it's clear that logs are being applied:

Wed Oct 22 13:26:13 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75074_681750063.arc
Wed Oct 22 13:26:33 2014
Media Recovery Waiting for thread 1 sequence 75075 (in transit)
Wed Oct 22 13:26:39 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[30]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log'
Wed Oct 22 13:26:43 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75075_681750063.arc
Media Recovery Waiting for thread 1 sequence 75076 (in transit)

Documentation used 

"Creating a Physical Standby Database"
"Creating a Standby Database with Recovery Manager"
Posted by at No comments:
Labels: ,

Tuesday, October 21, 2014

How to view stored scripts in RMAN

Three different ways to print scripts stored in your recovery catalog:

list all script names;
list global script names;
print script my_script;
Posted by at No comments:
Labels:

Friday, October 17, 2014

Script example: create database 

CREATE DATABASE "mydb"
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
DATAFILE '/u02/oradata/mydb/system01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
SYSAUX DATAFILE '/u02/oradata/mydb/sysaux01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED 
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/mydb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u03/oradata/mydb/redo01.log') SIZE 2048M,
             GROUP 2 ('/u03/oradata/mydb/redo02.log') SIZE 2048M,
             GROUP 3 ('/u03/oradata/mydb/redo03.log') SIZE 2048M
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword";

Note 1:
You can exchange "SMALLFILE" with "BIGFILE" for any tablespace, as long as they are locally managed with automatic segment space management.

There are three exceptions to this rule: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace does not have to use automatic segment space management; they *must* use manual segment space management but can still be created as BIGFILE. In one of my databases, I have the following setup:

SELECT tablespace_name,bigfile,extent_management,segment_space_management 
FROM dba_tablespaces;

TABLESPACE_NAME                          BIGFILE   EXTENT_MANAGEMENT              SEGMENT_SPACE_MANA
---------------------------------------- --------- ------------------------------ ------------------
SYSTEM                                   NO        LOCAL                          MANUAL
SYSAUX                                   NO        LOCAL                          AUTO
UNDOTBS1                                 YES       LOCAL                          MANUAL
TEMP                                     YES       LOCAL                          MANUAL
USERS                                    NO        LOCAL                          AUTO

Note 2:
The NATIONAL CHARACTER SET clause specifies the "national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Valid values are AL16UTF16 and UTF8. The default is AL16UTF16."
Posted by at No comments:
Labels:

Wednesday, October 15, 2014

Why aren't other schemas' procedures showing in TOADs schema browser?

Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
They all show up neatly in TOADs schema browser.

However, procedures and sequences are not visible. Why?

Answer: lack of privileges.

Solution: Try granting the following as user sys:

GRANT SELECT ON OBM.sequence1 to SCOTT;
GRANT DEBUG ON OBM.proceure1 to SCOTT;

Refresh the schema browser - voila!

To generate a list of objects with grant statements:

SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
FROM DBA_PROCEDURES 
WHERE OWNER = 'OBM' 
AND OBJECT_TYPE='PROCEDURE';

SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
FROM DBA_SEQUENCES 
WHERE SEQUENCE_OWNER = 'OBM';


Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:
GRANT SELECT ANY SEQUENCE TO SCOTT;
GRANT DEBUG ANY PROCEDURE TO SCOTT;

Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)