Tagged: Oracle database

Oracle Database

ORA – 00257 archiver error

When we see this error, the first thing is to control the flash recovery area In the event that indeed the FRA was full, you can delete old archive logs from the filesystem then on rman use the following run { crosscheck archivelog all; delete noprompt expired archivelog all; } The CROSSCHECK command is used to determine whether backups recorded in the repository still exist on disk or tape, if RMAN cannot locate the backups, then it updates their records in the RMAN repository to EXPIRED status. You can then use the DELETE EXPIRED command to remove records of expired...

Oracle Database

What is the Recycle Bin?

The recycle bin is 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. Enabling and Disabling the Recycle Bin ALTER SESSION SET recyclebin = OFF; ALTER SYSTEM SET recyclebin = OFF; ALTER SESSION SET recyclebin = ON; ALTER SYSTEM SET recyclebin = ON; An example of recovery: drop table SALARY2016; select object_name,original_name,type,can_undrop as “undo”,can_purge as “PUR”, droptime from dba_recyclebin where original_name like SALARY%’ order by droptime; OBJECT_NAME ORIGINAL_NAME TYPE undo PUR DROPTIME BIN$JZgAyoU+u1DgUxQVCApelQ==$0 SALARY2016 TABLE YES YES 2016-08-04:13:39:27...

Oracle Database

ORACLE : Quiescing database

Schema changes, reorganizations or other administrative tasks are difficult to make while users area conducting live transactions in the database. You have to perform these activity during a schedulated maintenance or you have to shut down the database and reopen in “restricted mode”. For obviate  this problem , “quescing database” gives the possibility to put the database in single mode without having to ever shutdown the database. When a database is in “quiesced state” the following conditions apply:   All inactive sessions are prevented from issuing any database commands until the database in in “unquiesced state”. All active sessions are...

Oracle Database

Move listener log to another filesystem

Run lsnrctl command to change listener’s settings: [db000oracle@]/app/oracle/listener/log > lsnrctl Set the listener interested: LSNRCTL> set current_listener LSNR_[db_name] Current Listener is LSNR_[db_name] LSNRCTL> show log_status Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=BSCS)) LSNR_[db_name] parameter “log_status” set to ON The command completed successfully Change log_status to off so you can move the associated log: LSNRCTL> set log_status OFF Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=BSCS)) listener_[db_name] parameter “log_status” set to OFF The command completed successfully At this point, you may rename the file and move it to the new directory mv listener_[db_name].log listener_[db_name].log_[date] mv listener_[db_name].log_[date] /LSNR_LOG Recreate the file you just renamed: touch listener_[db_name].log By now is possible to...

Cloud / Oracle Database

Oracle Public Cloud Services. Security (part 2 of 3)

In my preceding article I tried to explain how companies and Oracle partners can take advantage of the Oracle Cloud and its different and powerful services. Have I missed something? Yes, SECURITY. All of the services belonging to the Oracle Cloud are offered as public (i.e. shared by multiple companies) or private (i.e. used by a single, large company). A public cloud is inherently unsafe, you know, but even a private one could open security holes if badly managed. Luckily, security is one of the major strengths of the Oracle Cloud proposal. First of all, any service must be accessed...

Oracle Database

Restore Ocr and voting file

This article explains how to restore the OCR and Voting disk when they are lost 11gR2 – Restore OCR/ VOTEDISK based on ASM, when loss of CRS Diskgroup. Labels: 11GR2, loss of CRS Diskgroup, lost OCR on ASM, RAC, RESTORE OCR, RESTORE OCR ON ASM, RESTORE VOTEDISK 1. Locate the latest automatic OCR backup When using a non-shared CRS home, automatic OCR backups can be located on any node of the cluster, consequently all nodes need to be checked for the most recent backup: $ ls -lrt $CRS_HOME/cdata/rac_cluster1/ -rw——- 1 root root 7331840 Mar 10 18:52 week.ocr -rw——- 1 root...

Oracle Database

Guaranteed restore point

In front of any activity on the database ( example UPGRADE / application changes ) over the RMAN backup , in addition to exports and other types of rescue, a fast method (especially then in case of rollback ) to have a consistency point is to create a GUARANTEED RESTORE POINT for a possible rollback database. Requirements : COMPATIBLE parameter must be set to 10.2 or higher in the initialization parameter file of the database. the database must be active in ARCHIVELOG mode . The operation used FLASHBACK DATABASE to bring the database to a guaranteed restore point requires the...

Oracle Database

Oracle PL/SQL: implicit vs explicit cursors

PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an “implicit” cursor because you do not explicitly declare a cursor for the SQL statement. If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside of your programmatic control. You can, however, obtain information about the most recently executed SQL statement by examining the values in the implicit SQL cursor attributes, as explained later in this chapter. Implicit cursor is simpler...

Oracle Database 12 c

Oracle 12c: Move datafile feature

Until the release 11g move a datafile was an operation very problematic; We had to put the tablespace offline, copy the datafile to the new destination and rename it : SQL> alter database rename datafile “A” to “B”; From Oracle release Oracle 12c, you can move a datafile from a mount point to another without having to put the tablespace offline . All this is done while the database is active. So with the command: alter database     move datafile     ‘/u01/app/oracle/oradata/FILE.dbf’  to     ‘/u02/app/oracle/oradata/ORADB12/FILE.dbf’; It is also possible to move a datafile from specific number that Oracle has assigned...

Oracle Database

How to move controlfile from one diskgroup to another

dbbox2>show parameter control NAME TYPE VALUE ———————————— ———– —————————— control_file_record_keep_time integer 7 control_files string +DBBOX_DATA_DG/dbbox/contr ol01.ctl, +DBOLD_FRA_DG/dbold /control02.ctl alter system set control_files=’+DBBOX_DATA_DG/dbbox/control01.ctl’,’ +DBBOX_FRA_DG/dbbox/control02.ctl’ scope=spfile sid=’*’; oracle11@vboxdb:dbbox1 # srvctl stop database -d dbbox -o immediate ASMCMD> cp +DBBOX_DATA_DG/dbbox/control01.ctl +DBBOX_FRA_DG/dbbox/control02.ctl copying +DBBOX_DATA_DG/dbbox/control01.ctl -> +DBBOX_FRA_DG/dbbox/control02.ctl ASMCMD> ls -lrt DBBOX_FRA_DG/dbbox/control02.ctl WARNING:option ‘r’ is deprecated for ‘ls’ please use ‘reverse’ Type Redund Striped Time Sys Name N control02.ctl => +DBBOX_FRA_DG/dbbox/CONTROLFILE/control02.ctl.713.881859415 oracle11@vboxdb:dbbox1 # srvctl start database -d dbbox oracle11@vboxdb:dbbox1 # sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 8 16:58:12 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter user-name: /as sysdba Connected to: Oracle...