Question : A datafile was wrongly added on file system instead of ASM diskgroup. How to move it to the ASM disk group again? Answer : 1) Make sure the datafile to be moved is OFFLINE before proceeding : SQL> alter system switch logfile; System altered. SQL> select file_name, file_id from dba_data_files; FILE_NAME FILE_ID --------------------- /u01/oracle/oradata/test1.dbf 6 SQL> alter database datafile 6 offline; Database altered. SQL> select file_name, file_id, online_status from … [Read more...] about How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command
oracle
How to backup and delete archivelogs older than # number of days
Question : How can one achieve the following while using RMAN to backup the archived logs? Back up all archived redo logs until specified time and delete them after backing up, and also back up all the other archived redo logs that were not backed up before - all this with a single RMAN command. Solution Backup archivelogs older than 10 days and delete them First let us look at the command to backup all archived logs older than 10 days and delete them: RMAN> run { allocate … [Read more...] about How to backup and delete archivelogs older than # number of days
Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
ASMLIB is a support library for the Automatic Storage Management (ASM) feature of the Oracle Database and is available for the Linux operating system. When ASMLIB is used to manage ASM disks, the device path information is not presented in GV$ASM_DISK.PATH. This post shows how to get the physical disk device name associated with the ASMLIB disk name. Finding asmlib disk name and asm disk name By default when you scan disks with oracleasm scandisks, the corresponding disk device files (ASMLIB … [Read more...] about Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
CentOS / RHEL 7 : Oracleasm Create Disk Failed “Instantiating disk: failed”
Error: Oracleasm create disk failure on RHEL 7 with following error. # oracleasm createdisk DATA1 /dev/sde1 Writing disk header: done Instantiating disk: failed Clearing disk header: done Solution When disk is created with verbose option (-v) you would see : # oracleasm createdisk -v DATA1 /dev/sde1 Disk "DATA1" does not exist or is not instantiated Writing disk header: done Instantiating disk: oracleasm-instantiate-disk: Unable to open manager: No such file or … [Read more...] about CentOS / RHEL 7 : Oracleasm Create Disk Failed “Instantiating disk: failed”
How to move a datafile from file system to ASM
Moving a datafile from the file system can be achieved in two ways. 1. While the database is shutdown (in mount stage). 2. While the database is running (with the selected tablespace offline). While the database is shutdown (in mount stage) Moving oracle datafile while the database is in mount stage is performed in the following way: 1. Shutdown and mount the database. $ sqlplus '/as sysdba' SQL> shutdown immediate; SQL> startup mount; 2. Ensure you have enough space in the ASM … [Read more...] about How to move a datafile from file system to ASM
How To Change SYS user password for oracle database instance
If you have ever tried to change the SYS of a normal or ASM database instance Password, you would get errors as below : SQL> password Changing password for SYS Old password: New password: Retype new password: ERROR: ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], [] SQL> select INSTANCE_NAME from v$instance; INSTANCE_NAME ---------------- +ASM SQL> ALTER USER sys IDENTIFIED BY [new_password] REPLACE [old_password]; ALTER USER sys IDENTIFIED BY … [Read more...] about How To Change SYS user password for oracle database instance
How to recreate an ASM disk group
In the event you cannot mount your ASM disk groups, you will be unable to start any databases using those disk groups. Here is a possible error reported when mounting ASM disk groups: SQL> startup mount ORA-15032: not all alterations performed ORA-15063: diskgroup "[DISK GROUP NAME]"" lacks quorum of 2 PST disks; 0 found This error may occur if: a) ASM disk(s) is not visible on the operating system. b) asm_diskstring parameter is not set correctly on ASM instance(s) c) ASM metadata … [Read more...] about How to recreate an ASM disk group
CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
Enabling and Disabling Starting MySQL Server at Boot You can use the chkconfig script to verify whether MySQL has been configured to start when Linux is booted: # chkconfig --list mysql mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off The numbers 0 through 6 represents the run-levels and on/off signifies whether the service will be automatically started at that run-level. You can configure whether to start MySQL at a specific run level only using the chkconfig … [Read more...] about CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
Oracle database : Basics about pfile and spfile
What is a parameter file Parameter file is a text or binary to store the database initialization parameters. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance and many other aspects as well. Such as : memory allocation (SGA and PGA), startup of optional background processes, Setting of NLS parameters etc. There are 2 types of parameter files, namely : 1. pfile (parameter file) - older way [ not recommended by oracle … [Read more...] about Oracle database : Basics about pfile and spfile
Oracle Database : Shutdown Basics (How to Shutdown Oracle Database)
Oracle Database : Startup basics (How to start Oracle Database) Oracle Database and Instance The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. A database … [Read more...] about Oracle Database : Shutdown Basics (How to Shutdown Oracle Database)