Skip to content
Advertisements

Archive for

could not validate asmsnmp password due to following error ora-01031:insufficent privileges

During Create new database, It shows following errors

 

“could not validate asmsnmp password due to following error ora-01031:insufficent privileges”

 

 

Solution :
[oracle@mazar ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@mazar ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@mazar ~]$ export ORACLE_SID=+ASM
[oracle@mazar ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 3 20:54:14 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys/Admin543 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
SQL>  create user asmsnmp identified by Admin321;

User created.

SQL> grant sysdba to asmsnmp;

Grant succeeded.
Advertisements

can we recover dropped table from recyclebin if flashback mode off

yes ,We can recover dropped table from recyclebin even flashback database mode is disabled.

What is Recycle Bin?

recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

  • When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
  • When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
  • When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      OFF
SQL> alter system set recyclebin=ON scope=spfile;

System altered.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      OFF
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      ON
SQL> conn testuser/testuser;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ABC                            TABLE
ABCD                           TABLE
XXX                            TABLE

SQL> create table test(empno number);

Table created.

SQL> insert into test values(001);

1 row created.

SQL> insert into test values(002);

1 row created.

SQL> insert into test values(003);

1 row created.

SQL> insert into test values(004);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

     EMPNO
----------
         1
         2
         3
         4

SQL> drop table test;

Table dropped.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set lines 500
SQL> SELECT object_name,original_name,droptime,dropscn from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME               DROPSCN
------------------------------ -------------------------------- ---------
BIN$nkFizCIuTY23wgOmv1B1vA==$0 TEST                             2011-07-03:13:27:35    8811423

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

     EMPNO
----------
         1
         2
         3
         4

SQL> SELECT object_name,original_name,droptime,dropscn from recyclebin;

no rows selected

SQL>

Ref : http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables011.htm#i1011362