How to shrink LDF file in SQL server 2008 R2

  In  Server Management Studio Right click on the database in question and go to Properties:

• Go to "Options" on the left
• Change the line "Recovery Model:" from Full to "Simple" and hit OK
• Right click on the database again, go to Tasks - Shrink - Files
• Under File Type: select Log and click OK

This will temporarily free up space for you, make sure that your database is set to back up and that its configured to empty the log after back up. Once you have resolved the space issue and made sure your log file is set to flush after backups, I recommend you put the recovery model back on Full. If it was already set to flush the log after backup, check to see why the backups aren't being done.

Oracle datafile recovery without RMAN

Scenario : In this scenario we will delete a datafile without taking a backup.

TABLESPACE : NON PERMANENT ( To find query select property_name,property_value from database_properties where property_name = ‘DEFAULT_PERMANENT_TABLESPACE’ )

Note: PERMANENT TABLESPACE (for e.g – USERS which is created by default when we create a database using DBCA) cannot be recovered without a backup. Deleting a permanent tablespace datafile cannot be recreated unless an older backup is restored and archives are applied.

a) Create a tablespace
b) Create users
c) create a table and insert values.

CREATE tablespace TEST datafile '/oraDevBin/oradata/test01.dbf' SIZE 100M;
CREATE USER dbasupp IDENTIFIED BY abc DEFAULT tablespace TEST;
GRANT CREATE SESSION TO dbasupp;
GRANT CONNECT,resource TO dbasupp;
CONNECT dbasupp

Enter password:

CREATE TABLE emp (2 eno NUMBER(10),3 ename varchar2(20),4 dept varchar2(10));
INSERT INTO emp 2 VALUES (1,'Nazim','IT');
commit;

DROP THE DATAFILE

$ rm -f test01.dbf

FROM SQL PROMPT TRY TO BRING THE DATAFILE IN OFFLINE STATE

ALTER DATABASE datafile 6 offline; <<< 6 IS the file_id FOR TEST datafile

ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

TRY WITH OFFLINE DROP OPTION

ALTER DATABASE datafile 6 offline DROP;

— CREATE THE DATAFILE

ALTER DATABASE CREATE datafile 6;

RECOVER DATAFILE

recover datafile 6;

BRING THE DATAFILE IN ONLINE STATE

ALTER DATABASE datafile 6 online;

GO TO THE FILE LOCATION ON THE SERVER AND CHECK IF THE DATAFILE EXISTS
CHECK FOR ANY DATA LOSS