DB Notes

How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g When you accidentally create a datafile in the wrong folder, you can move it to the intended location. Unlike a regular file move, this process in Oracle 11g involves both operating system commands and an update to Oracle’s control file. Follow these steps carefully. Important: Backup: Before you begin, ensure you have a current backup of your database or at least the affected tablespace. Privileges: You must have the appropriate administrative privileges to perform these operations. Environment: The steps differ slightly based on whether you’re dealing with user tablespaces or system-critical tablespaces. Step 1: Identify the Datafile Start by confirming the file name and its current location. Connect to SQL*Plus or your preferred Oracle clie...

   The query below shows what tablespaces does user have files and what are the total sizes of their objects in there:   SELECT  o.owner,     s.tablespace_name, FLOOR(SUM(s.bytes) / (1024 * 1024)) AS size_mb  FROM      dba_objects o JOIN      dba_segments s ON      o.object_name = s.segment_name     AND o.owner = s.owner     group by  o.owner,     s.tablespace_name   order by owner;

  SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='MYUSER'; ALTER USER MYUSER IDENTIFIED BY VALUES 'S:5F20FE3B04A699B37E5D68F20C597E411AF0E541A0315C7D20B3F56A37C1;T:CADDBF4C3A94AE641BE5EB56718E1F323EA39157FCBBD3511232EA54C7EBD52FCBD2061BFB6120E70A525FD08E2556ECC17878DFC581894C7169E64BF394AD82D57C2671F2C4AC5651D9AFA47DF58052;';

Steps below can be used to recover disconnected & unrecoverable standby databases. The commands below should be run on standby server.   1) Create pfile: create pfile='/tmp/pfile.ora' from spfile;   2) Drop old database. RMAN> startup mount; RMAN> sql 'alter system enable restricted session'; RMAN> drop database including backups noprompt;   3) Create spfile from pfile. sqlplus / as sysdba create spfile from pfile='/tmp/pfile.ora'; startup nomount;   4) Start database recover operation. rman target sys@primarydb auxiliary sys@stbydb (names coming from tnsnames.ora) duplicate target database for standby from active database dorecover nofilenamecheck; 5) After recovery completed, run the command below: alter database recover managed standby database disconnect from session; Note: If you get an error, shutdown the database and re-mount it. (startup mount)    6) Run the select below, if GAP_STATUS column equals to "NO GAP"  then recover the da...

Here are the steps for MSSQL Connections. Download the Compatible Oracle Gateway Version depending on your database version. Setup via GUI. Be sure that ORACLE_BASE is as same as existing database, but ORACLE_HOME should be different than database's ORACLE_HOME.      database oracle_home: /oracle/db/19/dbhome_1     gateway oracle_home: /oracle/db/19/sql_gateway Specify only for SQL Server tools as that you'll need, ignore the rest. You can enter MSSQL Database ip, servername, port and databasename. On the next step where you have to configure listener, assign different port than existing database port (for example, GATEWAY_LISTENER) After GUI Setup has been finished, copy the init file like below and edit it. For the example, QADB is our remote MSSQL database name. [root@testserver admin]# cd /oracle/db/19/sql_gateway/dg4msql/admin [root@testserver admin]#cp initdg4msql.ora initQADB.ora [root@testserver admin]# vi initQADB.ora # # HS init parameters # HS_FDS_CON...

Sometimes if base object of a synonym is dropped without dropping synonym, ORA-00980 error may occur. For the fix, you can search related SCHEMA for synonyms that doesn't have its base objects with the PL/SQL codeblock below:

 Sometimes in production servers, you might need to check specific record(s) in order get older versions. Oracle has Flashback technology to revert your datas to a specific SCN or timestamp. In order to use it, Flashback option should be enabled. After enabling, Oracle will start to save tables at specific times. select * from <table_name> as of timestamp(sysdate - interval '10' minute) The query above shows you the table's older version. You can also narrow results with WHERE clause: select * from <table_name> as of timestamp(sysdate - interval '10' minute) where proc_id=3843

Follow these steps to upgrade an Oracle Database using the OPatch tool: 1. Download and Prepare Files Download the patch you want to install along with the latest OPatch tool from Oracle's website. Transfer all downloaded files to the /tmp/ directory, and unzip them. Ensure the correct permissions and ownership are set: chown -R oracle:oinstall p26710464_122010_Linux-x86-64.zip chown -R oracle:oinstall p6880880_122010_Linux-x86-64.zip unzip p26710464_122010_Linux-x86-64.zip unzip p6880880_122010_Linux-x86-64.zip 2. Replace the OPatch Folder Replace the current OPatch folder inside the Oracle database files with the new one: cd $ORACLE_HOME cp -R OPatch OPatch_yedek # Backup current OPatch folder. rm -rf OPatch cp -R /tmp/Opatch . Verify the OPatch version and list applied patches: cd OPatch ./opatch lspatches 3. Check for Conflicts Navigate to the patch folder and run the following command to check for conflicts before starting the upgrade: cd /tmp/26710464/ $O...