All posts by mpbod

Database

Size Oracle Database for Data File Type

Size Oracle Database for Data File Type

 

To select the size of Oracle database by type of data file, simply turn the SQL below:

on September serveroutput;
declare
dbf number;
tmpdbf number;
lgf number;
ctl number;
sum number;
begin
select trunc (sum (bytes / 1024/1024), 2) into DBF from v $ datafile;
select trunc (sum (bytes / 1024/1024), 2) into tmpdbf from v $ tempfile;
select trunc (sum (bytes / 1024/1024), 2) into lgf from v $ log l, v $ logfile lf where l.group # = # lf.group;
select trunc (sum (block_size * file_size_blks / 1024/1024), 2) into ctl from v $ controlfile;
select trunc ((DBF tmpdbf + + + lgf ctl) / 1024.2) into sum from dual;
DBMS_OUTPUT.PUT_LINE (chr (10));
DBMS_OUTPUT.PUT_LINE (‘Datafiles:’ dbf || || ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘tempfiles:’ || || tmpdbf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Logfiles:’ || || lgf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Controlfiles:’ || || ctl ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Total Size’ || || sum ‘GB’);
end;

 

Back to previous menu

Database

Manage Oracle JOB

Manage Oracle JOB

 

Create a JOB to run a certain procedure once a day for example, run the following script in SQLPLUS.

jobno variable number;
instno variable number;
begin
dbms_job.submit (: jobno,
‘BEGIN Nome_da_Procedure; END; ‘
, trunc (sysdate) +3/24,
‘SYSDATE + 1,
TRUE
);
END;
/

To check the scheduled jobs.

SELECT * FROM dba_jobs;

To check if it is running.

select * from dba_jobs_running;

To delete jobs.

DELETE FROM WHERE dba_jobs job = 99;

To check when was the last execution.

select JOB, LAST_DATE, LAST_SEC, NEXT_DATE, NEXT_SEC, INTERVAL, FAILURES from dba_jobs;

 

Back to previous menu

Database

Change Number of Oracle Cursors

Change Number of Oracle Cursors

 

Connect to the Oracle Database with SYS user through SQLPLUS

SQL> show parameter open_cursors;

NAME TYPE VALUE
———— —- ——–
open_cursors integer 300

SQL> alter system set open_cursors = 1000 scope = both;

Changed system.

SQL> alter system set open_cursors = 1000 scope = spfile;

Changed system.

SQL> show parameter open_cursors;

NAME TYPE VALUE
———— —- ——–
open_cursors integer 1000

 

Back to previous menu

Database

Create Oracle DBLINK

Create Oracle DBLINK

 

Connect to the Oracle Database with SYS user through SQLPLUS

SQL> CREATE PUBLIC DATABASE LINK ‘Name_DBLINK’ CONNECT TO ‘User’ IDENTIFIED BY ‘password’ USING ‘Name_used_in_TNSNAMES_for_conection’;

 

Back to previous menu

Database

ORACLE ENTERPRISE MANAGER not start or not connect

ORACLE ENTERPRISE MANAGER not start or not connect

 

An alternative is to drop and recreate the Oracle Enterprise Manager repository. In the operating system prompt proceed as follows:

Drop Enterprise Manager

$ EMCA -deconfig dbcontrol db -repos drop

Create Enterprise Manager

$ EMCA -config dbcontrol db create -repos

 

Note: Insert the information that is requested. User, SID, etc…

 

Back to previous menu

Database

PERFORMANCE ABA ENABLE IN ORACLE ENTERPRISE MANAGER

PERFORMANCE ABA ENABLE IN ORACLE ENTERPRISE MANAGER

 

Connect to the Oracle database with SYS user through SQLPLUS

SQL> show parameter control_management_pack_access;

NAME TYPE VALUE
———— —- ———-
control_management_pack_access string NONE

SQL> alter system set control_management_pack_access = ‘DIAGNOSTIC + TUNING’;

Changed system.

SQL> show parameter control_management_pack_access;

NAME TYPE VALUE
———— —- ———-
control_management_pack_access string DIAGNOSTIC + TUNING

 

Back to previous menu

Database

Oracle SPFILE and PFILE Manage

Oracle SPFILE and PFILE Manage

 

Oracle Startup Files

Following are the oracle boot files and the correct way to create them.

The boot process of a database is done from reading the boot file.

There are two types of files: the PFILE is a text file known by the init.ora and the SPFILE that is a server parameter file known as spfile.ora.

The database instance first looks for the SPFILE and not find seeks PFILE. The SPFILE file is a binary version of the PFILE. The biggest advantage of using the SPFILE is the optimization of the base, as there are several parameters that are changed and become persistent and will be reused on the next reboot of the base.

You can check if there is a SPFILE with the following command:

SQL> show parameter spfile;

To create the SPFILE from the PFILE can be used the following commands:

SQL> create SPFILE from PFILE;

SQL> create SPFILE from PFILE = ‘/app/oracle/product/10g/dbs/init.ora’;

SQL> create SPFILE = ‘/app/oracle/product/10g/dbs/spfile.ora’ FROM PFILE = ‘/opt/oracle/product/10g/dbs/init.ora’;

Then you must close the database and restart it. During the restart process is automatically used SPFILE created above.

SQL> shutdown immediate;

SQL> startup;

If you need to start with the PFILE can use the following commands:

