SAP BASIS - SAP on Oracle Cheat Sheet

Checking the SID
select instance from v$thread;
select name from v$database;
select name from v$controlfile;

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/oracle/QQ1/sapdata1/system_1/system.data1'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/QQ1/sapdata1/system_1/system.data1'

control files
*.control_files='
mv /oracle/QQ1/origlogA/cntrl/cntrlQQ1.dbf
mv /oracle/QQ1/origlogB/cntrl/cntrlQQ1.dbf
mv /oracle/QQ1/sapdata1/cntrl/cntrlQQ1.dbf

cd /oracle/QQ1/origlogA/cntrl
cd /oracle/QQ1/origlogB/cntrl
cd /oracle/QQ1/sapdata1/cntrl

brconnect -u / -f chpass -o SAPSR3 -p

sqlplus "/as sysdba"

select MANDT, bname from SAPSR3.USR02 where bname like 'SAP*' and mandt ='000';
SELECT MANDT, BNAME FROM SAPSR3.USR02 WHERE MANDT='000';
delete from .usr02
where bname like 'SAP*' and mandt ='';

Moving redo log files -------------------------------
http://docs.oracle.com/html/B10506_01/ch5.htm

1. Identify the location of the current log files:
SQL> SELECT * FROM V$LOGFILE;

2. Shut down the database, make a backup copy of the redo log files in the new location,
and mount the database in exclusive mode (not opened) using these steps:

$ SQLPLUS/NOLOG
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN
SQL> EXIT

3. Copy the redo log files to the new location

4. Bring up the system in exclusive mode
$ SQLPLUS/NOLOG
SQL> CONNECT / AS SYSDBA
SQL> STARTUP EXCLUSIVE MOUNT PP1

5. From SQL*Plus, rename the files in the database using the ALTER DATABASE command. Specify the full file path.
SQL> CONNECT / AS SYSDBA
SQL> ALTER DATABASE RENAME FILE 'LOCATION1' TO 'LOCATION2';

6. Verify the new locations
SQL> SELECT * FROM V$LOGFILE; (to Verify)

7. Shut down the database and make a backup of the control files with these commands:
SQL> SHUTDOWN
SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'PP1_control.bak'
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

8. Restart the database using the following commands.
SQL> CONNECT / AS SYSDBA
SQL> STARTUP OPEN PP1
SQL> EXIT

Rename and create additional control files -------------------------------
1. SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'LOCATION1'
2. SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
3. SQL> alter system set control_file='LOCATION1','LOCATION2','LOCATION3' scope=spfile;
4. SQL> shutdown immediate;
5. Rename and then copy the control files to the new locations.
6. SQL> startup;
7. confirm with: select * from v$controlfile;
8. SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'LOCATION1'
9. SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;