To create control file refer this….
http://oracleinstance.blogspot.in/2010/09/re-create-lost-controlfile.html
http://oracleinstance.blogspot.in/2010/09/re-create-lost-controlfile.html
Friday 22 March 2013
Export: Oracle all commands
Export: Oracle all commands
3.EXPORT WHOLE DB
How does one use the import/export utilities?
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)
Can one export a subset of a table?
exp scott/tiger tables=emp query="where deptno=10"
exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes
Can one monitor how fast a table is imported?
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
Can one export to multiple files?
exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log
In case of low-performance system, it is better to add RECORDLENGTH parameter with tiny value to ensure that gzip has enough time to extract data before imp reads it:
imp system/pwd@sid RECORDLENGTH=4096 file=imp_pipe log=imp_pipe.log
exp file= log= full= y direct=y
./exp manjit/manjit file=/var/backup/backup.dmp compress=y log=/var/backup/backup.log statistics=none full=y direct=y ----full backup}
exp sys/sys file=full.dmp log=full.log statistics=none full=y
exp file=CP_owner1.dmp log=dec16.log full=y direct=y
imp sys/sys file=full.dmp log=full.log fromuser=schema1 touser=schema2 ignore=y
EXPORT INTO MULTIPLE FILES:
exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log
3.EXPORT WHOLE DB
How does one use the import/export utilities?
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)
Can one export a subset of a table?
exp scott/tiger tables=emp query="where deptno=10"
exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes
Can one monitor how fast a table is imported?
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
Can one export to multiple files?
exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log
In case of low-performance system, it is better to add RECORDLENGTH parameter with tiny value to ensure that gzip has enough time to extract data before imp reads it:
imp system/pwd@sid RECORDLENGTH=4096 file=imp_pipe log=imp_pipe.log
exp file= log= full= y direct=y
./exp manjit/manjit file=/var/backup/backup.dmp compress=y log=/var/backup/backup.log statistics=none full=y direct=y ----full backup}
exp sys/sys file=full.dmp log=full.log statistics=none full=y
exp file=CP_owner1.dmp log=dec16.log full=y direct=y
imp sys/sys file=full.dmp log=full.log fromuser=schema1 touser=schema2 ignore=y
EXPORT INTO MULTIPLE FILES:
exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log
TABLESPACE CREATION:
TABLESPACE CREATION:
CREATE TABLESPACE "MAN" DATAFILE'/home/oracle/oracle/product/10.2.0/db_1/oradata/test/man2.dbf'
SIZE 524288000 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "MAN" DATAFILE'/home/oracle/oracle/product/10.2.0/db_1/oradata/test/man2.dbf'
SIZE 524288000 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
DURING INSTALLATION OF ORACLE LAUNCH SQLPLUS FAILS
DURING INSTALLATION OF ORACLE LAUNCH SQLPLUS FAILS
[oracle@~]$ sqlplus
-bash: sqlplus: command not found
Solution:
Go to ORACLE_HOME (Path oracle is installed)
[oracle@~]$ cd /opt/oracle/product/11.2.0/dbhome_1/
[oracle@ dbhome_1]$ export ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
[oracle@dbhome_1]$ cd bin/
[oracle@ bin]$ ./sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 20:53:19 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
To permanent fix this add following entries into .bash_profile file at /home/oracle location.
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
[oracle@~]$ sqlplus
-bash: sqlplus: command not found
Solution:
Go to ORACLE_HOME (Path oracle is installed)
[oracle@~]$ cd /opt/oracle/product/11.2.0/dbhome_1/
[oracle@ dbhome_1]$ export ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
[oracle@dbhome_1]$ cd bin/
[oracle@ bin]$ ./sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 20:53:19 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
To permanent fix this add following entries into .bash_profile file at /home/oracle location.
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
TNS-00507: Connection closed Linux Error: 29: Illegal seek
FIX TNS:Connection closed error?
I got an email from users saying they are unable to connect to one of the DEVELOPMENT server. They are getting “NO LISTENER” message. So, its clear from this that listener could have been shutdown.
I logged in and checked the listener status using both “lsnrctl status” command and “ps -ef | grep tns” command. Both of the commands didn’t given any posivitive result.
So I started the listener with the below command and got error as this…
[oracle@dbserver1 admin]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-APR-2012 18:16:24
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /u01/ora10g/bin/tnslsnr: please wait…
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
Linux Error: 29: Illegal seek
The problem is identified as a line is missing in /etc/hosts file (mine is a Linux server). I added below line and problem solved
127.0.0.1 localhost.localdomain localhost
So, whenever you hit with the above error (Illegal Seek), please check hosts file and add the above line which will solve the issue……
I got an email from users saying they are unable to connect to one of the DEVELOPMENT server. They are getting “NO LISTENER” message. So, its clear from this that listener could have been shutdown.
I logged in and checked the listener status using both “lsnrctl status” command and “ps -ef | grep tns” command. Both of the commands didn’t given any posivitive result.
So I started the listener with the below command and got error as this…
[oracle@dbserver1 admin]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-APR-2012 18:16:24
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /u01/ora10g/bin/tnslsnr: please wait…
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
Linux Error: 29: Illegal seek
The problem is identified as a line is missing in /etc/hosts file (mine is a Linux server). I added below line and problem solved
127.0.0.1 localhost.localdomain localhost
So, whenever you hit with the above error (Illegal Seek), please check hosts file and add the above line which will solve the issue……
FIX- ORA -00600
FIX- ORA -00600
SQL> alter system set "_OPTIMIZER_PUSH_PRED_COST_BASED"=false scope=both sid='*' ;
It worked for me.
SQL> alter system set "_OPTIMIZER_PUSH_PRED_COST_BASED"=false scope=both sid='*' ;
It worked for me.
ENABLING ARCHIVE LOG AND DISABLING ARCHIVE LOG MODE
ENABLING ARCHIVE LOG AND DISABLING ARCHIVE LOG MODE
TO ENABLE ARCHIVE LOG MODE
SQL>
archive log list;
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
archive log list;
NOTE: Please perform all the actions as sys user and take a full database backup after you toggle the database to archive or noarchive log mode
How to disable archive log mode :
sql> shutdown immediate;
sql> startup mount;
sql> alter database noarchivelog;
sql> alter database open;
select log_mode from v$database;
TO ENABLE ARCHIVE LOG MODE
SQL>
archive log list;
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
archive log list;
NOTE: Please perform all the actions as sys user and take a full database backup after you toggle the database to archive or noarchive log mode
How to disable archive log mode :
sql> shutdown immediate;
sql> startup mount;
sql> alter database noarchivelog;
sql> alter database open;
select log_mode from v$database;
To list the users and the tablespace being used in database
To check the users using which tablespace in the database;
select username,default_tablespace from dba_users ;
TO list all the users and the tablespace being used in database
set pages 999 lines 100
col username format a20
col status format a8
col tablespace format a20
col temp_ts format a20
select username
, account_status status
, created
, default_tablespace tablespace
, temporary_tablespace temp_ts
from dba_users
order by username
/
select username,default_tablespace from dba_users ;
TO list all the users and the tablespace being used in database
set pages 999 lines 100
col username format a20
col status format a8
col tablespace format a20
col temp_ts format a20
select username
, account_status status
, created
, default_tablespace tablespace
, temporary_tablespace temp_ts
from dba_users
order by username
/
FIX- ora-01033
FIX-ora-01033:
“alter database open”
SQL> alter database open; alter database open * ERROR at line 1: ORA-16038: log 1 sequence# 230 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 1 thread 1: '/opt/oracle/db/home/data/imapdb/redo01.log'
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH;
rman target / nocatalog
startup mount;
delete archivelog all;
alter database open;
SQL> shutdown abort
SQL> startup
open sqlplus an logon "/ as sysdba"
then try execute shutdown abort.
Next try start database step by step:
startup nomount
alter database mount
alter database open
“alter database open”
SQL> alter database open; alter database open * ERROR at line 1: ORA-16038: log 1 sequence# 230 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 1 thread 1: '/opt/oracle/db/home/data/imapdb/redo01.log'
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH;
rman target / nocatalog
startup mount;
delete archivelog all;
alter database open;
SQL> shutdown abort
SQL> startup
open sqlplus an logon "/ as sysdba"
then try execute shutdown abort.
Next try start database step by step:
startup nomount
alter database mount
alter database open
FIX-ORA -06512
FIX-ORA -06512:
1. Login into Server Manager as INTERNAL or SYS; or, login into SQL*Plus as
SYS
2. Enter the following SQL command:
DELETE FROM SYS.EXPACT$
WHERE FUNC_PACKAGE = 'DBMS_AQ_IMPORT_INTERNAL';
After the statement issue a 'commit' so the statement can take effect.
3. Exit Server Manager or SQL*Plus.
4. Now you should be able to do a full database export.
Refrence: https://forums.oracle.com/forums/thread.jspa?messageID=2325951
1. Login into Server Manager as INTERNAL or SYS; or, login into SQL*Plus as
SYS
2. Enter the following SQL command:
DELETE FROM SYS.EXPACT$
WHERE FUNC_PACKAGE = 'DBMS_AQ_IMPORT_INTERNAL';
After the statement issue a 'commit' so the statement can take effect.
3. Exit Server Manager or SQL*Plus.
4. Now you should be able to do a full database export.
Refrence: https://forums.oracle.com/forums/thread.jspa?messageID=2325951
MULTIPLEXING CONTROLFILES
MULTIPLEXING CONTROLFILES
1. SQL> alter database backup controlfile to trace;
2.
3. Database altered.
4. SQL> show parameter user_dump_dest
5.
6. NAME TYPE VALUE
7. ------------------------------------ ----------- ------------------------------
8. user_dump_dest string /u01/app/oracle/admin/rev1/udump
9.
10. or
11.
12. SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
13. 2 AS '/home/oracle/Desktop/create_ctl.sql';
14.
15. Database altered.
1. SQL> alter database backup controlfile to trace;
2.
3. Database altered.
4. SQL> show parameter user_dump_dest
5.
6. NAME TYPE VALUE
7. ------------------------------------ ----------- ------------------------------
8. user_dump_dest string /u01/app/oracle/admin/rev1/udump
9.
10. or
11.
12. SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
13. 2 AS '/home/oracle/Desktop/create_ctl.sql';
14.
15. Database altered.
FIX- ORA-01102: cannot mount database in EXCLUSIVE mode
ORA-01102: cannot mount database in EXCLUSIVE mode
Got Error when try open database :
SQL> startup
ORACLE instance started.
Total System Global Area 267227136 bytes
Fixed Size 2212496 bytes
Variable Size 205524336 bytes
Database Buffers 54525952 bytes
Redo Buffers 4964352 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
In alert.log
sculkget: lock held by PID: 12359
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12359
ORA-1102 signalled during: ALTER DATABASE MOUNT...
On OS level :
[oracle@localhost trace]$ ps -ef | grep pmon
oracle 10222 1 0 10:27 ? 00:00:00 ora_pmon_ORCL
oracle 2522 1 0 Nov09 ? 00:00:06 ora_pmon_orcl
Use :
Kill -9 10222 2522
Then
1-After that fire export ORACLE_SID= (watch case sensitive )
2-sqlplus / as sysdba
3-startup ;
IF the problem exists then after killing the process don't set oraclesid directly give startup.
Got Error when try open database :
SQL> startup
ORACLE instance started.
Total System Global Area 267227136 bytes
Fixed Size 2212496 bytes
Variable Size 205524336 bytes
Database Buffers 54525952 bytes
Redo Buffers 4964352 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
In alert.log
sculkget: lock held by PID: 12359
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12359
ORA-1102 signalled during: ALTER DATABASE MOUNT...
On OS level :
[oracle@localhost trace]$ ps -ef | grep pmon
oracle 10222 1 0 10:27 ? 00:00:00 ora_pmon_ORCL
oracle 2522 1 0 Nov09 ? 00:00:06 ora_pmon_orcl
Use :
Kill -9 10222 2522
Then
1-After that fire export ORACLE_SID= (watch case sensitive )
2-sqlplus / as sysdba
3-startup ;
IF the problem exists then after killing the process don't set oraclesid directly give startup.
TO INSTALL WEBLOGIC SERVER 11g 0r 12c AND FORMS, REPORTS
TO INSTALL WEBLOGIC SERVER 11g 0r 12c AND FORMS, REPORTS Refer this link:
http://www.oracle-base.com/articles/11g/oracle-forms-and-reports-11gr2-installation-on-windows.php
http://www.oracle-base.com/articles/11g/oracle-weblogic-server-11gr1-1035-installation-on-windows.php
http://www.oracle-base.com/articles/11g/oracle-forms-and-reports-11gr2-installation-on-windows.php
http://www.oracle-base.com/articles/11g/oracle-weblogic-server-11gr1-1035-installation-on-windows.php
IMPORTANT ORACLE SQL COMMANDS:
IMPORTANT ORACLE SQL COMMANDS:
Server identification:
Host name where the instance is running
select host_name from v$instance;
You can also obtain this information by running the following from bash:
hostname
or
uname –n
Operating system platform
select platform_name from v$database –-(10g)
Similar information is returned if you run uname –s
File Information
Control files
select name from v$controlfile;
Location of the database control files. The init.ora parameter control_files also contains this information.
Datafiles
select file_name from Dba_data_files;
Location of the database datafiles
Temp files
select file_name from Dba_temp_files;
Location of database temporary files
Log files
select member from v$logfile;
Location of redo logs
Archived logs
select name from v$archived_log;
Location of archived redo logs. The init.ora parameters log_archive_dest_n also contain this information. This query will not return results if your database is not in Archivelog mode.
Flash recovery area
select name from v$recovery_file_dest;
The directory being used in an Oracle 10g installation for the flash recovery area. The init.ora parameter db_recovery_file_dest also contains this information.
Other points of access on the file system indicated by parameters
select * from v$parameter
where value like '%/%'
or
value like '%/%';
The results of this query can vary significantly, depending on your installation and version of Oracle Database. Parameters that may be returned include:
spfile standby_archive_dest utl_file_dir background_dump_dest user_dump_dest core_dump_dest audit_file_dest dg_broker_config_file1 dg_broker_config_file2
Programmatic access to the file system
select directory_path from dba_directories;
The Oracle UTL_FILE_DIR parameter and DIRECTORY database objects can be used to access files that are not a part of standard database functioning.
Process Information
Session Processes
select p.spid, s.username, s.program
from v$process p, v$session s
where p.addr=s.paddr order by 2, 3, 1;
The spid can be correlated with ps –ef results to compare the information available within the database with OS information for a given process.
Processes related to parallelism
select slave_name, status from v$PQ_SLAVE;
Many aspects of Oracle Database, such as loading, querying, object creation, recovery, and replication, can take advantage of parallelism to speed up activities that can be broken down. The parameter parallel_threads_per_cpu sets an instance’s default degree of parallelism.
Memory Information
Program Global Area
select * from V$PGASTAT;
The parameter pga_aggregate_target is used to configure memory for all dedicated server connections.
Linux utilities, including vmstat and top, can be used to monitor memory usage.
System Global Area
select * from v$sga;
The parameters SGA_MAX_SIZE and SGA_TARGET are used to configure dynamic memory allocation features of Oracle Database 10 g. Other parameters can be used to manually allocate memory for particular purposes.
Again, various Linux utilities are available to monitor memory allocation.
Server identification:
Host name where the instance is running
select host_name from v$instance;
You can also obtain this information by running the following from bash:
hostname
or
uname –n
Operating system platform
select platform_name from v$database –-(10g)
Similar information is returned if you run uname –s
File Information
Control files
select name from v$controlfile;
Location of the database control files. The init.ora parameter control_files also contains this information.
Datafiles
select file_name from Dba_data_files;
Location of the database datafiles
Temp files
select file_name from Dba_temp_files;
Location of database temporary files
Log files
select member from v$logfile;
Location of redo logs
Archived logs
select name from v$archived_log;
Location of archived redo logs. The init.ora parameters log_archive_dest_n also contain this information. This query will not return results if your database is not in Archivelog mode.
Flash recovery area
select name from v$recovery_file_dest;
The directory being used in an Oracle 10g installation for the flash recovery area. The init.ora parameter db_recovery_file_dest also contains this information.
Other points of access on the file system indicated by parameters
select * from v$parameter
where value like '%/%'
or
value like '%/%';
The results of this query can vary significantly, depending on your installation and version of Oracle Database. Parameters that may be returned include:
spfile standby_archive_dest utl_file_dir background_dump_dest user_dump_dest core_dump_dest audit_file_dest dg_broker_config_file1 dg_broker_config_file2
Programmatic access to the file system
select directory_path from dba_directories;
The Oracle UTL_FILE_DIR parameter and DIRECTORY database objects can be used to access files that are not a part of standard database functioning.
Process Information
Session Processes
select p.spid, s.username, s.program
from v$process p, v$session s
where p.addr=s.paddr order by 2, 3, 1;
The spid can be correlated with ps –ef results to compare the information available within the database with OS information for a given process.
Processes related to parallelism
select slave_name, status from v$PQ_SLAVE;
Many aspects of Oracle Database, such as loading, querying, object creation, recovery, and replication, can take advantage of parallelism to speed up activities that can be broken down. The parameter parallel_threads_per_cpu sets an instance’s default degree of parallelism.
Memory Information
Program Global Area
select * from V$PGASTAT;
The parameter pga_aggregate_target is used to configure memory for all dedicated server connections.
Linux utilities, including vmstat and top, can be used to monitor memory usage.
System Global Area
select * from v$sga;
The parameters SGA_MAX_SIZE and SGA_TARGET are used to configure dynamic memory allocation features of Oracle Database 10 g. Other parameters can be used to manually allocate memory for particular purposes.
Again, various Linux utilities are available to monitor memory allocation.
TABLESPACE POINT IN TIME RECOVERY:
POINT IN TIME RECOVERY:
RMAN> recover tablespace TEST1,TEST2 until time "to_date('07/06/12 12:15:00','DD/MM/YY HH24:MI:SS')"
auxiliary destination '/aux/dest/oracle/atp/resto';
recover tablespace our_data until time
"to_date('2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS')"
auxiliary destination ’/opt/oracle/temp’;
Once the above statement is executed, RMAN does the following for us:
• Creates auxiliary instance (including the pfile etc..)
• Mounts the auxiliary instance
• Makes the candidate tablespace into OFFLINE
• Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
• Applies archives (completes recovery)
• Opens the database
• Performs an export of the objects residing in the tablespace
• Shutdown aux instance
• Import the objects into the target database
• Remove aux instance and cleanup
That's it....you now have all the objects back in the tablespace!
Refrence: http://www.online-database.eu/index.php/recovery-manager-rman/186-tablespace-point-in-time-recovery-tspitr
RMAN> recover tablespace TEST1,TEST2 until time "to_date('07/06/12 12:15:00','DD/MM/YY HH24:MI:SS')"
auxiliary destination '/aux/dest/oracle/atp/resto';
recover tablespace our_data until time
"to_date('2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS')"
auxiliary destination ’/opt/oracle/temp’;
Once the above statement is executed, RMAN does the following for us:
• Creates auxiliary instance (including the pfile etc..)
• Mounts the auxiliary instance
• Makes the candidate tablespace into OFFLINE
• Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
• Applies archives (completes recovery)
• Opens the database
• Performs an export of the objects residing in the tablespace
• Shutdown aux instance
• Import the objects into the target database
• Remove aux instance and cleanup
That's it....you now have all the objects back in the tablespace!
Refrence: http://www.online-database.eu/index.php/recovery-manager-rman/186-tablespace-point-in-time-recovery-tspitr
Thursday 21 March 2013
RECYCLE BIN IN ORACLE:
RECYCLE BIN IN ORACLE:
SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
Value
-----
On
SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;
SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;
Refrence:
http://www.orafaq.com/node/2397
SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
Value
-----
On
SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;
SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;
Refrence:
http://www.orafaq.com/node/2397
DATABASE HEALTH CHECK STEPS
DATABASE HEALTH CHECK STEPS:
• Oracle memory management analysis.
• Disk I/O Examination.
• CPU utilization bottleneck.
• High resource consumption.
• Latch contention.
• Potential Locking issues.
• Oracle memory management analysis.
• Disk I/O Examination.
• CPU utilization bottleneck.
• High resource consumption.
• Latch contention.
• Potential Locking issues.
FIX- ORA-00845: MEMORY_TARGET not supported on this system:
FIX- ORA-00845: MEMORY_TARGET not supported on this system:
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 506M 47G 2% /
tmpfs 7.8G 3.8G 4.1G 49% /dev/shm
/dev/sda1 485M 58M 402M 13% /boot
[oracle@exilant ~]$ su root
Password:
[root@exilant oracle]# mount -t tmpfs shmfs -o size=12g /dev/shm
[root@exilant oracle]# vi /etc/fstab add this line
shmfs /dev/shm tmpfs size=12g 0 0
root@exilant oracle]# su oracle
[oracle@exilant ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Follow this link for the refrence:
http://www.krenger.ch/blog/ora-00845-memory_target-not-supported-on-this-system/
http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 506M 47G 2% /
tmpfs 7.8G 3.8G 4.1G 49% /dev/shm
/dev/sda1 485M 58M 402M 13% /boot
[oracle@exilant ~]$ su root
Password:
[root@exilant oracle]# mount -t tmpfs shmfs -o size=12g /dev/shm
[root@exilant oracle]# vi /etc/fstab add this line
shmfs /dev/shm tmpfs size=12g 0 0
root@exilant oracle]# su oracle
[oracle@exilant ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Follow this link for the refrence:
http://www.krenger.ch/blog/ora-00845-memory_target-not-supported-on-this-system/
http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php
FIX- ORA-12505
FIX- ORA-12505
Solution:
Had a service name “ORCL” ,When I tried connect to that service it always gave ORA-12505, TNS:listener does not currently know of SID given in connect …
So I edited the listener.ora, added one more SID_DESC, restarted the listener. Now I can connect to the “ORCL” database. Below is the updated listener.ora
Before my listener file was
# listener.ora Network Configuration File:/oracle/data/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.11.50)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /opt/app/oracle
Solution
Now my listener file looks like :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/data/product)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/data/product)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.11.50)(PORT = 1521))
)
)
)
Restarted the listener & error got resolved.
refrence:
http://samadhandba.wordpress.com/2011/06/03/ora-12505-connection-refused-the-specified-sid/
Subscribe to:
Posts (Atom)