SQL> startup open pfile = ‘/app/oracle/product/10g/dbs/init.ora’;

SQL> startup open pfile = ‘/u01/app/oracle/admin/lab001/pfile/init.ora.5102011152549’;

SQL> startup open pfile = ‘/u01/home/oracle/product/11.2.0/db_1/dbs/spfilelab001.ora’;

To ensure better optimization of the database is better than the bank starts using the SPFILE. An important observation is NEVER manually editing the spfile.ora file. Otherwise the bank may not switch to the open state. The parameters contained in the file should be changed by the management software.

 

Back to previous menu

Database

ORACLE 11G Expired Password

ORACLE 11G Expired Password

 

In Oracle 11G R2 the PASSWORD_LIFE_TIME option in defaul profile, which is the lifetime of the option password is usually defined as 90 days.
To disable users of password expiration option (default in Oracle 11G R2), just change the default profile as follows:

solution:

– Connect to the database server (open the sqlplus prompt):

$ sqlplus / nolog

– Log in as the SYS SYSDBA:

SQL> conn / as sysdba

– Run the command:

SQL> Alter profile default limit PASSWORD_LIFE_TIME unlimited;

 

Back to previous menu

Database

Unlock Oracle User

Unlock Oracle User

 

To unlock an Oracle User:

 

ALTER USER name_user ACCOUNT UNLOCK;

 

Back to previous menu

Database

Oracle Undo Tablespace Corrupted

Oracle Undo Tablespace Corrupted

Steps to recreate corrupt UNDO tablespace

SQL> select segment_name, status from dba_rollback_segs where tablespace_name = ‘UNDOTBS1’;
SEGMENT_NAME STATUS
———- ——-
_SYSSMU1 $ NEED RECOVERY

Solution:

Create a new Undo tablespace:
SQL> Create undo tablespace UNDOTBS datafile ‘/app/oracle/oradata/UNDOTBS.dbf’ size 512m reuse autoextend on;;

Change the undo tablespace standard seat:
SQL> Alter system set undo_tablespace = ‘UNDOTBS’ scope = spfile;

Set offline rollback segment corrupt:
SQL> Alter system set “_offline_rollback_segments” = “_ $ SYSSMU1” scope = spfile;

Restart the database and delete the corrupted segment:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> Startup
startup ORACLE instance started.
Total System Global Area 353863224 bytes
Fixed Size 742968 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.

Drop rollback segment corrupted
SQL> Drop rollback segment “_SYSSMU1” $;

Delete the corrupted undo:
SQL> Drop table space UNDOTBS1 including contents and datafiles;

Ready, made this the database is available again for DML.

NOARCHIVELOG

If your database is in noarchivelog mode than better to open SR with Oracle, if it is test database than you can proced with below steps
If database database oradata folder size is less or manageable to take its backup while database is shutdown.
Once you took backup of oradata, now you proced for recovery

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status not in (‘ONLINE’, ‘OFFLINE’);

SEGMENT_NAME STATUS TABLESPACE_NAME
—————– ———– —————-
_SYSSMU1_2943671021$ NEEDS RECOVERY UNDO01
_SYSSMU2_4044142269$ NEEDS RECOVERY UNDO01
_SYSSMU3_2957612445$ NEEDS RECOVERY UNDO01

Note: Add the following lines to pfile.

*.DB_BLOCK_CHECKSUM = FALSE
*._DB_ALWAYS_CHECK_SYSTEM_TS = FALSE
*.event=”10231 trace name context forever, level 10″
*.event=”10233 trace name context forever, level 10″
*._allow_resetlogs_corruption= true
_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU1_2943671021$’)
_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU2_4044142269$’)
_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU3_2957612445$’)
_offline_rollback_segments=(_SYSSMU1_2943671021$)
_offline_rollback_segments=(_SYSSMU2_4044142269$)
_offline_rollback_segments=(_SYSSMU3_2957612445$)

Stop Database
SQL> SHUTDOWN IMMEDIATE;

Start Database with restrict session using PFILE
SQL> STARTUP RESTRICT PFILE=/oracle/app/dbs/init.ora;

Create a new undo tablespace UNDOTBS2
SQL> Create undo tablespace UNDOTBS2 datafile ‘/u01/oradata/unodtbs02.dbf’ size 1500M REUSE AUTOEXTEND ON;

Make entry in the pfile. Modify the following parameters in pfile and restart the database.
Undo_management=auto
Undo_tablespace=’UNDOTBS2′

Make entry in the spfile. Modify the following parameters in spfile and restart the database.
SQL> Alter system set undo_tablespace = ‘UNDOTBS2’ scope = spfile;

SQL> drop rollback segment “_SYSSMU1_2943671021$”;
SQL> drop rollback segment “_SYSSMU2_4044142269$”;
SQL> drop rollback segment “_SYSSMU3_2957612445$”;
Rollback segment dropped.
Rollback segment dropped.
Rollback segment dropped.

Drop Corrupted Tablespace
SQL> drop tablespace undotbs1;
Tablespace dropped.

SQL> select name from v$tablespace;

NAME
——————————
SYSTEM
UNDOTBS2
SYSAUX
USERS
EXAMPLE
TEMP

6 rows selected.

Now the corrupted UNDO tablespace is droped.

SQL> select * from v$rollname;

Back to previous menu