10G Bugs Questions And Answers
Q.How to create tablespaces of your own ? Ans: CREATE TABLESPACE NOLOGGING DATAFILE 'path to .dbf file storage location' SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED, 'path to the 2nd .dbf file storage location' SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL; Example: CREATE TABLESPACE CONFLUENCE_DATA NOLOGGING DATAFILE 'D:\oracle\product\10.1.0\oradata\confluen ce\confluence1.dbf' SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED, 'D:\oracle\product\10.1.0\or adata\confluence\confluence2.dbf' SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED EXTENT MANAGEME NT LOCAL SEGMENT SPACE MANAGEMENT MANUAL; Q.How to create & assign a user tablespaces with unlimited option. Ans: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE CONFLUENCE_DATA; GRANT CONNECT,CREATE SESSION,RESOURCE TO ;
Example: CREATE USER CONFLUENCE IDENTIFIED BY CONFLUENCE DEFAULT TABLESPACE CONFLUENCE_DATA; GRANT CONNECT,CREATE SESSION,RESOURCE TO CONFLUENCE; Q.How to drop a user with all its data and tablespaces. Ans: - DROP USER CASCADE;
- DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Example:
DROP TABLESPACE ECP55_DATA INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; Q.How to get deleted items back.(only possible in 10G or Above Version DB's) Ans: >Show recyclebin; >Flashback table to before drop;
> Purge recyclebin;
if you want to drop the table completely, without needing a flashback feature.
Ans:
DROP TABLE RECYCLE TEST PURGE; Q.Starting and stopping DataBase.
>Sqlplus /nolog >conn / as sysdba or connect sys/ as sysdba >shutdown abort; >startup;
Q.Altering Tablespace Options
Ans: ALTER TABLESPACE tablespace { DEFAULT [ table_compression ] storage_clause | MINIMUM EXTENT integer [ K | M ] | RESIZE size_clause | COALESCE | RENAME TO new_tablespace_name | { BEGIN | END } BACKUP | datafile_tempfile_clauses | tablespace_logging_clauses | tablespace_group_clause | tablespace_state_clauses | autoextend_clause | flashback_mode_clause | tablespace_retention_clause } ;
Q. How to debug ORA-12638 Error Database credential retrieval failed. How to debug sqlnet.log error Fatal NI connect error 12638 .
Ans: Open the Sqlnet.ora file located at \NETWORK\ADMIN
change the line: SQLNET.AUTHENTICATION_SERVICES = (NTS) to SQLNET.AUTHENTICATION_SERVICES = (NONE)
Now it will not show the error. Q.How to Unlock a User with unlimited login attempts privilege.
Ans: Alter user account unlock; Alter profile default limit failed_login_attempts unlimited; Select username, account_status from dba_users where username=’’; Q.How to know the privileges a user possess.
Ans: Select * from session_privs; select * from session_roles;
Q.How to debug Listener.log file’s Warning * ping * 0 WARNING: Subscription for node down event still pending
Ans:
Add a new line to listener.ora file at /NETWORK/ADMIN
SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF
Example:
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF
Default listener name: LISTENER
Restart the listener services. Q.How to solve ORA ERROR
ORA-16014: log 3 sequence# 52 not archived, no available destinations ORA-00312: online log 3 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\S156DB\REDO03.LOG'
Ans: This problem is due to overflowing flash recovery space.So we need to alter the flash_recovery_size to suite the archival.
login as sysdba and run the following command:
conn / as sysdba; or connect sys/ as sysdba;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4294967296 SCOPE=BOTH; OR ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4294967296 SCOPE=MEMORY; & ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4294967296 SCOPE=SPFILE;
To make the database open use following
ALTER DATABASE OPEN;
OR
To avoid this ORA Error we can disable the DB_RECOVERY_FILE_DEST & To do this run the following command :
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH; OR ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=MEMORY; & ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=SPFILE;
After the database altered run ALTER DATABASE OPEN to make the DB open.
Q. How to MOUNT a DB with out opening the DB.
Connect as SYSDBA the run STARTUP MOUNT; Q. How to solve the ERROR in SQLNET.LOG file.
Fatal NI connect error 12170. VERSION INFORMATION: TNS for 64-bit Windows: Version 10.2.0.3.0 - Production Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 10.2.0.3.0 - Production Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 10.2.0.3.0 - Production Time: 30-JAN-2008 10:47:16 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out ns secondary err code: 12606 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.17.1.13)(PORT=1539))
Ans:
Following the procedures of previous two questions will also remove this error.
Q. How to solve the following Error while logging as sysdba SQL> connect system/manager@bn259db as sysdba ERROR: ORA-01031: insufficient privileges SQL> connect system/manager as sysdba ERROR: ORA-12560: TNS:protocol adapter error Ans: Check the TNS Listeners status by typing “lsnrctl status” To Stop/Start type: lsnrctl stop/start Else Restart all the Started oracle Services. Else reconfigure the local net service naming configuration
If its necessary then reconfigure the tnslistener services.
Labels: ORACLE
1 Comments & Related Links:
Post / Read Comments
Back to Home
----------------------------------------------------------------------------------------------------------------------------------
|