I have written two scripts. These scripts will disable and enable of entire constraints of ARJU schema. However if you want to enable of disable another schema then change the AND t.owner='ARJU' line inside script and change text ARJU.
Disable constraints of entire ARJU schema
set feedback off
set verify off
set echo off
prompt This script is going to disable constraints ....
set termout off
set pages 500
set heading off
set linesize 150
spool cons_disable.sql
select 'spool constraint_disable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||' DISABLE CONSTRAINT '||constraint_name||' CASCADE;'
from dba_constraints c, dba_tables t
where c.table_name = t.table_name
AND t.owner='ARJU';
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@cons_disable.sql;
exit
/
Enable Constraints of Entire ARJU Schema
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool cons_enable.sql
select 'spool cons_enable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||
' ENABLE CONSTRAINT '||constraint_name||' ;'
from dba_constraints c, dba_tables u
where c.table_name = u.table_name AND u.owner='ARJU';
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@cons_enable;
exit
/
Related Documents
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
Saturday, September 20, 2008
Audit Trigger Activity in Oracle
Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
Related Documents
About Audit_trail Parameter
Configure and Administer Database Auditing
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
SQL> select username,sql_text from dba_audit_trail;
USERNAME SQL_TEXT
-------------- --------------------------
TEST insert into test values(2,'Test','Test2')
Related Documents
About Audit_trail Parameter
Configure and Administer Database Auditing
Query to check whether user has datapump privilege.
Check for whether user has full database export/import privilege
You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,
which indicates user ARJU,FARUK,MOMIN,SYS,SYSTEM can do both full data pump export and full datapump import operation. And user Test only has datapump export operation. Note that DBA role includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
Check for user has create table or create session privilege
In order to see whether a user has CREATE TABLE or CREATE SESSION privilege, query from dba_sys_privs table. Following is the check whether user TEST has these privilege or not.
Check for granted privileges on directory objects
Suppose we want to check whether user TEST has privilege on directory objects query,
Check for default tablespace on a user
In order to check what is the default tablespace of user TEST query,
From output we can see user TEST has quota on system tablespace 10M and he already used 65K.
Related Documents
Minimum privilege needed to take data pump export
You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,
SQL>SET lines 100
COL privilege FOR a50
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;
GRANTEE GRANTED_ROLE DEF
------------------------------ ------------------------------ ---
ARJU DBA YES
DBA EXP_FULL_DATABASE YES
DBA IMP_FULL_DATABASE YES
FARUK DBA YES
MOMIN DBA YES
SYS DBA YES
SYS EXP_FULL_DATABASE YES
SYS IMP_FULL_DATABASE YES
SYSMAN DBA YES
SYSTEM DBA YES
TEST EXP_FULL_DATABASE YES
which indicates user ARJU,FARUK,MOMIN,SYS,SYSTEM can do both full data pump export and full datapump import operation. And user Test only has datapump export operation. Note that DBA role includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
Check for user has create table or create session privilege
In order to see whether a user has CREATE TABLE or CREATE SESSION privilege, query from dba_sys_privs table. Following is the check whether user TEST has these privilege or not.
SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege
FROM dba_sys_privs
WHERE (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;
If user test have it then output will be like below.
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
TEST CREATE SESSION
TEST CREATE TABLE
Check for granted privileges on directory objects
Suppose we want to check whether user TEST has privilege on directory objects query,
SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name
FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
AND (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
ORDER BY 4,3,2;
A typical output is
GRANTEE PRIVILEGE OWNER DIRECTORY_NAME
-------------------- ---------- -------------------- ------------------------------
TEST READ SYS D
TEST WRITE SYS D
EXP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR
Check for default tablespace on a user
In order to check what is the default tablespace of user TEST query,
SQL>select default_tablespace from dba_users where username='TEST';
Check for tablespace quota
To check whether user TEST has quota on which tablespaces query,
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST');
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST SYSTEM 65536 10485760
From output we can see user TEST has quota on system tablespace 10M and he already used 65K.
Related Documents
Minimum privilege needed to take data pump export
Minimum privilege needed to take data pump export
In your organization you may assign a user who is only responsible to take data pump export. Suppose everyday evening he will be responsible to take a logical backup of the database.
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
Privileges Required to Create Views
Public Privileges in Oracle
How to make global read only user
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
Privileges Required to Create Views
Public Privileges in Oracle
How to make global read only user
Expdp fails with ORA-01950 and ORA-01536
Error Description
Whenever I run expdp the export process fails with ORA-01950 or ORA-01536.
SQL> host expdp test/t full=y dumpfile=a.dmp directory=d
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 15:54:18
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
Or,
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'
Cause of the Problem While exporting database objects a master table is created in the user's default tablespace. In above example in first case it is SYS_EXPORT_FULL_05 and in second case SYS_EXPORT_SCHEMA_01 under test schema. If user does not have quota on default tablespace then export operation fails.
Solution of the Problem
Check the default tablespace of user test by issuing,
SQL> select default_tablespace from dba_users where username='TEST';
DEFAULT_TABLESPACE
------------------------------
USERS
Solution 1)Grant sufficient quota to the default tablespace on user's default tablespace who is performing export operation. To do it as a dba user grant quota to test user in this example as is shown above error.
SQL>conn system/a
SQL>ALTER USER test QUOTA unlimited ON users;
If you don't want unlimited quota you can still give less for example 10M to default tablespace on user test like,
SQL>ALTER USER test QUOTA 10M ON users;
Solution 2)If user has quota on another tablespace then you can change the user default tablespace to another tablespace of test user.
To do this issue query from dba_ts_quotas to see on which tablespace user has sufficient quota,
As on tabespace data user has quota you can then change user default tablespace by,
SQL>ALTER user test default tablespace DATA;
Now you can able to perform export operation as test user.
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Whenever I run expdp the export process fails with ORA-01950 or ORA-01536.
SQL> host expdp test/t full=y dumpfile=a.dmp directory=d
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 15:54:18
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
Or,
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'
Cause of the Problem While exporting database objects a master table is created in the user's default tablespace. In above example in first case it is SYS_EXPORT_FULL_05 and in second case SYS_EXPORT_SCHEMA_01 under test schema. If user does not have quota on default tablespace then export operation fails.
Solution of the Problem
Check the default tablespace of user test by issuing,
SQL> select default_tablespace from dba_users where username='TEST';
DEFAULT_TABLESPACE
------------------------------
USERS
Solution 1)Grant sufficient quota to the default tablespace on user's default tablespace who is performing export operation. To do it as a dba user grant quota to test user in this example as is shown above error.
SQL>conn system/a
SQL>ALTER USER test QUOTA unlimited ON users;
If you don't want unlimited quota you can still give less for example 10M to default tablespace on user test like,
SQL>ALTER USER test QUOTA 10M ON users;
Solution 2)If user has quota on another tablespace then you can change the user default tablespace to another tablespace of test user.
To do this issue query from dba_ts_quotas to see on which tablespace user has sufficient quota,
SQL> select tablespace_name,username,bytes, max_bytes from dba_ts_quotas where username='TEST';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
DATA TEST 65536 10485760
As on tabespace data user has quota you can then change user default tablespace by,
SQL>ALTER user test default tablespace DATA;
Now you can able to perform export operation as test user.
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-01031
Error Description
Whenever I run the expdp command to export only my schema objects it fails with ORA-31626, ORA-31633, ORA-06512 and ORA-01031.
A screenshot is below from my console.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 14:58:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges
Cause of the Problem
As ORA-01031 indicates the test user that is performing data pump export does not have enough privilege.
In order to see the current privilege assigned to the user issue,
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
CREATE ANY DIRECTORY
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
SELECT ANY DICTIONARY
READ ANY FILE GROUP
11 rows selected.
Solution of the Problem
In order to do export operation at a minimum level the user must have the create table system privilege. But with above output we see the user does not have "create table" privilege. Do in order to avoid problem we can have different solution.
Solution 1)As a DBA user grant create table privilege to the user who will perform data pump export operation.
SQL> conn system/a
SQL> grant create table to test;
Then as a test user perform your operation.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Solution 2)Run the export operation as a different user who has the create table privilege. For example run the export operation as a system user.
Like,
SQL>host expdp system/a full=y directory=d dumpfile=a.dmp
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Whenever I run the expdp command to export only my schema objects it fails with ORA-31626, ORA-31633, ORA-06512 and ORA-01031.
A screenshot is below from my console.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 14:58:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges
Cause of the Problem
As ORA-01031 indicates the test user that is performing data pump export does not have enough privilege.
In order to see the current privilege assigned to the user issue,
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
CREATE ANY DIRECTORY
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
SELECT ANY DICTIONARY
READ ANY FILE GROUP
11 rows selected.
Solution of the Problem
In order to do export operation at a minimum level the user must have the create table system privilege. But with above output we see the user does not have "create table" privilege. Do in order to avoid problem we can have different solution.
Solution 1)As a DBA user grant create table privilege to the user who will perform data pump export operation.
SQL> conn system/a
SQL> grant create table to test;
Then as a test user perform your operation.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Solution 2)Run the export operation as a different user who has the create table privilege. For example run the export operation as a system user.
Like,
SQL>host expdp system/a full=y directory=d dumpfile=a.dmp
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Thursday, September 18, 2008
EM Daemon is not running
On windows whenever I try to start my dbconsole in one time during process it shows me message EM Daemon is not running.
First I invoke status command to see whether my dbconcole is started or not by,
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
So it is not running. To start it on windows you can fix it by,
Right click on my computer icon> Click Manage> Select Services and Application>Select Services>On the right side From a list a Name select OracleDBConsolearju where arju is the name of database> Right click on OracleDBConsolearju and click start.
or in other OS along with windows you can start with
$emctl start dbconsole
Now again see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
EM Daemon is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
The status has been changed now and it says EM Daemon is not running. In order to fix it you have to start the listener. To start listener you may follow,
Right click on my computer icon> Click Manage> Select Services and Application> Select Services> On the right side From a list a Name select OracleOraDb10g_home1TNSListener> Right click on OracleOraDb10g_home1TNSListener and click start.
Or in other OS along with windows you can start with,
$lsnrctl start [Listener_name]
Now let us see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
Related Documents
Export and Import from Enterprise Manager
Login to Dbconsole, Authentication failed!null Returned
First I invoke status command to see whether my dbconcole is started or not by,
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
So it is not running. To start it on windows you can fix it by,
Right click on my computer icon> Click Manage> Select Services and Application>Select Services>On the right side From a list a Name select OracleDBConsolearju where arju is the name of database> Right click on OracleDBConsolearju and click start.
or in other OS along with windows you can start with
$emctl start dbconsole
Now again see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
EM Daemon is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
The status has been changed now and it says EM Daemon is not running. In order to fix it you have to start the listener. To start listener you may follow,
Right click on my computer icon> Click Manage> Select Services and Application> Select Services> On the right side From a list a Name select OracleOraDb10g_home1TNSListener> Right click on OracleOraDb10g_home1TNSListener and click start.
Or in other OS along with windows you can start with,
$lsnrctl start [Listener_name]
Now let us see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
Related Documents
Export and Import from Enterprise Manager
Login to Dbconsole, Authentication failed!null Returned
Type of constraint in oracle
Constraint in oracle imposes rule that restrict the values in a oracle database. There are six types of constraint in oracle database and all these constraints except not null constraint can be declared in two ways. A brief description of these six types of constraints are listed below.
1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.
2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.
4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.
5)Check Constraint: Check constraint imposes restriction of values based on specified condition.
6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.
Related Documents
How to Disable and Enable all constraints using SQL
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.
2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.
4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.
5)Check Constraint: Check constraint imposes restriction of values based on specified condition.
6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.
Related Documents
How to Disable and Enable all constraints using SQL
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
How we can minimize our database recovery time greatly
Now a days storage space is no matter in terms of data availability. Everybody always wants or expects the least downtime if any problem happens in the database. Suppose one of my datafile got corrupted. How quickly we can recover data. An efficient backup strategy should always satisfy least downtime in case of any possible problems. In this topic I will give an idea about how we can minimize our recovery time.
We know with RMAN we can take two types of backup. 1)Backupset and 2)Image copy.
By default whenever we give backup database command then RMAN create backupset. We can also take image copy of backup by giving command as BACKUP AS COPY DATABASE. If flash recovery area is enabled then those backups reside in flash recovery area and maintained there based on retention policy of RMAN setting.
Let's now think about the scenario. We have terabytes size of database and one datafile of 100G is corrupted. Now if we would took our backup as backupset then our approach would be:
1)Take the tablespace containing affected datafile offline.
2)Restore the datafile from backupset.
3)Recover the datafile.
4)Make the tablespace online.
Between these four steps step 2 consumes most of the time. If the backupset contains in slow media then restore operation can take more than a day if datafile size is near 100G.
We can minimize our recovery time from days to seconds easily. If we use image copy (bit by bit copy or OS copy of datafile) can we can acheive our goal.
To take an image copy backup of database through RMAN just issue,
RMAN>run {
backup as copy
database;
}
With this command RMAN will create the copies of the datafiles in the Flash Recovery Area with an Oracle-generated name such as o1_mf_users_2rqnthy_.dbf for users tablespace.
Suppose now our users tablespace datafile got corrupted. Then our procedure will be following which will minimize our recovery time greatly.
1)Just get a list of affected file_id by querying,
SQL>file_id from users tablespace by,
select file_id, file_name
from dba_data_files
where tablespace_name = 'USERS';
2)Connect to RMAN and take the affected tablespace offline.
$rman target /
RMAN>sql 'alter tablespace users offline';
3)Switch the affected datafile to the copy in the FRA. We get the file_id of affected datafile in step 1. Suppose we get file_id 4 is corrupted. Then our RMAN command will just point to the image copy of file_id 4 in the flash recovery area.
Which is,
RMAN> switch datafile 4 to copy;
4)Recover the datafile.
RMAN> recover datafile 4;
5)Make tablespace online.
RMAN> sql 'alter tablespace users online';
This way of recovering a datafile just saves restoring time as we did in case of backup of backupsets.
After making tablespace online if you check the location of file_id 4 you will see location is pointed to flash recovery area.
You can check it by,
SQ>select name from v$datafile where file# = 4;
Recovery area may contain slow disk storage. If you want to get back your datafile as of original location then just do the following task.
1. Make an image copy of the datafile at the original location.
RMAN> backup as copy datafile 4 format '/oradata/PROD/users01.dbf';
2. Take the tablespace offline.
RMAN> sql 'alter tablespace users offline';
3. Switch the datafile to the "copy" (Here "copy" points to the original location).
RMAN> switch datafile 4 to copy;
4. Recover the tablespace.
RMAN> recover datafile 4;
or RMAN>recover tablespace users;
5. Place the tablespace online.
RMAN> sql 'alter tablespace users online';
In above example I just shown an example of using flash recovery area and RMAN. However if you don't use RMAN and or flash recovery area then still you can achieve the same goal.
We know with RMAN we can take two types of backup. 1)Backupset and 2)Image copy.
By default whenever we give backup database command then RMAN create backupset. We can also take image copy of backup by giving command as BACKUP AS COPY DATABASE. If flash recovery area is enabled then those backups reside in flash recovery area and maintained there based on retention policy of RMAN setting.
Let's now think about the scenario. We have terabytes size of database and one datafile of 100G is corrupted. Now if we would took our backup as backupset then our approach would be:
1)Take the tablespace containing affected datafile offline.
2)Restore the datafile from backupset.
3)Recover the datafile.
4)Make the tablespace online.
Between these four steps step 2 consumes most of the time. If the backupset contains in slow media then restore operation can take more than a day if datafile size is near 100G.
We can minimize our recovery time from days to seconds easily. If we use image copy (bit by bit copy or OS copy of datafile) can we can acheive our goal.
To take an image copy backup of database through RMAN just issue,
RMAN>run {
backup as copy
database;
}
With this command RMAN will create the copies of the datafiles in the Flash Recovery Area with an Oracle-generated name such as o1_mf_users_2rqnthy_.dbf for users tablespace.
Suppose now our users tablespace datafile got corrupted. Then our procedure will be following which will minimize our recovery time greatly.
1)Just get a list of affected file_id by querying,
SQL>file_id from users tablespace by,
select file_id, file_name
from dba_data_files
where tablespace_name = 'USERS';
2)Connect to RMAN and take the affected tablespace offline.
$rman target /
RMAN>sql 'alter tablespace users offline';
3)Switch the affected datafile to the copy in the FRA. We get the file_id of affected datafile in step 1. Suppose we get file_id 4 is corrupted. Then our RMAN command will just point to the image copy of file_id 4 in the flash recovery area.
Which is,
RMAN> switch datafile 4 to copy;
4)Recover the datafile.
RMAN> recover datafile 4;
5)Make tablespace online.
RMAN> sql 'alter tablespace users online';
This way of recovering a datafile just saves restoring time as we did in case of backup of backupsets.
After making tablespace online if you check the location of file_id 4 you will see location is pointed to flash recovery area.
You can check it by,
SQ>select name from v$datafile where file# = 4;
Recovery area may contain slow disk storage. If you want to get back your datafile as of original location then just do the following task.
1. Make an image copy of the datafile at the original location.
RMAN> backup as copy datafile 4 format '/oradata/PROD/users01.dbf';
2. Take the tablespace offline.
RMAN> sql 'alter tablespace users offline';
3. Switch the datafile to the "copy" (Here "copy" points to the original location).
RMAN> switch datafile 4 to copy;
4. Recover the tablespace.
RMAN> recover datafile 4;
or RMAN>recover tablespace users;
5. Place the tablespace online.
RMAN> sql 'alter tablespace users online';
In above example I just shown an example of using flash recovery area and RMAN. However if you don't use RMAN and or flash recovery area then still you can achieve the same goal.
Monday, September 15, 2008
How to Download Patchset or Opatch from metalink
Update:
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on Patches and Updates tab
- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
To download patchset as well as logon to metalink you must need a MetaLink account. To register for MetaLink, you will need a valid Support Identifier (CSI). To have an account go to https://metalink.oracle.com/ and select Register For MetaLink under First Time Users. Then give your CSI number Support Identifier Country and click proceed. After that follow instruction as indicated.
After you have your metalink account you can downlaod patchset or Opatch from metalink. Steps are stated below.
1)Login to metalink with username and password.
2)Beside quick find drop down menu select Patch Number.
3)Beside this box there is another box where you can give the patch number if you know the patch number previous. It is an integer number. For example,
-If you try to get patchset 10.2.0.3 then give number as 5337014 and click go. In order to know the patchset number just have a look at, List of Patchset number in metalink
-New pop up windows will appear. Enter again username and password.
-Select you platform from drop down menu and click download.
-You have finished download in this stage.
4)If you don't know patch number and you wish to find it out then simply click go.
5)Patches & Updates page will appear. From it you can proceed as you like.
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on Patches and Updates tab
- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
To download patchset as well as logon to metalink you must need a MetaLink account. To register for MetaLink, you will need a valid Support Identifier (CSI). To have an account go to https://metalink.oracle.com/ and select Register For MetaLink under First Time Users. Then give your CSI number Support Identifier Country and click proceed. After that follow instruction as indicated.
After you have your metalink account you can downlaod patchset or Opatch from metalink. Steps are stated below.
1)Login to metalink with username and password.
2)Beside quick find drop down menu select Patch Number.
3)Beside this box there is another box where you can give the patch number if you know the patch number previous. It is an integer number. For example,
-If you try to get patchset 10.2.0.3 then give number as 5337014 and click go. In order to know the patchset number just have a look at, List of Patchset number in metalink
-New pop up windows will appear. Enter again username and password.
-Select you platform from drop down menu and click download.
-You have finished download in this stage.
4)If you don't know patch number and you wish to find it out then simply click go.
5)Patches & Updates page will appear. From it you can proceed as you like.
ORA-07445: exception encountered: core dump SIGSEGV
Problem Symptoms
Whenever I check my alert log file I got the text as
Errors in file /var/opt/oracle/admin/udump/orastdby_ora_31795.trc
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+38] [SIGSEGV] [Address not mapped to object] [0x0] [] []
After checking the trace file I got,
Call Stack Trace shows:
ksedst ksedmp ssexhd intel_fast_memcmp
Recent Changes of The Database
We have changes the CURSOR_SHARING parameter to SIMILAR from EXACT.
Cause of The Problem
This is oracle bug. Bug number 4456646. When the cursor sharing parameter is not set to EXACT this bug may fire. This occur while literal replacement when there are empty string literals in use. This bug happened in oracle version 10.2.0.1. In our environment it was RHL linux 32 bit production server.
Solution of The Problem
Way 1:
As a workaround disable literal replacement. You can do it by,
ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=both;
if you use spfile.
Way 2:
This bug is fixed in 10.2.0.2 patchset. So, apply 10.2.0.2 patchset in order to fix the prolem.
Way 3:
Apply One-off patch. Download one-off patch 4456646 from metalink and apply if that is avialable for your OS. For Linux x86 and linux x86-64 only available currently.
Whenever I check my alert log file I got the text as
Errors in file /var/opt/oracle/admin/udump/orastdby_ora_31795.trc
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+38] [SIGSEGV] [Address not mapped to object] [0x0] [] []
After checking the trace file I got,
Call Stack Trace shows:
ksedst ksedmp ssexhd intel_fast_memcmp
Recent Changes of The Database
We have changes the CURSOR_SHARING parameter to SIMILAR from EXACT.
Cause of The Problem
This is oracle bug. Bug number 4456646. When the cursor sharing parameter is not set to EXACT this bug may fire. This occur while literal replacement when there are empty string literals in use. This bug happened in oracle version 10.2.0.1. In our environment it was RHL linux 32 bit production server.
Solution of The Problem
Way 1:
As a workaround disable literal replacement. You can do it by,
ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=both;
if you use spfile.
Way 2:
This bug is fixed in 10.2.0.2 patchset. So, apply 10.2.0.2 patchset in order to fix the prolem.
Way 3:
Apply One-off patch. Download one-off patch 4456646 from metalink and apply if that is avialable for your OS. For Linux x86 and linux x86-64 only available currently.
Related Documents
How to skip a tablespace for restore operation
Sometimes we want to restore full database except a certain tablespace. That is we need to skip a tablespace for restore operation. May be that the tablespace only contains tempoarary data and hence not useful for us. Or we are performing some database point in time recovery operation and we want to omit a big tablespace as the tablespace does not contain data needed for our requirement. In this way we can minimize our restore operation time.
With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.
Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.
To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02,INDX01_16K;
If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.
Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,
RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;
With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.
Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.
To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02,INDX01_16K;
If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.
Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,
RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;
Related Documents
How to Discover find DBID
How to catalog and uncatalog a backup to RMAN repository
Catalog Backup
Whenever we take any backup through RMAN, in the repository information of the backup is recorded. The RMAN respository can be either controlfile or recovery catalog. However if I take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database. So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.
This task can be done by catalog command in RMAN. With catalog command it can
-Add information of backup pieces and image copies in the repository that are on disk.
-Record a datafile copy as a level 0 incremental backup in the RMAN repository.
-Record of a datafile copy that was taken by OS.
But CATALOG command has some restrictions. It can't do the following.
-Can't catalog a file that belong to different database.
-Can't catalog a backup piece that exists on an sbt device.
Examples of Catalog command
1)Catalog an archive log: To catalog two archived logs named /oracle/oradata/arju/arc001_223.arc and /oracle/oradata/arju/arc001_224.arc the command is,
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc', '/oracle/oradata/arju/arc001_224.arc';
2)Catalog a file copy as an incremental backup: To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be,
RMAN>CATALOG DATAFILECOPY '/oradata/backup/users01.dbf' LEVEL 0;
Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command, or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.
3)Catalog multiple copies in a directory: To catalog all valid backups from directory /tmp/backups issue,
RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;
4)Catalog files in the flash recovery area: To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue,
RMAN>CATALOG RECOVERY AREA NOPROMPT;
5)Catalog backup pieces: To catalog backup piece /oradata2/o4jccf4 issue,
RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';
Uncatalog Backup
In many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To uncatalog all archived logs issue,
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;
To uncataog tablespace USERS issue,
RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;
To uncatalog a backuppiece name /oradata2/oft7qq issue,
RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG;
Whenever we take any backup through RMAN, in the repository information of the backup is recorded. The RMAN respository can be either controlfile or recovery catalog. However if I take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database. So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.
This task can be done by catalog command in RMAN. With catalog command it can
-Add information of backup pieces and image copies in the repository that are on disk.
-Record a datafile copy as a level 0 incremental backup in the RMAN repository.
-Record of a datafile copy that was taken by OS.
But CATALOG command has some restrictions. It can't do the following.
-Can't catalog a file that belong to different database.
-Can't catalog a backup piece that exists on an sbt device.
Examples of Catalog command
1)Catalog an archive log: To catalog two archived logs named /oracle/oradata/arju/arc001_223.arc and /oracle/oradata/arju/arc001_224.arc the command is,
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc', '/oracle/oradata/arju/arc001_224.arc';
2)Catalog a file copy as an incremental backup: To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be,
RMAN>CATALOG DATAFILECOPY '/oradata/backup/users01.dbf' LEVEL 0;
Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command, or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.
3)Catalog multiple copies in a directory: To catalog all valid backups from directory /tmp/backups issue,
RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;
4)Catalog files in the flash recovery area: To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue,
RMAN>CATALOG RECOVERY AREA NOPROMPT;
5)Catalog backup pieces: To catalog backup piece /oradata2/o4jccf4 issue,
RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';
Uncatalog Backup
In many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To uncatalog all archived logs issue,
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;
To uncataog tablespace USERS issue,
RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;
To uncatalog a backuppiece name /oradata2/oft7qq issue,
RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG;
How to make global read only user
In many cases we need to make a read only user for the entire data dictionary and for all the tables of all schemas. Suppose in order to analysis the entire schema or to monitor performance of the database you might give only select privilege of the entire schema of a user as well as on the dictionary objects of database. But that user will not be able to delete or alter or modify anything of other schema objects. If your goal is make just read only user of a schema then have a look at
http://arjudba.blogspot.com/2008/09/create-read-only-user-for-schema.html
You can simply achive this goal by giving him three permission.
1)Create Session: With this privilege he will be able to logon in the the database.
2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.
Following is an example. User arju will be globally read only user.
SQL> create user arju identified by a;
User created.
SQL> grant create session,select any dictionary,select any table to arju;
Grant succeeded.
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
What is O7_DICTIONARY_ACCESSIBILITY?
A user can do work in his schema with only Create Session Privilege.
Create Read only user for a Schema
http://arjudba.blogspot.com/2008/09/create-read-only-user-for-schema.html
You can simply achive this goal by giving him three permission.
1)Create Session: With this privilege he will be able to logon in the the database.
2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.
Following is an example. User arju will be globally read only user.
SQL> create user arju identified by a;
User created.
SQL> grant create session,select any dictionary,select any table to arju;
Grant succeeded.
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
What is O7_DICTIONARY_ACCESSIBILITY?
A user can do work in his schema with only Create Session Privilege.
Create Read only user for a Schema
Sunday, September 14, 2008
Screen -A very useful unix tool to work with remote system
Sometimes on remote server I have to ssh and run some long running operations. Like I have to take a dump on USA server while I am in Bangladesh, or have to backup or restore operation on remote server. If disk is slow and/or database size is big then this process may take hours after hours.
During this time if my session terminates for example network down, power outage then my whole process abnormally shutdown and I have to start from first again.
Unix screen tool in this case really makes our life easy.
screen tool will permit remote monitoring of program execution progress with automatic protection against disconnection. It is a so-called terminal multiplexer, which allows us to create, shuffle, share, and suspend command line sessions within one window. It provides protection against disconnections and the flexibility
to retrieve command line sessions remotely.
You can download the screen tool from Http GNU Screen.
After downloading you can install it by,
tar -zxvf screen-4.0.2.tar.gz
cd screen-4.0.2
./configure && make
make install
Here 4.0.2 is the version number. Note that version 4.0.3 is available now.
After you download and install you can work with it.
The working steps are,
1. log in remote system where you do work usually via secure shell.
2. start screen. Just write screen on console.
3. run your long running operation here. Like take a dump of do any backup or restore operation or anything that need much times.
4. detach the screen session, using Ctrl+a or using Ctrl+d, and
5. log out.
The session continues working in the background, contained within the screen session. If we want to revisit the session to check its progress, then
1. log in that remote system via secure shell,
2. start screen -r, which in fact recalls the unattached session,
3. examine the saved buffer; scrolling around, copying and pasting as necessary,
4. detach the screen session, using Ctrl+a , Ctrl+d, and
5. log out.
Related Documents
How to change/configure IP Address on Linux/ Fedora /Ubuntu
Copy files between Unix and Windows with rcp
Different ways to take Screenshot on Linux
How to change the hostname in Linux
Memory Usage of Solaris Operating System
During this time if my session terminates for example network down, power outage then my whole process abnormally shutdown and I have to start from first again.
Unix screen tool in this case really makes our life easy.
screen tool will permit remote monitoring of program execution progress with automatic protection against disconnection. It is a so-called terminal multiplexer, which allows us to create, shuffle, share, and suspend command line sessions within one window. It provides protection against disconnections and the flexibility
to retrieve command line sessions remotely.
You can download the screen tool from Http GNU Screen.
After downloading you can install it by,
tar -zxvf screen-4.0.2.tar.gz
cd screen-4.0.2
./configure && make
make install
Here 4.0.2 is the version number. Note that version 4.0.3 is available now.
After you download and install you can work with it.
The working steps are,
1. log in remote system where you do work usually via secure shell.
2. start screen. Just write screen on console.
3. run your long running operation here. Like take a dump of do any backup or restore operation or anything that need much times.
4. detach the screen session, using Ctrl+a or using Ctrl+d, and
5. log out.
The session continues working in the background, contained within the screen session. If we want to revisit the session to check its progress, then
1. log in that remote system via secure shell,
2. start screen -r, which in fact recalls the unattached session,
3. examine the saved buffer; scrolling around, copying and pasting as necessary,
4. detach the screen session, using Ctrl+a , Ctrl+d, and
5. log out.
Related Documents
How to change/configure IP Address on Linux/ Fedora /Ubuntu
Copy files between Unix and Windows with rcp
Different ways to take Screenshot on Linux
How to change the hostname in Linux
Memory Usage of Solaris Operating System
How to move LOB data to another tablespace
We know with the ALTER TABLE .. MOVE clause we can relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment.
If you want to make no other changes to the table other than rebuilding it then your statement is simply,
SQL>ALTER TABLE table_name MOVE;
Or if you want to move it to another tablespace then specify,
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
With this statement it does not affect any of the lob segments associated with the lob columns in the table.
If you want to move only lob segment to a new tablespace then your command will be,
ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);
Along with the log segment you can also move the table as well as storage attribute of table and log by following query,
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));
If you want to move all the lobs contained in a tablespace of a particular user then you can follow .
Let's have a look lob column_name and table_name of the specified tablespace of owner ARJU.
Now execute the script move_table.scr after modifying it.
SQL>@move_table.scr
If you want to make no other changes to the table other than rebuilding it then your statement is simply,
SQL>ALTER TABLE table_name MOVE;
Or if you want to move it to another tablespace then specify,
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
With this statement it does not affect any of the lob segments associated with the lob columns in the table.
If you want to move only lob segment to a new tablespace then your command will be,
ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);
Along with the log segment you can also move the table as well as storage attribute of table and log by following query,
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));
If you want to move all the lobs contained in a tablespace of a particular user then you can follow .
Let's have a look lob column_name and table_name of the specified tablespace of owner ARJU.
SQL> col COLUMN_NAME format a20
SQL> col TABLE_NAME format a20
SQL> select owner, table_name, column_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and segment_type='LOBSEGMENT' and owner='ARJU');
OWNER TABLE_NAME COLUMN_NAME
------------------------------ -------------------- --------------------
ARJU TEST_LONG_LOB B
ARJU LOB_TAB COL2_LOB
ARJU LOB_TAB2 COL3
ARJU LOB_TAB2 COL2_LOB
set pagesize 0
set heading off
set lines 130
set feedback off
set verify off
set echo off
set termout off
spool move_table.scr
select 'alter table '||owner||'.'||table_name ||' move lob (' ||column_name||')' ||
'store as (tablespace DATA02);' from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and owner='ARJU' and segment_type='LOBSEGMENT');
spool off
Now execute the script move_table.scr after modifying it.
SQL>@move_table.scr
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML